Setting up Windows Authentication on SQL Server 2017 Linux

In this post, we’re going to cover adding a standalone instance of SQL Server 2017 on Linux to an Active Directory Domain, so that we can use Windows authentication. We’ll mostly be following documentation provided by Microsoft, with just a few deviations.
I see this as being one of the more common use-cases for customers looking to integrate SQL Server on Linux, and is actually fairly straight forward. The process we’ll follow here is a bit more long-winded than just copy and pasting from the Microsoft documentation, but I feel it’s important to take your time and understand what you’re doing the first few times.

In part II of this post, due out soon, we’ll take this node and add it to an Availability Group, so stay tuned…

Here’s our environment setup:

  • Server OS: CentOS-7-x86_64-Everything-1708, with Infrastructure Server software selection chosen. Get it here
  • Virtualization: Hyper-V 2016 host
  • Server name: sqllinux01
  • Server IP: 192.168.80.10
  • Domain Name: ufp.cloud (AD domain name)
  • DNS Servers: 192.168.5.1, 192.168.5.2 (AD domain controllers)
  • Local User: dbadave (We’ll log in as this user, and use it to run commands)
  • Domain Admin User Name: dbadave@ufp.cloud (Used to join machine to AD domain)
  • SQL Service Account: sqlserver@ufp.cloud (we’ll use this for the SPN creation)

Assumptions:

  • Working, reasonably healthy Active Directory Infrastructure
  • VM built; OS installed and sitting on a login screen
  • Network Time enabled during installation
  • All IP, DNS, Hostname, and Search domain configuration done during installation ( screen shot below)
  • Network interface enabled on boot (not enabled by default… Hmm…)
  • Non-root user dbadave created during installation, and made administrator (checkbox on installation)
  • All traffic allowed between subnets on the Hyper-V 2016 host
  • Using Documentation from Microsoft; Available here
  • Using PuTTY as our terminal. Get it here

For reference, here’s a screenshot showing my network config during installation:

One very important note:
We’re using CentOS here, due to Red Hat Enterprise Linux (RHEL) requirements for having a support subscription to access certain packages that I need to build out Availability Groups during SQL Saturdays and other sessions. However, CentOS is almost identical to RHEL, just without the branding and support requirements. Certainly close enough to follow the same documentation. Unfortunately, it’s not on the list of fully supported operating systems for SQL Server 2017 on Linux – probably due to lack of paid support.
While CentOS works great for doing demos, please make sure you use supported OS’s in production.

Alright; let’s get started!

First thing we’ll do is use PuTTY to ssh in to the freshly built server, and log in as the User created during setup (dbadave)

OK; looks pretty empty; not much going on here. Let’s change that!

First thing I like to do is update, so let’s do that now.

Being a Linux box, it’s going to ask you for your password. This distribution also likes to quote Spider-Man at you. Or was uncle Ben a Unix geek? The world may never know.

Given the large amount of updates I had to apply (223, in this case) I opted to reboot. Unless it prompts you, its generally optional tho.
Now that updates are out of the way, let’s start installing the required packages to get AD Auth working.

Per documentation for RHEL, we should just need the following packages:
realmd and krb5-workstation

Easy enough, let’s install them:

And we get presented with the following:

What this is telling us is that the package manager found some dependencies that need to be squared away before thoe two packages can be installed. Pretty smart! let’s just answer ‘Y’ here to allow this to happen. We could have just run the yum command with the the -y flag to bypass the permission part, but I think it’s a good idea to understand what’s going on. After you’ve done this a few times, you’ll be set to just skip the extra messages.

After input, the package manager immediately goes to work, and finishes rather quickly:

Everything looks good here, so on to the next step. We’ve already addressed the network configuration step listed next in the Microsoft documentation during installation, so let’s skip that step.

Step 4: Join the domain

This is where we’ll use the Realmd package we downloaded earlier
This command tells our server to discover how to connect to our domain through DNS, and join the domain

And here’s what we get back:

So far so good – Realm discovered our domain just fine. Now it’s asking us to enter in our domain user password to complete the process, so let’s do that. Here’s what we get back:

Woah! our first error!
None of the dependencies were listed in previous steps, however there is a note that seems to fit our situation:
“If you see an error, “Necessary packages are not installed,” then you should install those packages using your Linux distribution’s package manager before running the realm join command again.

OK, no problem. Let’s install the packages it is asking us to install:

And here’s what we get back:

Wow, that’s a lot of additional packages… This is pretty common with Linux, as dependencies can have dependencies.
Well, we obviously need them, so let’s go ahead and  respond with ‘Y’. We’ll see lots of text scroll by as before, but we’ll eventually see this screen:

OK, everything looks good; Let’s try our realm command again:

Same as before, our domain is found, and we’re prompted to enter our domain user password.
However, this time we don’t get an error:

Notice the message above “Successfully enrolled machine in realm”? That’s the realm equivalent of “Welcome to the ufp.cloud domain!” you get on Windows machines, just with less popups and fanfare. Our Linux machine is now on the Active Directory domain.

Just for grins, let’s check Active Directory Users and Computers, and see if we can see the computer object:
…And sure enough its there, just as we’d expect with a Windows host

