Active Directory Authentication with SQL Server on Linux

THIS TOPIC APPLIES TO: noSQL Server Windows yesSQL Server Linux noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

This document explains how to configure SQL Server on Linux to support Active Directory (AD) authentication, also known as integrated authentication. AD Authentication enables domain-joined clients on either Windows or Linux to authenticate to SQL Server using their domain credentials and the Kerberos protocol. AD Authentication has the following advantages over SQL Server Authentication:
• Users authenticate via single sign-on, without being prompted for a password.
• By creating logins for AD groups, you can manage access and permissions in SQL Server using AD group memberships.
• Each user has a single identity across your organization, so you don’t have to keep track of which SQL Server logins correspond to which people.
• AD enables you to enforce a centralized password policy across your organization.

Prerequisites

Before you configure AD Authentication, you need to:

Important

At this time, the only authentication method supported for database mirroring endpoint is CERTIFICATE. WINDOWS authentication method will be enabled in a future release

Step 1: Join SQL Server host to AD domain

Numerous tools exist to help you join the SQL Server host machine to your AD domain. This walkthrough uses realmd, a popular open source package. If you haven't already, install both the realmd and Kerberos client packages on the SQL Server host machine using your Linux distribution's package manager:

# RHEL
sudo yum install realmd krb5-workstation

# SUSE
sudo zypper install realmd krb5-client

# Ubuntu
sudo apt-get install realmd krb5-user software-properties-common python-software-properties packagekit

If the Kerberos client package installation prompts you for a realm name, enter your domain name in uppercase.

Note

This walkthrough uses "contoso.com" and "CONTOSO.COM" as example domain and realm names, respectively. You should replace these with your own values. These commands are case-sensitive, so make sure you use uppercase wherever it is used in this walkthrough.

Run the following command to verify that the SQL Server host machine is configured to use the AD domain controller for as a DNS nameserver:

sudo realm discover contoso.com -v

If your domain is not found, you need to configure your SQL Server host machine to use your AD domain controller's IP address as a DNS nameserver. The specific steps to do this depend on your network device configuration, domain configuration, and Linux distribution. Here are some example approaches.

Example DNS configuration: Ubuntu

Edit the /etc/network/interfaces file so that your AD domain controller's IP address is listed as a dns-nameserver. For example:

<...>
# The primary network interface
auth eth0
iface eth0 inet dhcp
dns-nameservers **<AD domain controller IP address>**
dns-search **<AD domain name>**
Note

The network interface (eth0) might differ for differnet machines. To find out which one you are using, run ifconfig and copy the interface that has an IP address and transmitted and received bytes.

After editing this file, restart the network service:

sudo ifdown eth0 && sudo ifup eth0

Now check that your /etc/resolv.conf file contains a line like the following:

nameserver **<AD domain controller IP address>**

Example DNS configuration: RHEL

Edit the /etc/sysconfig/network-scripts/ifcfg-eth0 file (or other interface config file as appropriate) so that your AD domain controller's IP address is listed as a DNS server:

<...>
PEERDNS=no
DNS1=**<AD domain controller IP address>**

After editing this file, restart the network service:

sudo systemctl restart network

Now check that your /etc/resolv.conf file contains a line like the following:

nameserver **<AD domain controller IP address>**

Join the domain

Once you've confirmed that your DNS is configured properly, join the domain by running the command below. You'll need to authenticate using an AD account that has sufficient privileges in AD to join a new machine to the domain. Specifically, this command will create a new computer account in AD, create the /etc/krb5.keytab host keytab file, and configure the domain in /etc/sssd/sssd.conf:

sudo realm join contoso.com -U 'user@CONTOSO.COM' -v
<...>
 * Successfully enrolled machine in realm
Note

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.

If you receive an error, "Insufficient permissions to join the domain," then you will need to check with a domain administrator that you have sufficient permissions to join Linux machines to your domain.

Verify that you can now gather information about a user from the domain, and that you can acquire a Kerberos ticket as that user.

We will use id, kinit and klist commands for this.

id user@contoso.com
uid=1348601103(user@contoso.com) gid=1348600513(domain group@contoso.com) groups=1348600513(domain group@contoso.com)

kinit user@CONTOSO.COM
Password for user@CONTOSO.COM:

klist
Ticket cache: FILE:/tmp/krb5cc_1000
Default principal: user@CONTOSO.COM
<...>
Note

If id user@contoso.com returns, "No such user," make sure that the SSSD service started successfully by running the command sudo systemctl status sssd. If the service is running and you still see the "No such user" error, try enabling verbose logging for SSSD. For more information, see the Red Hat documentation for Troubleshooting SSSD.

