Configure SQL Server on Linux for Active Directory Authentication

In this post, we’re going to walk through configuring Active Directory authentication for SQL Server on Linux. We will start by joining the Linux server to the domain, configuring SQL Server on Linux to communicate to the domain, and then use adutil to create our AD users and set up Kerberos for SQL Server login authentication.

Before getting started

First, let’s get some environment requirements set. We’ll need an Active Directory domain, a Linux host to install SQL Server on, some DNS records for that host, and the DNS client on that host configured for our environment. Here are the settings I used in this walk-through.

• Active Directory Domain
  • Domain: nocentino.lab
  • Domain Controller is dc01.nocentino.lab with an IP address of 172.17.0.10
• Linux host
  • Hostname: webinar.nocentino.lab
  • IP Address: 172.17.0.5
• DNS Records for the Linux host
  • A Record - webinar.nocentino.lab -> 172.17.0.5
  • PTR Record - 172.17.0.5 -> webinar.nocentino.lab
• Configure DNS on the Linux Server
  • Search suffix - nocentino.lab
  • DNS Server Address - 172.17.0.10

Install SQL Server on Linux and Tools

Next, let’s install SQL Server on Linux and get the appropriate SQL tools installed. This walk-through is for Ubuntu 20, so we’re using apt.

wget -qO- https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
sudo add-apt-repository "$(wget -qO- https://packages.microsoft.com/config/ubuntu/20.04/mssql-server-2019.list)"
sudo apt-get update
sudo apt-get install -y mssql-server
sudo /opt/mssql/bin/mssql-conf setup

During installation, you need to enter your SQL Server edition, accept the EULA, and set the sa password. Once finished, look for the output Setup has completed successfully. SQL Server is now starting.

Next, let’s get the SQL tools we need installed and ensure they’re available in our path for easy access at the command line.

curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
sudo apt-get update 
sudo apt-get -y install mssql-tools unixodbc-dev

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

Then test connecting to your instance with sqlcmd

sqlcmd  -S localhost -U sa
Password: 
1> SELECT @@VERSION
2> GO

------------------------------------------------------------------------------------------------------
Microsoft SQL Server 2019 (RTM-CU14) (KB5007182) - 15.0.4188.2 (X64) 
	Nov  3 2021 19:19:51 
	Copyright (C) 2019 Microsoft Corporation
	Developer Edition (64-bit) on Linux (Ubuntu 20.04.3 LTS) <X64>

(1 rows affected)

Join the Linux Server to the Active Directory Domain

With the Linux server networking and DNS configured and SQL Server on Linux installed, let’s join the Linux server to the domain. Listen, y’all have heard it a million times…it’s always DNS…before you move forward, make sure you have the DNS records and client configuration that I called out in the “Before Getting Started” section configured. Don’t skip that. Kerberos is very strict about host names matching. If they don’t resolve from DNS correctly…things won’t work.

From your Linux server test out your DNS config and ensure you get the proper results for each. Here are the commands I used to ensure I have have the appropriate DNS config in place.

dig nocentino.lab
dig dc01.nocentino.lab
dig webinar.nocentino.lab
ping 172.17.0.5
ping 172.17.0.10

Install required packages

Time to install the packages needed to join the Linux server to the domain. This includes kerberos, sssd and also adutil.

sudo apt-get install -y realmd krb5-user software-properties-common python3-software-properties packagekit adcli libpam-sss libnss-sss sssd sssd-tools adutil

During the installation, you need to enter your Kerberos Realm name. The Realm name is your Active Directory domain name in all caps for my lab that’s NOCENTINO.LAB.

If you are prompted for a kdc or admin server enter your Active Directory DNS name in lower case. For my lab, that’s nocentino.lab.

Set the hostname of your Linux server to the fully qualified domain name (FQDN) sudo hostname webinar.nocentino.lab

Join the server to the domain

sudo realm join nocentino.lab -U 'aen@NOCENTINO.LAB' -v

Let’s break this down a bit. I’m specifying the join parameter using the’ realm’ command then passing in the domain that I’m joining nocentino.lab. In the -U parameter, I’m using an AD account that has rights to join computers to the domain here, I’m using my account aen@NOCENTINO.LAB and I’m using the -v parameter because I love the verbose output and that output is very helpful when things go wrong.

When you execute this command, if you don’t have a cached sudo credential, you need to enter your local linux user password first. Then after than you will need to enter your Active Directory user password for the account specified in the -U parameter. This account needs to be able to join computer accounts to the domain. Once this command completes, look for this output 'Successfully enrolled machine in realm'

Confirm domain connectivity

When that command finishes, your Linux server is joined to the domain and has the appropriate client configuration to query LDAP and authenticate users to the local Linux server using Active Directory accounts.

