AWS Database Blog

Join Amazon RDS for SQL Server to your self-managed Active Directory

In this post, we show you how to integrate your Amazon Relational Database Service (Amazon RDS) for SQL Server instances with your self-managed Active Directory (AD). Previously, you could only join your RDS for SQL Server instances to an AWS Managed Microsoft AD directory. You were able to connect your on-premises AD via a trust relationship between an AWS Managed Microsoft AD and your on-premises AD, as shown in the following figure.

With the launch of self-managed Active Directory support, you can join your RDS for SQL Server instances directly to your self-managed AD domains regardless of where it is hosted. Your AD can be hosted in your corporate data centers, on Amazon Elastic Compute Cloud (Amazon EC2), or other cloud providers. The following diagram shows the scenario when joining to a self-managed AD.AWS Direct Connect or VPN can be configured for the traffic between the corporate environment and the VPC.

Solution overview

To implement RDS for SQL Server instances with self-managed Active Directory, you first need to create 3 objects in Active Directory:

  • An Organizational Unit (OU) for the Amazon RDS for SQL Server deployment
  • An AD service account for the Amazon RDS for SQL Server deployments and management
  • Delegate the AD service account with limited permissions to the OU

We recommend creating a new OU and AD service account for each RDS for SQL Server instance deployment. Doing so increases your security boundary.

The following diagram illustrates the solution architecture.

To simplify the setup of the AD objects, we created a PowerShell script to create the objects and set the appropriate permissions for you. The script can be downloaded here.

In the following sections, we discuss the steps to create an AD OU and an AD service account, and create an AWS Key Management Service (AWS KMS) key and secret in AWS Secrets Manager to store your Amazon RDS AD service account credentials.

First, you create the AD objects required to integrate Amazon RDS for SQL Server with a self-managed Microsoft AD. You use a PowerShell script to generate all of the prerequisites prior to deploying Amazon RDS for SQL Server. Specifically, the script creates the following:

  • An OU for Amazon RDS for SQL Server named RDS-MSSQL (or any name of your choice)
  • An AD service account for Amazon RDS for SQL Server named RdsServiceAccount (or any name of your choice) with the proper least-privilege permissions

Next, you create a new Secrets Manager secret to store your service account information and a new KMS key to encrypt the secret. After you have stored the service account information, you update the secret’s resource permission, granting the Amazon RDS service principal GetSecretValue permissions.

Lastly, you create an RDS subnet group, deploy your RDS for SQL Server instance integrated with your self-managed AD, and validate the deployment.

Pricing estimate for resources deployed in this post

If you plan on using your own directory, make sure you use the information for your environment.

The estimated total cost to run the resources for 24 hours is $29.54 (USD) in the us-west-2 region. The following table summarizes the pricing details.

Service Count Price per Hour (USD) Region Estimated 24 Hour Cost (USD) Calculations
Secrets Manager secret 2 $0.00056 us-west-2 $0.03 = 0.00056 * 2 * 24
t3.large EC2 Windows instance 1 $0.1108 us-west-2 $2.66 = 0.1108 *24
t3.medium EC2 Windows instance 1 $0.06 us-west-2 $1.44 = 0.06 * 24
Amazon EBS General Purpose SSD (gp3) – storage 90 $0.08 GB-Month us-west-2 $0.24 = .08 * 90 * 86400 / 2,592,000
KMS service key 1 $0.0014 us-west-2 $0.03 = 0.0014 * 24
Amazon RDS for SQL Server Standard (single AZ – db.t3.xlarge) 1 $1.044 us-west-2 $25.06 = 1.044 *24
Amazon RDS database storage (General Purpose (SSD) storage) 20 $0.115 GB-Month us-west-2 $0.08 = .115 * 20 * 86400 / 2,592,000

Prerequisites

You need the following resources deployed to follow along with this post:

  • An Active Directory, either self-managed or using AWS Managed Microsoft AD. Both work with the implementation steps. For this post, we use a single domain controller self-managed AD with the domain name of corp.example.com. Note that this post uses a bootstrapped self-managed AD on an EC2 instances and should not be used for production purposes. If you are using a different domain name, make sure you update it where appropriate.
  • An EC2 Windows Server instance (referred to as the MGMT EC2 instance in this post) joined to your AD with the Active Directory Administration tool and SQL Server Management Studio (SSMS) installed.

We provide an AWS CloudFormation template to help you deploy the prerequisites in a new VPC. The template can be downloaded here.

