Tutorial: Use Active Directory authentication with SQL Server on Linux

Applies to: yesSQL Server (all supported versions) - Linux

This tutorial explains how to configure SQL Server on Linux to support Active Directory (AD) authentication, also known as integrated authentication. For an overview, see Active Directory authentication for SQL Server on Linux.

This tutorial consists of the following tasks:

  • Join SQL Server host to AD domain
  • Create AD user for SQL Server and set SPN
  • Configure SQL Server service keytab
  • Secure the keytab file
  • Configure SQL Server to use the keytab file for Kerberos authentication
  • Create AD-based logins in Transact-SQL
  • Connect to SQL Server using AD Authentication

Prerequisites

Before you configure AD Authentication, you need to:

Join SQL Server host to AD domain

Join your SQL Server Linux host with an Active Directory domain controller. For information on how to join an active directory domain, see Join SQL Server on a Linux host to an Active Directory domain.

Create AD user (or MSA) for SQL Server and set SPN

Note

The following steps use your fully qualified domain name. If you are on Azure, you must create one before you proceed.

  1. On your domain controller, run the New-ADUser PowerShell command to create a new AD user with a password that never expires. The following example names the account mssql, but the account name can be anything you like. You'll 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 optionally reuse an existing AD account if you know the account's password (which is required to generate a keytab file in the next step). Additionally, the account should be enabled to support 128-bit and 256-bit Kerberos AES encryption (msDS-SupportedEncryptionTypes attribute) on the user account.

  2. 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. 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
    setspn -A MSSQLSvc/**<netbios name of the host machine>**:**<tcp port>** mssql
    

    Note

    If you receive an error, Insufficient access rights, check with your domain administrator that you have sufficient permissions to set an SPN on this account. The account that is used to register an SPN will need the Write servicePrincipalName permissions. For more information, see Register a Service Principal Name for Kerberos Connections.

    If you change the TCP port in the future, you must 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.

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

Configure SQL Server service keytab

Configuring AD authentication for SQL Server on Linux requires an AD account (MSA or an AD user account) and the SPN created in the the previous section.

Important

If the password for the AD account is changed or the password for the account that the SPNs are assigned to is changed, you must update the keytab with the new password and Key Version Number (KVNO). Some services might also rotate the passwords automatically. Review any password rotation policies for the accounts in question and align them with scheduled maintenance activities to avoid unexpected downtime.

SPN keytab entries

  1. 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 commands:

    • The below examples assume the user is in the @CONTOSO.COM domain. Modify the user and domain name to your user and domain name.
    kinit user@CONTOSO.COM
    kvno user@CONTOSO.COM
    kvno MSSQLSvc/**<fully qualified domain name of host machine>**:**<tcp port>**@CONTOSO.COM
    

    Note

    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.
    The above commands will only work if the server has been joined to an AD domain, which was covered in the previous section.

  2. Using ktpass, add keytab entries for each SPN using the following commands on a Windows machine Command Prompt:

    • <DomainName>\<UserName> - Could be an MSA or AD user account
    • @CONTOSO.COM - Use your domain name
    • /kvno <#> - Replace <#> with the KVNO obtained in an earlier step
    • <StrongPassword> - Use a strong password
    ktpass /princ MSSQLSvc/<fully qualified domain name of host machine>:<tcp port>@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser <DomainName>\<UserName> /out mssql.keytab -setpass -setupn /kvno <#> /pass <StrongPassword>
    
    ktpass /princ MSSQLSvc/<fully qualified domain name of host machine>:<tcp port>@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser <DomainName>\<UserName> /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno <#> /pass <StrongPassword>
    
    ktpass /princ MSSQLSvc/<netbios name of the host machine>:<tcp port>@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser <DomainName>\<UserName> /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno <#> /pass <StrongPassword>
    
    ktpass /princ MSSQLSvc/<netbios name of the host machine>:<tcp port>@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser <DomainName>\<UserName> /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno <#> /pass <StrongPassword>
    
    ktpass /princ <UserName>@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto aes256-sha1 /mapuser <DomainName>\<UserName> /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno <#> /pass <StrongPassword>
    
    ktpass /princ <UserName>@CONTOSO.COM /ptype KRB5_NT_PRINCIPAL /crypto rc4-hmac-nt /mapuser <DomainName>\<UserName> /in mssql.keytab /out mssql.keytab -setpass -setupn /kvno <#> /pass <StrongPassword>
    

    Note

    The commands above allow both AES and RC4 encryption ciphers for AD authentication. RC4 is an older encryption cipher and if a higher degree of security is required, you can choose to create the keytab entries with only the AES encryption cipher. The last two UserName entries must be in lowercase, or the permssion authentication might fail.

  3. After executing the above command, you should have a keytab file named mssql.keytab. Copy the file over to the SQL Server machine under the folder /var/opt/mssql/secrets.

  4. Secure the keytab file.

    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
    
  5. The following configuration option needs to be set with the mssql-conf tool to specify the account to be used while accessing the keytab file.

    sudo mssql-conf set network.privilegedadaccount <username>
    

    Note

    Only include the username and not domainname\username or username@domain. SQL Server internally adds domain name as required along with this username when used.

  6. Use the following steps to configure SQL Server to start using the keytab file for Kerberos authentication.

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

    Tip

    Optionally, disable UDP connections to the domain controller to improve performance. In many cases, UDP connections consistently 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:

    /etc/krb5.conf
    [libdefaults]
    udp_preference_limit=0
    

At this point, you are ready to use AD-based logins in SQL Server.

Create AD-based logins in Transact-SQL

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

    CREATE LOGIN [CONTOSO\user] FROM WINDOWS;
    
  2. Verify that the login is now listed in the sys.server_principals system catalog view:

    SELECT name FROM sys.server_principals;
    

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 examples in the following sections.

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

Different from SQL Windows, Kerberos authentication works for local connection in SQL Linux. However, you still need to provide the FQDN of the SQL Linux host, and AD Authentication will not work if you attempt to connect to '.' ,'localhost','127.0.0.1',etc.

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

AD Authentication using other client drivers

The following table describes recommendations for other client drivers:

Client driver Recommendation
JDBC Use Kerberos Integrated Authentication to Connect SQL Server.
ODBC Use Integrated Authentication.
ADO.NET Connection String Syntax.

Additional configuration options

If you are using third-party utilities such as PBIS, VAS, or Centrify to join the Linux host to AD domain and you would like to force SQL server in using the openldap library directly, you can configure the disablesssd option with mssql-conf as follows:

sudo mssql-conf set network.disablesssd true
systemctl restart mssql-server

Note

There are utilities such as realmd which set up SSSD, while other tools such as PBIS, VAS and Centrify do not setup SSSD. If the utility used to join AD domain does not setup SSSD, it is recommended to configure disablesssd option to true. While it is not required as SQL Server will attempt to use SSSD for AD before falling back to openldap mechanism, it would be more performant to configure it so SQL Server makes openldap calls directly bypassing the SSSD mechanism.

If your domain controller supports LDAPS, you can force all connections from SQL Server to the domain controllers to be over LDAPS. To check your client can contact the domain controller over ldaps, run the following bash command, ldapsearch -H ldaps://contoso.com:3269. To set SQL Server to only use LDAPS, run the following:

sudo mssql-conf set network.forcesecureldap true
systemctl restart mssql-server

This will use LDAPS over SSSD if AD domain join on host was done via SSSD package and disablesssd is not set to true. If disablesssd is set to true along with forcesecureldap being set to true, then it will use LDAPS protocol over openldap library calls made by SQL Server.

Post SQL Server 2017 CU14

Starting with SQL Server 2017 CU14, if SQL Server was joined to an AD domain controller using third-party providers and is configured to use openldap calls for general AD lookup by setting disablesssd to true, you can also use enablekdcfromkrb5 option to force SQL Server to use krb5 library for KDC lookup instead of reverse DNS lookup for KDC server.

This may be useful for the scenario where you want to manually configure the domain controllers that SQL Server attempts to communicate with. And you use the openldap library mechanism by using the KDC list in krb5.conf.

First, set disablesssd and enablekdcfromkrb5conf to true and then restart SQL Server:

sudo mssql-conf set network.disablesssd true
sudo mssql-conf set network.enablekdcfromkrb5conf true
systemctl restart mssql-server

Then configure the KDC list in /etc/krb5.conf as follows:

[realms]
CONTOSO.COM = {
  kdc = dcWithGC1.contoso.com
  kdc = dcWithGC2.contoso.com
}

Note

While it is not recommended, it is possible to use utilities, such as realmd, that set up SSSD while joining the Linux host to the domain, while configuring disablesssd to true so that SQL Server uses openldap calls instead of SSSD for Active Directory related calls.

Next steps

In this tutorial, we walked through how to set up Active Directory authentication with SQL Server on Linux. You learned how to:

  • Join SQL Server host to AD domain
  • Create AD user for SQL Server and set SPN
  • Configure SQL Server service keytab
  • Create AD-based logins in Transact-SQL
  • Connect to SQL Server using AD Authentication

Next, explore other security scenarios for SQL Server on Linux.