Configure Kerberos authentication in Linux clients for Amazon RDS for SQL Server with AWS Managed Microsoft AD

Amazon Relational Database Service (Amazon RDS) is a managed database service that simplifies the setup, operation, and scaling of popular database engines, including Microsoft SQL Server. In on-premises environments, SQL Server is typically configured to work with Microsoft Active Directory (AD) for NTLM and Kerberos authentication. AWS offers managed services for both components, making it straightforward to migrate these workloads to the cloud while maintaining compatibility with your existing infrastructure. In this post, we present a solution that uses Amazon RDS for SQL Server in conjunction with AWS Directory Service for Microsoft Active Directory (AWS Managed Microsoft AD) to enable client authentication via the Kerberos protocol.

Amazon RDS for SQL Server makes it straightforward to set up, operate, and scale SQL Server deployments in the cloud. With Amazon RDS, you can deploy multiple editions of SQL Server in minutes with cost-efficient and resizable compute capacity. It also frees you up to focus on application development by managing time-consuming database administration tasks, including provisioning, backups, software patching, monitoring, and hardware scaling.

AWS Directory Service provides multiple directory choices for customers who want to use existing Microsoft AD-aware or Lightweight Directory Access Protocol (LDAP)-aware applications in the cloud. AWS Managed Microsoft AD activates your directory-aware workloads and AWS resources to use managed AD on AWS. It is a highly available, managed infrastructure that includes patching and software updates and automatic domain controller replacement.

Windows Authentication in SQL Server and Kerberos authentication

SQL Server offers two authentication modes:

• Windows Authentication mode
• SQL Server and Windows Authentication mode (mixed mode)

Mixed mode consists of Windows Authentication and SQL Server Authentication, and with SQL Server Authentication, both the user name and the password are created and stored in SQL Server. Windows Authentication is possible via both authentication modes available in SQL Server. Also, Windows Authentication can be tightly integrated with Microsoft AD, using AD users and groups for the authentication. Many on-premises SQL Server workloads prefer Windows Authentication, due to several key advantages, including:

• It centralizes the management of users and groups within the same Microsoft AD for multiple SQL Server instances
• It provides additional password policies compared to SQL Server Authentication

When using Windows Authentication in SQL Server, it supports either the NTLM or Kerberos protocols. However, it’s important to note that in contrast to Windows clients, Linux clients support Kerberos only. Adopting Kerberos can ensure a robust and secure network authentication protocol. By integrating Amazon RDS for SQL Server with AWS Managed Microsoft AD, you enable both Windows and Linux clients to authenticate via Kerberos protocol, providing them access to SQL Server databases in the AWS environment.

In this post, we guide you through the process of configuring Amazon RDS for SQL Server using AWS Managed Microsoft AD, showcasing Kerberos authentication on Linux client machines. This guide serves as a blueprint for seamlessly expanding Microsoft AD-integrated SQL Server workloads into AWS environments while maintaining compatibility with your existing on-premises infrastructure.

Solution overview

The following diagram illustrates the architecture and the AWS components used in this post to implement Kerberos authentication for Linux clients. The primary components of this setup involve the following Amazon resources:

• AWS Managed Microsoft AD
• RDS for SQL Server instance
• Amazon Elastic Compute Cloud (Amazon EC2) instance (Windows)
• EC2 instance (Linux)

This visualization provides an overview of how these elements work together to achieve Kerberos authentication for Linux clients.

The architecture comprises the following essential components:

• A directory of AWS Managed Microsoft AD with a pair of highly available domain controllers (DCs) is provisioned within the VPC. To learn more about application compatibility, refer to Application compatibility policy for AWS Managed Microsoft AD. The AWS Managed Microsoft AD serves as a Key Distribution Center (KDC) for Kerberos authentication.
• A Windows EC2 instance is configured to join the AWS Managed Microsoft AD. AD administration tools are installed on this instance to facilitate the creation of AD domain users for the purpose of testing the Kerberos authentication. For more information about the Windows EC2 instance with AD administration tools, refer to Installing the Active Directory administration tools.
• An RDS for SQL Server instance is configured to join the AWS Managed Microsoft AD in order to authenticate users with Windows Authentication. This includes enabling Kerberos authentication for Linux clients. For more information on the RDS domain join, refer to Working with AWS Managed Active Directory with RDS for SQL Server.
• A Linux EC2 instance is set up to demonstrate Kerberos authentication against the RDS for SQL Server instance. The Microsoft-provided SQL Server client tool, sqlcmd, is installed together with its necessary packages, such as unixODBC. Additionally, package krb5-user is installed, providing the client tools (such as kinit) for Kerberos authentication. The Linux instance is not required to join the AWS Managed Microsoft AD; using the client tool from package krb5-user is sufficient.
• A SQL client tool in Linux, which is sqlcmd in this case, initiates and performs Kerberos authentication against the RDS for SQL Server instance:
  1. To start, the Kerberos ticket-granting ticket (TGT) must be available. By using kinit, the TGT can be obtained from the KDC by providing the AD domain user name and password.
  2. sqlcmd uses the cached TGT to request Kerberos service tickets from the KDC.
  3. After sqlcmd obtains the Kerberos service ticket from the KDC, it performs the Kerberos service authentication against the RDS for SQL Server instance.

In the following sections, we detail the steps to implement the solution:

1. Provision necessary AWS components using AWS CloudFormation.
2. Create AD domain users using a Windows EC2 instance.
3. Install the required packages on the Linux EC2 instance and configure the /etc/krb5.conf file.
4. Set up a sample database, create database tables, and configure Windows logins in Amazon RDS for SQL Server.
5. Retrieve the Kerberos TGT for dbuser1 using kinit.
6. Perform Kerberos authentication for dbuser1 using sqlcmd.
7. Perform Kerberos authentication for dbuser2 using sqlcmd.
8. Perform Kerberos authentication with JDBC.
9. Automate the retrieval of the Kerberos TGT using keytab.

Prerequisites

To use the solution in this post, you need the following prerequisites:

• An AWS account along with an AWS Identity and Access Management (IAM) user that possesses adequate permissions for deploying the AWS resources for the solution.
• Sufficient permissions to use Session Manager, a capability of AWS Systems Manager, to run sessions for running the necessary commands on both the Windows EC2 instance (PowerShell) and Linux EC2 instance (bash). For detailed instructions on initiating a session via the Systems Manager console, refer to Start a session.

Provision necessary AWS components using AWS CloudFormation

To deploy the CloudFormation template, follow these steps:

1. Choose Launch Stack:
   
2. Use the default value, rds-sql-server-kerberos-linux, for Stack name.
3. Use the default values for all inputs in Parameters section.
4. In the Capabilities section, select I acknowledge that AWS CloudFormation might create IAM resources with custom names, and complete the stack creation.

The CloudFormation template takes approximately 45 minutes to complete, and provisions the following resources:

• Two AWS Secrets Manager secrets:
  • rdsktest/ad – Use {"password":"<Dynamically generated>"} as the secret value, containing the password of the admin user of the AD domain, rdsktest.awsexample.org.
  • rdsktest/rds – Use {"username":"admin","password":"<Dynamically generated>"} as the secret value, containing the user name and password of the primary user of the RDS instance, rds-instance1.
• An AWS Managed Microsoft AD resource with the following properties:
  • DNS name – rdsktest.awsexample.org
  • NetBIOS name – RDSKTEST
  • DNS address – Use two IPs for two DNS serves in the assigned subnets (shown in the CloudFormation template output, MicrosoftAdDnsIpAddresses).
• An RDS DB instance named rds-instance1. For the directory, use the directory ID of rdsktest.awsexample.org.
• A Windows EC2 instance (Windows 2019) named rdsktest-win. This instance will join the domain rdsktest.awsexample.org, and AD administration tools will be installed for creating AD domain users to test the Kerberos authentication.
• A Linux EC2 instance (Ubuntu 20) named rdsktest-linux. This instance will install sqlcmd for the demonstration of Kerberos authentication against Amazon RDS for SQL Server.

Create AD domain users using a Windows EC2 instance

In this step, we create two AD domain users, dbuser1 and dbuser2, for the AD domain using the Windows EC2 instance rdsktest-win. Complete the following steps:

1. On the Amazon EC2 console, locate and select the rdsktest-win instance.
2. Choose Connect and choose Session Manager to initiate the Session Manager session for rdsktest-win.
3. Run the following PowerShell commands, one line at a time:

Invoke-WebRequest -UseBasicParsing -Uri https://aws-blogs-artifacts-public.s3.amazonaws.com/artifacts/DBBLOG-3532/2-create-ad-users.ps1 -Outfile .\2-create-ad-users.ps1 

.\2-create-ad-users.ps1

The command creates two AD domain users, dbuser1 and dbuser2. The PowerShell script will prompt for the AD domain user password for dbuser1 and dbuser2. Verify that the password you input meets the default complexity requirements of AD, including at least one uppercase letter, one lowercase letter, one digit, and a minimum length of 7 characters.

The commands serve the following purposes:

• Print the AD domain information of the Windows instance
• Retrieve the password of the AD domain user, Admin, from Secrets Manager and construct a PowerShell PSCredential object
• Create two AD domain users (dbuser1 and dbuser2) using the PowerShell PSCredential object
• List the two AD domain users using the PowerShell PSCredential object

Because the Session Manager session starts with a local user, ssm-user, which can’t be used to create an AD domain user, we explicitly construct the PSCredential object for the subsequent commands, which is responsible for creating the AD domain users.

Install required packages on the Linux EC2 instance and configure /etc/krb5.conf

In this step, we install the packages krb5-user and sqlcmd on the Linux EC2 instance, rdsktest-linux. Additionally, we configure /etc/krb5.conf, which is required for enabling Kerberos authentication. Complete the following steps:

1. Start a Session Manager session for rdsktest-linux.

By default, Session Manager sessions for Linux use sh shell. It is recommended to use bash shell and switch to the user’s home directory, such as /home/ssm-user, for this post. You achieve this by either configuring custom shell profiles for the Session Manager session using the following commands or running these commands explicitly at the beginning of each session for rdsktest-linux.

bash
cd ~

2. Run the following commands in the bash shell during the Session Manager session, one line at a time:

wget -O 3-install-krb5-sqlcmd.sh https://aws-blogs-artifacts-public.s3.amazonaws.com/artifacts/DBBLOG-3532/3-install-krb5-sqlcmd.sh

chmod 755 3-install-krb5-sqlcmd.sh

./3-install-krb5-sqlcmd.sh

The preceding commands perform the following actions:

• Print the DNS servers configured for the Linux instance, which should point to the AWS Managed Microsoft AD.

• Install the krb5-user package, with a default configuration file at /etc/krb5.conf.

• Download the sample file krb5.conf.sample and override the /etc/krb5.conf file. The sample /etc/krb5.conf file will reference rdsktest.awsexample.org as the KDC.

• Install the sqlcmd tool by installing the mssql-tools18 package and its dependent package, unixodbc-dev.

3. To verify the installation of sqlcmd and review the contents of /etc/krb5.conf, use the following commands:

sqlcmd -? | grep -A 1 -B 1 Version
cat /etc/krb5.conf

The output looks as follows:

Microsoft (R) SQL Server Command Line Tool
Version 18.2.0001.1 Linux
[libdefaults]
 default_realm = RDSKTEST.AWSEXAMPLE.ORG

[realms]
RDSKTEST.AWSEXAMPLE.ORG = {
  kdc = rdsktest.awsexample.org
  admin_server = rdsktest.awsexample.org
}

[domain_realm]
.rdsktest.awsexample.org = RDSKTEST.AWSEXAMPLE.ORG
rdsktest.awsexample.org = RDSKTEST.AWSEXAMPLE.ORG

Set up a sample database, create database tables, and configure Windows logins in Amazon RDS for SQL Server

To complete the setup, enter these commands in the bash shell on the rdsktest-linux instance, one line at a time:

wget -O 4-create-db-login-table.sh https://aws-blogs-artifacts-public.s3.amazonaws.com/artifacts/DBBLOG-3532/4-create-db-login-table.sh