Deploy the CloudFormation template

To deploy the CloudFormation template, complete the following steps:

  1. On the AWS CloudFormation console, choose Create stack.
  2. Select Upload a template file, then choose Choose file.
  3. Browse to the CloudFormation template you downloaded, then choose Next.
  4. For Stack name, enter RDS-Self-AD.
  5. In the Parameters section, leave the defaults.
  6. On the Configure stack options page, leave the defaults and choose Next.
  7. On the Review stack page, select I acknowledge that AWS CloudFormation might create IAM resources with custom names and choose Submit.

Create the Active Directory OU and service account

To set up your Active Directory OU and service account, complete the following steps:

  1. On the Secrets Manager console, navigate to the OnPremAdministratorSecret secret.
  2. Under Secret details, choose Retrieve secret value.
  3. Note the key-value for the password to use later.
  4. Open the AWS Systems Manager Fleet Manager Remote Desktop console.
  5. Choose Add new session, then select the MGMT EC2 instance node and choose Add.
  6. Select User credentials and enter the following.
    1. For Username, enter corp\Administrator.
    2. For Password, enter the value you retrieved from the OnPremAdministratorSecret secret.
  7. Choose Connect.
  8. Download the PowerShell script here that creates the OU and service account.
  9. Choose (right-click) Start and select Windows PowerShell (Admin).
  10. In the Windows PowerShell window, make sure you are in the directory you downloaded the script to and run the following code (if you’re deploying this in your own environment, update the $RDSDeployment hash table variable with the proper values).
$RDSDeployment = @{
    RdsOUBaseDn       = 'DC=corp,DC=example,DC=com'
    RdsOUName         = 'RDS-MSSQL'
    RdsSvcAccountName = 'RdsServiceAccount'
    RdsSvcAccountPw   = Get-Credential -Message 'Please provide a password for the RDS Service Account RdsServiceAccount' -User 'RdsServiceAccount' -ErrorAction Stop | Select-Object -ExpandProperty 'Password'
}

.\Set-RDSAdObjects.ps1 @RDSDeployment

The script output should look like the following:

PS C:\temp> .\Set-RDSAdObjects.ps1 @RDSDeployment
Getting AD domain information.
Getting RootDSE information.
Getting computer SchemaNamingContext.
Getting ExtendedRightsMap.
Creating OU RDS-MSSQL.
Creating RDS Service Account RdsServiceAccount.
Getting RdsServiceAccount SID.
Creating ACL object S-1-5-21-1232016207-3383558643-744262169-1104 CreateChild, DeleteChild Allow bf967a86-0de6-11d0-a285-00aa003049e2 
All 00000000-0000-0000-0000-000000000000.
Getting ACL for OU=RDS-MSSQL,DC=corp,DC=example,DC=com and adding new rule.
Setting ACL for OU=RDS-MSSQL,DC=corp,DC=example,DC=com.
Creating ACL object S-1-5-21-1232016207-3383558643-744262169-1104 Self Allow f3a64788-5306-11d1-a9c5-0000f80367c1 Descendents bf967a86
-0de6-11d0-a285-00aa003049e2.
Getting ACL for OU=RDS-MSSQL,DC=corp,DC=example,DC=com and adding new rule.
Setting ACL for OU=RDS-MSSQL,DC=corp,DC=example,DC=com.
Creating ACL object S-1-5-21-1232016207-3383558643-744262169-1104 Self Allow 72e39547-7b18-11d1-adef-00c04fd8d5cd Descendents bf967a86
-0de6-11d0-a285-00aa003049e2.
Getting ACL for OU=RDS-MSSQL,DC=corp,DC=example,DC=com and adding new rule.
Setting ACL for OU=RDS-MSSQL,DC=corp,DC=example,DC=com.

Create a KMS key and secret to store the Amazon RDS service account credentials

Now that you have created the target OU and service account with proper delegation, you need to store the service account information in a Secrets Manager secret.

Set up the KMS key

