How to Configure Amazon RDS SQL Server for Windows Authentication

By:   |   Comments   |   Related: > Amazon AWS RDS


We have a large number of users, applications, and services accessing our RDS SQL Server environment. The DBAs have to continuously manage and keep track of these user accounts and their permission levels. So far, they have been creating and managing standard SQL Server logins and roles with custom scripts, but now there is a new requirement for users to log in with their Active Directory account only. How can we make the RDS SQL Servers use an AD-based authentication system?


Database Administrators in large enterprises often need to manage many SQL Server logins for users and services. DBAs have to create SQL Server standard logins and roles without any centralized authentication system, assign role-based permissions, and manually audit login events. With a growing database environment, the process becomes difficult to manage and monitor.

Amazon Web Service now allows RDS SQL Server to integrate with the AWS Managed Active Directory platform. The managed AD can also integrate with existing Active Directories of an enterprise. This allows creating a single, centralized authentication mechanism for all SQL Server database users. By default, Active Directory uses Kerberos as a built-in authentication protocol that encrypts passwords sent over the network. Kerberos makes the authentication process faster and secure. The benefits of Windows-integrated authentication also include better control over user management and auditing. Also, to use services like SSIS and SSRS with RDS, it has to use Windows-based authentication.

This article will show how to set up an AWS Managed Active Directory and integrate it with an AWS RDS SQL Server instance. We will verify the integration by logging in to the database instance.

Setting up an AWS Managed Active Directory

To create an AWS Managed Active Directory, follow these steps:

Select the "Directory Service" link in the AWS Management Console under the "Security, Identity, & Compliance" section.

Directory Service link in the AWS Management Console.

In the Directory Service console, click the "Set up directory" button:

AWS Directory Service console

This shows three options for creating a directory service. Select the option "AWS Managed Microsoft AD" and click "Next":

Selecting the directory type.

In the next screen, select the edition of the Active Directory. In this case, we are choosing the Standard Edition:

Selecting the Active Directory edition.

In the same screen, specify the FQDN (Fully Qualified Domain Name) of the directory, its NetBIOS name, and the administrator password:

Specifying directory FQDN, NetBIOS name and Admin password.

In the next screen, enter the VPC and preferred subnets for the Active Directory. To ensure communication between the RDS SQL Server instance and the Active Directory, select the same VPC where the RDS instance is running. Many organizations use a separate "admin" VPC for its core systems like Active Directory. In such cases, the target VPC and the RDS instance VPC must be peered.

Selecting the VPC and subnets for the Active Directory.

In the next screen, review the options chosen and click "Create directory". It will take approximately half-hour for the Active Directory to become available.

Reviewing the options before creating the Active Directory.

Configure RDS SQL Server for AD Authentication

Both new and existing RDS instances can be configured for Active Directory authentication. In the image below, we are selecting the option to use Active Directory authentication for an RDS SQL Server instance:

Configuring Amazon RDS SQL Server instance for Windows authentication.

Note how this process also authorizes Amazon RDS to create the IAM role necessary for using Windows authentication.

Integrating RDS SQL Server with Amazon Managed Microsoft AD creates a role

This IAM role has a policy with the following permissions:

The IAM policy for the IAM role created by RDS SQL Server and AD integration.

Create an Active Directory User

As a next step, install the "Active Directory Users and Computers" applet in a Windows Server and register the newly created AD in the applet. You can do so using the default Admin user:

Registering the Active Directory as the Admin user

Create a new user in the directory:

Creating a new AD user.

For our test purposes, we have created a user named USER01.

Create SQL Server Windows Login

Once the user is created, log in to the RDS SQL Server instance as the master user from SSMS:

Logging in to RDS SQL Server as the master user

Next, create a login account for the AD user created before:

DEFAULT_LANGUAGE = [us_english]; 
USE [tempdb]
Creating a login for the Windows user.

You can now create a database user for this login and grant the user permissions to database schemas and objects.

To test the login process for PROD\USER01, log out from the current session, select Windows as the authentication mode, and specify the AD account name:

Logging into the RDS SQL Server instance with Windows authentication.

If everything was done successfully, the user should be logged in successfully.


As you can see, configuring an RDS SQL Server for Windows-based authentication is simple but offers data security benefits. Security auditing can easily check log in/log off events for AD users, just like SQL standard logins. However, it also means users can be temporarily disabled or removed from an AD group without anything done from the database side. Role-based permissions assigned to AD groups can free up DBAs from assigning roles to individual users. AD user accounts can also be made to conform to various Active Directory policies. Finally, using AWS-managed Microsoft AD means system administrators don’t have to manually install, configure, and manage a separate Active Directory in EC2 instances and worry about backup, fault tolerance, etc.

Next Steps

Learn more about:

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Poojan Mehta Poojan Mehta is a DevOps and cloud enthusiast with 1.5 years of experience in developing and managing SQL-based databases. His specializations include AWS, DevOps tools, networking, and SQL.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article