Customer-managed process for configuring Kerberos authentication on an Amazon RDS for SQL Server DB instance, joined to a self-managed Active Directory

Many organizations rely on Windows Authentication and Kerberos for secure access to their SQL Server databases. When using Amazon RDS for SQL Server with a self-managed Active Directory, organizations can enhance their authentication beyond the default NTLM protocol to support Kerberos authentication.

In this post, we show you how to manually configure and maintain Kerberos authentication for Amazon RDS for SQL Server DB instances joined to a self-managed Active Directory. We walk through the process of configuring service principal names (SPNs), adding necessary user principal name (UPN) suffixes, and automating SPN updates to handle failovers and host replacements. By following these steps, you can make sure your clients connecting to RDS for SQL Server DB instances use Kerberos authentication, improving security and maintaining compatibility with your existing applications and tools.

This post provides database administrators, security professionals, and IT architects the steps to implement robust Kerberos authentication for RDS DB instances.

AWS Managed Active Directory provides a fully managed solution for Kerberos authentication when RDS for SQL Server DB Instances are joined to the AD domain. Consider this option if your environment supports it.

Kerberos endpoint in AWS Managed Active Directory is available at <db-instance-identifier>.<ManagedAD FQDN>

RDS for SQL Server authentication method and protocols

The default authentication used by Amazon RDS for SQL Server is the “SQL Server authentication.” Customers can enable “Windows Authentication” by joining the DB Instance to an Active Directory domain.

Users authenticate using a SQL Server login and password that are managed by the SQL Server instance. The RDS master user creates and maintains the SQL Server login and password. This authentication method is useful when you have existing applications or tools that rely on SQL Server authentication.

The second authentication method supported by RDS for SQL Server is Windows Authentication. To use Windows Authentication, the RDS DB instance must be joined to an Active Directory domain. Users can then connect to the RDS DB instance using their Active Directory domain username and password. This authentication method allows you to centrally manage your user credentials in Active Directory.

The default authentication protocol for Windows Authentication is Kerberos, a ticket-based security protocol. The customer’s client won’t use Kerberos if it doesn’t find an SPN for the endpoint it’s trying to connect to. Clients will fall back to NTLM authentication protocol when joined to a self-managed Active Directory or to a Multi-AZ deployment with Always On availability groups (AGs) joined to AWS Directory Service for Microsoft Active Directory.

Amazon RDS uses a public DNS name in the format <db-instance-identifer>.<unique-id>.<region>.rds.amazonaws.com for the default endpoint. Kerberos authentication fails because no SPN is registered in Active Directory for this endpoint. In the next sections we show how to create and maintain the SPN to allow Kerberos authentication.

Kerberos authentication protocol and SPNs

Kerberos is a modern and secure authentication protocol developed by MIT that uses a trusted third-party authentication server, known as a Key Distribution Center (KDC), to facilitate the authentication process. Kerberos authentication relies on ticket-granting tickets (TGTs) and service tickets to authenticate users and grant them access to resources, providing stronger security guarantees such as mutual authentication, message integrity, and confidentiality. Kerberos is the preferred authentication protocol for Windows domains and is generally considered more secure than NTLM. When configuring Windows Authentication for an RDS for SQL Server DB instance, the underlying authentication protocol used will depend on the configuration of the Active Directory domain that the RDS DB instance is joined to. Kerberos is the default authentication protocol used in Microsoft Active Directory environments.

Here’s how Kerberos authentication works in an Active Directory environment:

Authentication request: When a user wants to access a resource on the network, their client (such as a Windows computer) sends an authentication request to the KDC, which is a service running on the domain controller.
TGT: The KDC verifies the user’s credentials. Upon successful verification, it issues a ticket-granting ticket (TGT) to the client. The TGT is encrypted with the KDC’s secret key and contains information about the user, including their group memberships and other authorization data.
Service ticket: When the user wants to access a specific resource (a SQL Server instance, a file share, a web server, or other), the client uses the TGT to request a service ticket from the KDC. The KDC verifies the TGT and issues a service ticket that is specific to the requested resource.
Authentication to the resource: The client then presents the service ticket to the resource server, which verifies the ticket using its own secret key. If the ticket is valid, the resource server allows the client to access the requested resource.

The key advantages of Kerberos authentication in Active Directory are:

Single sign-on: Users only need to authenticate once (with their username and password) to access multiple resources on the network.
Secure authentication: Kerberos uses symmetric-key cryptography and ticket-based authentication, which helps prevent common authentication vulnerabilities such as password sniffing and man-in-the-middle attacks. (While true compared to NTLM, Kerberos is not an internet protocol and should never be exposed to the internet. It is vulnerable to golden and silver)
Scalability: Kerberos is designed to be scalable, allowing it to handle large numbers of users and resources in a domain.
Active Directory integrates Kerberos seamlessly, making it the default authentication method for Windows-based networks. This helps improve security and simplify the user experience for accessing network resources.
NTLM is no longer under development by Microsoft.

The SPN is required in the Kerberos authentication process within a Microsoft Active Directory environment. It’s a unique identifier for a service running on a server and is used by Kerberos to associate a service instance with a service login account. The SPN has the following format: ServiceClass/HostName:PortNumber

The SPN for a SQL Server instance running on the default TCP port would look like: MSSQLSvc/sqlserver01.example.local:1433

The service class is MSSQLSvc, which is the standard service class for SQL Server DB instances. The hostname is sqlserver01.example.local, which is the fully qualified domain name (FQDN) of the server hosting the SQL Server instance. The port number is 1433, which is the default port for SQL Server. This SPN must be registered in the Active Directory so that the Kerberos authentication process can properly identify and authenticate the SQL Server service. When a client (such as an application or a user) connects to the SQL Server instance, the client’s system will look up the appropriate SPN in the Active Directory, obtain a Kerberos service ticket for that SPN, and then use the service ticket to authenticate with the SQL Server instance.

When you join an RDS for SQL Server DB instance to a self-managed Active Directory, the following SPNs are created:

C:\Users\Administrator>setspn -L EC2AMAZ-0UKMMKS

Registered ServicePrincipalNames for CN=EC2AMAZ-0UKMMKS,OU=RDS,DC=example,DC=local:
        MSSQLSvc/EC2AMAZ-0UKMMKS.example.local
        MSServerClusterMgmtAPI/EC2AMAZ-0UKMMKS
        MSServerClusterMgmtAPI/EC2AMAZ-0UKMMKS.example.local
        TERMSRV/EC2AMAZ-0UKMMKS
        TERMSRV/EC2AMAZ-0UKMMKS.example.local
        WSMAN/EC2AMAZ-0UKMMKS
        WSMAN/EC2AMAZ-0UKMMKS.example.local
        RestrictedKrbHost/EC2AMAZ-0UKMMKS
        HOST/EC2AMAZ-0UKMMKS
        RestrictedKrbHost/EC2AMAZ-0UKMMKS.example.local
        HOST/EC2AMAZ-0UKMMKS.example.local

EC2AMAZ-0UKMMKS is an example hostname on which the SQL Server instance is running. example.local is the FQDN of the self-managed Active Directory

Why Windows Authentication falls back to NTLM

According to AWS documentation, the connection to the DB instance uses the NTLM authentication protocol. You can verify this in SQL Management console by running the following query:

SELECT @@SERVERNAME, protocol_type, auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID

As shown in the following figure:

Enabling the Kerberos event logging on the client when trying to connect to the DB instance using Windows Authentication shows that Windows tried Kerberos, but fell back to NTLM:

A Kerberos error message was received:
on logon session
 Client Time:
 Server Time: 12:16:58.0000 6/12/2024 Z
Error Code: 0x7  KDC_ERR_S_PRINCIPAL_UNKNOWN
Extended Error:
 Client Realm:
 Client Name:
 Server Realm: EXAMPLE.LOCAL
Server Name: MSSQLSvc/mssql02.cdmhjzvkau9c.us-east-1.rds.amazonaws.com:1433
Target Name: MSSQLSvc/mssql02.cdmhjzvkau9c.us-east-1.rds.amazonaws.com:1433@EXAMPLE.LOCAL
Error Text:
 File: onecore\ds\security\protocols\kerberos\client2\kerbtick.cxx
Line: 12f8
Error Data is in record data.

The error occurred because the KDC in the self-managed Active Directory domain doesn’t have an SPN for MSSQLSvc/mssql02.cdmhjzvkau9c.us-east-1.rds.amazonaws.com:1433@EXAMPLE.LOCAL, which is the endpoint we use to connect to the RDS for SQL Server DB instance.

Using Name Suffix Routing for RDS SPNs

Each Active Directory forest can process authentication requests for specific name suffixes. Forests can contain multiple unique name suffixes, and children of unique name suffixes are routed implicitly. Amazon RDS endpoints use the suffix rds.amazonaws.com. Adding rds.amazonaws.com as an alternative UPN to an Active Directory forest will allow Kerberos authentication. This applies not only to users but also to services through their SPN.

If a forest trust exists between two forests, then name suffixes that don’t exist in one forest can be used to route authentication requests to a second forest. If you have multiple Active Directory forests in your environment, you might add more specific suffixes—such as eu-central-1.rds.amazonaws.com and us-east-1.rds.amazonaws.com—to your forests to avoid Kerberos routing conflicts. When a duplicate name suffix is detected, the routing for the newest name suffix will be disabled by default.

You can add an alternative UPN suffix using the Active Directory Domains and Trusts Microsoft Management Console, as shown in the following figure:

Adding a UPN suffix at the forest level will change what user creation in Active Directory Users and Computers looks like, as shown in the following figure:

To avoid this, you can add the suffix only to the Organizational Unit (OU) where your RDS computer objects reside.

Hostname / Computer object in RDS for SQL Server DB Instance

An Amazon RDS Single-AZ DB instance is a managed instance running in a single Availability Zone, and database files are stored on Amazon Elastic Block Store (Amazon EBS) volumes. A single host runs the database software and the RDS automation.

Amazon RDS Multi-AZ DB instance deployment automatically provisions and maintains a synchronous standby replica in another Availability Zone. Use RDS Multi-AZ synchronous replication technologies to keep data on your standby database instance up to date with the primary instance. Amazon RDS supports Multi-AZ deployments for Microsoft SQL Server by using either SQL Server database mirroring (DBM) or Always On AGs. When you set up SQL Server Multi-AZ, RDS automatically configures databases on the instance to use DBM or AGs. RDS handles the primary, the witness, and the secondary DB instances for you. Because configuration is automatic, RDS selects DBM or Always On AGs based on the version of SQL Server that you deploy.

To connect to an RDS DB instance, we use the connection endpoint URL visible in the Amazon RDS console—shown in the following figure—or the describe-db-instances command from the AWS Command Line Interface (AWS CLI) or the DescribeDBInstances API call. This URL needs to be added as the SPN in Active Directory.

RDS DB Instance Endpoint as shown in the RDS console

To set the correct SPN, we use the SetSPN command line tool:

SetSPN -S MSSQLSvc/<RDS endpoint>:<RDS Port> <Computername or domainname\computername$>

<computername> is the computer account for which we are registering the SPN, in your case it will be the host on which the database instance is running. It will change if the host is replaced, see the following example where:

<RDS endpoint>= MSSQLSvc/mssql02.cdmhjzvkau9c.us-east-1.rds.amazonaws.com

<RDS Port>= 1433

<Computername or domainname\computername$>= EXAMPLE\EC2AMAZ-OT44PR2$

C:\Users\Administrator>setspn -S MSSQLSvc/mssql02.cdmhjzvkau9c.us-east-1.rds.amazonaws.com:1433 EXAMPLE\EC2AMAZ-OT44PR2$
Checking domain DC=example,DC=local

Registering ServicePrincipalNames for CN=EC2AMAZ-OT44PR2,OU=RDS,DC=example,DC=local
        MSSQLSvc/mssql02.cdmhjzvkau9c.us-east-1.rds.amazonaws.com:1433

Updated object

C:\Users\Administrator>

Setting the correct SPN in an Amazon RDS Multi-AZ DB instance deployment is more challenging because there are two active hosts running the database software as the active node and the standby node of a cluster. The SetSPN syntax remains the same. Computername is the host on which the database instance is running.

Note that the computername will change after every failover. When updating SPN entries in Active Directory, delete any existing, outdated SPNs for a database instance before adding an updated entry to avoid duplicate SPN conflicts.

Configuring Kerberos Authentication

1. Join an RDS for SQL Server DB instance to a self-managed Active Directory.Join your RDS DB instance to your self-managed Active Directory as described in Setting up self-managed Active Directory and Join RDS for SQL Server to your self-managed Active Directory.
2. Add an alternative UPN suffix to your self-managed Active Directory.
  To allow Kerberos routing and authentication for Amazon RDS, add the UPN suffix rds.amazonaws.com to your Active Directory. You can add it to the OU where your RDS computer accounts are stored. You might need to install Remote Server Administration Tools (RSAT) for Windows to get the following PowerShell modules:
```
Import-Module ActiveDirectory
Get-ADObject -Identity ‘DN of your OU’ | Set-ADObject -Add @{UPNSuffixes=”rds.amazonaws.com”}
```
  PowerShell
  Or, on the Active Directory forest level:
```
Import-Module ActiveDirectory
Get-AdForest | Set-AdForest -UPNSuffixes @{Add="rds.amazonaws.com"}
```
  PowerShell
  If you have multiple Active Directory forests with trust relationships, you might want to add AWS Region-specific sub-domains, such as eu-central-1.amazonaws.com, in each forest. UPN suffixes should remain unique within your environment to allow Kerberos referral processing across forest trust relationships because having RDS DB Instances in the same AWS region joined to different domains with trust relationships will prevent cross trust authentication.
  
  Adding an UPN suffix requires elevated privileges in the domain, especially if done on the forest level.
3. Add an SPN to your RDS DB instance.
  1. Add the correct SPN to your RDS for SQL Server DB instance. First, find the Windows server the database instance is running on. Log in to your SQL Server through SQL Management Studio and run the following query. The result shows the computer account name, user account, and the login protocol used:
    select @@SERVERNAME from sys.dm_exec_connections where session_id=@@spid
    SQL
  2. Now add the SPN to the computer using the SetSPN command line tool:
    SetSPN -S MSSQLSvc/<RDS endpoint>:<RDS Port> <Computer Account or domainname\computername$>
    PowerShell
    $Output of SetSPN -S MSSQLSvc/<RDS endpoint>:<RDS Port> <Computer Account or domainname\computername$> command$
  3. Confirm that your SPN has been added to the correct computer object in Active Directory:
    Setspn -T * -F -Q MSSQLSvc/<RDS endpoint>*
    PowerShell
    Note that SQL connections for your RDS for SQL Server DB instance will no longer fall back to NTLM when an SPN is present. If you face troubles, you can remove the SPN:
    SetSPN -D MSSQLSvc/<RDS endpoint>:<RDS Port> <Computer Account>
    PowerShell
4. Verify that the Kerberos protocol is used.
  1. Verify that you can access your RDS for SQL Server DB instance through Kerberos. Connect to your SQL Server DB instance through SQL Management Studio using Windows Authentication.
  2. Run the following SQL query to confirm Kerberos protocol has been used for authentication.
    select @@SERVERNAME,protocol_type, auth_scheme where session_id=@@spid select SUSER_NAME()
    SQL
5. Update the SPN on host replacement or failover.
  Certain events—such as host replacement for RDS Single-AZ DB instances or failovers—cause the database instance to run on another Windows server. Amazon RDS will update the IP address for the DNS entry for the SQL endpoint. The DNS Time-to-Live (TTL) on this A-Record is 5 seconds. But RDS will not update the SPN that you manually created in Active Directory.
  1. For an RDS Multi-AZ DB, you can force an update to the SPN by running the following command in Cloudshell:
    aws rds reboot-db-instance --db-instance-identifier <your DB identifier> --force-failover
    Code
    When you try to connect using Integrated Windows Authentication afterwards, you will receive an SSPI context error.
  2. To enable Integrated Windows Authentication again, you need to adjust the SPN by deleting it on the previous computer account and adding it to the current account again as described in Step 3.
  3. To delete an existing SPN for a computer use the following command where computername is the computer account of the old primary instance.
    SetSPN -d MSSQLSvc/<RDS endpoint>:<RDS Port> <Computername or domainname\computername$>
    PowerShell
  4. If you don’t know the computername the SPN is currently registered to, you can also search for the SPN using the following command.
```
SetSPN -T * -F -Q MSSQLSvc/<RDS endpoint>*
```
  PowerShell

Automate the SPN updates upon host replacement

To automate SPN updates and respond to host replacement or failover events, we’ve created an AWS Serverless Application Model (AWS SAM) template (available on GitHub) that deploys the necessary services. This template facilitates seamless updates of the hostname in the SPN.

Disclaimer:

By implementing this solution, you acknowledge that you are responsible for owning, maintaining, and troubleshooting the solution. AWS Support may provide assistance, but ultimate responsibility for the solution’s functionality and any future errors lies with you, the customer.