To configure your KMS key, complete the following steps:

  1. On the AWS KMS console, choose Customer managed keys in the navigation pane.
  2. Choose Create key.
  3. For Key type, select Symmetric.
  4. For Key usage, select Encrypt and decrypt.
  5. In the Advanced options section, for Key material origin, select KMS.
  6. For Regionality, select Single-Region key.
  7. Choose Next.
  8. On the Add labels page, enter an alias (for this post, we enter RDS-Self-AD), then choose Next.
  9. On the Define key administrative permissions page, select your IAM user or role, then choose Next.
  10. On the Define key usage permissions page, select your IAM user or role, then choose Next.
  11. On the Review page, review your selections and choose Finish.
  12. On the AWS KMS console, choose the alias of the key you just created.
  13. On the Key policy tab, select Switch to policy view, then choose Edit.
  14. Append the following to your key policy and choose Save changes:
{
  "Sid": "Allow use of the key",
  "Effect": "Allow",
  "Principal": {
    "Service": [
      "rds.amazonaws.com"
    ]
  },
  "Action": "kms:Decrypt",
  "Resource": "*"
}

Note: For Encryption Key, don’t use the AWS default KMS key. Be sure to create the AWS KMS key in the same AWS account that contains the RDS for SQL Server DB instance that you want to join to your self-managed AD

Store the service account credentials in a Secrets Manager secret

To store your credentials in a Secrets Manager secret, complete the following steps.

  1. On the Secrets Manager console, choose Store a new secret.
  2. For Secret type, select Other type of secret.
  3. For Key/value pairs, add the following key pair.
    1. For the key, enter CUSTOMER_MANAGED_ACTIVE_DIRECTORY_USERNAME.
    2. For the value, enter the name you passed in the RdsSvcAccountName parameter of the PowerShell script.
  4. Choose Add row and add another key pair.
    1. For the key, enter CUSTOMER_MANAGED_ACTIVE_DIRECTORY_PASSWORD.
    2. For the value, enter the password you passed for the RdsSvcAccountPw parameter of the PowerShell script.
  5. For Encryption key, choose the key you created earlier.
  6. Choose Next.

  7. For Secret name, enter a name (for this post, we use RDS-Self).
  8. In the Resource permissions section, choose Edit permissions.
  9. Enter the following resource policy, then choose Save: 
    {
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "Service": [
          "rds.amazonaws.com"
        ]
      },
      "Action": "secretsmanager:GetSecretValue",
      "Resource": "*",
      "Condition":
      {
          "StringEquals":
          {
              "aws:sourceAccount": "123456789012"
          },
              "ArnLike":
          {
              "aws:sourceArn": "arn:aws:rds:us-west-2:123456789012:db:*"
          }
      }
    }
  ]
}


    Note: The AWS account IDs used in this blog are for demonstration only. Readers should change the AWS account IDs accordingly.

  10. On the Configure rotation page, leave the defaults and choose Next.
  11. On the Review page, review your selections and choose Store.
  12. On the Secrets Manager console, choose the refresh icon and choose the secret name you just created.
  13. On the Secret details page, make note of the secret ARN to use when you deploy the RDS for SQL instance.

Create an RDS subnet group

In this step, you create a DB subnet group. A DB subnet group is a collection of subnets in a VPC that you then designate for your DB instances. Each DB subnet group should have subnets in at least two Availability Zones in a given Region. When creating a DB instance in a VPC, you choose a DB subnet group for it. From the DB subnet group, Amazon RDS chooses a subnet and an IP address within that subnet to associate with the DB instance. The DB uses the Availability Zone that contains the subnet.

  1. On the Amazon RDS console, choose Subnet groups in the navigation pane.
  2. Choose Create DB subnet group.
  3. For Name, enter a name (for this post, we enter RDS-Self-AD).
  4. For Description, enter a description (for this post, we enter RDS-Self-AD).
  5. For VPC, choose the VPC that contains the subnets you wish to deploy your RDS instance to.
  6. For Availability Zones, choose the Availability Zones containing the subnets you wish to deploy your RDS instance to.
  7. For Subnets, choose the subnets you wish to deploy your RDS instance to.
  8. Choose Create.

Deploy the RDS for SQL instance integrated with the self-managed AD

In this step, you deploy the RDS for SQL Server instance and join it to your self-managed AD. We don’t walk you through the whole process of deploying an RDS for SQL Server instance; we only point out the configuration settings needed to join RDS for SQL Server instances to AD. For full deployment instructions, refer to Creating an DB instance.

  1. On the Amazon RDS console, choose Databases in the navigation pane.
  2. Choose Create database.
  3. Select Enable Microsoft SQL Server Windows authentication.
  4. For Windows authentication type, select External Active Directory Domain.
  5. For Fully qualified domain name, enter the name of the domain your RDS instance will use (for this post, we use corp.example.com).
  6. For Domain organizational unit, enter the location for your Amazon RDS AD objects (for this post, we enter OU=RDS-MSSQL,DC=corp,DC=example,DC=com).
  7. For Authorization secret ARN, enter the ARN of the secret containing the service account credentials you created.
  8. For Primary DNS, enter a DNS resolver IP of the DNS service that can resolve your self-managed AD.
  9. For Secondary DNS, enter a DNS resolver IP of the DNS service that can resolve your self-managed AD.
  10. Validate all your other settings.

