SQL Server on Linux - Active Directory authentication with Kerberos

Before using the Windows Authentication mechanism in SQL Server on Linux, the following steps need to be configured:

1.      An Active Directory domain account for the SQL Server on Linux.

2.      SPN needs to be set for the Linux host and for that AD account.

3.      Installation of Kerberos and Samba on Linux.

4.      Joining the Linux Server to the Windows domain.

5.      Verifying that the AD account is able to acquire the Kerberos Ticket.

6.      Attaching the AD account with the SQL Server process on Linux.

7.      Configuring and securing SQL Server.

Configuration steps:

1.      Create an Windows domain account on the AD Server with "User never expires" and "User cannot change password" options.

2.      On the AD Server, create a SPN for this account. The general syntax is the following:

setspn -A MSSQLSvc/[FQDN of Linux Host]:[tcp port]  [AD-Account Name]

3.      On the Linux Server, join the domain with the following command with the AD account, which has sufficient privileges in AD to join a new machine to the domain:

sudo realm join myhome.org -U '[AD Admin Account]@[UPPERCASE DOMAIN NAME]' -v

Or

sudo realm join --user=[AD Admin Account]  [lowercase domain name]

4.      Verify and take note of kvno:

id [AD Account for Linux]@[lowercase domain name]

kinit [AD Account for Linux]@[UPPERCASE DOMAIN NAME]

kvno MSSQLSvc/[lowercase fqdn of linux server]:1433 

klist

5.      Create a keytab file with ktutil.

sudo ktutil

ktutil> addent -password -p MSSQLSvc/[lowercase fqdn of linux server]:[tcp port]@[UPPERCASE DOMAIN NAME] -k [kvno] -e aes256-cts-hmac-sha1-96

ktutil> addent -password -p MSSQLSvc/[fully qualified domain name of host machine]:[tcp port]@[UPPERCASE DOMAIN NAME] -k [kvno] -e rc4-hmac

ktutil> wkt /var/opt/mssql/secrets/mssql.keytab

ktutil> quit

6.      Restrict access to the keytab file and ensure only the mssql user can read the file.

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

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

7.      Configure the SQL Server to use this keytab file for the Kerberos authentication:

sudo /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab

8.      Restart the SQL Server service on Linux server.

sudo systemctl restart mssql-server

Step by step joining to a domain:

We will be adding the linux03 (CentOS 7.4) to use Kerberos authentication to join the domain myhome.org for the AD user linuxsqlsvc:

For this task, we have the following environment information:

Linux Host/IP: linux03/192.168.0.156

FQDN:             linux03.myhome.org (lowercase fqdn of linux server)

Domain:          myhome.org (192.168.0.100)

Realm:            MYHOME.ORG (uppercase domain name)

AD Admin:     sqladmin (is an Admin account in Windows AD)

1.      Create the new AD account linuxsqlsvc with "User never expires" and "User cannot change password" options:

AD account:     linuxsqlsvc

Password:        Very$ecret

2.      Create SPN for Linux03 and linuxsqlsvc on the admin command prompt of the AD Server:

setspn -A MSSQLSvc/linux03.myhome.org:1433 linuxsqlsvc

3.      Install both the realmd and Kerberos client packages on the SQL Server host:

sudo yum -y install realmd krb5-workstation

4.      Join to the domain myhome.org; choose a command from the following. Here the sqladmin has sufficient privileges in AD to join a new machine to the domain:

sudo realm join myhome.org -U 'sqladmin@MYHOME.ORG' -v

or

sudo realm join --user=sqladmin myhome.org

5.      Verify and take note of kvno:

id linuxsqlsvc@myhome.org

kinit linuxsqlsvc@MYHOME.ORG (domain name in upper case)

kvno MSSQLSvc/linux03.myhome.org:1433 

klist

6.      Create a keytab file with ktutil:

sudo ktutil

> addent -password -p MSSQLSvc/linux03.MYHOME.ORG:1433@MYHOME.ORG -k 2 -e aes256-cts-hmac-sha1-96

> addent -password -p MSSQLSvc/linux03.MYHOME.ORG:1433@MYHOME.ORG -k 2 -e rc4-hmac

> wkt /var/opt/mssql/secrets/mssql.keytab

> quit

7.      Restrict access to the keytab file and ensure only the mssql user can read the file.

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

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

8.      Configure the SQL Server to use this keytab file for the Kerberos authentication:

sudo /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab

9.      Restart the SQL Server service on the Linux server:

sudo systemctl restart mssql-server

Configuring Windows Authentication for SQL Server on Linux:

Reference: https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-authentication