Microsoft Workloads on AWS

Switching Amazon RDS for SQL Server from AWS Managed Microsoft AD to self-managed AD

In this blog post, I will show how to move Amazon Relational Database Service (Amazon RDS) for SQL Server deployments from AWS Directory Service for Microsoft Active Directory (AWS Managed Microsoft AD) to your self-managed Microsoft AD. This blog post is not saying you should move away from AWS Managed Microsoft AD to self-manged AD. Keep in mind AWS Managed Microsoft AD is still required for the other Amazon RDS database engines to interact with AD. AWS Managed Microsoft AD is still an excellent target for workloads including Amazon RDS for SQL Server workloads. The concepts in this blog post can target AWS Managed Microsoft AD besides self-managed AD. A couple of scenarios include using the self-managed AD deployment option to avoid having to share your AWS Managed Microsoft AD with other accounts or extending your AWS Managed Microsoft AD into additional regions.

Introduction

A common deployment method for Microsoft SQL Server includes integration with AD, which allows users and AD-aware applications access to SQL Server instances using existing AD credentials. Previously, Amazon RDS for SQL Server instances could only join an AWS Managed Microsoft AD. A trust relationship between AWS Managed Microsoft AD and your on-premises AD is required for on-premises identities to access your Amazon RDS for SQL Server instances. Based on customer conversations, many organizations do not want to add another AD in the authentication chain.

With the launch of self-managed AD support, you can integrate Amazon RDS for SQL Server instances directly with your self-managed AD domains regardless of where you host your AD. But what about the Amazon RDS for SQL Server instances deployed prior to this recent launch and joined to AWS Managed Microsoft AD? Can you move those deployments from AWS Managed Microsoft AD and join it to your self-managed AD? The quick answer is yes, and the rest of this post will explain how.

At the time of this blog post’s publication, only Amazon RDS for SQL Server and Amazon RDS Custom support direct integration with self-managed AD deployments. While the focus of this post is about moving an Amazon RDS for SQL Server instance away from an AWS Managed Microsoft AD, AWS recommends evaluating everything that AWS Managed Microsoft AD is used for prior to making any changes. AWS Managed Microsoft AD may be used for other Amazon RDS types and AWS applications like Amazon WorkSpaces.

Solution overview

In this blog post, I will show an AD configuration change for a sample Amazon RDS for SQL Server deployment. I recommend you TEST this process in a non-production environment prior to changing production instances. This change requires the Amazon RDS for SQL Server instance to reboot two times. The reboots are required to remove the instance from the AWS Managed Microsoft AD domain and join it to the self-managed AD domain. The actual change only requires calling the ModifyDBInstance API. This process is not a one-way door. If you would like to move from a self-managed AD domain to an AWS Managed Microsoft AD domain, you can do that as well.

Prerequisites

To follow along in this post, you will need the following resources deployed:

Figure 1 shows the prerequisites used for this post. The only resources you will create besides these resources are an AWS Key Management Service (KMS) Key and an AWS Secrets Manager Secret encrypted with the KMS key storing the service account credentials for the Amazon RDS for SQL Server instance.

Figure 1: Prerequisites

Figure 1 Prerequisites

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

Steps to deploy the AWS CloudFormation template

I am providing you with an AWS CloudFormation template to deploy the prerequisites in a new Amazon Virtual Private Cloud (Amazon VPC). Download the template to your computer.

Here are the steps to deploy the AWS CloudFormation template. It should take around 90 minutes for the CloudFormation template to deploy. The estimated total cost to run the resources for 24 hours is ~$50 USD in the AWS US West (Oregon) Region. If you would like to price out all resources deployed by the AWS CloudFormation template, please visit the AWS Pricing Calculator.

1. Sign in to the AWS CloudFormation console.

2. Select Create stack.

3. In the Specify template section, select Upload a template file, select Choose file, browse to and select the AWS CloudFormation Template you downloaded from here, and select Next.

4. In the Specify stack details page, set the following and select Next.

a. Stack name: RDS-Self-AD.

b. Parameters: Leave the default settings.

5. In the Configure stack options page, leave the default settings, and select Next.

In the Review stack page, check I acknowledge that AWS CloudFormation might create IAM resources with custom names and select Submit.

Step-by-step instructions

