Encrypt your database connection using SSL encryption to Amazon RDS Custom for SQL Server

Amazon Relational Database Server (Amazon RDS) Custom for SQL Server is a managed database service for applications that require operating system access and database customization that is not available in Amazon RDS for SQL Server. The ability to encrypt an application’s connection to RDS Custom is a common security requirement. Network encryption protects data in transit by securing communication channels between client applications and the RDS Custom instance using Secure Socket Layer (SSL) and Transport Layer Security (TLS) certificates.

In this post, we show you how to configure SSL/TLS encryption on RDS Custom for SQL Server using a self-managed certificate. Additionally, we show you how to optionally configure SSL/TLS encryption and enable Kerberos authentication. We use a certificate issued by an internal CA for this post.

Solution overview

For this post, we use State Manager, a capability of AWS Systems Manager, to automate the SSL encryption on an RDS Custom instance, as shown in the following diagram.

The automation workflow steps are as follows:

1. Create an RDS Custom instance with a tag (RDSCertSSL: do-not-delete-rds-custom-<db-instance-id>).
2. During creation, the RDS Custom instance registers with Systems Manager.
3. State Manager is configured to run a Systems Manager command document (aws:runPowerShellScript) on instances that match the tag.
4. While running the command, the secrets that are needed to import the certificate and the SSL certificate itself are retrieved from AWS Secrets Manager and Amazon Simple Storage Service (Amazon S3), respectively.
5. The RDS Custom instance is configured with SSL encryption.
6. Optionally, the RDS Custom instance is joined to the domain and restarted to enable Kerberos authentication.

In the following sections, we provide detailed steps to set up this automation. We also provide optional steps to perform a domain join and change the SQL Server service account to support Kerberos authentication using automation. At the time of writing this post, you can’t create an RDS instance with tags via the AWS Management Console. We use the AWS Command Line Interface (AWS CLI) for this implementation.

Prerequisites

Before we begin, you should have the following prerequisites:

• An AWS account.
• An S3 bucket to store your certificate and to log Systems Manager command output.
• The requirements met for creating an RDS Custom instance.
• The AWS CLI installed and configured.
• An Amazon Elastic Compute Cloud (Amazon EC2) Windows instance with SQL Server Management Studio (SSMS) installed.
• An SSL certificate that meets the requirements for SQL Server encryption and is uploaded to the S3 bucket. Because we are able to obtain the RDS endpoint before creating the RDS Custom instance, we create a certificate that contains the RDS endpoint in the subject property of the certificate. The following is an example of an RDS endpoint URL.

To obtain the consistent part of your RDS endpoint URL, refer to Finding the DB instance endpoint and port number.

• DNS resolution configured, if you plan to perform a domain join. There are multiple ways to achieve this; one method is to use an Amazon Route 53 outbound endpoint to forward the DNS request to the DNS server. For more information, refer to Forwarding outbound DNS queries to your network.

This solution involves the creation and utilization of new AWS resources; therefore, it will incur costs on your account. Refer to AWS Pricing for more information.

We recommend that you test this setup in a non-production environment before you implement this solution in a production environment.

Create a certificate password secret

Adding the certificate password to Secrets Manager prevents the password from unauthorized users and enables automation using a Systems Manager command document. In this post, we use a certificate that is issued by an Enterprise Certificate Authority (CA) that contains the certificate, private key, and intermediate certificate exported in Personal Information Exchange (PFX) format and secured using a password. The same password is required to import the certificate into RDS Custom.

Complete the following steps:

1. Create a file called CertPass.json with the following contents:

{
"password": "<CERTPASSWORD>"
}

2. To create a secret, enter the following AWS CLI command:

aws secretsmanager create-secret –name RDSSSLCert –description "Password to perform SSL certificate import into RDS Custom" –secret-string file://CertPass.json –region <AWS region>

The details of the secret are returned in the command.

You need to allow the AWS Identity and Access Management (IAM) role used by RDS Custom to access the secret you created. You also need to allow the RDS Custom instance to download the SSL certificate from the S3 bucket and log output to the S3 bucket you created as part of the prerequisites.

3. Create a file called policy.json with the following contents:

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

4. To add the policy to the RDS Custom IAM role, enter the following AWS CLI command:

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

The command does not have an output.

Create a Systems Manager command document

A Systems Manager document (SSM document) is a collection of instructions that Systems Manager runs against your managed instances. In this post, you define the commands to be run on you RDS Custom instance during creation. The following is a set PowerShell commands that are defined in a YAML file. At a high level, the scripts automate the following steps:

1. Create a folder on drive C: and download the certificate file from Amazon S3.
2. Obtain the password needed to import the certificate from Secrets Manager.
3. Import the certificate into your local machine certificate store.
4. Grant read permission on the certificate private key to the network service account.
5. Move the intermediate certificate to the local machine Trusted Root Certification Authorities certificate store.
6. Enable SSL encryption on SQL Server.
7. Restart the SQL Server service.

Create a file called RDSSSLConfig.yaml with the following contents. Replace the placeholders with information from your account. This includes the S3 bucket name where you store your SSL certificate and the certificate file name. In the PowerShell script, uncomment the section that moves the intermediate certificate to the Trusted Root Certification Authorities certificate store to automate this task. This will run the commands and perform the certificate move operation automatically. In our example, the certificate was issued by a non-trusted CA, so we need to move the intermediate certificate to the local trusted root certificate store. This is required to remove the need to specify the Trust Server Certificate option when connecting using SSL.

