Connecting a SQL Server client on Linux using Active Directory authentication

or: How I learned to stop worrying, and love all-caps domain names.

I’m a complete beginner at Linux, so I should preface this post with the fact that these are my humble notes after hours of pulling my hair. It’s not really a fully-fledged how-to article, and there are lot of things I’m not covering. But I figured it may help someone out there at some point.

Also, different Linux distros and versions will behave differently, so your mileage will most likely vary.

For the purposes of this post, I’m on Red Hat Enterprise 8.3.

Installing the Kerberos stuff

Yum is a package manager that will install components and programs from a single command line input. We’re going to add a bunch of packages to make all of the nuts and bolts work.

$ sudo yum install sssd realmd oddjob oddjob-mkhomedir adcli samba-common samba-common-tools krb5-workstation openldap-clients policycoreutils-python krb5-libs

Network setup

Next, make sure that your machine can reach the DNS server of your Active Directory domain. You may have to reconfigure your VM to use another, non-public, DNS server. Verify the results by using nslookup or ping:

$ nslookup my-dc.my-domain.local.

Server:     10.1.2.3
Address:    10.1.2.3#53

Name:       my-dc.my-domain.local.
Address:    10.1.2.4

$ ping my-dc.my-domain.local.

If you’re in a segmented/firewalled network environment, you’ll also want to check that your network allows all the requisite traffic through.

DNS: 53 (tcp, udp) from all machines to DNS server
Kerberos: 88 (tcp, udp) from all machines to domain controller(s)
NTP: 123 (udp) both directions between all machines and domain controller(s)
LDAP: 389 (tcp, udp) from all machines to domain controller(s)
LDAP: 3268 (tcp) from all machines to domain controller(s)

The NTP service synchronizes time between machines, which is essential for Kerberos to work. The clocks of two machines cannot be off by more than five minutes, or the authentication will fail.

Adding the Linux machine to your domain

You can check that your machine can discover the domain…

$ sudo realm discover MY-DOMAIN.LOCAL

my-domain.local
  type: kerberos
  realm-name: MY-DOMAIN.LOCAL
  domain-name: my-domain.local
  configured: no
  server-software: active-directory
  client-software: sssd
  required-package: oddjob
  required-package: oddjob-mkhomedir
  required-package: sssd
  required-package: adcli
  required-package: samba-common-tools

… or just go ahead and join it right away:

$ sudo realm join --user=my.name MY-DOMAIN.LOCAL

The account used to join the machine needs to be a domain account that has permissions to add computers to the domain. This is often, but not necessarily, a domain admin.

At any time, you can verify that you’re joined to a domain using:

$ sudo realm list

MY-DOMAIN.LOCAL
  type: kerberos
  realm-name: MY-DOMAIN.LOCAL
  domain-name: my-domain.local
  configured: kerberos-member
  server-software: active-directory
  client-software: sssd
  required-package: oddjob
  required-package: oddjob-mkhomedir
  required-package: sssd
  required-package: adcli
  required-package: samba-common-tools
  login-formats: %[email protected]
  login-policy: allow-realm-logins

If you change your mind, you can leave the domain with:

$ sudo realm leave

Configuration

Next up, we’ll be reviewing some config files. I’m a nano type of guy myself, but any of the popular text editors will do here:

$ sudo nano /etc/sssd/sssd.conf

I found that the default settings in the file work for me, but you may have to tweak them a little for your machine. This article goes through most of the settings in some detail. If you make any changes to the config file, remember to restart the service:

$ systemctl restart sssd

Installing sqlcmd for Linux

The download instructions for sqlcmd on Linux depend on your distribution. See this page.

Authenticating and connecting

To make the magic work, we first need to acquire a Kerberos token.

$ kinit [email protected]

You can view your current Kerberos tickets using:

$ klist

Ticket cache: KCM:1001
Default principal: [email protected]

Valid starting     Expires            Service principal
04/15/21 15:21:23  04/16/21 01:21:23  krbtgt/[email protected]
	renew until 04/22/21 15:21:22

And now, you can run sqlcmd to connect to your SQL Server instance.

The -E switch tells sqlcmd to use Windows/Active Directory authentication.

$ sqlcmd -E -S SQL01.my-domain.local.

1> SELECT SUSER_SNAME() AS SayMyName;
2> GO

SayMyName
--------------------------------------
MY-DOMAIN\my.name

(1 rows affected)

Gotchas

After a couple of long walks and hours of googling, I finally found that the “realm” needs to be specified in all-caps – both in the config files, and when given as an argument to “kinit” and other commands. Otherwise, the authentication process will fail.