Use KTPASS instead of adden to configure mssql.keytab
Published Nov 22 2019 09:01 PM 5,683 Views
Microsoft

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

 

 

 

 

Version history
Last update:
‎Jun 01 2020 11:36 AM
Updated by: