Tutorial: Use adutil to configure Active Directory authentication with SQL Server on Linux
Applies to:
SQL Server - Linux
This tutorial explains how to configure Windows Active Directory authentication for SQL Server on Linux using adutil. For another method of configuring Active Directory 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 Active Directory domain
- Create an Active Directory user for SQL Server and set the Service Principal Name (SPN) using adutil
- Create the SQL Server service keytab (key table) file
- Configure SQL Server to use the keytab file
- Create Active Directory-based SQL Server logins using Transact-SQL
- Connect to SQL Server using Active Directory authentication
Prerequisites
Before configuring Active Directory authentication, you'll need:
- A Windows Domain Controller running Active Directory Domain Services in your network.
- The adutil tool installed on a 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.
For this tutorial, we're using an environment in Azure with three virtual machines (VMs). One VM is a Windows Server computer named adVM.contoso.com
, running as a Domain Controller (DC) with the domain name contoso.com
. The second VM is a client machine running Windows 10 named winbox
, which 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 Active Directory domain
To join myubuntu
to the Active Directory domain, see Join SQL Server on a Linux host to an Active Directory domain.
Install adutil
To install adutil, follow the steps explained in the article Introduction to adutil - Active Directory utility on the host machine that you added to the domain in the previous step.
Use adutil to create an Active Directory user for SQL Server and set the Service Principal Name (SPN)
Obtain or renew the Kerberos TGT (ticket-granting ticket) using the
kinit
command. You must use a privileged account for thekinit
command, and the host machine should already be part of the domain. The account needs permission to connect to the domain, and create accounts and SPNs in the domain.In this example script, a privileged user called
privilegeduser@CONTOSO.COM
has already been created on the domain controller.kinit privilegeduser@CONTOSO.COM
Using adutil, create the new user that will be used as the privileged Active Directory account by SQL Server.
Passwords can be specified in three different ways. If you use more than one of these methods, they take precedence in the following order:
- Using the password flag:
--password <password>
- In an environment variable:
ADUTIL_ACCOUNT_PWD
- Interactive input at a command line prompt
The environment variable or interactive input methods are more secure than the password flag.
adutil user create --name sqluser --distname CN=sqluser,CN=Users,DC=CONTOSO,DC=COM --password 'P@ssw0rd'
You can specify the name of the account using the distinguished name (
--distname
) as shown above, or you can use the Organizational Unit (OU) name. 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
- Using the password flag:
Register SPNs to the principal created above. You must use the machine's fully qualified domain name (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
addauto
will create the SPNs automatically, as long as there are sufficient privileges for thekinit
account.-n
: The 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 the SQL Server service, which is why the service name isMSSQLSvc
.-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 ismyubuntu
and the FQDN ismyubuntu.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 four 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
The possible command line options are:
-k
: The path where themssql.keytab
file will 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 ismyubuntu
and the FQDN ismyubuntu.contoso.com
.-s
: The service name to use for generating SPNs. For this example, the SQL Server service name isMSSQLSvc
.--password
: The password of the privileged Active Directory 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.
You can choose more than one encryption type, as long as your host and domain support the encryption type. In this example, you might choose
aes256-cts-hmac-sha1-96
andaes128-cts-hmac-sha1-96
. However, you should avoidarcfour-hmac
in a production environment because it has weak encryption.If you'd like to choose the encryption type without being prompted, you can specify your choice of encryption type using the
-e
argument in the above command. For more help on theadutil keytab
options, run this command:adutil keytab createauto --help
Add an entry in the keytab for the principal name and its password that will be used by SQL Server to connect to Active Directory:
adutil keytab create -k /var/opt/mssql/secrets/mssql.keytab -p sqluser --password 'P@ssw0rd'
-k
: Path where you would like themssql.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.Make sure the created keytab is owned by the
mssql
user, and that only themssql
user has read/write access to the file. You can run thechown
andchmod
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 Active Directory 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 Active Directory-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\privilegeduser] FROM WINDOWS;
SELECT name FROM sys.server_principals;
Connect to SQL Server using Active Directory 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'
See also
- Understanding Active Directory authentication for SQL Server on Linux and containers
- Troubleshooting Active Directory authentication for SQL Server on Linux and containers
Next steps
Feedback
Submit and view feedback for