Skip to content

Use KTPASS instead of adden to configure mssql.keytab

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(MS will update the official document )

—update 12/30/2019. MS has updated the steps.

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

I didn’t run setspn, why? because the step3 will create SPN entries automatically.

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 /opt/mssql/bin/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

1 Comment »

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: