Published in Articles on by Michiel van Oosterhout ~ 8 min read

Using macOS as a developer in an enterprise environment built on Windows Server means you may need to authenticate to servers using the Kerberos protocol at one point or another. This is because Windows Server and Active Directory use Kerberos 1. Having struggled with this recently, I decided to write this guide for connecting to a SQL Server using Kerberos.

Hercules and the three-headed dog *Cerberus*
Hercules and the three-headed dog Cerberus Felice Giani, via Look and Learn

About Kerberos

Kerberos is a client-server authentication protocol, and it can be used to connect to SQL Server using an Active Directory credential from a non-Windows operating system such as macOS. This involves a trusted 3rd-party (the authentication server). The current version of Kerberos is version 5, and is described in RFC 4120.

Tickets

The client (a principal) requests a credential to be used with the SQL Server server (also a principal) from the authentication server. The authentication server has a database of principals, which in our case, is the Active Directory database. This server and its database is said to be in a realm, and the realm is part of a principal's identifier.

The client receives either:

  • a ticket for the SQL Server server from the authentication server,
  • or a ticket for a ticket-granting server.

We'll call the former a normal ticket (not an official term). The latter ticket is called a ticket-granting ticket, and can be used to ask the ticket-granting server for a normal ticket for the SQL Server server.

Exchanges

Communications between client and server are known as exchanges, and there are three such exchanges:

  • authentication server exchange: to obtain a normal ticket or a ticket-granting ticket
  • ticket-granting server exchange: to obtain a normal ticket (requires a ticket-granting ticket)
  • client/server exchange: requires a normal ticket

Configuration on macOS

With all of that theory out of the way, we need to configure Kerberos on our macOS computer. This is done in /etc/krb5.conf. We will add realms that contain servers we'd like to use to this file. The file uses sections ([section]) and relations (key-value pairs, key = value, optionally nested key = { ... }).

We can add the default_realm relation to the [libdefaults] section, but for the sake of learning it helps to do this only when everything is working, because it forces you to be specific in your command-line commands at the start.

First we add the realm to the [realms] section, with the address of the KDC (i.e. the authentication server and ticket-granting server):

[realms]
EXAMPLE.LOCAL = {
kdc = dc.example.local
}

The address of the KDC is made known to clients via a special type of DNS record, the SRV record. These records are service-, protocol- and domain-specific, meaning they have a key like _service._protocol.domain., and they point to an A record. For Kerberos, the key to look for is _kerberos._tcp.example.local..

We can use nslookup on macOS to query for Kerberos SRV records:

nslookup -type=srv _kerberos._tcp.example.local 10.0.0.1

The output of the nslookup command should give you one or more SRV records, if not, you will have to ask your network administrator for the KDC address.

Configure the realm's kdc relation in the Kerberos configuration file with the fully-qualified domain name of the KDC.

Getting our first ticket

Now we can use the kinit command to obtain a ticket. But before we do, we will set an environment variable that will ensure full trace information is printed to stderr when running kinit. (Only stdout output will be provided in the examples below.)

export KRB5_TRACE=/dev/stderr

Running just kinit you may receive a prompt like this:

> kinit
michiel@LOCAL's password:

And if you provide a password (any password) you'll see this output:

kinit: krb5_get_init_creds: unable to reach any KDC in realm LOCAL, tried 0 KDCs

From this we can surmise that by default, my principal name is michiel@LOCAL. This is probably not my principal name in the database of the authentication server of the EXAMPLE.LOCAL realm. But doesn't matter, since kinit did not even try to connect to that server, it tried to find a server for the LOCAL realm.

Remember I said we should be explicit while learning? This is our first opportunity:

kinit michiel.van.oosterhout@EXAMPLE.LOCAL

When you see no output after typing your password, then the command completed successfully. (You can always run echo $? to check that the exit code of kinit was 0.) Otherwise, you should be able to use the output on stderr to understand the problem.

Now we can add the default realm to our configuration, so that we don't have to type it on the command-line:

[libdefaults]
default_realm = EXAMPLE.LOCAL