chmod 755 4-create-db-login-table.sh

./4-create-db-login-table.sh

These commands perform the following actions:

• Retrieve the primary user name and password for the RDS for SQL Server instance from Secrets Manager.
• Download the SQL file 4-create-db-login-table.sql. The SQL file creates the database, database table, and Windows logins.
• Run the SQL file using sqlcmd with the Amazon RDS primary user name and password.

In summary, the following database resources will be created:

• A database named test_db1.
• A database table named dbo.test_table1. This is a sample database table for demonstrating Kerberos authentication, with three sample rows inserted.
• The Windows login RDSKTEST\dbuser1, which is mapped to the AD domain user dbuser1. The login has the following permissions:
  • Server permission: VIEW SERVER STATE
  • Database role: db_owner on test_db1
• The Windows login RDSKTEST\dbuser2, which is mapped to the AD domain user dbuser2. The login has the following permissions:
  • Server permission: VIEW SERVER STATE
  • Database role: db_datareader on test_db1
  • Database permission: CONNECT on test_db1

Retrieve the Kerberos TGT for dbuser1 using kinit

Run the kinit command in the bash shell on rdsktest-linux:

kinit dbuser1

When you run this command, it prompts you to enter the AD domain user password for dbuser1. Enter the password that corresponds to the value you previously specified. If the entered password matches, a Kerberos TGT will be obtained from the KDC and cached within the Linux instance.

To verify the Kerberos TGT, run the klist command:

klist

The sample output of the klist command is as follows:

Ticket cache: FILE:/tmp/krb5cc_1001
Default principal: dbuser1@RDSKTEST.AWSEXAMPLE.ORG

Valid starting Expires Service principal
09/15/23 11:09:19 09/15/23 21:09:19 krbtgt/RDSKTEST.AWSEXAMPLE.ORG@RDSKTEST.AWSEXAMPLE.ORG
renew until 09/16/23 11:09:12

From the preceding output, you can observe the following details:

• The Kerberos TGT is stored at the cache file located at /tmp/krb5cc_1001. The value 1001 at the end of the file name represents the Linux UID of ssm-user.
• The Kerberos TGT is associated with the principal dbuser1@RDSKTEST.AWSEXAMPLE.ORG.
• The Kerberos TGT was issued at 09/15/23 11:09:19 and is valid until 09/15/23 21:09:19, providing a ticket lifetime of 10 hours.
• When the Kerberos TGT expires, you have the option to renew it using the kinit -R command, without the need to provide the AD domain user password. This renewal is possible as long as the current time is before 09/16/23 11:09:12. After this time, renewal is no longer possible and you will need to obtain a new TGT.

Perform Kerberos authentication for dbuser1 using sqlcmd

In the bash shell of rdsktest-linux, run the following commands to download the test_user.sql file and run it with sqlcmd, one line at a time:

nslookup rds-instance1.rdsktest.awsexample.org

wget -O test_user.sql https://aws-blogs-artifacts-public.s3.amazonaws.com/artifacts/DBBLOG-3532/test_user.sql

sqlcmd -S rds-instance1.rdsktest.awsexample.org -E -C -i test_user.sql

The -E option of sqlcmd indicates the use of a trusted connection, eliminating the need for a user name and password for authentication. The test_user.sql script performs the following tasks:

• Display the user and Auth_Scheme information
• Run the SQL query SELECT * FROM test_db1.dbo.test_table1
• Run the SQL query UPDATE test_db1.dbo.test_table1 SET val='b' WHERE id=2

Kerberos authentication mandates that the Fully Qualified Domain Name (FQDN) of the endpoint follows the format *.rdsktest.awsexample.org. Because rdsktest-linux is configured with DNS servers pointing to the AWS Managed Microsoft AD, resolving FQDN record rds-instance1.rdsktest.awsexample.org will be successful in this scenario. Using an incorrect FQDN for Kerberos authentication will result in the following error:

SSPI Provider: Server not found in Kerberos database.

From the output of sqlcmd, you will observe that the current domain user name is RDSKTEST\dbuser1 and KERBEROS is the authentication scheme. Both the SELECT and UPDATE statements will run successfully.