schemaVersion: "2.2"
description: "Command Document to configure SSL"
mainSteps:
- action: "aws:runPowerShellScript"
precondition:
StringEquals:
- platformType
- Windows
name: "RunPowershellScript"
inputs:
runCommand:
- $folder="C:\Cert"
- New-Item $folder -ItemType Directory
- $secret=(Get-SECSecretValue -SecretId <SecretArn>).SecretString | ConvertFrom-Json
- $mypwd=ConvertTo-SecureString $secret.password -AsPlainText -Force
- Copy-S3Object -BucketName <S3BucketName> -Key <CertFile> -LocalFolder C:\Cert
- $fp="C:\Cert\<CertFile>"
- $csl='Cert:\LocalMachine\My'
- $pass=$mypwd
- $bp=Import-PfxCertificate -FilePath $fp -CertStoreLocation $csl -Password $pass
- $tb=$bp.Thumbprint
- $CertObj=Get-ChildItem Cert:\LocalMachine\my\$tb
- $rsaCert=[System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPrivateKey($CertObj)
- $fileName=$rsaCert.key.UniqueName

- Set-Location $env:ALLUSERSPROFILE\Microsoft\Crypto\

- $file=Get-ChildItem $filename -Recurse

- $filepath=$file.DirectoryName

- $path="$filepath\$fileName"
- $permissions=Get-Acl -Path $path
- $rule=new-object security.accesscontrol.filesystemaccessrule "Network Service", "read", allow
- $permissions.AddAccessRule($rule)
- Set-Acl -Path $path -AclObject $permissions

# This code moves the intermediate certificate to the trusted certificate store.

# - $cacert= (Get-PfxData -FilePath $fp -Password $mypwd).OtherCertificates.Thumbprint

# - Set-Location Cert:

# - $cert=Get-ChildItem -path $cacert -recurse | Where-Object {$_.PSParentPath.Contains("LocalMachine")}

# - Move-Item -Path $cert.PSPath -Destination "Cert:\LocalMachine\Root"

# end of code that moves the intermediate certificate
- Set-ItemProperty -Path $(get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\*\MSSQLServer\SuperSocketNetLib").PsPath -Name "Certificate" -Type String -Value "$($tb)"
- Set-ItemProperty -Path $(get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\*\MSSQLServer\SuperSocketNetLib").PsPath -Name "ForceEncryption" -Type DWORD -Value "1"

- $server = $env:COMPUTERNAME
- $service = get-wmiObject win32_service -computername $server | ?{$_.name -eq 'mssqlserver'}
- $stopservice = $service.stopservice()
- Start-Sleep -Seconds 5
- $startservice = $service.startservice()

Enter the following AWS CLI command to create the SSM document:

aws ssm create-document --name RDSSSLConfig --content file://RDSSSLConfig.yaml --document-type Command --target-type /AWS::EC2::Instance --document-format YAML --region <AWS region>

Create a State Manager association

State Manager automates the process of keeping a managed instance in a specific state (in this case, an RDS Custom instance). In this step, you configure State Manager to invoke a run of the SSM document after the RDS Custom instance is created with a specific tag. You use the tag key RDSSSLConfig and a value of do-not-delete-rds-custom-<db-instance-id> to run the SSM document. You use a unique value in the tag to distinguish the association between instances if you have more than one RDS Custom instance that requires SSL configuration. You configure State Manager to log the command output to an S3 bucket that you created as part of the prerequisites.

Enter the following AWS CLI command to configure State Manager. Replace the placeholders with the information from your account. These include the instance ID, AWS Region, and S3 bucket name.

aws ssm create-association --association-name RDSSSLConfigTags --targets Key=tag:RDSSSLConfig,Values=do-not-delete-rds-custom-<db-instance-id> --name RDSSSLConfig --output-location S3Location={OutputS3Region=<region>,OutputS3BucketName=<S3 bucket name>}

Create an RDS Custom instance

It’s important that you tag the RDS Custom instance during creation. This makes sure that future events that trigger new instance creation (like disk corruption) inherit the tag that invokes the run of the SSM document to automate the configuration of SSL on RDS Custom. At the time of this writing, the AWS console does not support adding tags during creation, so you use AWS CLI.

Enter the following AWS CLI command to create the RDS Custom instance. Replace the placeholders with the information from your account.

aws rds create-db-instance \
--engine custom-sqlserver-se \
--engine-version 15.00.4322.2.v1 \
--db-instance-identifier <db-instance-id> \
--db-instance-class db.m6i.large \
--allocated-storage 100 --storage-type gp3 \
--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 <your security group id> \
--no-publicly-accessible \
--region <AWS region>
--tags Key=RDSSSLConfig,Value=do-not-delete-rds-custom-<db-instance-id>

During the creation, Systems Manager runs the SSM document to download and configure SSL settings on the RDS Custom instance.

(Optional) Domain join and configure SSL on RDS Custom

In some scenarios, you are required to domain join and configure SSL encryption on your RDS Custom instance. This section details the SSM document that automates the joining of the RDS Custom instance to a domain, configures SQL Server to use a domain account, and sets up SSL encryption during instance creation.

One reason why you domain join the RDS Custom instance is to enable Kerberos authentication. To enable Kerberos authentication, Service Principal Name (SPN) registration is required. SPN registration can be done automatically by the SQL Server service account or by manually pre-staging it. For automatic registration, the service account needs to be granted the necessary permission in Active Directory. For more information, refer to Register a Service Principal Name for Kerberos connections.

The following are the high-level steps to configure SSL and enable Kerberos authentication:

1. Create an Active Directory secret.
2. Create a Systems Manager command document.
3. Create a State Manager association.
4. Create an RDS Custom instance.

You should have an SSL certificate that contains the fully qualified domain name (FQDN) of the instance that is required to make the encrypted connection and upload to your S3 bucket.

Create an Active Directory secret

Apart from creating a secret to store the certificate’s password, you create a secret to store the credentials and domain information in Secrets Manager. Storing this information in Secrets Manager prevents unauthorized usage and supports the Systems Manager automation. The Active Directory account only needs permissions to create computer objects in the specified Active Directory. Following the principle of least privilege, do not reuse an account with permissions that aren’t required.

Create a file called RDSADJoin.json with the following contents:

{
"username": "<ADUSER>",
"password": "<DOMAINPASSWORD>",
"domainname": "<DOMAINNAME>"
}

To create a secret, enter the following command:

aws secretsmanager create-secret --name RDSADJoin --description "Credentials and details for RDS Custom instances to join Active Directory" --secret-string file://RDSADJoin.json --region <AWS region>

Create a Systems Manager command document

This document automates the joining of the RDS Custom instance to Active Directory, configures the SQL Server service account, and sets up SSL encryption using the PowerShell commands defined in a .yaml file. At a high level, the scripts automate the following steps:

1. Obtain the domain user name and password needed to domain join.
2. Domain join the instance.
3. Change the service account of the SQL Server service.
4. Create a folder on drive C: and download the certificate file from Amazon S3.
5. Obtain the password needed to import the certificate from Secrets Manager.
6. Import the certificate into your local machine certificate store.
7. Grant read permission on the certificate private key to the network service account.
8. Move the intermediate certificate to the local machine Trusted Root Certification Authorities certificate store.
9. Enable SSL encryption on SQL Server.
10. Restart the SQL Server service.

Create a file called RDSADJoinCert.yaml with the following contents. Replace the placeholders with information from your account. In the PowerShell script, uncomment the section that moves the intermediate certificate to the Trusted Root Certification Authorities certificate store to automate this task. This will run the commands and perform the certificate move operation automatically.

schemaVersion: "2.2"
description: "Command Document to configure SSL"
mainSteps:
- action: "aws:runPowerShellScript"
precondition:
StringEquals:
- platformType
- Windows
name: "RunPowershellScript"
inputs:
runCommand:
- |
If (!(Test-Path -Path "C:\Phase\fileA.txt" -PathType Leaf)) {
New-Item "C:\Phase" -ItemType Directory
$secret = (Get-SECSecretValue -SecretId <SecretArn>).SecretString | ConvertFrom-Json
$domainname = $secret.domainname
$username = $domainname + '\' + $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
New-Item C:\Phase\fileA.txt
exit 3010
}
If (!(Test-Path -Path "C:\Phase\fileB.txt" -PathType Leaf)) {
$server = $env:COMPUTERNAME
$service = get-wmiObject win32_service -computername $server | ?{$_.name -eq 'mssqlserver'}
$secret = (Get-SECSecretValue -SecretId <SecretArn>).SecretString | ConvertFrom-Json
$domainname = $secret.domainname
$username = $secret.username
$serviceaccount = $domainname + '\' + $username
$password = $secret.password
$changestatus = $service.change($null,$null,$null,$null,$null,$null,"$serviceaccount","$password",$null,$null,$null)
$stopservice = $service.stopservice()
Start-Sleep -Seconds 5
$startservice = $service.startservice()
New-Item C:\Phase\fileB.txt
}
If (!(Test-Path -Path "C:\Phase\fileC.txt" -PathType Leaf)) {
$folder="C:\Cert"
New-Item $folder -ItemType Directory
$secret = (Get-SECSecretValue -SecretId <SecretArn>).SecretString | ConvertFrom-Json
$mypwd = ConvertTo-SecureString $secret.password -AsPlainText -Force
$secret2 = (Get-SECSecretValue -SecretId <SecretArn>).SecretString | ConvertFrom-Json
$domainname = $secret2.domainname
$username = $secret2.username
$serviceaccount2 = $domainname + '\' + $username
Copy-S3Object -BucketName <S3Bucket> -Key <CertFile> -LocalFolder C:\Cert
$fp="C:\Cert\<CertFile>"
$csl = 'Cert:\LocalMachine\My'
$certpass = $mypwd
$bp=Import-PfxCertificate -FilePath $fp -CertStoreLocation $csl -Password $certpass
$tb=$bp.Thumbprint
$CertObj=Get-ChildItem Cert:\LocalMachine\my\$tb
$rsaCert=[System.Security.Cryptography.X509Certificates.RSACertificateExtensions]::GetRSAPrivateKey($CertObj)
$fileName=$rsaCert.key.UniqueName

Set-Location $env:ALLUSERSPROFILE\Microsoft\Crypto\

$file=Get-ChildItem $filename -Recurse

$filepath=$file.DirectoryName

$path="$filepath\$fileName"

$permissions=Get-Acl -Path $path
$rule=new-object security.accesscontrol.filesystemaccessrule "$serviceaccount2", "read", allow
$permissions.AddAccessRule($rule)
Set-Acl -Path $path -AclObject $permissions

# This code moves the intermediate certificate to the trusted certificate store.

# - $cacert= (Get-PfxData -FilePath $fp -Password $mypwd).OtherCertificates.Thumbprint

# - Set-Location Cert:

# - $cert=Get-ChildItem -path $cacert -recurse | Where-Object {$_.PSParentPath.Contains("LocalMachine")}

# - Move-Item -Path $cert.PSPath -Destination "Cert:\LocalMachine\Root"

# end of code that moves the intermediate certificate
Set-ItemProperty -Path $(get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\*\MSSQLServer\SuperSocketNetLib").PsPath -Name "Certificate" -Type String -Value "$($tb)"
Set-ItemProperty -Path $(get-ItemProperty -Path "HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server\*\MSSQLServer\SuperSocketNetLib").PsPath -Name "ForceEncryption" -Type DWORD -Value "1"

$server = $env:COMPUTERNAME
$service = get-wmiObject win32_service -computername $server | ?{$_.name -eq 'mssqlserver'}
$stopservice = $service.stopservice()
Start-Sleep -Seconds 5
$startservice = $service.startservice()
New-Item C:\Phase\fileC.txt
}

Enter the following AWS CLI command to create the SSM document:

aws ssm create-document --name RDSADSSLConfig --content file://RDSADJoinCert.yaml --document-type Command --target-type /AWS::EC2::Instance --document-format YAML --region <AWS region>

After you create the document, refer to the previous steps to create the SSM association linking to the SSM document created earlier and create your RDS Custom instance.

Verify SSL encryption and Kerberos authentication (if configured)

When the RDS Custom instance is available, connect to it using Remote Desktop (RDP). Refer to Connecting to your RDS Custom DB instance using RDP for instructions to connect to an RDS Custom instance.

To verify if the connection is encrypted, enter the following command in SSMS that connects to the RDS Custom instance using an RDS endpoint. When connecting, select Encrypt connection on the Connection Properties tab.

select encrypt_option from sys.dm_exec_connections where session_id = @@SPID

You can see that the encrypt_option column value is TRUE.

For Kerberos authentication to work, you connect to the RDS Custom instance using its FQDN and Windows Authentication instead of an RDS endpoint. To verify that the connection is encrypted and uses Kerberos, enter the following command:

select encrypt_option, auth_scheme from sys.dm_exec_connections where session_id = @@SPID

The output should be similar to the following screenshot.

To encrypt connections from the clients to the RDS Custom instance, follow the instructions in Special cases for encrypting connections to SQL Server.

Clean Up

To avoid on-going charges, delete the resources that you have created as part of this post.

1. Terminate Amazon EC2 instance
2. Delete RDS Custom instance
3. Delete Secrets Manager secret
4. Delete SSM association
5. Delete SSM document

Conclusion

In this post, we showed you how to automate the setup of SSL encryption on RDS Custom for SQL Server using Systems Manager. You can perform this configuration through Systems Manager automation to support RDS Custom automation activities like instance scaling, patching, and instance replacement due hardware degradation. Try out this solution and if you have any comments or questions, leave them in the comments section.

About the Authors

Barry Ooi is a Senior Database Specialist Solution Architect at AWS. His expertise is in designing, building and implementing data platform using cloud native services for customers as part of their journey on AWS. His areas of interest include data analytics and visualization.

Arief Nugraha is a Senior Database Solutions Architect with Amazon Web Services based out of Indonesia. He works with AWS customers to migrate data workloads and optimize database services usage in AWS cloud.