Ok, now let’s validate connectivity with the domain, and make sure we’re able to get our all-important Kerberos ticket. We’re going to run 3 commands at once here, since they’re all related:

  • id returns user id and group information about our user
  • kinit obtains and caches our ticket. *Note the UPPERCASE domain name*
  • klist returns information about the kerberos ticket we grabbed from kinit

So, let’s see what we get:

Alright – looks like we can authenticate on the domain, and get a kerberos ticket. Good deal. Everything is looking great. Now let’s go ahead and get SQL Server 2017 installed. We could have done it earlier, but I wanted to get the domain joining out of the way

This is a very easy process. Our first step is to install the Microsoft Repository on the server. This tells the server how to find the SQL Server packages to install. To do that, we download the .repo file, and put it in the right directory. Here’s the command:

After we run it, we see this. This is the closest that we get to a confirmation.

Alright, now let’s install SQL Server 2017 on Linux:

After a bit of text scrolls by, we’ll see this.

One interesting thing here is that the entire SQL Server 2017 installation is only 167 Megabytes. Granted, this is just for the database engine, but still impressively small. Anyway, as before, let’s just respond with ‘Y’.

After the package downloads, you may see this prompt. This is asking you if it’s OK to install the Microsoft key. Answer ‘Y’ here, too.

After a few moments, you should see this, which lets you know installation was successful, but there’s still more to do

Well, might as go as we’re told. Let’s run the setup command and finish configuration. To do that, we run:

We’re now asked to choose our edition of SQL Server 2017. Since this instance will be part of my Availability Group demo, I’m just going to choose ‘Evaluation”. I may be lazy, but even I can get a demo out in 180 days. let’s just enter ‘1’ here. It will then ask us to accept the EULA, then choose and confirm your sa password . So, lets do that, too. After a few moments, it will finish up and tell you SQL Server is installed:

We now have a fully functional SQL Server installation, and a Linux server that can communicate on the domain.
Now it’s time to combine the two and get something more useful.

Our next step is to create a Service Principal Name (SPN) for the instance. We do this on the Windows side, in an elevated command prompt, using the setspn command. We need to provide the FQDN for the Linux Server, as well as the domain account we want to use for SQL Server.

For our particular situation, our SPN command will look like this:

setspn -A MSSQLSvc/sqllinux01.ufp.cloud:1433 sqlservice

Running the command gives us this:

All right, command worked. So far so good. I;m a trust, but verify guy though. So let’s make sure we see the right SPN by using the setspn -l command, passing in the domain account – and see what we get:

Ok – we’ve validated that the SPN is in place as we’d expect. We’re at the home stretch – just a bit more configuration to do. First, we need to validate the key version number, or knvo, for the service account. To do that, we run the two commands below.

as we learned earlier, kinit obtains and caches the kerberos ticket – although this time we’re running it for a different account, so be sure to have the password for this account handy. knvo will return the key version number needed to complete the rest of the setup. The knvo is generally 2, but we have to validate

Running the command and entering the password needed gives us this:

Alright. Now that we’ve got that out of the way, lets create the keytab file that will tell the SQL Server service how to use the SPN we created earlier. To do that, we use the ktutil command.  Let’s use these commands to get us going. You have to run these commands interactively, as opposed to just copying and pasting the whole block. Also be careful about entering in the passwords when prompted, as ktutil does not check if the password is actually valid

Once that’s all done, we need to allow the mssql:mssql user/group that gets created on the linux host after you install SQL Server 2017 to have permissions to read the file. By default, it’s only readable by the root user. That’s fairly straight forward to do, just run the commands.

Alright – Now the final thing we need to do to get this thing working. We need to tell SQL Server that we want it start up with kerberos support, and tell it how to find the keytab file we created earlier, and then to restart.

And we’re done! That’s the end of the configuration needed on the host.

Now let’s test things. First, let’s connect to the instance using the SQL Server instance using sa account, and the password we created earlier. After we connect, let’s try and create a windows user, and then try to connect using my domain account.

When we connect to this server from now on, we need to make sure we need to that we use the fully qualified domain name, otherwise we’ll have errors when trying to connect with windows authorization. In our case it’s sqllinux01.ufp.cloud.

So, let’s see if this works

First, let’s connect to the instance using the sa account, using sqlcmd from my windows 10 machine

OK; that worked as expected. We can log in as the SA account. Now let’s create a new user from Windows. Just to make things a bit more interesting, let’s use a different user than any of the others we’ve tested with. This time, let’s use [UFP\dave], and make them (me?) a sysadmin:

All right! Everything looks like it worked. Now let’s test the login using Windows authentication:

Wow, looks like we have Windows authentication! Before we get too excited, let’s try with SSMS:

…And we’re in!

We now have a fresh install of Linux and SQL Server 2017, with Windows authentication working! Time for a drink, heh

My next post will show how to add this node, and two others like it, to a 3-node availability group. I’ll also show a few quicker ways for us to do what we did today with setting up Windows authentication. Just this first time we discussed it I wanted to do it the longer way just so we could cover all the steps.

Anyway, thanks for reading – I know it’s a long one.

If you have any questions,  or have suggestions for things you’d like to see covered, please reach out!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.