Skip to content

An example of SQL Linux Windows Authentication configuration using Managed Service Accounts

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.)

1.Create a MSA account with password specified in Powershell command prompt. Please note, the password of MSA should be fixed.

New-ADServiceAccount -Name msa01 -Enabled $true -AccountPassword (Read-Host -AsSecureString "Enter Password") RestrictToSingleComputer

image

2.Get the KVNO, running following command in SQL Linux box.

kinit msa01@CONTOSO.COM
kvno MSSQLSvc/red1.contoso.com:1433

image

3.Alternatively, you can run following Powershell command to get the KVNO.

get-ADServiceAccount -Identity msa01 -property msDS-KeyVersionNumber

image_thumb7_thumb

4.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

5.Copy the file keytab to SQL Server Linux box and rename to mssql.keytab , put in folder /var/opt/mssql/secrets/

image

6.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

image_thumb11_thumb

7.Secure the keytab file.

sudo chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab
sudo chmod 400 /var/opt/mssql/secrets/mssql.keytab

8.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.

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: