Automate the configuration of Amazon RDS Custom for SQL Server using AWS Systems Manager

In our previous post Use a self-hosted Active Directory with Amazon RDS Custom for SQL Server, we explained the manual steps to join Amazon Relational Database Service (Amazon RDS) Custom for SQL Server to a self-hosted Active Directory. We highlighted the importance of using repeatable, idempotent scripts because changes would be lost on new instances, for example when read scale-out occurs.

In this post, we show how to implement the automation for joining to a self-hosted Active Directory using AWS Systems Manager.

Solution overview

The following diagram illustrates how the automation works.

The workflow steps are as follows:

1. An RDS Custom for SQL Server instance is created with a tag RDSDomainJoin:TRUE.
2. During creation, the RDS instance registers with Systems Manager.
3. AWS Systems Manager State Manager is configured to run a Systems Manager command document on instances matching the tag.
4. While the command is running, the credentials that are needed to join Active Directory are retrieved from AWS Secrets Manager and command output is logged to an Amazon Simple Storage Service (Amazon S3) bucket
5. RDS Custom instance is joined to domain and restarted

In the following sections, we detail the implementation steps to set up this automation. Note that as of this writing, you cannot create an RDS instance with tags via the AWS Management Console, so we use the AWS Command Line Interface (AWS CLI) for this implementation. See Getting Started with the AWS CLI for installation instructions. We use AWS CloudShell which has the AWS CLI preinstalled

Prerequisites

You need to have created a customer managed symmetric AWS Key Management Service (AWS KMS) key, a DB subnet group, and a security group before you can create your RDS instance. See Make sure that you have a symmetric encryption AWS KMS key for instructions. An Amazon S3 Bucket following security best practices is required to log AWS System Manager command output.

Create an Active Directory join secret

Adding the name and credentials for your Active Directory domain to AWS Secrets Manager protects the credentials from unauthorized use and enables automation via the Systems Manager command document. The Active Directory account only needs permissions to create computer objects in the specified Active Directory Organization Unit, following principle of least privilege, do not reuse an account with permissions which are not required. Additionally it is possible to restrict the Active Directory credentials for one time use, see Use Case 3 in the Implement Advanced Access Control Mechanisms Using AWS KMS workshop to learn more.

Create a file RDSADJoin.json with content as follows:

{
   "username": "<ADUSER>",
   "password": "<PASSWORD>",
   "domainname": "example.local",
   "ou": "OU=SQLServers,OU=example,DC=example,DC=local"
}

To create a secret, run the following command:

aws secretsmanager create-secret --name RDSADJoin --description "Credentials and details for RDS Instances to join self managed Active Directory" --secret-string file://RDSADJoin.json

Details of the created secret are returned in the command output

We need to add a policy to the AWS Identity and Access Management (IAM) role used for the RDS Custom instance, which allows the instance to access the secret. We also need to allow the RDS Custom instance to log output to our Amazon S3 bucket created as part of prerequisites. Refer to Creating an RDS Custom for SQL Server DB instance for instructions to create the IAM role.

Create a file policy.json with content as follows:

{
   "Version": "2012-10-17",
   "Statement": [
   {
      "Sid": "domainJoinSecret",
      "Effect": "Allow",
      "Action": "secretsmanager:GetSecretValue",
      "Resource": "<SecretARN>"
    },
    {
      "Sid": "s3ssmlogging",
      "Effect": "Allow",
      "Action": [
	"s3:PutObject",
        "s3:GetObject",
        "s3:PutObjectAcl"
     ],
      "Resource": "<S3LoggingBucketARN>"
    }
    ]
}

Run the following command to add a policy to the RDS Custom IAM role:

aws iam put-role-policy --role-name <AWSRDSCustomSQLServerInstanceRole> --policy-name RDSADJoin --policy-document file://policy.json.

This command returns no output

Create a Systems Manager document

A Systems Manager document (SSM document) defines actions that Systems Manager performs on managed instances. In this case, we define commands to be run on our RDS Custom instance.

The following PowerShell commands used in the SSM document are defined as a YAML file.

Create a file RDSDomainJoinSSM.yaml with the content as follows:

---
schemaVersion: "2.2"
description: "Command Document to add computer to Active Directory domain"
parameters:
mainSteps:
   - action: "aws:runPowerShellScript"
     precondition:
        StringEquals:
           - platformType
           - Windows
     name: "RunPowershellScript"
     inputs:
        runCommand:
           - "Write-Output {{Joining Instance to Domain}}"
           - $secret = (Get-SECSecretValue -SecretId <SecretArn>).SecretString | ConvertFrom-Json
           - $domainname = $secret.domainname
           - $ou = $secret.ou
           - $username = $secret.username
           - $password = ConvertTo-SecureString $secret.password -AsPlainText -Force
           - $domainCredential = New-Object System.Management.Automation.PSCredential -ArgumentList ($username, $password)
		- Add-Computer -DomainName $domainname -DomainCredential $domainCredential -oupath $ou -Restart

Run the following command to create the SSM document:

aws ssm create-document --name RDSDomainJoin --content file://RDSDomainJoinSSM.yaml --document-type Command --target-type /AWS::EC2::Instance --document-format YAML

Details of the SSM document are returned in the command output

Create a Systems Manager State Manager association

Systems Manager State Manager automates the process of keeping managed instances (in this case an RDS Custom instance) in a defined state. We configure a State Manager association to trigger and run the SSM document one time when new RDS Custom instances with a specific tag are created. We use the tag key RDSDomainJoin and value TRUE to trigger the SSM document. We configure SSM to log command output to an Amazon S3 bucket we created as part of prerequisites

Run the following command to create the State Manager association:

aws ssm create-association --association-name RDSDomainJoinTags --targets Key=tag:RDSDomainJoin,Values=TRUE --name RDSDomainJoin --output-location '{"S3Location":{"OutputS3Region":"<Region>","OutputS3BucketName":"<S3bucketname>"}}'

Details of the SSM association are returned in the command output

Create an RDS Custom instance

It’s important that the instance is tagged when it is created so that subsequent events that create new instances (like scaling) inherit the tags. If tags are added post-creation, new instances don’t inherit the tags. At the time of writing, the AWS console doesn’t support adding tags while creating an RDS Custom instance, so we use the AWS CLI.

Run the following command to create the RDS Custom instance:

aws rds create-db-instance \
--engine custom-sqlserver-ee \
--engine-version 15.00.4073.23.v1 \
--db-instance-identifier example-instance-name \
--db-instance-class db.m5.xlarge \
--allocated-storage 100 --storage-type gp2 \
--master-username <sausername> \
--master-user-password <sapassword> \
--kms-key-id <kmskeyid> \
--custom-iam-instance-profile <AWSRDSCustomSQLServerInstanceProfile> \
--db-subnet-group-name <dbsubnetgroup> \
--vpc-security-group-ids <ec2-securitygroup> \
--no-publicly-accessible \
--tags Key=RDSDomainJoin,Value=TRUE

Details of the RDS Custom instance are returned in the command output

During creation, Systems Manager runs the SSM document, joins the instance to the Active Directory domain and restarts the instance.

Further automation

You can extend the SSM command document to perform additional automation, for example customizing settings within SQL Server. For this you will need to know the initial System Administrator (sa) user name and password; you can use Secrets Manager similar to how we protected the Active Directory credentials in this post. A common additional automation is to perform a Manual SPN Registration so Kerberos authentication can be used.

Additionally, you can implement automation for removing machines from Active Directory when they are deleted.

It is also possible to automate customizations using a Custom Engine Version (CEV). Third-party agents or other Operating System customizations can be persisted in an Amazon Machine Image (AMI) and new RDS Custom instances created from the customized AMI.

Conclusion

Amazon RDS Custom for SQL Server allows you to perform customizations like joining to a self-managed Active Directory. It’s best practice to perform these customizations in a repeatable, idempotent way, especially to support automated activities like read scale-out. Systems Manager can trigger commands on RDS Custom instances as they are created, enabling customizations to be automatically applied.

To learn more about Amazon RDS Custom, refer to Working with Amazon RDS Custom.

About the authors

Peter Giuliano is a Senior Migration Solution Architect at AWS with 18 years experience working with OnPremise and Cloud infrastructure. At AWS he helps enterprise customers plan and execute large scale migrations to the Cloud making sure customer experiences and lessons are shared. Outside of work Peter is a keen runner and enjoys traveling to new places.

Santhosh Reddy Talla is an accomplished Database Engineer , works with RDS SQL Server team at AWS, with expertise in relational databases such as SQL Server, Oracle, Postgres, and MySQL. He has a strong ability to solve complex problems, optimize database performance, and automate tasks using Python, Shell, and PowerShell. Outside of work he is avid outdoor enthusiast, with a love for skiing and hiking.