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

Note

adutil is currently in public preview

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-preview
  • 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-preview tool on a Linux host machine. Follow the section below based on the Linux distribution that you're running to install adutil-preview.

Install adutil-preview

On the Linux host machine, use the following commands to install adutil-preview.

Note

For this preview version, we are aware that on certain Linux distributions, if the adutil installation is attempted without the ACCEPT_EULA parameter, the installation experience is hindered. Our recommendation below is to install the adutil-preview tool with ACCEPT_EULA=Y set. You can read the preview EULA ahead of the installation. We are actively working on this and this should be fixed for the GA release.

RHEL

  1. Download the Microsoft Red Hat repository configuration file.

    sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config/rhel/8/prod.repo
    
  2. If you had a previous version of adutil installed, remove any older adutil packages.

    sudo yum remove adutil
    
  3. Run the following commands to install adutil-preview. ACCEPT_EULA=Y accepts the preview EULA for adutil. The EULA is placed at the path '/usr/share/adutil/'.

    sudo ACCEPT_EULA=Y yum install -y adutil-preview
    

Ubuntu

  1. Import the public repository GPG keys and then register the Microsoft Ubuntu repository.

    curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
    sudo curl https://packages.microsoft.com/config/ubuntu/18.04/prod.list | sudo tee /etc/apt/sources.list.d/msprod.list
    
  2. If you had a previous version of adutil installed, remove any older adutil packages using the below commands

    sudo apt-get remove adutil
    
  3. Run the following command to install adutil-preview. ACCEPT_EULA=Y accepts the preview EULA for adutil. The EULA is placed at the path '/usr/share/adutil/'.

    sudo apt-get update
    sudo ACCEPT_EULA=Y apt-get install -y adutil-preview
    

SLES

  1. Add the Microsoft SQL Server repository to Zypper.

    sudo zypper addrepo -fc https://packages.microsoft.com/config/sles/12/prod.repo 
    
  2. If you had a previous version of adutil installed, remove any older adutil packages.

    sudo zypper remove adutil
    
  3. Run the following command to install adutil-preview. ACCEPT_EULA=Y accepts the preview EULA for adutil. The EULA is placed at the path '/usr/share/adutil/'.

    sudo ACCEPT_EULA=Y zypper install -y adutil-preview
    

Domain machine preparation

Make sure there is 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.

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