The automation process for writing Service Principal Names (SPNs) may add a few seconds to the normal RDS DB Instance failover time. This slight increase in failover duration is due to the additional steps required to update the SPNs.

The successful operation of this solution relies on proper permissions and network connectivity. If future SPN updates fail due to removed permissions or firewall rules preventing proper communication, Windows logon to the RDS DB Instance may fail. It is crucial to maintain the necessary permissions and verify network connectivity for the solution to function correctly.

By proceeding with the implementation, you acknowledge these considerations and agree to manage the solution accordingly.

Prerequisites

We use the Active Directory user created during the process of joining the RDS for SQL Server DB instance to the self-managed Active Directory to establish the connection to the SQL Server instance.

Clone the GitHub repository and verify the AWS SAM CLI is installed.

The RDS for SQL Server DB Instance runs in a private subnet.

The user needs to be manually added to the SQL Server instance LOGINS with the PUBLIC server role:

USE [master]

GO

CREATE LOGIN [EXAMPLE\svcRDS] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]

GO

Initialize the SAM Project (if needed)
```
sam init
```
PowerShell
– Follow the prompts to generate a new project or use an existing template.yaml.
Build the SAM Application
```
sam build
```
PowerShell
– This processes the template.yaml file, preparing dependencies and creating artifacts.
Package the Application
– Upload artifacts to an S3 bucket by running:
```
sam package --s3-bucket <YOUR_S3_BUCKET> --output-template-file packaged.yaml
```
PowerShell
– Replace <YOUR_S3_BUCKET> with the name of an existing S3 bucket in your AWS account.
Deploy the Application
```
sam deploy --template-file packaged.yaml --stack-name <STACK_NAME> --capabilities CAPABILITY_IAM
```
PowerShell
– Replace <STACK_NAME> with your desired CloudFormation stack name.

Notes

Use sam deploy --guided for an interactive deployment process that saves configuration settings for future use.

The template deploys an AWS Lambda function within the specified subnet and virtual private cloud (VPC). This function connects to an RDS DB instance to retrieve the new hostname.

Consider re-using the secret created for the Self-Managed Active Directory integration.

The function is triggered automatically whenever a replacement or failover event occurs, managed by Amazon EventBridge. After the new hostname is obtained, the Lambda function executes an AWS Systems Manager document on the Windows instance to deregister the old hostname and update it with the new one.

Throughout this process, each step is logged in AWS CloudWatch to facilitate integration and diagnostics, providing visibility into the workflow, as shown in the following figure.

Clean up

To remove the resources deployed using the SAM template, you can use the sam delete command. This will delete the resources provisioned for the solution through CloudFormation from your AWS account.

Conclusion

Enhancing the already robust security features of Amazon RDS for SQL Server, implementing Kerberos authentication for instances joined to a self-managed Active Directory provides an additional layer of protection and reliability for your database connections. By following the steps outlined in this post, you can avoid using the default NTLM authentication protocol and help facilitate consistent use of Kerberos authentication. This approach not only improves security by using the strengths of Kerberos, such as mutual authentication and ticket-based access, but also maintains compatibility with applications and tools designed to work with Windows Authentication.

The process we’ve described—from adding UPN suffixes and configuring SPNs to automating updates for failover scenarios—provides a comprehensive solution to a common challenge faced by organizations using RDS for SQL Server with self-managed Active Directory. By implementing these practices, you can:

Enhance security through stronger authentication methods
Improve compatibility with existing Windows-based applications
Automate SPN management to handle dynamic infrastructure changes

Leave your questions and feedback in the comments section.

About the Authors

Andrea Caldarone is a Principal Technical Account Manager at AWS. He is a database enthusiast with 30 years of experience in relational database systems. Outside of work, Andrea plays football for an amateur team and loves to make pizza for his family and friends.

Arne Scherhag is a Senior Technical Account Manager at AWS. He is a Microsoft Infrastructure specialist with over 20 years of experience in Active Directory migrations.

Marcos Hernandez Alvarez is a Senior Solutions Architect at AWS. His passion for AWS technologies began more than 15 years ago, and today he is a member of the team dedicated to Volkswagen Group. He also works as an expert in developing solutions for Spatial Computing.

Select your cookie preferences

AWS Database Blog