I wrote the following instructions, expecting the prerequisites are in place. The instructions are based on the resources deployed by the CloudFormation template. If you have an environment already in place, some steps may not be required. For example, I store the AWS Managed Microsoft AD Admin, self-managed AD Administrator, and Amazon RDS for SQL Server master credentials in AWS Secrets Manager Secrets. This may not be the case in your environment. The process to remove an Amazon RDS for SQL Server instance from an AWS Managed Microsoft AD domain and joining it to the self-managed AD domain is the same.

Step 1: Back up your Amazon RDS for SQL Server instance

I recommend you make a backup of your Amazon RDS for SQL Server instance prior to making any configuration changes. Please refer to the Amazon RDS administrator guide for creating a DB snapshot. Note that for SQL Server, I/O activity is briefly suspended during backup for multi-AZ deployments. The time to create a snapshot varies since the snapshot includes the entire storage volume. The size of files, such as temporary files, affects the time to create a snapshot.

In the event you need to restore your Amazon RDS for SQL Server instance, please refer to restoring from a DB snapshot.

Step 2: Gather the credentials from AWS Secrets Manager

1. Log in to the AWS Console and go to AWS Secrets Manager console.

2. In the Secrets console are Secrets named MadAdminSecret-<Stackname>, OnPremAdministratorSecret-<Stackname>, and the RdsAdminSecret-<Stackname>. The Secrets contain the corp\Admin, onpremises\Administrator, and the Amazon RDS Instance Master (and onpremises\RDSUser) passwords, respectively.

3. Select each Secret on the Secret details page, browse to the Secret value section, and select Retrieve secret value. Note the password value for each Secret as you will use them later.

Step 3: Create the Amazon RDS for SQL Server instance self-managed AD prerequisites

This set of instructions guides you through the creation of the AD objects required to integrate an Amazon RDS for SQL Server instance with a self-managed AD. You are going to use a PowerShell script to generate all the prerequisites prior to deploying Amazon RDS for SQL Server. Specifically, the script creates:

  • An OU for the Amazon RDS for SQL Server. In this post, I use RDS-MSSQL, but you can use any name of your choice.
  • Service account for the Amazon RDS for SQL Server. In this post, I use the account named RdsServiceAccount (or any name of your choice) with proper least-privilege permissions.

1. Open the AWS Systems Manager Fleet Manager – Remote Desktop console. Select Add new session, select the Onprem MGMT EC2 Instance Node, and select Add.

2. Select User credentials, enter the following parameters, and select Connect.

a. Username: onpremises\Administrator.

b. Password: In the OnPremAdministratorSecret Secret (from Step 2: Gather the credentials from AWS Secrets Manager).

3. Download the PowerShell script that creates the OU and Service Account.

4. Right-click on Start and select Windows PowerShell (Admin).

5. In the elevated Windows PowerShell window (make sure you are working from the directory you downloaded the script to) run the PowerShell script as shown in the text below.

$RDSDeployment = @{
    RdsOUBaseDn       = 'DC=onpremises,DC=local'
    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

Note: If you are deploying this in your own environment, you must update the $RDSDeployment hash-table variable above with the proper values for your environment.

The script output should look like:

.\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-3795784878-3116411042-3825159932-1104 CreateChild, DeleteChild Allow bf967a86-0de6-11d0-a285-00aa003049e2 All 00000000-0000-0000-0000-000000000000.
Getting ACL for OU=RDS-MSSQL,DC=onpremises,DC=local and adding new rule.
Setting ACL for OU=RDS-MSSQL,DC=onpremises,DC=local.
Creating ACL object S-1-5-21-3795784878-3116411042-3825159932-1104 Self Allow f3a64788-5306-11d1-a9c5-0000f80367c1 Descendents bf967a86-0de6-11d0-a285-00aa003049e2.
Getting ACL for OU=RDS-MSSQL,DC=onpremises,DC=local and adding new rule.
Setting ACL for OU=RDS-MSSQL,DC=onpremises,DC=local.
Creating ACL object S-1-5-21-3795784878-3116411042-3825159932-1104 Self Allow 72e39547-7b18-11d1-adef-00c04fd8d5cd Descendents bf967a86-0de6-11d0-a285-00aa003049e2.
Getting ACL for OU=RDS-MSSQL,DC=onpremises,DC=local and adding new rule.
Setting ACL for OU=RDS-MSSQL,DC=onpremises,DC=local.

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

Step 4: Create AWS KMS Key and Secret to store Amazon RDS service account credentials

This set of instructions guides you through the creation of a new AWS Secrets Manager Secret to store your service account information and a new AWS KMS Key to encrypt the Secret. Once you have stored the service account information, you will update the Secret’s resource permission granting the Amazon RDS service principal GetSecretValue permissions.

Step 4a: Setup the AWS KMS Key

1. Open the AWS Key Management Service console. In the left-hand navigation menu, select Customer managed keys.

2. Select Create key.

3. In the Configure Key page, set the following and select Next.

a. Key type: Symmetric.

b. Key usage: Encrypt and decrypt.

c. Advanced Options.

i. Key material origin: KMS.

ii. Regionality: Single-Region key.

4. In the Add labels page, set the following and select Next.

a. Alias: Anything you wish. In this post, I am using RDS-Self-AD.

5. In the Define key administrative permissions page, set the following and select Next.

a. Key administrators: Select your IAM User or Role.

6. In the Define key usage permissions page, set the following and select Next.

a. Key users: Select your IAM User or Role.

7. In the Review page, review your selections, and select Finish.

8. In the AWS Key Management Service, select the Alias of the AWS KMS key you just created.

9. Select the Key policy tab, select Switch to policy view, and select Edit.

10. Append the following to your key policy and select Save changes.

This policy allows the Amazon RDS service principal to decrypt the AWS Secrets Manager Secret that is encrypted by this AWS KMS Key.

{
  "Sid": "Allow use of the key",
  "Effect": "Allow",
  "Principal": {
    "Service": [
      "rds.amazonaws.com"
    ]
  },
  "Action": "kms:Decrypt",
  "Resource": "*"
}

Step 4b: Store the service account credentials in an AWS Secrets Manager Secret

1. Open the AWS Secrets Manager console and select Store a new secret.

2. In the Choose secret type page, set the following and select Next.

a. Secret type: Other type of secret.

b. Key/value pairs, set the pair for one and select + Add row to create another pair:

i. Key 1: CUSTOMER_MANAGED_ACTIVE_DIRECTORY_USERNAME

ii. Value 1: The name you passed in the RdsSvcAccountName parameter of the PowerShell script in Step 3: Create the Amazon RDS for SQL Server instance self-managed AD prerequisites.

iii. Key 2: CUSTOMER_MANAGED_ACTIVE_DIRECTORY_PASSWORD

iv. Value 2: The password you passed for the RdsSvcAccountPw parameter of the PowerShell script in Step 3: Create the Amazon RDS for SQL Server instance self-managed AD prerequisites.

c. Encryption key: Select the key created in Step 4a: Setup the AWS Key Management Service Key

3. In the Configure Secret page, set the following and select Next.

a. Secret name: Anything you wish. In this post, I am using RDS-Self-AD.

b. Resources permissions, select Edit permissions, set the following resource policy, and select Save.

This policy allows the Amazon RDS service principal to retrieve the Secret values stored in the AWS Secrets Manager Secret.

Note: Update the aws:sourceAccount and aws:sourceArn in the sample policy below to reflect your AWS account id and region.

{
  "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:*"
        }
      }
    }
  ]
}

4. In the Configure rotation – optional page, leave the default settings and select Next.

5. In the Review page, review your selections and select Store.

6. In the AWS Secrets Manager console, select the refresh button and select the Secret name you just created.

7. On the Secret details page, note the Secret ARN. You will need this to join the Amazon RDS for SQL Server instance to your self-managed AD domain. Figure 2 below gives an example of what the ARN will look like.

Figure 2: Secret ARN

Figure 2: Secret ARN

Step 5: Add your Amazon RDS for SQL Server instance to self-managed AD

This set of instructions guides you through adding Amazon RDS for SQL Server instance to the self-managed AD domain. In this set of steps, you are again using the ModifyDBInstance API to invoke adding the Amazon RDS for SQL Server instance to the self-managed AD domain.

1. Open the Amazon RDS console, select Databases in the left-hand navigation pane.

2. Select the DB identifier of the database you would like to change. In this post, the identifier is rds-self-ad.

3. Select Modify. In the Modify DB instance page, scroll down to the Microsoft SQL Server Windows Authentication section, and set the following. Figure 3 below shows the fields that need to be populated.

a. Enable Microsoft SQL Server Windows authentication: Checked.

b. Windows authentication type: External Active Directory Domain.

c. Fully qualified domain name: The FQDN of the domain your Amazon RDS for SQL Server instance will use. In this post, I am using onpremises.local.

