Integrate Amazon RDS for SQL Server DB Instances with an Existing Active Directory Domain

Vlad Vlasceanu is a solutions architect at Amazon Web Services.

Last year, we launched support for Windows Authentication in Amazon RDS for SQL Server. This feature has been quite popular with customers. It lets you deploy Microsoft SQL Server-based workloads with Amazon RDS using the same centralized user management patterns that you use in your own data centers, by leveraging Microsoft Active Directory.

In Amazon RDS for SQL Server, you can join both new and existing DB instances to a managed Active Directory domain that is provided by AWS Directory Service. Just as Amazon RDS gives you the ability to operate fully managed database instances (including SQL Server), AWS Directory Service provides fully managed directories that you can use in your workload deployment. In both cases, you get a turnkey solution. AWS handles the deployment, operation, high availability, reliability, and security of the service—allowing you to focus on what matters most: operating your own workload effectively.

This post describes how you can use forest trust relationships to extend your existing Windows Authentication capabilities to SQL Server instances that are deployed using Amazon RDS.

Using AWS Directory Service with Amazon RDS for SQL Server

AWS Directory Service provides several different directory service types, each with its own feature set, and each designed for specific use cases. To use the Windows Authentication feature in RDS for SQL Server, you must use the Microsoft Active Directory (AD) directory service type.

With a Microsoft AD directory, you can build out a fully managed Active Directory environment in the cloud. You can join your Amazon EC2 instances to the provided domain and join your RDS for SQL Server DB instances to that same domain. You can create and manage users, groups, and policies directly in the managed domain. The result is an environment that looks and works a lot like a traditional Active Directory environment that you’re used to. However, you don’t have to spend time managing the day-to-day operational aspects of the resources underpinning your domain controllers or SQL Server databases.

But what if you already have an existing corporate Active Directory domain—either running in AWS in a virtual private cloud (VPC) using EC2 instances, on-premises in your data center, or even extended between both? In these cases, you can still use RDS for SQL Server and Windows Authentication, even if your users and computers are joined to your existing domain.

Forest trusts

With a Microsoft AD directory, you can establish forest trusts. Your RDS SQL Server resources must be joined to an AWS managed Microsoft AD directory. But you can establish a trust relationship between that AWS managed forest and the forest of your corporate domain. For Windows Authentication in RDS for SQL Server to work, you only need a one-way directional forest trust, where the AWS managed forest trusts the corporate domain forest.

Your corporate domain takes the role of the trusted domain, and the AWS Directory Service managed domain takes the role of the trusting domain. Validated authentication requests travel between the domains in only one direction—allowing accounts in your corporate domain to authenticate against resources shared in the managed domain. In this case, RDS for SQL Server resources interact only with the managed domain, and the managed domain then passes on the authentication requests to your corporate domain.

Trust relationships and authentication flow

The following diagram illustrates the trust relationship flow, factoring in an RDS for SQL Server resource and a user from the corporate domain:

The following are steps in the trust relationship flow, as illustrated using AWS based resources:

1. The end user connects to an Amazon EC2 instance that is joined to the corporate domain.
2. The authentication requests flow from the EC2 instance to the corporate domain controllers.
3. The end user connects to an RDS for SQL Server DB instance that is joined to the managed domain, using corporate domain credentials from the EC2 instance that is joined to the corporate domain.
4. The authentication requests flow from the RDS DB instance to the managed domain controllers.
5. Because the two forests are configured in a one-way trust, authentication requests are relayed to the corporate domain controllers from the managed domain.

This configuration also extends single sign-on (SSO) benefits for corporate domain users to resources joined to the managed domain. Both your application service accounts and end-user accounts from the corporate domain can authenticate against resources in the managed domain. This allows both applications and your database administrators to connect to RDS for SQL Server using Windows Authentication, as if it were a resource in the corporate domain.

However, if you create a user account in the managed domain, that user can’t log in to a computer that is joined to your corporate domain or access resources in that domain with the current trust relationship configuration. This protects your corporate domain from any malicious users located in the managed domain.

Walkthrough: Set up a forest trust relationship to use with RDS for SQL Server

