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.

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

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 following command. You must authenticate using an AD account that has sufficient privileges in AD to join a new machine to the domain.

Specifically, this command creates a new computer account in AD, create the /etc/krb5.keytab host keytab file, and configure the domain in /etc/sssd/sssd.conf:

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 need to check with a domain administrator that you have sufficient permissions to join Linux machines to your domain.

If you receive an error, "KDC reply did not match expectations," then you may not have specified the correct realm name for the user. Realm names are case-sensitive, usually uppercase, and can be identified with the command realm discover contoso.com.

SQL Server uses SSSD and NSS for mapping user accounts and groups to security identifiers (SID's). SSSD must be configured and running in order for SQL Server to create AD logins successfully. Realmd usually does this automatically as part of joining the domain, but in some cases you must do this separately.

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.

Create AD user for SQL Server and set SPN

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:

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).

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.

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 need to run the setspn command again with the new port number. You also need to add the new SPN to the SQL Server service keytab by following the steps in the next section.

Configure SQL Server service keytab

Check the Key Version Number (kvno) for the AD account created in the previous step. Usually it is 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:

SPNs can take several minutes to propagate through your domain, especially if the domain is large. If you receive the error, "kvno: Server not found in Kerberos database while getting credentials for MSSQLSvc/**<fully qualified domain name of host machine>**:**<tcp port>**@CONTOSO.COM", please wait a few minutes and try again.

Create a keytab file with ktutil for the AD user you created in the previous step. When prompted, enter the password for that AD account.

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

Add the machine account to your keytab with ktutil. The machine account (also called a UPN) is present in /etc/krb5.keytab in the form "<hostname>$@<realm.com>" (e.g. sqlhost$@CONTOSO.COM). We will copy these entries from /etc/krb5.keytab to mssql.keytab.

sudo ktutil
# Read all entries from /etc/krb5.keytab
ktutil: rkt /etc/krb5.keytab
# List all entries
ktutil: list
# Delete all entries by their slot number which are not the UPN one at a
# time.
# Warning: when an entry is deleted (e.g. slot 1), all values slide up by
# one to take its place (e.g. the entry in slot 2 moves to slot 1 when slot
# 1's entry is deleted)
ktutil: delent <slot num>
ktutil: delent <slot num>
...
# List all entries to ensure only UPN entries are left
ktutil: list
# When only UPN entries are left, append these values to mssql.keytab
ktutil: wkt /var/opt/mssql/secrets/mssql.keytab
quit

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:

Optional: Disable UDP connections to the domain controller to improve performance. In many cases, UDP connections will always fail when connecting to a domain controller, so you can set config options in /etc/krb5.conf to skip UDP calls. Edit /etc/krb5.conf and set the following options:

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. Consider the following examples:

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-host.contoso.com

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 (e.g. "mssql-host.contoso.com") by specifying Windows Authentication in the Connect to Server dialog.