So let’s query LDAP using the id command on the Linux server. The output below shows the account information for the account aen@NOCENTINO.LAB attributes such as its uid, gid and groups are returned. If this succeeds, your Linux server can communicate properly with your Active Directory via LDAP for user information.

id aen@NOCENTINO.LAB uid=761200500(aen@nocentino.lab) gid=761200513(domain users@nocentino.lab) groups=761200513(domain users@nocentino.lab),761200520(group policy creator owners@nocentino.lab),761200572(denied rodc password replication group@nocentino.lab),761200519(enterprise admins@nocentino.lab),761200518(schema admins@nocentino.lab),761200512(domain admins@nocentino.lab)

Confirm that you can Authenticate via Kerberos

LDAP is just for user information lookup. Kerberos is used to authenticate the users. So let’s test that out. Using kinit, we ask the configured Kerberos server, and in our case that’s our Active Directory server, to grant our user a ticket. This ticket authenticates the user to the environment. By default, a ticket has a cache lifetime of 12 hours. When that expires, a new ticket needs to be requested with kinit. In this section, we test authentication with kinit, and then once that’s good, we’ll test logging into our Linux server via ssh.

Using kinit username@REALM_NAME to test Kerberos authentication.

In the output, I use kinit which will prompt me for my Active Directory user passwords. Once successful, I’m returned to the command line. To check our ticket status, we use klist. And in the output below, you can see the location of the ticket cache file, the principal authenticated, and the ticket’s lifetime.

kinit aen@NOCENTINO.LAB
Password for aen@NOCENTINO.LAB: 

aen@webinar:~$ klist 
Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: aen@NOCENTINO.LAB

Valid starting     Expires            Service principal
12/01/21 15:46:08  12/02/21 01:46:08  krbtgt/NOCENTINO.LAB@NOCENTINO.LAB
  renew until 12/02/21 15:46:05

Confirm that you can authenticate via SSH

Now that I know, I can authenticate to AD via Kerberos from our Linux server. Let’s test to see if I can log into the Linux server via SSH. I’m using Ubuntu 20 in this environment, and it has password authentication disabled by default. So let’s enable that by changing PasswordAuthentication no to PasswordAuthentication yes in the sshd config file /etc/ssh/sshd_config and restart ssh with sudo systemctl restart sshd.

sudo vi /etc/ssh/sshd_config

PasswordAuthentication no
PasswordAuthentication yes

sudo systemctl restart sshd

Once that’s restarted, create a home directory for your AD user and set the permissions properly using this code. Note this can be automated, but i’m going to leave that out of scope for this article. Check this link out for more details.

sudo mkdir /home/aen@nocentino.lab
aen@webinar:~$ sudo chown aen@nocentino.lab /home/aen@nocentino.lab

Then log in using ssh and your AD user account. Take note of the username aen@nocentino.lab and be sure to use the FQDN of your Linux server; here, I’m using webinar.nocentino.lab. Using the FQDN is key (see what I did there :P ) to your session getting logged in properly via Kerberos as Kerberos relies on host names as part of its security architecture. And then finally, in the output below, you can see that I get an ssh session to our Linux server.

ssh -l aen@nocentino.lab webinar.nocentino.lab

aen@nocentino.lab@webinar:/$ 

Now, I don’t like using password authentication for ssh. I much prefer using ssh keys. But we needed to use a password here to ensure Kerberos authentication was working and use our Active Directory account information, username and password. At this point, I can copy my ssh key into the Linux server and disable password authentication in sshd.

Create SQL Server Service Account in Active Directory with adutil

At this point, we have a domain-joined Linux server that can authenticate users via Kerberos. Let’s start configuring our SQL Server on Linux instance to use Active Directory authentication. We’ll start by creating a SQL Server Service Account in Active Directory using adutil.

First, ensure you still have a valid ticket, you can confirm that with klist

klist 
Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: aen@NOCENTINO.LAB

Valid starting     Expires            Service principal
12/01/21 15:46:08  12/02/21 01:46:08  krbtgt/NOCENTINO.LAB@NOCENTINO.LAB
  renew until 12/02/21 15:46:05

If you do not, get a new one with kinit aen@NOCENTINO.LAB.

Then we’ll use adutil to create a user name sqluser in Active Directory. This user is the account that the SQL Server on Linux instance will use to authenticate itself to Active Directory, primarily for user lookup when users log into the local SQL Server. Note --accept-eula is only needed the first time you use adutil.

adutil user create --name sqluser --distname CN=sqluser,CN=Users,DC=NOCENTINO,DC=LAB --password 'P@ssw0rd' --accept-eula

Next, we add an Service Principal Name (spn) to Active Directory. The SPN associates the service to its login account. You specify which username, the host the service is logging in from, the type of service, and the network port used.

adutil spn addauto -n sqluser -s MSSQLSvc -H webinar.nocentino.lab -p 1433

Create SQL Server Service Keytab File