d. Domain organizational unit: The location for your Amazon RDS for SQL Server instance AD objects. In this post, I am using OU=RDS-MSSQL,DC=onpremises,DC=local.

e. Authorization secret ARN: The ARN of the Secret containing the service accounts credentials you created in Step 4b: Store the service account credentials in an AWS Secrets Manager Secret.

f. Primary DNS: A DNS resolver IP that can resolve your self-managed AD.

g. Secondary DNS: A DNS resolver IP that can resolve your self-managed AD. Since the CloudFormation template I provided only has one domain controller, you can reuse the same IP twice.

Figure 3: Amazon RDS self-managed AD Configuration

Figure 3: Amazon RDS self-managed AD Configuration

4. In the next window, select Apply immediately, and select Modify DB instance.

Note: If this is a production / actively used instance, perform this during a maintenance window. This process will remove the SQL Server instances from the AWS Managed Microsoft AD domain and reboot them.

In testing for this blog post, the process took around 15 -20 minutes to join a single-AZ Amazon RDS for SQL server deployment from a self-managed AD. Multi-AZ deployments may take longer.

Step 6: Validate that your on-premises user can authenticate into the database

Once your Amazon RDS for SQL Server instance is active, you will connect to the instances from the Onprem MGMT EC2 Instance node using the Microsoft SQL Server Management Studio (SSMS) with the onpremises\RDSUser credentials to confirm the user can still access the instance post modification.

1. Open the AWS Systems Manager Fleet Manager – Remote Desktop console.

2. Select Add new session, select the Onprem MGMT EC2 Instance node, and select Add.

3. Select User credentials, enter the following parameters, and select Connect.

a. Username: onpremises\RDSUser.

b. Password: In the RdsAdminSecret Secret (from Step 2: Gather the credentials from AWS Secrets Manager).

4. Select Start, type SSMS, and select SQL Server Management Studio.

5. At the dialog box, enter or set the following, and select Connect.

a. Server name: The name of the Amazon RDS for SQL Server instance Endpoint. If you need help to find your Amazon RDS for SQL Server instance, endpoint see Finding the DB instance endpoint and port number.

b. Authentication: Windows Authentication.

Figure 4: SQL Authentication

Figure 4: SQL Authentication

Congratulations! You have successfully signed into your Amazon RDS for SQL Server instance with a user from your self-managed AD.

Cleanup

If you decide you would like to remove the resources deployed in your account from this post, perform these steps:

1. Delete the RDS-Self-AD Secret you created to store the Amazon RDS credentials. The instructions for deleting a Secret can be found here.

2. Delete the RDS-Self-AD Key you created to encrypt the Secret for the Amazon RDS credentials. The instructions for deleting a Key can be found here.

3. Finally, delete the AWS CloudFormation stack named RDS-Self-AD that deployed all the prerequisites for this post. The instructions for deleting a stack can be found here.

Summary

In this blog post, I showed you how to how to change an Amazon RDS for SQL Server instance integrated with AWS Managed Microsoft AD to be integrated with a self-managed AD. You used a sample PowerShell script to set up the self-managed AD prerequisites for this new capability. Next, you created a new AWS KMS Key used to encrypt a new AWS Secrets Manager Secret containing the Amazon RDS AD service account credentials created by the PowerShell script. Then you removed an existing Amazon RDS for SQL Server instance integrated with AWS Managed Microsoft AD and joined it to a self-managed Microsoft AD. Finally, you validated that a self-managed Microsoft AD user could authenticate into the Amazon RDS for SQL Server instance.

For more information on the Amazon RDS for SQL Server self-managed AD feature, please refer to the Amazon RDS for SQL Server Admin Guide. Also, there will be more blogs that touch on this subject. Please keep an eye out for future blog posts related to this subject listed on the AWS Blogs page.

AWS can help you assess how your company can get the most out of cloud. Join the millions of AWS customers that trust us to migrate and modernize their most important applications in the cloud. To learn more on modernizing Windows Server or SQL Server, visit Windows on AWS. Contact us to start your migration journey today.

TAGS: ,
Jeremy Girven

Jeremy Girven

Jeremy is a solutions architect specializing in Microsoft workloads on AWS. He has over 16 years’ experience with Microsoft Active Directory and over 25 years of industry experience. One of his fun projects is using SSM to automate the Active Directory build processes in AWS. To see more, check out the Active Directory AWS Partner Solution (https://aws.amazon.com/solutions/partners/active-directory-ds/).