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.
Here is a recommended way to create mssql.keytab in case you running into the issue.
Prerequisites
===
The SQL Server Linux serve has joined domain
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.Run following command in Windows server in the domain using Domain administrator to create the mssql.keytab (this step is applicable to both AD user and MSA)
(KVNO should be replaced with the value in step 2, <StrongPassword> should be replaced with the password specified in step1)
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> ktpass /princ accountName@**<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 accountName@**<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>
4.Copy over the file mssql.keytab to /var/opt/mssql/secrets in the SQL Server Linux server.
5.Use the mssql-conf tool to specify the account to be used while accessing the keytab file.
sudo /opt/mssql/bin/mssql-conf set network.privilegedadaccount accountName
Note
Only include the account name. Domain name is not necessary. and not the domain\account name.
5.Grant the permission.
sudo chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab sudo chmod 400 /var/opt/mssql/secrets/mssql.keytab sudo mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab sudo systemctl restart mssql-server
Here is an example
Here is an example of ktpass for an account
Env
===
SQL Linux box:red1
port:1433
FQDN:red1.CONTOSO.COM
Domain:CONTOSO.COM
Account Name:contoso\user1
Password:Strong@P@ssword1
KVNO:2
ktpass /princ MSSQLSvc/red1.CONTOSO.COM:1433@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser contoso\user1 /out mssql.keytab -setpass -setupn /kvno 2 /pass Strong@P@ssword1 ktpass /princ MSSQLSvc/red1.CONTOSO.COM:1433@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser contoso\user1 /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass Strong@P@ssword1 ktpass /princ MSSQLSvc/red1:1433@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser contoso\user1 /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass Strong@P@ssword1 ktpass /princ MSSQLSvc/red1:1433@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser contoso\user1 /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass Strong@P@ssword1 ktpass /princ user1@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser contoso\user1 /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass Strong@P@ssword1 ktpass /princ user1@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser contoso\user1 /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass Strong@P@ssword1
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.