Active Directory Authentication with SQL Server on Ubuntu

With the release of SQL Server for Linux, Microsoft Data Platform professionals now have a whole new frontier of potential configurations and applications to add to their ever growing list of responsibilities. While Linux is maybe (probably?) nothing new to your organization, if you’re primarily concerned with SQL Server, there’s a pretty good chance you’ve never had to worry about authentication and SQL Server before. You had a Microsoft Data Platform product, running on a Microsoft Operating System, and it (probably?) was a member of a Microsoft Active Directory Domain. All of these products co-exist really well. Even if provisioning new server builds falls outside of your daily job duties, you’ve probably joined a machine or two to a domain in your career.

Active Directory domains, though, aren’t limited to containing just Windows-based machines. It’s possible integrate domain authentication to other non-Windows products. Linux is one example: you can enable domain authentication on Linux machines, and even join Linux machines to an Active Directory domain. It’s not as straightforward as doing it on a Windows-based machine, but it’s entirely possible. This becomes really important to SQL Server on Linux. After all, if you’re standing up new SQL Servers in an existing domain environment, you’ll want to leverage the same authentication mechanisms you’re used to.

In the following post, we’ll walk through joining a Linux SQL Server on Ubuntu to an Active Directory domain, and here’s the steps we’re going to take:

1. Installing the required software and services to enable a Linux host to talk to and join an Active Directory Domain,
2. Configuring the Linux host’s network configuration to talk to the Domain Controller(s),
3. Setting up Samba, Kerberos, Winbind, and the System Security Services Daemon (SSSD) to properly talk to and digest authentication tokens from Active Directory, and
4. Creating a Kerberos Keytab file for the SQL Server service to run as a domain service account.

Seems like a lot, doesn’t it? If you’re new to Linux, a lot of this configuration can seem a little daunting and a lot tedious, but as we walk through it, I’ll stop and talk a little bit about each step and what it does.

What you need before we start

Before we start though, there’s a few things you’re going to need to have already set up:

• An Active Directory Domain to test in, and rights to administer it. Since we’re going to be creating (and possibly deleting, if there are errors) computer objects and a service account, you’ll need a domain account with adequate permissions.
• My example assumes you have a Microsoft DNS server running alongside your domain services. It is possible to use a separate DNS server to get this to work, but you might need some additional network configuration (see below). Also, depending on your environment, you might need a reverse lookup zone defined. If you notice long ping times or other weird lookups, I’d set one up in your DNS.
• A machine (virtual or otherwise) that is running Ubuntu 16.04 Server or later (and this guide was written and tested against Ubuntu Server versions 16.04, 16.10, and 17.04). For this demo, we’ll be using Ubuntu Server.  If you’re new to Linux, you might opt for the desktop version. This guide should still work, but you might need some extra steps to configure the Window Manager to allow for authentication to AD. I added a section at the bottom for some additional things to consider when using the desktop version, so make sure you scroll down and give it a read. Regardless, you’ll need “sudo” (read: admin rights) to the machine. When you install Linux, you’ll set up an initial user account. I recommend creating an account name that doesn’t exist in your domain already (something like “localadmin”). This initial account is also an administrator account. Make sure you write down the password.
• Your machine should have access to the internet. We’re going to be installing a lot of software packages.
• Your machine ideally will have a static IP address. This will make configuring the network interfaces a lot easier. You can use DHCP, but it might add a few extra steps to your process (basically, you need to tell the networking service to override a lot of DHCP settings, and it’s a huge pain, but doable).
• Also, you’ll want to make sure your machine’s date and time is synced to your domain controller’s date and time. This has everything to do with Kerberos; if your date and times are off, you might get authentication errors. You can either set the time manually or use an NTP service. I’m going to omit setting that up here; but there are plenty of tutorials online.
• An installation of SQL Server on Linux. Microsoft has really straightforward documentation on how to do this. As part of setting up the instance, you’ll set an “sa” password. Make sure you write that down somewhere for now.
• Finally, you’ll need a way to connect to the host. The easiest way is to use PuTTY, but you can just as easily use a virtual console in your virtual machine manager du jour.

If you have all these prerequisites, then we can begin.

Can’t find my way home

In this demo, I will be using a lab machine, lab-linuxsql-04 to join my domain, boatmurder.net (so if you’re following along at home, just substitute my hostname/domain name/IPs with your info). Before we even get to the fun part of manually installing and configuring all the required software and services to make authentication work, we first need to make sure our domain resolves correctly. To make all this work, your Linux machine needs to know the IP address(es) of your domain controller(s) and the name of the domain for searches. If you’re using the Server version of Ubuntu, you’ll do this by manually editing your network interfaces configuration file. To edit the file, you’ll run the following command:

sudo nano /etc/network/interfaces

Below is a screen shot of my configuration for my network. You’ll obviously need to change these entries to satisfy your network configuration.

# /etc/network/interfaces

# This file describes the network interfaces available on your system
# and how to activate them. For more information, see interfaces(5).

# The loopback network interface
auto lo
iface lo inet loopback

# The primary network interface
auto ens160
iface ens160 inet static
address 192.168.2.144
netmask 255.255.255.0
gateway 192.168.2.1
dns-nameservers 192.168.2.100
dns-search boatmurder.net

Notice my entries for dns-nameservers and dns-search: these should correspond to your domain controller(s). If you’re using a separate DNS server/service, you’ll need add those, too (just separate each with a space).

Next, we need to edit our hosts file to account for our domain name. This step makes sure that when we attempt to join the machine to an Active Directory domain, it will be able to register itself in the domain’s DNS server. At a terminal window, run the following command:

sudo nano /etc/hosts

Here’s what my file looks like. What we’re going to do is set out “localhost” entries to include our fully qualified domain name. Note the order of the entries; what I found interesting in my testing was I had much more success putting my entries with my FQDN first, and then adding in the non-FQDN entry after that. Unless you need IPV6, you can also comment out the lines below your IPV4 settings.

# /etc/hosts
127.0.0.1       localhost
127.0.1.1       lab-linuxsql-04.boatmurder.net lab-linuxsql-04
192.168.2.144   lab-linuxsql-04.boatmurder.net lab-linuxsql-04

# The following lines are desirable for IPv6 capable hosts, but I'm commenting them out (disabling IPv6, essentially)
#::1     localhost ip6-localhost ip6-loopback
#ff02::1 ip6-allnodes
#ff02::2 ip6-allrouters

Once you make the changes to the file, save it (in nano, use Control-O to write out the file, and then Control-X to exit).

Next, you can either restart the network services on your machine or do a full reboot (I’d err on the side of completeness and just reboot). Once things come back online, you’ll want to test your domain name resolution. You can do this with a simple ping command. You want to make sure when you ping the domain name, you get an address back that matches your domain controller.

ping -c2 your_domain_name

If that returns your DC’s FQDN, you’re in business.

What’s in a daemon?

With that out of the way, we’re ready to start configuring our Linux installation to talk to Active Directory. To do that, we need some software. We’re going to install the following packages:

• Samba
• Krb5
• Winbind
• PAM and NSS libraries for Winbind
• SSSD

To simplify some of the configuration, we’re going to install them all at once with the following command:

sudo apt-get install samba krb5-config krb5-user winbind libpam-winbind libnss-winbind sssd

This will tell the Ubuntu package manager to download and install them. As part of the install, you might see a screen asking you for some domain information: If asked, enter your fully-qualified domain name in ALL CAPS. The rest of the entries should point to your domain controller(s). You might also see some errors/red text during the install. This is normal; the OS will try to start these new services, which we haven’t fully configured, so they’ll fail. No big deal.

When everything is done, we’re going to do our first test of the software and network configuration: we’re going to request a Kerberos token from our domain. At the terminal, run the following command:

sudo kinit <active directory domain admin user account>

You should be prompted for a password for the domain user, which you’ll enter. If everything works correctly you should see… nothing. That means the request was successful. You can view the token by running the following command.

sudo klist

Which will return your Kerberos token info. If you get an error, or you don’t see your token, take a few steps back and check your configuration. Make sure your network configuration is correct, and that you installed all the required packages.

With our Kerberos token test completed, we’re ready to start configuring our various Samba-related services to talk to Active Directory. First, we need to configure Samba itself. To do that, we’re going to use our own custom-made configuration file. Samba has quite a list of configuration options, but here’s the base settings you’ll need. Before we do that, we’re going to make a backup of the initial configuration file. We’ll do that by “moving” the existing file to a new name, and then editing a new, empty file:

#Back up the default settings file that was created
sudo mv /etc/samba/smb.conf /etc/samba/smb.conf.old

#Let's create a new file!
sudo nano /etc/samba/smb.conf

Here’s the configuration we’re going to use. Again you’ll want to change your values accordingly (specifically, the workgroup, realm, netbios name, and dns forwarder entries).

# /etc/samba/smb.conf

[global]
workgroup = BOATMURDER
realm = BOATMURDER.NET
netbios name = lab-linuxsql-04
security = ADS
dns forwarder = 192.168.2.100
idmap config * : backend = tdb        
idmap config * : range = 50000-1000000
template homedir = /home/%D/%U
template shell = /bin/bash
winbind use default domain = true
winbind offline logon = false
winbind nss info = rfc2307
winbind enum users = yes
winbind enum groups = yes
vfs objects = acl_xattr
map acl inherit = Yes
store dos attributes = Yes
client signing = yes
client use spnego = yes
kerberos method = secrets and keytab

Once you create this file with these settings, save it (again use Control-O to write out the file, and then Control-X to exit). We’ll also need to restart the Samba daemon. A daemon is just another name for a service. Services in Unbuntu are controlled by the systemctl utility. To restart the services, we’ll issue the following command:

#Systemctl is a command that lets your control daemons. Let's restart the samba services
sudo systemctl restart smbd nmbd

We’re going to attempt to join the machine to our domain now. There’s a few different ways to do this: we can do use a username and password scheme (just like how we do it with Windows already) or we can use our existing Kerberos ticket to do it too. Either of the following commands should work:

#to join with your existing kerberos token
sudo net ads join -k

#to join with a username and password
sudo net ads join -u <domain admin username>

After you run that command, your machine should join the domain. Here’s a side-by-side showing my DNS and Active Directory objects that get created once I join my lab domain:

However, there’s also a good chance that you might get an error. The most common is a DNS update failure, and this is either caused by a bad network configuration or a missing host file entry. Double check your settings, manually delete the computer object from your domain, and try joining again. If everything works correctly, you should see no errors, and in AD you should have a new computer object and a new DNS entry for the Linux machine too.

Congratulations, you just joined your Linux machine to an Active Directory domain! Feels good, doesn’t it? Unfortunately, just joining the machine do the domain doesn’t mean you can start authenticating right away. Before we do that, we need to configure our Linux machine and tell it how to handle login requests. To do that, we need to start to leverage winbind.

First, we need to update Linux’s name service switching configuration file, nsswitch.conf. This file controls what services Linux will look to for authentication information. To do that, we’re again going to fire up nano in a terminal window by typing in:

sudo nano /etc/nsswitch.conf

You need to add three entries to this file, and yes, the order matters! I’ve highlighted what I added to the file:

# /etc/nsswitch.conf
#
# Example configuration of GNU Name Service Switch functionality.
# If you have the `glibc-doc-reference' and `info' packages installed, try:
# `info libc "Name Service Switch"' for information about this file.

passwd:         compat winbind sss
group:          compat winbind sss
shadow:         compat winbind sss
gshadow:        files

hosts:          files resolve [!UNAVAIL=return] dns
networks:       files

protocols:      db files
services:       db files sss
ethers:         db files
rpc:            db files

netgroup:       nis sss
sudoers:        files sss

Once that’s done, save it again (Control-O, Control-X). Now we’re ready to try and authenticate our machine logins to AD.

Moment of truth

Before we move on, let’s issue a restart of all the services like we did above, but now we’re going to add the winbind service to the list:

#restart samba, and now add winbind to the mix
sudo systemctl restart smbd nmbd winbind

Now we’re going to see if your machine can parse AD login info. At the terminal window, type in the following command:

wbinfo -u

You should start to see a listing of all your domain users. Here’s a sample of my lab users:

This list might be really long if you have a lot of users, and if you get tired of looking at it, just hit Control-C to cancel the command. However, if you don’t see your users here, then we’re missing something. Most likely, it’s a network configuration issue and your machine can’t resolve your domain name to the Active Directory controllers. if that’s the case, double-check everything, restart services, and then try again.

If that works, we’re going to see if the machine can get the identity information from Active Directory. At your terminal window, run the following command:

getent passwd | grep drew

This command will evaluate all AD users, and then use the Linux grep command to search parse the results. Here’s an example where I look for anyone with the string “drew” in their credentials. I get two results, one for my actual account, and one for another user. I’d say it’s working.

You can test this with groups too by substituting group for passwd.

Sweet, everything is working! Again, if you can’t get these to return, just return to the beginning and check all your configuration settings. Isn’t Linux fun?

Now we need to configure the system security services deamon, or sssd. Just like all the steps before this, we’re just going to have to create some configuration files. To start with, you’re going to create a new sssd.conf file with the command:

sudo nano /etc/sssd/sssd.conf

You’re going to be presented with an empty file. Here’s what you’ll need to put type in. Some lines are optional, so I’m going to leave them commented out (but if the settings they provide apply to you, then by all means uncomment them and set them to the correct values):

# /etc/sssd/sssd.conf

[sssd]
services = nss, pam
config_file_version = 2

#Set this to your domain, and yes, CAPS ARE NEEDED!
domains = BOATMURDER.NET

#You need one entry for each domain you want to set up
[domain/BOATMURDER.NET]
id_provider = ad
access_provider = ad
override_homedir = /home/%d/%u

# Uncomment if the client machine hostname doesn't match the computer object
# on the DC.
# ad_hostname = mymachine.myubuntu.example.com

# Uncomment if DNS SRV resolution is not working
# ad_server = dc.mydomain.example.com

# Uncomment if the AD domain is named differently than the Samba domain
# ad_domain = MYUBUNTU.EXAMPLE.COM

Once you save the file, you need to set permissions to it. Run the following two commands:

sudo chown root:root /etc/sssd/sssd.conf
sudo chmod 600 /etc/sssd/sssd.conf

And then, we need to restart all our services again, this time adding in sssd as a service:

#let's start all the services now
sudo systemctl restart smbd nmbd winbind sssd

There’s two more steps we need to take before we can attempt a login. First, we need to update pam, or the “pluggable authentication modules” configuration. Basically, the last four things we configured (samba, nss, winbind, and sssd) are all authentication modules. Now that they are in place we need to tell pam about them and how to handle them. At the terminal window, you’ll need to run the following command:

sudo pam-auth-update

In the window that opens, make sure every box has a star in it, then hit tab to select “Ok” then hit enter again.

With that done, we need to tell Linux how to handle domain logins. If you were to create a user locally, they’d have a home directory set up at that time. Since these are domain users, we need to tell Ubuntu how to handle logins for users without home directories. To do that, we’re going to edit one last file by typing in:

sudo nano /etc/pam.d/common-session

If you scroll to the end of the file, you’re going to add one line at the very end (after the #end of pam-auth-update config line).

# /etc/pam.d/common-session

...

# end of pam-auth-update config
session required pam_mkhomedir.so skel=/etc/skel/ umask=0022

Finally, we’re ready to attempt a domain log in. The command is simple: we’re going to run the following command to log in to a “new” session as a domain user:

su - <ad username goes here>

The command “su” means “substitute user.” I should be asked for a password, and if everything works correctly…

Et voila.Domain authentication! It’s Miller Time. Or is it?

Important: don’t forget, we’re currently using a domain user’s session.

Even though my account is a member of the Domain Admins group, this Linux server couldn’t care less and I’m essentially running as a non-privileged user. Before continuing, make sure you type “logout” to leave this session and return to the local user account that is an admin. If you forget, a lot of the following commands in this tutorial won’t work.

But wait, there’s more!

Now we’re ready to set up the SQL Server Instance to run as a domain service account. Before we do that, we need to create a service account and set up an SPN for the account and service running on the Linux machine. You can do either this manually in the Active Directory GUI or with PowerShell (guess which one I prefer?). As before, you need a domain account with sufficient privileges to do this. To create the user account, you also need the Remote Server Administration Tools or Active Directory PowerShell module.

Here’s a quick and dirty PowerShell command you can run (again, as a domain administrator) to create a service account. I am going to use the account name linuxsqlsvc  as my service account:

Import-Module ActiveDirectory
New-ADUser linuxsqlsvc -AccountPassword (Read-Host -AsSecureString "Enter Password") -PasswordNeverExpires $true -Enabled $true

Once that’s done, you need to set up an SPN for the account we just created. There’s lots of ways to do this (including with PowerShell using dbatools), or you can just use the command line:

setspn -A MSSQLSvc/lab-linuxsql-04.boatmurder.net:1433 linuxsqlsvc

For my example, I’m setting it to my fully qualified hostname, setting the port (default 1433) and providing the account name. With that done, we can return to the Linux machine. To enable SQL Server to run as a domain service account, we’re going to create a keytab file. A keytab is sort of like a certificate; it contains encrypted credentials of an authenticated user with a valid Kerberos token. These credentials are then read by services and applications.  This also requires that the account we create the keytab for is also the same account we set up the SPN for above. We validate this by making sure key version number is valid. First, we get the a new Kerberos token using the account we just created, and then key version number (kvno) by querying the SPN we just created:

kinit linuxsqlsvc
kvno MSSQLSvc/lab-linuxsql-04.boatmurder.net:1433

If this SPN is brand new, the kvno should equal “2” but make note of it. To create the keytab, we’re going to use a utility called ktutil. The ktutil process has it’s own prompts. You may be asked for the account password during the process; you’ll enter the password you created for the service account. This utility doesn’t validate that password, so make sure you type it correctly!

sudo ktutil
ktutil: addent -password -p MSSQLSvc/lab-linuxsql-04.boatmurder.net:1433@BOATMURDER.NET -k 2 -e aes256-cts-hmac-sha1-96
ktutil: addent -password -p MSSQLSvc/lab-linuxsql-04.boatmurder.net:1433@BOATMURDER.NET -k 2 -e rc4-hmac
ktutil: wkt /var/opt/mssql/secrets/mssql.keytab
ktutil: quit

Above, you can see my example. Yes, you do need the FQDN and port number, and the @DOMAIN after it (in all caps, as well). Without it, you might get “the user is from an unauthorized domain” errors when attempting to connect to your SQL Server with domain authentication.

Once the keytab is created, we need to set the ownership and permissions on the file. Instead of giving “root” the ownership, we’re going to set the mssql process as the owner:

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

Finally, we tell the SQL Server instance to use the keytab file. We’ll run the mssql-conf command (like when SQL Server was first installed) and set the network.kerberoskeytabfile parameter and point it to where we saved the keytab:

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

Finally, we’ll restart the SQL Server instance:

sudo systemctl restart mssql-server

Next, either with sqlcmd on the Linux machine or with SQL Server management studio, connect to the server with your “sa” account and password, and add a windows user or group login (I’d suggest it be either your account or a group you’re a member of), and then disconnect. Here’s an example. I’m connecting with SSMS using the “sa” password that was created when we installed SQL Server on Linux. I am then going to run a couple of T-SQL statements to add a Windows group that my account is a member of, and add it to the sysadmin role on the server.

Then we’ll disconnect, and when you reconnect, use domain authentication. If everything worked…

That was a lot of work!

I know, and that’s the beauty and tragedy of Linux all encapsulated into one use case. Linux can be configured to do a lot of very important things, but that’s just it: it needs configured. Adding to that, over time the OS or required software may change slightly and render this method either obsolete or wrong! Which is why keeping up with all of this can be a challenge. However, I hope this tutorial helped you at least dip your toes into Linux just a bit. Plus, if you or your organizations aren’t using Linux machines today, this will help get the machines at least on your domain for testing.

Bootnote: Using a desktop version of Ubuntu

If you want to stand up a desktop version of Ubuntu for your testing, this guide will still work. There’s just a few slight things you need to change or be aware of:

1. The desktop version of Ubuntu has a network manager that will override whatever you change in your /etc/network/interfaces file. You need to use the GUI to configure your network settings, but the same settings still apply.
2. During the software package installation step, you might get a pop-up asking about your realm/domain. You’ll enter your domain name in ALL CAPS. It will then ask you for your domain controller info. Caps don’t matter there.
3. To enable domain user logins in the desktop manager, you also need to edit one more configuration file. To edit the file, you’ll run:

   sudo nano /usr/share/lightdm/lightdm.conf.d/50-ubuntu-conf

   Then, you need to add the following two lines to this file (at the end is fine)

   # /usr/share/lightdm/lightdm.conf.d/50-ubuntu.conf      
   ...      
   greeter-show-manual-login=true       
   greeter-hide-users=true