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:
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.
sqlcmd uses the cached TGT to request Kerberos service tickets from the KDC.
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:
Provision necessary AWS components using AWS CloudFormation.
Create AD domain users using a Windows EC2 instance.
Install the required packages on the Linux EC2 instance and configure the /etc/krb5.conf file.
Set up a sample database, create database tables, and configure Windows logins in Amazon RDS for SQL Server.
Retrieve the Kerberos TGT for dbuser1 using kinit.
Perform Kerberos authentication for dbuser1 using sqlcmd.
Perform Kerberos authentication for dbuser2 using sqlcmd.
Perform Kerberos authentication with JDBC.
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:
Choose Launch Stack:
Use the default value, rds-sql-server-kerberos-linux, for Stack name.
Use the default values for all inputs in Parameters section.
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:
On the Amazon EC2 console, locate and select the rdsktest-win instance.
Choose Connect and choose Session Manager to initiate the Session Manager session for rdsktest-win.
Run the following PowerShell commands, one line at a time:
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:
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.
Run the following commands in the bash shell during the Session Manager session, one line at a time:
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.
To verify the installation of sqlcmd and review the contents of /etc/krb5.conf, use the following commands:
The output looks as follows:
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:
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 RDSKTESTdbuser1, 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 RDSKTESTdbuser2, 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:
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:
The sample output of the klist command is as follows:
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 [email protected].
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:
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:
From the output of sqlcmd, you will observe that the current domain user name is RDSKTESTdbuser1 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:
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:
If the previous Kerberos TGT cache had already cleared before you ran kdestory, you can expect the following output:
Now run kinit for dbuser2 and provide the password when prompted, based on the value you provided earlier:
Next, rerun the same test_user.sql using sqlcmd for dbuser2:
Because the Kerberos TGT is now targeted for dbuser2, you should observe that the current domain user name is RDSKTESTdbuser2, 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:
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:
Read MoreAWS Database Blog
