Tutorial: Configure Active Directory authentication with SQL Server on Linux using adutil

This tutorial explains how to configure Active Directory (AD) authentication for SQL Server on Linux using adutil. For another method of configuring AD authentication using ktpass, see Tutorial: Use Active Directory authentication with SQL Server on Linux.

This tutorial consists of the following tasks:

  • Install adutil
  • Join Linux machine to your AD domain
  • Create an AD user for SQL Server and set the ServicePrincipalName (SPN) using the adutil tool
  • Create the SQL Server service keytab file
  • Configure SQL Server to use the keytab file
  • Create AD-based SQL Server logins using Transact-SQL
  • Connect to SQL Server using AD authentication

Prerequisites

The following are required before configuring AD authentication:

  • Have an AD Domain Controller (Windows) in your network.
  • Install the adutil tool on the domain joined host machine.

Domain machine preparation

Make sure there's a forwarding host (A) entry added in Active Directory for the Linux host IP address. In this tutorial, the IP address of myubuntu host machine is 10.0.0.10. We add the forwarding host entry in Active Directory as shown below. The entry ensures that when users connect to myubuntu.contoso.com, it reaches the right host.

add host record

For this tutorial, we're using an environment in Azure with three VMs. One VM acting as the Windows domain controller (DC), with the domain name contoso.com. The Domain Controller is named adVM.contoso.com. The second machine is a Windows machine called winbox, running Windows 10 desktop, which is used as a client box and has SQL Server Management Studio (SSMS) installed. The third machine is an Ubuntu 18.04 LTS machine named myubuntu, which hosts SQL Server.

Join the Linux host machine to your 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.

Install adutil

To install adutil tool, follow the steps explained in: Introduction to adutil - Active Directory utility on the host machine that you added to the domain in the previous step.

Create an AD user for SQL Server and set the ServicePrincipalName (SPN) using the adutil tool

  1. Obtain or renew the Kerberos TGT (ticket-granting ticket) using the kinit command. Use a privileged account for the kinit command. The account needs to have permission to connect to the domain, and also should be able to create accounts and SPNs in the domain.

    Important

    Before you run this command, the host machine should already be part of the domain as shown in the previous step.

    kinit privilegeduser@DOMAIN.COM
    

    Example: For the environment described above, my privileged account is amvin@CONTOSO.COM

    kinit amvin@CONTOSO.COM
    
  2. Using the adutil tool, create the new user that will be used as the privileged AD Account by SQL Server.

    adutil user create --name sqluser --distname CN=sqluser,CN=Users,DC=CONTOSO,DC=COM --password 'P@ssw0rd'
    

    Note

    Passwords may be specified in any of the three ways:

    • Password flag: --password <password>
    • Environment variables - ADUTIL_ACCOUNT_PWD
    • Interactive input

    The precedence of password entry methods follows the order of options listed above. The recommended options are to provide the password using Environment variables or interactive input, as they more secure compared to the password flag.

    You can specify the name of the account using the distinguished name (-distname) as shown above, or you can also use the Organizational Unit (OU) name as well. The OU name (--ou) takes precedence over distinguished name in case you specify both. You can run the below command for more details:

    adutil user create --help
    
  3. Register SPNs to the principal created above. Use the machine FQDN. In this tutorial, we're using SQL Server's default port, 1433. Your port number could be different.

    adutil spn addauto -n sqluser -s MSSQLSvc -H myubuntu.contoso.com -p 1433
    

    Note

    • addauto will create the SPNs automatically, provided sufficient privileges are present for the kinit account.
    • -n: Name of the account the SPNs will be assigned to.
    • -s: The service name to use for generating SPNs. In this case, it is for SQL Server service, and hence the service name is MSSQLSvc.
    • -H: The hostname to use for generating SPNs. If not specified, the local host's FQDN will be used. In this case, the host name is myubuntu and the FQDN is myubuntu.contoso.com.
    • -p: The port to use for generating SPNs. If not specified, SPNs will be generated without a port. SQL connections will only work in this case when the SQL Server is listening to the default port, 1433.

Create the SQL Server service keytab file

Create the keytab file that contains entries for each of the 4 SPNs created previously, and one for the user.

adutil keytab createauto -k /var/opt/mssql/secrets/mssql.keytab -p 1433 -H myubuntu.contoso.com --password 'P@ssw0rd' -s MSSQLSvc 

Note

  • -k: Path where you would like the mssql.keytab file to be created. In the above example the directory /var/opt/mssql/secrets/ should already exist on the host.
  • -p: The port to use for generating SPNs. If not specified, SPNs will be generated without a port.
  • -H: The hostname to use for generating SPNs. If not specified, the local host's FQDN will be used. In this case, the host name is myubuntu and the FQDN is myubuntu.contoso.com.
  • -s: The service name to use for generating SPNs. In this case, it is for SQL Server service, and hence the service name is MSSQLSvc.
  • --password: This is the password of the privileged AD user account that was created earlier.
  • -e or --enctype: Encryption types for the keytab entry. Use a comma-separated list of values. If not specified, an interactive prompt will be presented.

When given a choice to choose the encryption types, you can choose more than one. For this example, we chose aes256-cts-hmac-sha1-96 and arcfour-hmac. Ensure you choose the encryption type that is supported by the host and domain.

If you’d like to non-interactively choose the encryption type, you can specify your choice of encryption type with the -e argument in the above command. For additional help on the adutil commands, run the command below.

adutil keytab createauto --help

Note

arcfour-hmac is a weak encryption and not a recommended encryption type to be used in production environment.

Add an entry in the keytab for the principal name and its password that will be used by SQL Server to connect to AD:

adutil keytab create -k /var/opt/mssql/secrets/mssql.keytab -p sqluser --password 'P@ssw0rd!'

Note

  • -k: Path where you would like the mssql.keytab file to be created.
  • -p: Principal to add to the keytab.

The adutil keytab create/autocreate doesn't overwrite the previous files, it just appends to the file if already present.

Ensure the keytab created is owned by the mssql user and only the mssql user has read/write access to the file. You can run the chown and chmod commands as shown below:

chown mssql. /var/opt/mssql/secrets/mssql.keytab
chmod 440 /var/opt/mssql/secrets/mssql.keytab

Configure SQL Server to use the keytab

Run the below commands to configure SQL Server to use the keytab created in the previous step, and set the privileged AD account as the user created above. In our example, the user name is sqluser.

/opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab
/opt/mssql/bin/mssql-conf set network.privilegedadaccount sqluser

Restart SQL Server

Run the below command to restart the SQL Server service:

sudo systemctl restart mssql-server

Create AD-based SQL Server logins in Transact-SQL

Connect to the SQL Server and run the following commands to create the login, and confirm that it's listed.

create login [contoso\amvin] From Windows
SELECT name FROM sys.server_principals;

Connect to SQL Server using AD authentication.

To connect using SSMS or Azure Data Studio, log into the SQL Server with your Windows credentials.

You can also use a tool like sqlcmd to connect to the SQL Server using Windows Authentication.

sqlcmd -E -S 'myubuntu.contoso.com'

Next Steps