AWS Database Blog

Deploying and configuring Active Directory authentication with SQL Server 2017 on Amazon Linux 2

SQL Server on Windows allows you to configure SQL Server instances to use Windows authentication with Active Directory accounts. This post addresses how to do that with SQL Server 2017 on Amazon Linux 2. This solution allows you to log in with your Active Directory accounts using Windows authentication to manage SQL Server Linux instances on Amazon EC2.

In this post, you learn how to do the following:

  1. Deploy and join SQL Server Linux instance to your domain.
  2. Create a new Active Directory user and set the Service Principal Name for SQL Server Linux.
  3. Add a SQL Server service keytab; you use the keytab file to authenticate to Active Directory.
  4. Create an Active Directory based SQL login using SQL Server Management Studio (SSMS).
  5. Test the Windows Authentication with SSMS from a Windows machine using a domain account.

Walkthrough

Follow these steps to deploy and configure Active Directory authentication with SQL Server 2017 on Amazon Linux.

Prerequisites

Before starting, you need:

  • An Active Directory domain: It can be on-premises Windows domain or a Windows domain running on EC2. You can also use AWS Managed Microsoft AD or Simple AD (Samba) in AWS Directory Services.
  • SQL Server 2017 Linux (Express, Web, Standard, or Enterprise Edition) on Amazon Linux 2.
  • A Windows Server (2012 R2 or 2016) machine joined to the same domain. Install SQL Server Management Studio (SSMS) to connect to the SQL instance, and Active Directory management tools to manage the domain.

Step 1: Deploy and join SQL Server Linux instance to your domain

First, you must deploy and join a SQL Server Linux instance to your domain.

Deploy SQL Server 2017 Express, Web, Standard, or Enterprise edition on Amazon Linux 2. This post uses an Amazon Linux 2 instance with SQL Server 2017 Enterprise edition.

Use SSH to connect to your Linux instance and refer to these steps to join your Linux instance to the domain:

  1. If you’re not using a DHCP option set to point to your Active Directory DNS servers, manually configure your Linux host to point to your servers. You can use vi /etc/resolv.conf to configure the Active Directory DNS servers. Make sure to statically configure DNS so when you reboot the host, the DNS settings associated your domain persist.
  2. To ensure your system is up-to-date, run the following command:
    sudo yum -y update
  3. To install the realmd package, run the following command:
    sudo yum -y install sssd realmd krb5-workstation samba-common-tools
  4. Restart the Linux host, because you can’t join it to the domain until you reboot.
  5. To join the Linux machine to the domain, run the following command:
    sudo realm join -U join_account@example.com example.com ––verbose

Make sure to change example.local to your domain name and verify that the joiner Active Directory account has permissions to create computer objects.

If the Linux machine joins successfully, you should get the following message at the end:* Successfully enrolled machine in realm.

Step 2: Create a new Active Directory user and set the service principal name for SQL Server Linux:

Use Remote Desktop Protocol (RDP) to connect to your Windows machine with Active Directory management tools. Create a SQL Server account to run the service in Linux. The user account name can be anything you want it to be. You can also use an existing Active Directory account instead of creating a new account.

To associate the SQL Server Linux instance with the service account, create the service principal name (SPN).

From a Windows domain joined machine, open PowerShell as administrator, and run the following command:

setspn -A MSSQLSvc/SQL-LINUX.corp.local:1433 nour

This Linux instance hostname is “SQL-LINUX” and the Active Directory account name is “nour”. Make sure to change it to your applicable Hostname and Active Directory account logon name. If SQL Server is configured to listen on a different TCP port, make sure to change it to that port. Also note that SPNs may take some time to propagate through your domain.

In the attribute editor, you should also see the following:

Step 3: Add a SQL Server service keytab

You use a keytab file to authenticate to Active Directory.

Acquire a Kerberos ticket for your service account by running the following command from the Linux host.

kinit nour@CORP.LOCAL

It prompts you to enter your Active Directory account password. Make sure to replace “nour” with your account logon name, and capitalize all the letters of your applicable domain name.

After you enter the Active Directory password, run the following command to use the kvno tool to configure the SPN.

kvno MSSQLSvc/SQL-LINUX.corp.local:1433

Take a note of the key version number “kvno” for the Active Directory account service account. It should be number “2,” but it can be different if the password has changed before.

Make sure to change SQL-LINUX to your applicable Linux Hostname. Also change corp.local to your applicable domain name. You might get something similar to the following error: kvno:server not found in kerberos database while…. If so, allow some time before running the command again. It may be because the SPN info hasn’t replicated to other domain controllers in your environment.

If everything goes well, you should see the following:

Now, create the keytab file for the SQL Server service account using the ktutil utility. A keytab file is used to authenticate into your Windows domain using Kerberos and without entering a password. If you ever plan to change the Active Directory password in the future, you must create a new keytab file again.

Run the following commands from your Linux SQL host. Make sure to change SQL-Linux to your applicable Linux hostname and corp.local to your domain name. The ktutil utility doesn’t validate the correct Active Directory password, so make sure that you type the correct Active Directory password for the SQL Server service account. I added -k 2 for the key version kvno. If you get a different number in step 3, make sure to use that number.

ktutil

After you run ktutil, run the following commands:

addent -password -p MSSQLSvc/SQL-LINUX.corp.local:1433@CORP.LOCAL -k 2 -e aes256-cts-hmac-sha1-96

addent -password -p MSSQLSvc/SQL-LINUX.corp.local:1433@CORP.LOCAL -k 2 -e rc4-hmac

wkt /var/opt/mssql/secrets/mssql.keytab

exit

Anyone who has access to this keytab file can impersonate the service account on the Active Directory domain. Make sure to restrict access to the keytab file to only allow the mssql account to have read access.

sudo chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab 

sudo chmod 400 /var/opt/mssql/secrets/mssql.keytab

Configure SQL Server on Linux to use Kerberos authentication and restart SQL Server service. Run the following commands from the Linux host. 

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

sudo systemctl restart mssql-server

=====================================================================================

Step 4: Create an Active Directory–based SQL login using SQL Server Management Studio

Follow these steps to create an Active Directory–based SQL login with SQL Server Management Studio (SSMS).

If you deployed a SQL Server 2017 instance from an Amazon Linux 2 AMI, run the following command to reset the sa password.

sudo systemctl stop mssql-server 

sudo /opt/mssql/bin/mssql-conf set-sa-password 

sudo systemctl start mssql-server
  1. Make sure to create a record in DNS to point to the SQL Linux instance IP address in the VPC. If you want to connect using the IP address, make sure to create a PTR record. If you don’t have a DNS server, you can modify the host file in Windows.
  2. Load SQL Server Management Studio from a Windows machine that is part of the same domain and connect to the SQL Linux instance using the sa
  3. After you log in with the sa account, you must create an SQL login for your domain account so it can use Windows Authentication.
  4. Assign a Server Role to the domain account.

Step 5: Test the Windows authentication with SSMS from a Windows machine using a domain account

This is an example:

You are now ready to login and administer your SQL Server on Linux using your Windows Active Directory accounts.

 

About the Author

 

Noureddine Ennacir is a Cloud Support Engineer with Amazon Web Services.