Using Windows Authentication with an Amazon RDS for SQL Server DB Instance (Part 1/3)

In this series of posts, we will talk about how to use Windows Authentication with an Amazon RDS for SQL Server DB instance.

This post was supported and reviewed by the Valcann team (www.valcann.com.br).

Using Microsoft Windows Authentication to authenticate users when they connect to your Amazon RDS for Microsoft SQL Server DB instance

You can use Microsoft Windows Authentication to authenticate users when they connect to your Amazon RDS for Microsoft SQL Server DB instance. The DB instance works with AWS Directory Service for Microsoft Active Directory, also called AWS Managed Microsoft AD, to enable Windows Authentication. When users authenticate with a SQL Server DB instance joined to the trusting domain, authentication requests are forwarded to the domain directory that you create with AWS Directory Service.

Amazon RDS supports Windows Authentication for SQL Server in all AWS Regions. RDS supports only AWS Managed Microsoft AD for Windows Authentication. RDS does not support AD Connector.

Steps to set up Windows Authentication on an RDS for SQL Server instance

To set up Windows Authentication for a SQL Server DB instance, perform the following steps, which we will explain in detail in the next posts:

1. Use AWS Managed Microsoft AD, from the AWS Management Console or the AWS Directory Service API, to create an AWS Managed Microsoft AD directory;
2. If you use the AWS CLI or the Amazon RDS API to create your SQL Server DB instance, create an AWS Identity and Access Management (IAM) role. This role uses the AmazonRDSDirectoryServiceAccess managed IAM policy and allows Amazon RDS to make calls to your directory. If you use the console to create your SQL Server DB instance, AWS creates the IAM role for you.

For the role to allow access, the AWS Security Token Service (AWS STS) endpoint must be activated in the AWS Region for your AWS account. AWS STS endpoints are active by default in all AWS Regions, and you can use them without any further action;

1. Create and configure users and groups in the AWS Managed Microsoft AD directory using the Microsoft Active Directory tools;
2. If you plan to place the directory and the DB instance in different VPCs, enable cross-VPC traffic;
3. Use Amazon RDS to create a new SQL Server DB instance from the console, the AWS CLI or the Amazon RDS API. In the create request, you provide the domain identifier (“ d-*” identifier) that was generated when you created your directory and the name of the role you created. You can also modify an existing SQL Server DB instance to use Windows Authentication by setting the domain and IAM role parameters for the DB instance;
4. Use the Amazon RDS master user credentials to connect to the SQL Server DB instance as you would with any other DB instance. Because the DB instance is joined to the AWS Managed Microsoft AD domain, you can provision SQL Server logins and users from the Active Directory users and groups in your domain. (These are known as SQL Server “Windows” logins.) Database permissions are managed through standard SQL Server permissions granted to and revoked from these Windows logins.

Creating the endpoint for Kerberos authentication

Kerberos-based authentication requires the endpoint to be the customer-specified host name, a period, and then the fully qualified domain name (FQDN). For instance, the following is an example of an endpoint you can use with Kerberos-based authentication. In this example, the SQL Server DB instance host name is ad-test and the domain name is corp-ad.company.com.

ad-test.corp-ad.company.com

If you want to make sure your connection is using Kerberos, run the following query:

SELECT net_transport, auth_scheme

FROM sys.dm_exec_connections

WHERE session_id = @@SPID;

In the next posts, we will talk about how to set up Windows Authentication for SQL Server DB instances.

See you then!