If kinit user@CONTOSO.COM returns, "KDC reply did not match expectations while getting initial credentials," make sure you specified the realm in uppercase.

For more information, see the Red Hat documentation for Discovering and Joining Identity Domains.

Step 2: Create AD user for SQL Server and set SPN

Note

In the next steps we will use your fully qualified domain name. If you are on Azure, you will have to create one before you proceed.

On your domain controller, run the New-ADUser PowerShell command to create a new AD user with a password that never expires. This example names the account "mssql," but the account name can be anything you like. You will be prompted to enter a new password for the account:

Import-Module ActiveDirectory

New-ADUser mssql -AccountPassword (Read-Host -AsSecureString "Enter Password") -PasswordNeverExpires $true -Enabled $true
Note

It is a security best practice to have a dedicated AD account for SQL Server, so that SQL Server's credentials aren't shared with other services using the same account. However, you can reuse an existing AD account if you prefer, if you know the account's password (required to generate a keytab file in the next step).

Now set the ServicePrincipalName (SPN) for this account using the setspn.exe tool. The SPN must be formatted exactly as specified in the following example: You can find the fully qualified domain name of the SQL Server host machine by running hostname --all-fqdns on the SQL Server host, and the TCP port should be 1433 unless you have configured SQL Server to use a different port number.

setspn -A MSSQLSvc/**<fully qualified domain name of host machine>**:**<tcp port>** mssql
Note

If you receive an error, "Insufficient access rights," then you need to check with a domain administrator that you have sufficient permissions to set an SPN on this account.

If you change the TCP port in the future, then you will need to run the setspn command again with the new port number. You will also need to add the new SPN to the SQL Server service keytab by following the steps in the next section.

For more information, see Register a Service Principal Name for Kerberos Connections.

Step 3: Configure SQL Server service keytab

First, check the Key Version Number (kvno) for the AD account created in the previous step. Usually it will be 2, but it could be another integer if you changed the account's password multiple times. On the SQL Server host machine, run the following:

kinit user@CONTOSO.COM

kvno MSSQLSvc/**<fully qualified domain name of host machine>**:**<tcp port>**

Now create a keytab file for the AD user you created in the previous step. To do so we will use ktutil. When prompted, enter the password for that AD account.

sudo ktutil

ktutil: addent -password -p MSSQLSvc/**<fully qualified domain name of host machine>**:**<tcp port>**@CONTOSO.COM -k **<kvno from above>** -e aes256-cts-hmac-sha1-96

ktutil: addent -password -p MSSQLSvc/**<fully qualified domain name of host machine>**:**<tcp port>**@CONTOSO.COM -k **<kvno from above>** -e rc4-hmac

ktutil: wkt /var/opt/mssql/secrets/mssql.keytab

quit
Note

The ktutil tool does not validate the password, so make sure you enter it correctly.

Anyone with access to this keytab file can impersonate SQL Server on the domain, so make sure you restrict access to the file such that only the mssql account has read access:

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

Next, configure SQL Server to use this keytab file for Kerberos authentication:

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

Step 4: Create AD-based logins in Transact-SQL

Connect to SQL Server and create a new, AD-based login:

CREATE LOGIN [CONTOSO\user] FROM WINDOWS;

Verify that the login is now listed in the sys.server_principals system catalog view:

SELECT name FROM sys.server_principals;

Step 5: Connect to SQL Server using AD Authentication

Log in to a client machine using your domain credentials. Now you can connect to SQL Server without reentering your password, by using AD Authentication. If you create a login for an AD group, any AD user who is a member of that group can connect in the same way.
The specific connection string parameter for clients to use AD Authentication depends on which driver you are using. A few examples are below.

Examples

Example 1: sqlcmd on a domain-joined Linux client

Log in to a domain-joined Linux client using ssh and your domain credentials:

ssh -l user@contoso.com client.contoso.com

Make sure you've installed the mssql-tools package, then connect using sqlcmd without specifying any credentials:

sqlcmd -S mssql.contoso.com

Example 2: SSMS on a domain-joined Windows client

Log in to a domain-joined Windows client using your domain credentials. Make sure SQL Server Management Studio is installed, then connect to your SQL Server instance by specifying Windows Authentication in the Connect to Server dialog.

AD Authentication using other client drivers

• JDBC: Using Kerberos Integrated Authentication to Connect SQL Server
• ODBC: Using Integrated Authentication
• ADO.NET: Connection String Syntax