So from now on I don't need to be explicit about this realm, I can type kinit michiel.van.oosterhout and @EXAMPLE.COM is implied.

Checking the ticket

When the kinit command completes without error, you will have received a ticket. Typically, this is a ticket for the ticket granting server. You can check your tickets with klist:

> klist
        Principal: michiel.van.oosterhout@EXAMPLE.LOCAL

  Issued                Expires               Principal
Sep 25 14:35:42 2023  Sep 26 00:35:38 2023  krbtgt/EXAMPLE.LOCAL@EXAMPLE.LOCAL

This indicates that I have a ticket for the server krbtgt/EXAMPLE.LOCAL@EXAMPLE.LOCAL. The name of the server starts with krbtgt, so it looks like the ticket is for a Kerberos ticket-granting server.

Getting a 'normal' Kerberos ticket for a SQL Server server

The ticket for the ticket-granting server can only be used for that server. To get a normal ticket for some other server, we need to request it from the ticket-granting server using the kgetcred command. We need to specify the principal identifier of the service for which we want to obtain a ticket.

For SQL Server, there is a default naming scheme for the SPN part of the principal identifier that we can use: MSSQLSvc/<FQDN>, where <FQDN> should be replaced by the fully-qualified domain name of the SQL Server server.

Combine the SPN with the realm by adding the @EXAMPLE.LOCAL suffix to get the principal identifier. Use this to get a ticket for the SQL Server server:

kgetcred MSSQLSvc/sql297b.example.local@EXAMPLE.LOCAL

When the command exits without any output (and has exit code 0), then you can use klist again to check the normal ticket:

> klist
        Principal: michiel.van.oosterhout@EXAMPLE.LOCAL

  Issued                Expires               Principal
Sep 25 14:39:32 2023  Sep 26 00:39:32 2023  krbtgt/EXAMPLE.LOCAL@EXAMPLE.LOCAL
Sep 25 15:01:45 2023  Sep 26 00:39:32 2023  MSSQLSvc/sql297b.example.local@EXAMPLE.LOCAL

As you can see, the ticket for the SQL Server server expires at the same time that the ticket-granting ticket expires. This implies that the ticket-granting ticket was used to obtain the ticket.

With all of this working, we can be sure that our Kerberos configuration is correct, our username and password are correct, and we are authorized to access the SQL Server server. Now we can configure our database management tool (e.g. Microsoft's Azure Data Studio or JetBrains' DataGrip) to obtain the ticket for the SQL Server server itself. Let's look at the configuraton for DataGrip as an example

Configuring JetBrains DataGrip

DataGrip is a Java application, and it uses JDBC to connect to databases. JDBC uses drivers, Java packages specific to some type of database server (e.g. SQL Server). We will be using the Microsoft SQL Server driver from Microsoft (class com.microsoft.sqlserver.jdbc.SQLServerDriver).

We will create a new Data Source. We give it a name and optionally a color (helpful to distinguish different environments), select Microsoft's SQL Server driver, set the Host field, and then select Kerberos in the Authentication field.

As we've seen above, when acquiring a ticket for the SQL Server server, we did not need to specify our username and/or password, because we were using our ticket-granting ticket. So we will not enter anything in the User or Password fields.

This should be enough to connect to SQL Server from DataGrip on macOs.

Ticket Viewer.app

macOS has a built-in application to get a ticket-granting ticket. It's called Ticket Viewer, and it can take the place of running the initial kinit command on the command-line. You can add multiple identities, and it can store the password for each identity in the macOS keychain. After that a single click can renew or refresh a ticket-granting ticket.

Summary

When you have difficulty connecting to SQL Server from some database management software (DBMS) running on macOS using Kerberos, it helps to use the Kerberos command-line programs kinit and kgetcred, along with nslookup to ensure you are able to authenticate and are authorized. Once you can obtain a Kerberos ticket for the SQL Server this way, any DBMS that supports Kerberos should be able to connect to that SQL Server as well.

  1. Kerberos Authentication Overview ↩︎