Environment
===
Domain:contoso.com
Linux server:red1(Redhat 7.6)
MSA:msa01
SQL Server 2017 CU17
Red1 has joined the domain contoso.com(For information on how to join an active directory domain, see Join SQL Server on a Linux host to an Active Directory domain.)
- Create a MSA account with password specified in Powershell command prompt. Please note, the password of MSA should be
New-ADServiceAccount -Name msa01 -Enabled $true -AccountPassword (Read-Host -AsSecureString "Enter Password") -RestrictToSingleComputer
- Get the KVNO, running following command in SQL Linux box
kinit msa01@CONTOSO.COM
kvno MSSQLSvc/red1.contoso.com:1433
- Alternatively, you can run following Powershell command to get the KVNO
get-ADServiceAccount -Identity msa01 -property msDS-KeyVersionNumber
- Run following bash commands in command prompt in windows server to prepare the mssql.keytab file. SPN entries will be created in this step.(Please replace the Password1 with the password you specified in step1, and kvno 2 got from step 2)
ktpass /princ MSSQLSvc/red1.CONTOSO.COM:1433@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser contoso\msa01 /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\msa01 /in mssql.keytab /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\msa01 /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\msa01 /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass Password1
ktpass /princ msa01@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 msa01@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 the file keytab to SQL Server Linux box and rename to mssql.keytab , put in folder /var/opt/mssql/secrets/
- 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 /opt/mssql/bin/mssql-conf set network.privilegedadaccount msa01
systemctl restart mssql-server.service
- Secure the keytab file
sudo chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab
sudo chmod 400 /var/opt/mssql/secrets/mssql.keytab
- Configure SQL Server to use the keytab file for Kerberos authentication
sudo /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab
sudo systemctl restart mssql-server
There is a known issue in AES 256 encryption in krb5 library in redhat/centos and Ubuntu. There has been a patch in krb5 library, but the patch hasn't shipped on distributions we support.
If SPN entries are added using adden, you can't even connect to SQL Server using Windows Authentication. And you will see following messages in PALLOG.
Request ticket server MSSQLSvc/red1:1433@CONTOSO.COM kvno 2 enctype aes256-cts found in keytab but cannot decrypt ticket
If MSA entries are added using adden, you will fail to run 'create login','sp_addsrvrolemember', or other privileged operation and get following message
Could not obtain information about Windows NT group/user '%ls', error code 0x80090304
I’ll discuss the issue in other articles.