by Liwei

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

7 thoughts on “Use KTPASS instead of adden to configure mssql.keytab”

  1. Pingback: Use KTPASS instead of adden to configure mssql.keytab – Programming language
  2. Pingback: Use KTPASS instead of adden to configure mssql.keytab | Develop Paper

  3. Hi Liwei,
    We are trying to achieve this in our environment with no luck so far. (The Linux host has been integrated to our domain using Centrify). Should the AD account(service account that was used to create SPN) be added as a user to the Linux host as a prerequisite? Should we be able to SSH to the linux host using that AD account before we begin doing anything in the first place?

    Like

    Reply

    1. Hi Sree, SQL Server also support the Centrify. SSH is not used by Centrify, you don’t need to install it. However, the prerequisites are still same, SPN, mssql.keytab, hostfile….. are still needed . ‘Should the AD account(service account that was used to create SPN)’ this is not required.

      Like

      Reply

  4. Thanks Liwei, we were able to intergrate SQL with our AD after multiple attempts, but things are running extremely slow, almost unusable. It’s allowing us to create AD users/groups as logins(even this takes 60 seconds just to create login) but connecting to SQL using windows authentication is timing out almost 9 out of 10 times(Pre login handshake). I was hoping, creating logger.ini with below entries would help us pinpoint the issue.
    [Logger:security.kerberos]
    Level = Error
    Outputs = security
    [Logger:security.ldap]
    Level = Error
    Outputs = security
    The log file just has one entry as shown below…not exactly sure what it’s looking for.
    “Error [Security.ldap} Could not lookup short domain name due to error: Name or service not known”

    Like

    Reply

Leave a reply to Liwei Cancel reply