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 Controller is
dc01.nocentino.labwith an IP address of
- Linux host
- IP Address:
- DNS Records for the Linux host
- A Record -
- PTR Record -
- A Record -
- Configure DNS on the Linux Server
- Search suffix -
- DNS Server Address -
- Search suffix -
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
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 -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
If you are prompted for a kdc or admin server enter your Active Directory DNS name in lower case. For my lab, that’s
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
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(firstname.lastname@example.org) gid=761200513(domain email@example.com) groups=761200513(domain firstname.lastname@example.org),761200520(group policy creator email@example.com),761200572(denied rodc password replication firstname.lastname@example.org),761200519(enterprise email@example.com),761200518(schema firstname.lastname@example.org),761200512(domain email@example.com)
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.
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 /firstname.lastname@example.org aen@webinar:~$ sudo chown email@example.com /firstname.lastname@example.org
Then log in using ssh and your AD user account. Take note of the username
email@example.com 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 firstname.lastname@example.org webinar.nocentino.lab email@example.com@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
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
First, ensure you still have a valid ticket, you can confirm that with
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
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 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 firstname.lastname@example.org webinar.nocentino.lab email@example.com@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
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
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