Validate deployment

When your RDS for SQL Server instance is active, you can connect to the instances from the MGMT EC2 instance node using SSMS with the local service account credentials you set when you deployed the instance. Once logged in, you give the corp\Administrator account permission to authenticate into the RDS for SQL Server instance. Complete the following steps:

  1. On the Secrets Manager console, navigate to the secret OnPremAdministratorSecret.
  2. On the Secret details page, in the Secret value section, choose Retrieve secret value.
  3. Note the password for the corp\Administrator account to use in later steps.
  4. Open the AWS Systems Manager Fleet Manager Remote Desktop console.
  5. Choose Add new session, select the MGMT EC2 instance node, and choose Add.
  6. Select User credentials and enter the following:
    1. For Username, enter corp\Administrator.
    2. For Password, enter the password you copied for the OnPremAdministratorSecret secret.
  7. Choose Connect.
  8. Choose Start and launch SSMS.
  9. In the dialog box, provide the following information:
    1. For Server name, enter the name of the RDS for SQL Server instance endpoint.
    2. For Authentication, choose SQL Server Authentication.
    3. For Login, enter the user name you set when you launched the directory.
    4. For Password, enter the password you set when you launched the directory.
  10. Choose Connect.
  11. Once in SSMS, in the navigation pane, expand Security and Logins.
  12. Choose (right-click) Logins and choose New Login.
  13. Enter corp\Administrator for the login name and choose OK.
  14. Choose Start and open a new SSMS window.
  15. In the dialog box, provide the following information:
    1. For Server name, enter the name of the RDS for SQL Server instance endpoint.
    2. For Authentication, choose Windows Authentication.
  16. Choose Connect.

Congratulations! You have successfully signed in to your RDS for SQL Server instance with a user from your self-managed Active Directory.

Clean up

To remove the resources deployed in your account from this post, complete the following steps:

  1. Delete the RDS for SQL Server instance you deployed for this post. For instructions, refer to Deleting a DB instance.
  2. Delete the RDS-Self-AD secret you created. For instructions, refer to Delete an AWS Secrets Manager secret.
  3. Delete the RDS-Self-AD key you created. For instructions, refer to Deleting AWS KMS keys.
  4. Delete the CloudFormation stack that deployed all the prerequisites for this post. For instructions, refer to Deleting a stack on the AWS CloudFormation console.

Summary

In this post, we showed how to deploy an RDS for SQL Server instance integrated with a self-managed AD. We used a sample PowerShell script that easily set up all the Active Directory prerequisites for this new capability. Next, we created a KMS key to encrypt a Secrets Manager secret containing the Amazon RDS AD service account credentials, and deployed a new RDS for SQL Server instance integrated with a self-managed Microsoft AD. Finally, we showed how to validate a self-managed Microsoft AD user to authenticate into the RDS for SQL instance.

Keep an eye out for future posts on this new and exciting capability. if you have any questions or comments please add them below.

About the Authors

Jeremy Girven is a solutions architect specializing in Microsoft workloads on AWS. He has over 17 years’ experience with Microsoft Active Directory and over 25 years of industry experience. One of his fun projects is using SSMS to automate the Active Directory build processes in AWS. To see more, check out the Active Directory AWS Partner Solution.

Siva Thang is a Senior Solutions Architect, Partners with AWS. His specialty is in databases and analytics, and he also holds a master’s degree in Engineering. Siva is deeply passionate about helping customers build a modern data platform in the cloud that includes migrating to modern relational databases and building data lakes and data pipelines at scale for analytics and machine learning. Siva also likes to present in various conferences and summits on the topic of modern databases and analytics.

Pradipta Kishore Das has vast experience in database management systems and has over 18 years of experience with Microsoft SQL Server. He is currently working as a Database Engineer with Amazon Web Services. He works with the Amazon RDS team in building new and exciting features for customers, focusing on commercial database engines and SQL Server. In the past, he was a Technical Lead at Microsoft, which involved troubleshooting complex SQL Server issues and debugging. He has worked on environments of various scales.