SQL Server on Linux with AD authentication

SQL Server has been supported on several Linux distributions for a couple of years now. For some people, the primary stumbling block to implementing SQL Server on Linux is the need to retain Active Directory (ie Windows-based) authentication for their database users and applications. Below we’ll go over how to join a Linux server (Ubuntu release 20.04) with SQL Server 2019 to an Active Directory domain, and then configure SQL Server to allow Windows-based logins.

Prerequisites

• Linux server (in this example it is a virtual server with Ubuntu 20.04 installed).
• Internet connection (for downloading packages to the server).
• Root permission (ie ability to run sudo commands).
• Active Directory Domain and Forest functional levels of Windows 2012R2 or higher.
• Access to a domain-joined Windows server (2012R2 or higher) to run ktpass and PowerShell commands.
• Active Directory account with permissions to create & configure user and computer objects.
• PSCP and Putty (or another SSH utility able to provide shell access to the Linux server and transfer files between the Windows and Linux servers).
• SQL Server Management Studio (SSMS)

Note: Case sensitivity is an issue for some of the configuration settings below. Pay close attention to places where the Domain Name or FQDN MUST be in UPPERCASE (eg. DOMAIN\username or DOMAIN.LOCAL).

Join Linux Server To Active Directory

1. Log in to the Linux server
2. Verify the server network config includes the AD domain name in the search setting, and that the nameservers include the AD domain controller IP or a DNS server IP that is in a Reverse DNS (RDNS) zone which includes the AD domain controller and Linux server.
   • sudo vim /etc/netplan/***.yaml (the name of the yaml file may vary between Linux distributions and server configurations)
   • sudo netplan apply
3. Install required packages
   • If the Kerberos client package installation prompts you for a realm name, enter your domain name in UPPERCASE.
   • sudo apt-get install realmd krb5-user software-properties-common python3-software-properties packagekit
   • sudo apt-get install adcli libpam-sss libnss-sss sssd sssd-tools
4. Check for, and apply, any updates
   1. sudo apt update
   2. sudo apt upgrade
5. Restart the server (or services) to ensure conf files are up-to-date.
   1. sudo reboot
6. Verify the krb5.conf file shows the correct FQDN in the default_realm setting.
   • sudo vim /etc/krb5.conf
   • 
7. Set the server hostname FQDN. The domain name must be in UPPERCASE.
   • hostnamectl set-hostname <server FQDN>
8. Join the server to the domain using an AD account with create/join permissions.
   • sudo realm join <domain name> -U '<AD username>@<FQDN>' -v
   • The domain FQDN above must be in UPPERCASE (ie. wick@DOMAIN.LOCAL).
9. Reboot the server for all settings and conf files to be updated.
   • sudo reboot
10. Verify that /etc/sssd/sssd.conf contains the domain information and the access provider is set to ad.
    • sudo vim /etc/sssd/sssd.conf
      • Eg. domains = domain.local and krb5_realm = DOMAIN.LOCAL

Install SQL Server

1. Download and install the SQL Server correct package for the Linux distribution (Ubuntu 18.04 is used as there currently isn’t a repo for 20.04).
   1. wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
   2. sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/18.04/mssql-server-2019.list)"
   3. sudo apt-get update
   4. sudo apt-get install -y mssql-server
2. Configure SQL Server using /opt/mssql/bin/mssql-conf.
   1. sudo /opt/mssql/bin/mssql-conf setup
3. Open firewall port 1433 for remote connections to SQL Server.
   1. sudo ufw allow from any to any port 1433 proto tcp
4. Install SQL Server tools for the Linux distribution (line 2 below is specifying the SQL Tools package for Ubuntu 20.04)
   1. curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
   2. curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
   3. sudo apt-get install mssql-tools unixodbc-dev
5. Add /opt/mssql-tools/bin folder to PATH
   1. export PATH="/opt/mssql-tools/bin:$PATH" >> ~/.profile
6. Check for any outstanding updates and reboot the server
   1. sudo apt-get update
   2. sudo apt-get upgrade
   3. sudo reboot
7. Verify SQL Server is installed and running correctly.
   1. Connect to the SQL instance using SSMS and login with the SA account.

Create Active Directory Service Account

An AD account is required to allow the SQL Server Service to authenticate logins using Kerberos. In this example a Managed Service Account (MSA) is being used, instead of a Group Managed Service Account (gMSA) or AD user (Microsoft recommends using MSAs as a best practice, as they are more secure than User objects/accounts). In the PowerShell example below, the MSA (named sqllinuxtest) is being restricted to a single computer object. Additionally, the Read-Host -AsSecureString option is being used to prompt for the account password. Save the password in a password safe (or other secure location), as it will be needed later on when creating the keytab file.

Import-Module ActiveDirectory;
New-ADServiceAccount -Name <service account name> -Enabled $true -AccountPassword (Read-Host -AsSecureString "Enter Password") -RestrictToSingleComputer;

Verify that the MSA supports using 128-bit and 256-bit AES encryption by looking at the msDS-SupportedEncryptionTypes attribute on the MSA object in Active Directory.

Once the MSA has been created, use SetSPN to create Service Principal Names (SPN) for the service account.

setspn -A MSSQLSvc/<fully qualified domain name of host machine>:<tcp port> <svc account name>

setspn -A MSSQLSvc/<netbios name of the host machine>:<tcp port> <svc account name>

Create Keytab File

A keytab file is an encrypted file on the local Linux host that is used to authenticate to the Key Distribution Center (ie. Domain Controller). Before the keytab file can be created, the Key Version Number (KVN) of the AD service account (created above) must be known. The KVN (usually 2) can be found by looking at the msDS-KeyVersionNumber property using PowerShell.

get-ADServiceAccount -Identity <service account name> -property msDS-KeyVersionNumber;

Once the KVN value is found, the keytab file can be created using the ktpass utility from a domain-joined Windows server. The commands below will create the mssql.keytab file and allow both AES and RC4 encryption ciphers. RC4 is an older cipher and may be excluded if a higher level of security is desired and all systems support AES. (Note: the MSA name in the bottom 2 commands should be entered in lowercase, or the authentication may fail.)

ktpass /princ MSSQLSvc/<server FQDN>:1433@<UPPERCASE FQDN>  /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser <domain name>\<MSA> /out mssql.keytab -setpass -setupn /kvno 2 /pass <password from step 11>
			
ktpass /princ MSSQLSvc/<server name>:1433@<UPPERCASE FQDN>  /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser <domain name>\<MSA> /out mssql.keytab -setpass -setupn /kvno 2 /pass <password from step 11>
			
ktpass /princ MSSQLSvc/<server FQDN>:1433@<UPPERCASE FQDN>  /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser <domain name>\<MSA> /out mssql.keytab -setpass -setupn /kvno 2 /pass <password from step 11>
			
ktpass /princ MSSQLSvc/<server name>:1433@<UPPERCASE FQDN>  /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser <domain name>\sqllinuxtest /out mssql.keytab -setpass -setupn /kvno 2 /pass <password from step 11>
			
ktpass /princ <MSA>@<UPPERCASE FQDN> /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser <domain name>\<MSA> /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass <password from step 11>
			
ktpass /princ <MSA>@<UPPERCASE FQDN> /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser <domain name>\<MSA> /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno 2 /pass <password from step 11>

The ktpass utility supports 3 values for ptype: KRB5_NT_PRINCIPAL, KRB5_NT_SRV_INST and KRB5_NT_SRV_HST. Usually, the KRB5_NT_PRINCIPAL option works (despite the warning shown in the example below) and it is the recommended option.

Once the keytab file has been created, it can be copied from the Windows server to the Linux server using Putty/PSCP (or another file transfer utility). The /var/opt/mssql/secrets folder on the Linux host is restricted, so it may be easier to transfer it to another folder and then use the root account to move it into the secrets folder.

Example:

1. Copy keytab from Windows to Linux using PSCP.
   • pscp -P 22 c:\mssql.keytab <linux admin account>@<server fqdn>:/home/<linux admin account>
2. Copy file to secrets folder using root account.
   1. sudo su
   2. sudo mv mssql.keytab /var/opt/mssql/secrets/mssql.keytab
3. Restrict access to the keytab file to the Linux mssql service account. (Note: This is a VERY important security setting. The keytab file is what allows the SQL Server service to authenticate as the AD MSA. Any other service/login with access to the keytab file can authenticate as the MSA to the domain.)
   1. sudo chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab
   2. sudo chmod 400 /var/opt/mssql/secrets/mssql.keytab
4. The file permissions can be confirmed using the ls -lh command.

SQL Privileged Account

The mssql-conf utility is used to specify the privileged account (ie. the MSA) that will use the keytab file. (Note: do not prepend the domain name to the MSA in the commands below.)

sudo /opt/mssql/bin/mssql-conf set network.privilegedadaccount <MSA>

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

After the privileged account has been configured, the SQL Server service must be restarted, or the server rebooted. systemctl restart mssql-server.service OR sudo reboot

Create SQL Server Login with Windows Authentication

Using SSMS or PowerShell, connect to the SQL server using the sa account and create a new login that uses Windows (AD) authentication.

Login using Windows Authentication

Using SSMS, open a new connection to the SQL server and connect using the Windows login specified above. (Note: it may be necessary to close and reopen SSMS before logging in to SQL with the Windows account. A ‘ login is from an untrusted domain’ error may be returned for the Windows login if SSMS has been open for a while.)

Once the Windows login has connected, verify that kerberos authentication is being used. SELECT auth_scheme FROM sys.dm_exec_connections;

Security Considerations

The default configuration for a Linux server joined to an Active Directory domain will allow ANY AD user to login to the server. There are several options available for restricting login access to the Linux host. This issue should be discussed with your organization’s security team to determine what your organization’s policy/procedure is to limit access to the server.

Additional Information & References

• Joining Linux server to Active Directory domain
  • https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-join-domain?view=sql-server-ver15
• Installing SQL Server on Linux
  • https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu?view=sql-server-ver15
• Register SPN for Kerberos
  • https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-ver15
• Creating a keytab file
  • https://techcommunity.microsoft.com/t5/sql-server-support/an-example-of-sql-linux-windows-authentication-configuration/ba-p/1007112