This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Tech Community.
There is a known issue with the krb5 library that is shipped on RHEL(7.3, 7.4, 7.5, or 7.6) and Ubuntu(16.04) where AES keys are incorrectly salted on Windows AD systems. There has been a patch in the krb5 library, but that patched version hasn’t shipped on the version Microsoft supports.
The incorrect salted value(created by addent command) may cause Windows authentication fail.
I'm going to list the updated process(We are going to update the official document soon.)
1.Create AD user (or MSA) for SQL Server and set SPN
1)AD User.
On your domain controller, run the New-ADUser PowerShell command to create a new AD user with a password that never expires. The following example names the account mssql, but the account name can be anything you like. You will be prompted to enter a new password for the account.
Import-Module ActiveDirectory
New-ADUser mssql -AccountPassword (Read-Host -AsSecureString "Enter Password") -PasswordNeverExpires $true -Enabled
2)Managed Service account
On your domain controller, run the New-ADServiceAccount PowerShell command to create a Managed Service Account with a password that never expires. The following example names the account mssql, but the account name can be anything you like. You will be prompted to enter a new password for the account.
Import-Module ActiveDirectory
New-ADServiceAccount -Name mssql -Enabled $true -AccountPassword (Read-Host -AsSecureString "Enter Password") -RestrictToSingleComputer
2.Get the KVNO of the account.
1)Run following powershell command for AD User
Import-Module ActiveDirectory
get-aduser userName -property msDs-KeyVersionNumber
2)Run following powershell command for MSA account
Import-Module ActiveDirectory
get-ADServiceAccount -Identity MsaName -property msDS-KeyVersionNumber
3.Prepare mssql.keytab (this step is applicable to both AD user and MSA)
(KVNO should be replaced with the value in step 2)
ktpass /princ MSSQLSvc/**<fully qualified domain name of host machine>**:**<tcp port>**@**<Full qualified domain name>** /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser domainName\accountName /out mssql.keytab -setpass -setupn /kvno **<kvno from above>** /pass <StrongPassword>
ktpass /princ MSSQLSvc/**<fully qualified domain name of host machine>**:**<tcp port>**@**<Full qualified domain name>** /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser domainName\accountName /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno **<kvno from above>** /pass <StrongPassword>
ktpass /princ MSSQLSvc/**<netbios name of the host machine>**:**<tcp port>**@**<Full qualified domain name>** /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser domainName\accountName /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno **<kvno from above>** /pass <StrongPassword>
ktpass /princ MSSQLSvc/**<netbios name of the host machine>**:**<tcp port>**@**<Full qualified domain name>** /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser domainName\accountName /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno **<kvno from above>** /pass <StrongPassword>
Please note,This step will create two SPNs as well if they don’t exist. So you don’t need to run setspn anymore.
For MSA account only, add following MSA UPN entries in the keytab file using ktpass.
ktpass /princ msaAccount@<Full qualified domain name> /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser domainName\accountName /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno **<kvno from above>** /pass <StrongPassword>
ktpass /princ msaAccount@<Full qualified domain name> /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser domainName\accountName /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno **<kvno from above>** /pass <StrongPassword>
Here is an example
Env
===
SQL Linux box:red1
port:1433
FQDN:red1.CONTOSO.COM
Domain:CONTOSO.COM
Account Name:contoso\sqlcss
KVNO:2
ktpass /princ MSSQLSvc/red1.CONTOSO.COM:1433@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser contoso\sqlcss /out mssql.keytab -setpass -setupn /kvno 2 /pass Password1
ktpass /princ MSSQLSvc/red1.CONTOSO.COM:1433@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser contoso\sqlcss /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass Password1
ktpass /princ MSSQLSvc/red1:1433@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser contoso\sqlcss /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass Password1
ktpass /princ MSSQLSvc/red1:1433@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser contoso\sqlcss /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass Password1
If this is a MSA account, additional steps are needed:
ktpass /princ sqlcss@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser contoso\msa01 /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass Password1
ktpass /princ sqlcss@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser contoso\msa01 /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass Password1
- Copy over the keytab file to Linux server, place it in /var/opt/mssql/secrets folder as mssql.keytab.
The next step is almost same to https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-ver15 , with little change.
Option 1: Using UPN to configure the keytab(No change here)
Add the machine account to your keytab with ktutil. The machine account (also called a UPN) is present in /etc/krb5.keytab in the form <hostname>$@<realm.com> (for example, sqlhost$@CONTOSO.COM). Copy these entries from /etc/krb5.keytab to mssql.keytab.
- Start ktuil with the following command:
sudo ktutil - Use the rkt command to read all of the entries from /etc/krb5.keytab.
rkt /etc/krb5.keytab - Next, list out the entries.
list - Delete all the entries by their slot number that are not the UPN. Do this one at a time by repeating the following command:
delent <slot num>
Important
When an entry is deleted, such as slot 1, all values slide up by one to take its place. This means the entry in slot 2 moves to slot 1 when slot 1's entry is deleted. - List out the entries again until only UPN entries are left.
list - When only UPN entries are left, append these values to mssql.keytab:
wkt /var/opt/mssql/secrets/mssql.keytab - Quit ktutil.
quit
Option 2: Using MSA to configure the keytab
The original step1~4 are not needed, just the step 5
5.When using the MSA approach, a configuration option needs to be set with the mssql-conf tool to specify the MSA to be used while accessing the keytab file. Ensure the values below are in /var/opt/mssql/mssql.conf.
sudo mssql-conf set network.privilegedadaccount <MSA_Name>
Note
Only include the MSA name and not the domain\account name.
The rest of steps are exactly same as the steps in https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication?view=sql-server-ver15