How to Enable Windows Integrated Authentication for RDS for SQL Server Using On-Premises Active Directory
On March 23, 2016, AWS announced that Amazon Relational Database Service for SQL Server (RDS for SQL Server) now supports authentication to AWS Directory Service for Microsoft Active Directory (Enterprise Edition), also known as Microsoft AD. On April 7, 2016, AWS launched a new console feature for Microsoft AD that makes it easy for you to configure domain trust relationships and conditional forwarders. Combined, these two features enable running your SQL Server applications in an Amazon Virtual Private Cloud (Amazon VPC) while authenticating users from an on-premises Windows Active Directory domain. Furthermore, you can do so without having to synchronize directories, migrate users to AWS, or use multiple tools to configure domain trusts.
If you want to run your SQL Server applications in AWS and secure access with on-premises Active Directory user accounts, this blog post is for you. In this blog post, I walk you through the steps to enable RDS for SQL Server to authenticate with Microsoft AD and configure trusts between Microsoft AD and your on-premises Active Directory. With that configuration in place, you can run your SQL Server databases and applications in AWS, and authenticate access with on-premises Active Directory user accounts.
For the purposes of this post, I will assume that you already use Active Directory for an on-premises domain. You also should be familiar with what fully qualified domain names (FQDNs) and NetBIOS names are. You will also need to be using Microsoft SQL Server on-premises or RDS for SQL Server without Microsoft AD authentication to follow along.
The goal of this post is to give you single sign-on (SSO) to RDS for SQL Server with your on-premises Active Directory users. To achieve this goal, you need to have Microsoft AD running in your Amazon VPC, RDS for SQL Server enabled for Microsoft AD authentication in your Amazon VPC, and a trust established between the Microsoft AD domain in your Amazon VPC and your on-premises Active Directory domain. Without a trust, a user who logged in to your on-premises Active Directory would not be permitted to access any resources (such as RDS for SQL Server) that are part of your Amazon VPC domain.
Choose a trust direction
Before you start, you must decide between a one-way or two-way trust between the Microsoft AD domain in your Amazon VPC and your on-premises Active Directory domain. The trust direction determines which domains trust users from the other domain.
For example, let’s say you have two domains: VPC-Domain and On-Prem-Domain (see following diagram). A one-way trust from VPC-Domain to On-Prem-Domain means that users authenticated in On-Prem-Domain are trusted in VPC-Domain (the trust direction indicated by the purple arrow in the following diagram). A one-way trust from On-Prem-Domain to VPC-Domain (the trust direction indicated by the green arrow in the following diagram) means users authenticated in VPC-Domain are trusted in On-Prem-Domain. A two-way trust is really two one-way trusts with each domain trusting the other. With a two-way trust, users authenticated in either domain are trusted in both domains. For more details regarding trusts, go to the Microsoft TechNet Domain and Forest Trusts Technical Reference.
To decide between a one-way and a two-way trust, answer this question: do you have anything that authenticates inside your Amazon VPC domain that needs access to something managed in your on-premises Active Directory? For example, is there a service or a user that logs in to an account in your Amazon VPC domain that needs access to printers inside your on-premises domain? If your answer is “yes,” you need a two-way trust. Otherwise, use a one-way trust from the Amazon VPC domain to your on-premises Active Directory (it is always best to grant a minimal set of privileges).
Now that you have decided your trust type, I will go through the steps to set things up.
Step 1: Set up RDS for SQL Server in your Amazon VPC
If you already have RDS for SQL Server running in your Amazon VPC, skip to Step 2 now.
If RDS for SQL Server is not yet running in your Amazon VPC, use Creating a SQL Server DB Instance as a guide to get going. Then go to Step 2.
If you have an existing database instance that is not in an Amazon VPC, move the instance into an Amazon VPC by following the instructions in Moving a DB Instance Not in a VPC into a VPC.
Step 2: Set up Microsoft AD in your Amazon VPC
If you have Microsoft AD already running, skip to Step 3 now. Otherwise, you must get Microsoft AD running. Begin by choosing an FQDN name and NetBIOS name for your Microsoft AD domain that are different from your on-premises names. To create your directory in your Amazon VPC, follow the steps in AWS Directory Service for Microsoft Active Directory (Enterprise Edition).
Step 3: Enable RDS for SQL Server for Windows authentication
To enable RDS for SQL Server for Windows authentication, follow the instructions in Amazon RDS for SQL Server – Support for Windows Authentication.
Step 4: Set up the trust between your VPC domain and your on-premises domain
When setting up a trust, two actions are required: (1) you must configure the trusting domains, and (2) you must configure a conditional forwarder so that the domains can properly route requests. These requirements used to be a little tricky to accomplish, but with the new console feature in Microsoft AD, you can accomplish these configurations from the same screen where you set up your trust relationship. Tutorial: Create a trust relationship between your Microsoft AD on AWS and your on-premises domain explains the configuration steps to configure your on-premises trust and your Microsoft AD trust.
When you are finished, you can confirm your configuration in the Directory Service console by clicking your directory and then clicking the Trust relationships tab. You should see something similar to what the following screenshot shows: a Status of Verified indicates the trust is working.
Congratulations! You have just enabled RDS for SQL Server to authenticate with Microsoft AD, and you have set up the trust from Microsoft AD to your on-premises Active Directory. Now you can run your RDS for SQL Server applications on AWS and authenticate with on-premises user accounts.
If you need to move on-premises SQL Server databases and applications to AWS, here are some related links to help you with that process:
- By using SQL Server Management Studio, you can move your databases and manage access for the users who log in using on-premises Active Directory domain accounts.
- Example: Migrating a Simple Web Application shows how to move your applications to RDS for SQL Server in your Amazon VPC.
- To manage your RDS for SQL Server databases with SQL Server Management Studio, follow Connecting to a SQL Server DB Instance Using SQL Server Management Studio.
If you have comments about this post, please add them to the “Comments” section below. If you have questions about or issues implementing this solution, open a new thread on the Directory Service forum.
Want more AWS Security how-to content, news, and feature announcements? Follow us on Twitter.