This walkthrough assumes that your corporate domain already exists and is either deployed on-premises, extended to your Amazon VPC, or deployed in your Amazon VPC on EC2 instances.

Note: If you want to deploy a proof of concept independent of your corporate domain, you can use the deployment in the Active Directory DS on AWS Quick Start to configure an initial corporate domain that is isolated from your production domain. This Quick Start also has a detailed deployment guide. You can use Scenario 1 to follow along with the steps in this walkthrough.

Before you begin

To complete the walkthrough, your AWS environment and corporate domain deployment should include at least the following:

• A VPC with at least two subnets in different Availability Zones. We recommend using private subnets.
• One or more domain controllers for your corporate domain, with working DNS resolution throughout the domain. (The examples identify it as corp.quackynature.com, or QNCORP.) If your domain controllers are deployed on-premises, we recommend extending your domain to the VPC by deploying secondary domain controllers in a highly available configuration in your VPC, with private connectivity (e.g., VPN) between the two environments.
• One or more bastion hosts (Remote Desktop EC2 instances), or other mechanism that allows you to connect to the corporate domain and change the configuration.
• Security groups that control inbound and outbound network access between the domain controller and domain member EC2 instances.
• A user account with membership in the Domain Admin or Enterprise Admin groups to establish a forest trust relationship.
• AWS Identity and Access Management (IAM) permissions that allow you to create and manage AWS Directory Service resources and Amazon RDS DB resources, along with any additional supporting resources mentioned here that don’t exist in your environment.
  In AWS, you use IAM to control user access to AWS services. Attaching the following AWS managed policies will allow you to create, update and delete RDS and Directory Service resources as indicated further in this blog post:
  • AmazonRDSFullAccess
  • AmazonDirectoryServiceFullAccess

  Additionally, the following policy is necessary to create, update and delete the IAM role used by your RDS DB instance to access the domain:

  {
      "Version": "2012-10-17",
      "Statement": [
          {
              "Effect": "Allow",
              "Action": [
                  "iam:ListPolicies",
                  "iam:GetRole",
                  "iam:GetPolicyVersion",
                  "iam:ListRoleTags",
                  "iam:GetPolicy",
                  "iam:UpdateRoleDescription",
                  "iam:ListRoles",
                  "iam:CreateRole",
                  "iam:DeleteRole",
                  "iam:AttachRolePolicy",
                  "iam:PutRolePolicy",
                  "iam:PassRole",
                  "iam:ListAttachedRolePolicies",
                  "iam:DeleteRolePolicy",
                  "iam:UpdateRole",
                  "iam:ListRolePolicies",
                  "iam:GetRolePolicy"
              ],
              "Resource": "*"
          }
      ]
  }

  Depending on the prerequisites that you do not meet, you may also need additional permissions to create or update the relevant networking constructs and bastion hosts.

Setting up the forest trust relationship

In this procedure, you create a Microsoft AD directory, a forest trust, and an RDS for SQL Server instance. You can launch and manage AWS resources by using the AWS Management Console, AWS Command Line Interface (CLI), AWS Tools for Windows PowerShell, or the AWS SDKs. These examples use the AWS Tools for Windows PowerShell. For information about installing and configuring these tools, see Setting up the AWS Tools for Windows PowerShell.

1. Launch the AWS Directory Service in the same VPC as your existing domain. Choose two subnets that have connectivity to your existing domain controllers and are in different Availability Zones. Replace the values in this example with values that are representative of your environment:

   PS C:\> New-DSMicrosoftAD `
   -Name "rds.quackynature.com" `
   -Description "Domain for RDS for SQL Server" `
   -Password "************" `
   -ShortName "QNRDS" `
   -VpcSettings_SubnetId "subnet-xxxxxxxx", "subnet-yyyyyyyy" `
   -VpcSettings_VpcId "vpc-xxxxxxxx"

   The Directory Service identifier is available immediately: d-xxxxxxxxxx. However, the resource may take several minutes to provision. You can retrieve the properties of the domain using the following cmdlet:

   PS C:\> Get-DSDirectory -DirectoryId “d-xxxxxxxxxx”

   The output should look similar to the following.

   

   Note the DnsIpAddrs values. These are your domain controller IP addresses, and you will need them later.