Now run klist again, and you should see output similar to the following:

Ticket cache: FILE:/tmp/krb5cc_1001
Default principal: dbuser1@RDSKTEST.AWSEXAMPLE.ORG

Valid starting Expires Service principal
09/15/23 11:09:19 09/15/23 21:09:19 krbtgt/RDSKTEST.AWSEXAMPLE.ORG@RDSKTEST.AWSEXAMPLE.ORG
renew until 09/16/23 11:09:12
09/15/23 11:18:38 09/15/23 21:09:19 MSSQLSvc/EC2AMAZ-PD65EUL.rdsktest.awsexample.org:1433@RDSKTEST.AWSEXAMPLE.ORG
renew until 09/16/23 11:09:12

This indicates that a Kerberos service ticket for MSSQLSvc was issued at 09/15/23 11:18:38.

Perform Kerberos authentication for dbuser2 using sqlcmd

Because the current Kerberos TGT is cached for dbuser1, you must clear it first by running kdestroy on rdsktest-linux:

kdestroy
klist

If the previous Kerberos TGT cache had already cleared before you ran kdestory, you can expect the following output:

klist: No credentials cache found (filename: /tmp/krb5cc_1001)

Now run kinit for dbuser2 and provide the password when prompted, based on the value you provided earlier:

kinit dbuser2
klist

Next, rerun the same test_user.sql using sqlcmd for dbuser2:

sqlcmd -S rds-instance1.rdsktest.awsexample.org -E -C -i test_user.sql

Because the Kerberos TGT is now targeted for dbuser2, you should observe that the current domain user name is RDSKTEST\dbuser2, and the authentication scheme used is KERBEROS.

However, for dbuser2, you will notice that only the SELECT statement runs successfully. The UPDATE statement fails with the following error:

The UPDATE permission was denied on the object 'test_table1', database 'test_db1', schema 'dbo'.

This is because in a previous step, dbuser2 was only granted the database role db_datareader.

Perform Kerberos authentication with JDBC

In previous steps, we used the sqlcmd tool, which relies on the unixODBC package for Kerberos authentication. Alternatively, you can use Java Database Connectivity (JDBC) for Kerberos authentication. For instance, you can use Microsoft JDBC Driver for SQL Server, which is a standard Type 4 JDBC driver provided by Microsoft.

To demonstrate how to implement Kerberos authentication against Amazon RDS for SQL Server using JDBC, follow these steps on rdsktest-linux, running each line one at a time:

wget -O 8-jdbc-kerberos-auth.sh https://aws-blogs-artifacts-public.s3.amazonaws.com/artifacts/DBBLOG-3532/8-jdbc-kerberos-auth.sh

chmod 755 8-jdbc-kerberos-auth.sh

./8-jdbc-kerberos-auth.sh

SELECT SUSER_NAME() domain_user, net_transport, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID

domain_user net_transport auth_scheme
---------- ---------- ----------
RDSKTEST\dbuser2 TCP KERBEROS

ktutil

ktutil: add_entry -password -p dbuser1 -k 1 -e aes256-cts-hmac-sha1-96
Password for dbuser1@TESTRDS.AWSEXAMPLE.COM:
ktutil: write_kt /home/ssm-user/krb5.keytab
ktutil: quit

kinit -k -t /home/ssm-user/krb5.keytab dbuser1
klist

crontab -l | { cat; echo “*/1 * * * * kinit -k -t /home/ssm-user/krb5.keytab dbuser1”; } | crontab –

crontab -l

Valid starting Expires Service principal
09/15/23 11:23:01 09/15/23 21:23:01 krbtgt/RDSKTEST.AWSEXAMPLE.ORG@RDSKTEST.AWSEXAMPLE.ORG
renew until 09/16/23 11:23:01

Valid starting Expires Service principal
09/15/23 11:24:01 09/15/23 21:24:01 krbtgt/RDSKTEST.AWSEXAMPLE.ORG@RDSKTEST.AWSEXAMPLE.ORG
renew until 09/16/23 11:24:01