With the service account created, we need to create a keytab file. A keytab securely stores Kerberos credentials, called keys, used by services to authenticate. A keytab can hold one or more keys. In this section, I will create a keytab and configure SQL Server on Linux to use that keytab to authenticate to Active Directory. The keytab allows our SQL Server on Linux instance to authenticate to Active Directory to query user information from the domain.

First, let’s create the keytab file. In the code below, we are creating a file name mssql.keytab and pass in additional parameters that match the spn created above. Also, one point to note, in the encryption parameter -e, I’m adding several encryption types that are specific to my environment. There are several supported encryption types. Select the ones needed for your environment. You may need to work with your AD administrators for this information.

adutil keytab createauto -k mssql.keytab -p 1433 -H webinar.nocentino.lab --password 'P@ssw0rd' -s MSSQLSvc -e aes256-cts-hmac-sha1-96,aes128-cts-hmac-sha1-96,aes256-cts-hmac-sha384-192,aes128-cts-hmac-sha256-128,des3-cbc-sha1,arcfour-hmac -y

adutil keytab create -k mssql.keytab -p sqluser --password 'P@ssw0rd!' -e aes256-cts-hmac-sha1-96,aes128-cts-hmac-sha1-96,aes256-cts-hmac-sha384-192,aes128-cts-hmac-sha256-128,des3-cbc-sha1,arcfour-hmac

Once the keytab is created, move it into a known location for SQL Server and then set the ownership and permissions so that the SQL Server process can access the keytab file.

sudo mv mssql.keytab /var/opt/mssql/secrets/
sudo chown mssql. /var/opt/mssql/secrets/mssql.keytab
sudo chmod 440 /var/opt/mssql/secrets/mssql.keytab

If you want to dig deeper into what adutil did inside the keytab file automatically for us, use the command sudo klist -kte /var/opt/mssql/secrets/mssql.keytab to pull that info out. This is useful for debugging in things go wrong.

Configure SQL Server to use the Keytab File

Moving along, now that we have a keytab file, storing the credentials SQL Server on Linux instance used to authenticate to the domain. Let’s tell SQL Server on Linux where to find that file with mssql-conf. The commands below specify the keytab file location with network.kerberoskeytabfile and then specify the AD Service Account with the parameter network.privilegedadaccount. Once configured, restart the SQL Server on Linux instance.

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

sudo /opt/mssql/bin/mssql-conf set network.privilegedadaccount sqluser

sudo systemctl restart mssql-server

Create SQL Server logins for AD user

Hey SQL Server DBA friends reading this, that’s the end of the gory Linux’y stuff…now let’s configure a SQL Server on Linux Instance’s login using a Windows (Active Directory) account. We can use straight TSQL for that. I’m using sqlcmd loging in as sa for this. You can use any tools you like for this part, such as SSMS, ADS, and so on.

sqlcmd -S localhost -U sa -Q 'CREATE LOGIN [NOCENTINO\aen] FROM Windows'

Connect to SQL Server using AD authentication

Now let’s try logging into our SQL Server on Linux instance using our Active Directory account. The easiest way for this is logging in via SSH (remember we tested that out a bit ago, we’re going to leverage that here.). Also, since this will likely be a new user on your Linux server, let’s add the SQL Tools binary locations to our path for easy access.

ssh -l aen@nocentino.lab webinar.nocentino.lab
aen@nocentino.lab@webinar:/$ 
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc

Once that’s finished, we can use sqlcmd to log into the instance. Notice how I’m not specifying any logging information! Sweeeeeet, huh? Run a quick query, and you can see that I’m logged in as NOCENTINO\aen.

sqlcmd -S webinar.nocentino.lab
1> SELECT SYSTEM_USER
2> GO

------------------------------------------------------------------------------------
NOCENTINO\aen                            

(1 rows affected)

Confirm that the connection is using Kerberos

Let’s ask the SQL Server on Linux instance about how the currently logged in users authenticated, and we can do that using the DMV sys.dm_exec_connections. To do this, we need to open up another connection using a privileged account. You need a second connection because the user above NOCENTINO\aen isn’t privileged in SQL Server. With that second connection open, you can see below that in row one, there’s my privileged connection logged in as login_name sa who’s auth_scheme is SQL and then in the second row, my login_name [NOCENTINO\aen] is using the auth_scheme KERBEROS. Bravo!

sqlcmd -S . -U sa 
SELECT  s.host_name, auth_scheme 
    FROM sys.dm_exec_connections AS C JOIN sys.dm_exec_sessions AS S ON C.session_id = S.session_id;

host_name              auth_scheme     login_name         
---------------------  --------------- ----------------
webinar.nocentino.lab  SQL             sa
webinar.nocentino.lab  KERBEROS        NOCENTINO\aen                 

References

• https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-ad-auth-adutil-introduction
• https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-ad-auth-adutil-tutorial
• https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-active-directory-join-domain