2. Before you can establish the trust relationship between the two forests, you must meet the following prerequisites. The AWS Directory Service documentation on trusts provides a comprehensive guide to addressing these requirements.
   1. Ensure that the Microsoft AD directory service can receive inbound connections and can initiate network connections for Active Directory-related traffic with your corporate domain controllers. AWS Directory Service automatically creates a security group for the newly created resource. It must be adjusted to allow communication with corporate domain controllers.
   2. Ensure that your corporate domain controllers can receive inbound connections from the new directory controllers.
   3. Create a DNS conditional forwarder on your corporate domain to the new domain’s DNS name, providing the IP addresses of the directory’s domain controllers.
3. After you meet all the prerequisites, you can create the inbound forest trust in your corporate domain. Connect to the domain controller instance, or another domain-joined computer that has the “AD DS and AD LDS Tools” and “DNS Server Tools” Remote Server Administration Tools installed:
   1. Launch the Active Directory Domains and Trusts
   2. In the selector tree on the left, open the context (right-click) menu for the domain you want, and choose Properties.
   3. On the Trusts tab, choose New Trust.
   4. Follow the steps in the New Trust Wizard, choosing the following settings:
      • Provide the Name of the remote (AWS managed) domain (e.g., rds.quackynature.com)
      • On the Trust Type screen, choose Forest Trust.Note: If you don’t see this as an option, your network configuration probably prevents the domain controllers from connecting to each other, or the conditional forwarder is not set up correctly.
      • Under Direction of Trust, choose One-way: incoming.
      • Under Sides of Trust, choose This domain only. You cannot configure the other domain from this wizard in one step.
      • Provide a Trust Password. You need the same password to complete the relationship later.
      • Under Confirm Incoming Trust, choose No, do not confirm the incoming trust. You cannot confirm the other domain’s trust side from this wizard in one step.
4. You can now establish the Outgoing forest trust relationship between the new domain and your existing one:

   PS C:\> New-DSTrust `
   -DirectoryId "d-xxxxxxxxxx" `
   -ConditionalForwarderIpAddr "10.0.11.10", "10.0.12.10" `
   -RemoteDomainName "corp.quackynature.com" `
   -TrustDirection "One-Way: Outgoing" `
   -TrustPassword "************" `
   -TrustType "Forest"

   Provide the same trust password you used to create the incoming trust side earlier. The AWS API returns a trust identifier: t-xxxxxxxxxx. You can track the status of the trust using this cmdlet:

   PS C:\> Get-DSTrust -DirectoryId "d-xxxxxxxxxx" -TrustId "t-xxxxxxxxxx"

   The output should look similar to the following:

   

   Note the TrustState value—this indicates the trust relationship verification status.

