Using AD Authentication in Azure Data Studio on a Non-Windows, Non-Domain Machine

This ain’t your daddy’s Windows-only world

Time was, if you were a SQL Server-based administrator/analyst/developer you probably were locked into using a Windows-based machine. Likewise, if you worked for an organization that had SQL Server running, you probably were subject to being part of an Active Directory domain. That probably still holds true for my totally-made-up-just-now statistic of 80-90% of the general SQL Server users out there. Granted, I’m totally making this up but if you ever make a trip to a decent sized technical conference centered around SQL Server, I think if you took a straw poll, I bet I’d be closer than not.

Today though, we live an increasingly cross-platform and cloudy world. It’s getting less and less important what kind of hardware you’ve got on your desk and more about where the things you’re trying to connect to are. You need not take my word for it; take a look at how many products and components now have been ported to Linux and/or Mac. One such product is Azure Data Studio. It can run on Windows, on a Mac, and even on Linux with an appropriate desktop experience installed.

Who are you?

If you work for a company, chances are you’re subject to some kind of network identity management system. Most often, this is Microsoft’s Active Directory. And, given such a system, you probably have access defined to things like SQL Server via your domain credentials (probably through group membership). When you’re using Active Directory to manage user identities and devices like you company computer, the simple act of logging in successfully each day handles a lot of things behind the scenes as to determining who you are, what groups you belong to, and other domain-related features like group policies.

But what if you’re using a computer that isn’t tied to (or even knows about) your domain? Maybe you’ve got a Mac (like me) or a Linux PC that isn’t joined to the domain. A lot of people will just create a SQL Server-based login and use that, but what if you can’t (or don’t want) to enable SQL authentication? Are you out of luck?

Turns out that, no, you’re not.

No ticket, no laundry

SQL Server supports different kinds of authentication mechanisms and protocols: the older NTLM protocol, and Kerberos. A lot of people cringe when you mention Kerberos because, well, Kerberos is hard. It’s arcane, it’s complex, and it’s hard to even describe unless you use it on the regular.

Simply put, it’s a ticketing and key system: you, a user, requests a ticket from a store, usually by authenticating to it via a username and password. If you succeed, you get a ticket that get stored within your local machine. Then, when you want to access a resource (like a SQL Server), the client re-ups with the store you got your initial ticket from (to make sure it’s still valid), and you get a “key” to access the resource. That key is then forwarded onto the resource, allowing you to access the thing you were trying to connect to. It’s way, way more complex than this, with lots of complicated terms and moving parts, so I’m doing a lot of hand-waving, but that’s the core of the system. If that kind of stuff excites you, go Google it, and I promise you’ll get more than you ever bargained for.

Back on topic: so when you log into an Active Directory domain, this is managed for you, even on non-Windows machines. It’s all part of your handshake with the domain controller. But if you don’t authenticate via Active Directory, there’s no reason you can’t do this manually. You just have to manually request your ticket (and authenticate in the process).

“One, please” (demo time)

Okay, so what if you wanted to request a ticket on your own? Well, before we get to this demo, there’s a couple really big assumptions I’m going to make:

• You’re currently on the same local area network as a domain controller. Obviously, you need to be somewhere that has a place that you can get a ticket and key from.
• That same network has a properly configured DNS zone for your domain. It’s always DNS, isn’t it? In most Active Directory environments, DNS plays really well with AD because you usually configure one when you configure the other. That’s not always the case, and if you have some other appliance or system to manage your DNS, this might not work for you (but see below for some workarounds). You can test this really easily by just pinging your domain name (for instance, if I ping boatmurder.net should return an IP of one of my domain controllers.) If this doesn’t work, well, just keep reading.
• Your SQL Server(s) have Service Principal Names (SPNs) set up. This is Kerberos, after all.
• You actually have login credentials, i.e. you have a login, you just didn’t log into the domain with them on your current machine.
• You’re using Linux or Mac OS X (this IS possible on Windows, but, well, read on to the end)

Got all that? Cool. Let’s give this a whirl. First, we need to request our Kerberos ticket. To do that, we’re going to use a built-in utility called kinit.

To use kinit you need to get to a terminal window, and then run the utility. Then, you’ll pass in your domain user name followed by an “at” (@) sign, and your domain name in ALL CAPS (it’s a convention). So, let’s say I wanted to get a ticket to my local domain boatmurder.net:

As you can see, the terminal will prompt me for a password. If it worked, you won’t see… anything. To prove that you have an actual Kerberos ticket, though, you can use a second utility: klist

klist lets you see your actual Kerberos ticket info, and as you can see from the GIF it looks like I’ve got an actual ticket. Pretty sweet. Now, armed with this info, I can try logging into a SQL Server using integrated authentication, even on my Mac:

If you got a problem yo I’ll solve it…

Since this is Kerberos we’re talking about, this simple walkthrough might not have worked for you. Maybe you can’t resolve your domain/domain controller correctly. Or Maybe you’re using a Windows PC and want this same functionality. Let me walk you through a few workarounds you can try if this example didn’t work:

Manually specifying your domain controller(s)

The biggest hurdle you might have to getting this to work is not being able to resolve your domain controllers (or, in the parlance of Kerberos, your Key Distribution Center, or KDC).

The good news here is that this is all manually configurable. There’s a file known as krb5.conf that can contain all the proper info for routing your requests. The ticklish part is knowing where to find it:

• On Linux, you can usually find it at /etc/krb5.conf
• On Mac OS X you can find it… nowhere. It doesn’t exist and you have to create it. There’s a couple different places you can create this file, and it mostly revolves around whether you want everyone who uses your Mac to be able to route these requests, or just certain users. To keep it simple, I recommend just throwing it into your /etc/ folder. Enjoy your time in trying to quit vim!

Your krb5.conf file is where you can map out your Domain Controller KDC lists. And guess what? If you travel between a lot of different domains, you can keep this file up to date. For instance, here’s what my local krb5.conf file looks like:

[libdefaults]
default_realm = BOATMURDER.NET

[realms]
BOATMURDER.NET = {
    kdc = mac-ad-01.boatmurder.net
}
BOATMURDER.NET = {
    kdc = nuc-dc-01.boatmurder.net
}

Let’s break this down:

• At a minimum, you’re krb5.conf file is going to have two main sections, the [libdefaults] and [realms] sections. All you really need for the first part, or [libdefaults] is what you want your Kerberos client to append to any request. So I just specify kinit drew it’s going to assume kinit drew@BOATMURDER.NET which is handy if you hate typing all caps.
• Your [realms] section is where you’re going to list out your domains. Inside this section, you need to list the domain name you want to authenticate to (again, the convention is all-caps), and then the fully-qualified domain name(s) of the domain controllers where you’re going to try to request a ticket from. You can have multiple servers here, and you can even specify ports. The official docs also list out a bunch of different options, too, but most times you can get by with just listing a KDC.

Sharp-eyed readers will also note that my config file has the same domain listed twice. This is also perfectly fine; the reason for this is my home lab’s domain is the same domain name I use for my local VM’s for demos. You might have a slow response if it has to try multiple kdc’s but it still works. The biggest catch here is that your machine needs to be able to resolve whatever host you specify, and the kdc server should be serving tickets for the realm you specify it in.

Missing SPNs

This whole exercise won’t work unless you’ve taken the time to set up Service Principal Names (SPNs) for your SQL Servers. Chances are you’re set here, but if not, there’s a couple different ways to handle this:

• I highly recommend checking out dbatools to use some very easy and very efficient PowerShell to set up your SPNs. There’s a whole big write-up about how to manage SPNs using dbatools, which you can read about here.
• If you prefer a more GUI-friendly experience, Microsoft has a Kerberos tool for just this purpose.
• And if you’re so old school you burned the school down, you can just use good ol’ setspn.exe. Here’s the official skinny from MSDN.

Installing Kerberos tools on Linux / Mac OS X

Of course, you won’t be able to run these commands on Linux and/or your Mac if you don’t have these tools installed. By default, your installation of Mac OS X should have these tools. Your Linux install may vary, but you should be able to install them if you don’t have them by using your’s distribution’s package manager and installing krb5-workstation package.

Using kinit and klist on Windows

If you’re using Windows and want this kind of functionality, you can get it, but you need some utilities that don’t come standard. To get them, you need to… install Java SE. Yeah. Sorry. Once you have it installed, you may want to add their paths to your PATH environment variable(s) so you don’t have to navigate to these directories over and over to get tickets.

Kerberos is important

Everyone has a Kerberos joke, and a lot of time it is justified. Given that we’re seeing more and more tools exist on multiple platforms, a secure, inter-operable authentication scheme like Kerberos matters. It behooves you to spend some time getting familiar with Kerberos, especially if you or your organization starts to explore running SQL Server on non-Windows servers and you plan on doing domain based authentication. This is a good place to start to get that comfort level. Let me know how you make out!