5. Before you can provision the RDS for SQL Server DB instance, you must provision the following prerequisites:
   1. If you don’t already have one, create a DB subnet group spanning at least two subnets in different Availability Zones. This group designates the subnets where your DB instances can be deployed. Create one using the following cmdlet:

      PS C:\> New-RDSDBSubnetGroup `
      -DBSubnetGroupName "sql-server-subnets" `
      -DBSubnetGroupDescription "Subnets for Use with RDS for SQL Server" `
      -SubnetId "subnet-xxxxxxxx", "subnet-yyyyyyyy"

   2. If you don’t already have one, create a security group to control access to your DB instances. At a minimum, this group should allow inbound connections on port 1433, or whichever port you choose for database traffic from known sources.Create a security group using the following example script:

      PS C:\> $groupid = New-EC2SecurityGroup `
      -VpcId "vpc-xxxxxxxx" `
      -GroupName "sql-server-access" `
      -GroupDescription "Access RDS for SQL Server DB instances"
      $rule = New-Object Amazon.EC2.Model.IpPermission
      $rule.IpProtocol = "tcp"
      $rule.FromPort = 1433
      $rule.ToPort = 1433
      $rule.IpRanges.Add("10.0.0.0/16")
      Grant-EC2SecurityGroupIngress -GroupId $groupid -IpPermissions @( $rule )

   3. Configure an IAM role that permits your DB instance to join the domain. The following script shows an example:

      PS C:\> New-IAMRole `
      -RoleName "rds-ds-access-role" `
      -AssumeRolePolicyDocument @”
      {
        "Version": "2012-10-17",
        "Statement": [
          {
            "Sid": "",
            "Effect": "Allow",
            "Principal": {
              "Service": "rds.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
          }
        ]
      }
      “@
      Register-IAMRolePolicy `
      -RoleName "rds-ds-access-role" `
      -PolicyArn "arn:aws:iam::aws:policy/service-role/AmazonRDSDirectoryServiceAccess"

6. You can now provision the RDS for SQL Server instance. The following example script shows some available options:

   PS C:\> New-RDSDBInstance `
   -DBInstanceIdentifier "demo-sqlsrv" `
   -DBInstanceClass "db.m4.large" `
   -Engine "sqlserver-se" `
   -EngineVersion "13.00.2164.0.v1" `
   -AllocatedStorage 200 `
   -StorageType "gp2" `
   -LicenseModel "license-included" `
   -MultiAZ 1 `
   -AutoMinorVersionUpgrade 1 `
   -BackupRetentionPeriod 35 `
   -Domain "d-xxxxxxxxxx" `
   -DomainIAMRoleName "rds-ds-access-role" `
   -DBSubnetGroupName "sql-server-subnets" `
   -VpcSecurityGroupId "sg-xxxxxxxx" `
   -MasterUsername "demoadmin" `
   -MasterUserPassword "************" `
   -StorageEncrypted 1

7. After the DB instance is provisioned, you can start creating logins for the domain users. First, log in with the master user name and password provided at launch, using SQL Server Authentication.
8. If you prefer to use your existing domain database administrator logins to manage the RDS DB instance, you can configure the users using the following example T-SQL script:

   CREATE LOGIN [DOMAIN\DBAdmin] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english];
   GO
   EXEC sp_addsrvrolemember 'DOMAIN\DBAdmin', 'processadmin';
   EXEC sp_addsrvrolemember 'DOMAIN\DBAdmin', 'setupadmin';   
   GO
   GRANT ALTER ANY CONNECTION, ALTER ANY LINKED SERVER, ALTER ANY LOGIN, ALTER ANY SERVER ROLE, ALTER SERVER STATE, ALTER TRACE, CONNECT SQL, CREATE ANY DATABASE, VIEW ANY DATABASE, VIEW ANY DEFINITION, VIEW SERVER STATE TO [DOMAIN\DBAdmin] WITH GRANT OPTION;
   GO

   Now, you can use your existing domain credentials to manage access permissions for your users:

   CREATE LOGIN [DOMAIN\Domain Users] FROM WINDOWS WITH DEFAULT_DATABASE = [master], DEFAULT_LANGUAGE = [us_english];
   GO

Using forest trust relationships with RDS for SQL Server

Forest trusts help you manage a segmented Active Directory infrastructure. The reasons for implementing such segmentation range widely, including achieving a certain degree of administrative autonomy. Although this architecture adds the complexity of having to operate an additional domain and forest, it ensures that AWS can provide operational consistency over time for your RDS DB instances, regardless of the policies enforced in your existing domain that might conflict with that ability.

From a security perspective, this architecture reinforces security imperatives and requirements that you already need to follow for your corporate domain today. You should also apply the same practices to your managed domain. Any malicious user who is located in your existing domain would now potentially have access to shared resources in the managed domain, along with resources in your existing domain.

However, the trust relationship itself is not sufficient to gain access to data that is stored on RDS for SQL Server DB instances. Because RDS for SQL Server is a managed service, access is restricted to only authenticated connections to the SQL Server service itself, with no desktop or file system access. Any legitimate users who connect to the service must have corresponding logins configured in SQL Server, either individually or as part of groups.

In summary, forest trust relationships can be an effective mechanism to extend existing Windows Authentication capabilities to SQL Server instances that are deployed using Amazon RDS.

This post has been updated as of March 10, 2020.