Back up and restore transparent data encrypted databases across accounts in Amazon RDS for SQL Server

Amazon Relational Database Service (Amazon RDS) for SQL Server is an AWS managed database service that simplifies operating and managing Microsoft SQL Server databases. Amazon RDS for SQL Server provides value by handling the provisioning, management, and monitoring of SQL Server databases. Although this operational agility is valuable, data protection and security become even more critical when considering the risks of migrating databases outside physical data centers. Amazon RDS empowers you to employ SQL Server’s Transparent Data Encryption (TDE) feature, which protects your data at rest and limits access to authorized users, however, it does not encrypt data in-transit or in-use.

The use of multiple AWS accounts is a common best practice to logically separate environments. When managing TDE-enabled SQL Server databases across AWS accounts, several key steps ensure data protection. This post provides the steps to migrate TDE-enabled databases between Amazon RDS for SQL Server instances across AWS accounts.

Prerequisites

We assume you have prior knowledge of Amazon RDS for SQL Server and TDE in SQL Server. We use SQL Server 2019 Enterprise Edition in this post.

To follow this post, you need the following prerequisites:

Amazon RDS for SQL Server with TDE Options group enabled in account A and account B AWS accounts and appropriate permissions to interact with resources in your AWS account
Access to work with AWS KMS Key
S3 buckets in account A and account B

Solution overview

This solution requires backing up a TDE-enabled database on Amazon RDS for SQL Server in AWS account A, then restoring it to another instance in account B. The TDE certificate, encrypted with an AWS Key Management Service (AWS KMS) key, also needs to be restored in the destination instance. The ciphertext of the private key used to back up the TDE certificate in the source database is decrypted by a KMS key in account A and re-encrypted with a KMS key in account B.

To implement the solution, complete the following high-level steps in account A:

If not exist, create a new symmetric AWS KMS key.
Back up the TDE certificate using the AWS KMS key to an Amazon Simple Storage Service (Amazon S3) bucket.
Back up the RDS for SQL Server database to the S3 bucket.
Extract the ciphertext-blob from the S3 metadata of the private key file.
Share the AWS KMS key.

In account B, complete the following steps:

Decrypt the ciphertext from account A and extract the plaintext password.
Create a new symmetric AWS KMS key in account B and encrypt the key with the plaintext password.
Before restoring the database, the TDE certificate has to be restored in the target instance under account B.
Restore the database and encrypt the AWS KMS key to Amazon RDS for SQL Server in account B.

The following diagram illustrates the solution architecture for migrating TDE-enabled SQL Server databases across accounts.

Back up the TDE certificate and RDS for SQL Server database from account A to an S3 bucket

Back up the TDE certificate on account A using the rds_backup_tde_certificate stored procedure in the RDS instance using a AWS KMS key. You can run the following sample code on Amazon RDS for SQL Server in account A.

Below is an example of statements to be executed in account A.

First, we query the sys.certificates system view to obtain the TDE certificate name, :

USE [master]
GO
SELECT * FROM sys.certificates WHERE name LIKE 'RDSTDECertificate%'
GO

Then we back up the Amazon RDS TDE Certificate using a the KMS key:

EXECUTE msdb.dbo.rds_b ackup_tde_certificate
    @certificate_name='RDSTDECertificate20230323T215533',
    @certificate_file_s3_arn='arn:aws:s3:::<BUCKET-NAME-ACCOUNTA>/mssql-inst02.cer’,
    @private_key_file_s3_arn=’arn:aws:s3:::<BUCKET-NAME-ACCOUNTA>/mssql-inst02.pvk',
    @kms_password_key_arn='arn:aws:kms:us-east-1:<ACCOUNTA-ID>:key/mrk-<Key ID>',
    @overwrite_s3_files=1;

Finally, we back up the encrypted database natively to Amazon S3:

exec msdb.dbo.rds_backup_database
    @source_db_name='tpcc',
    @s3_arn_to_backup_to='arn:aws:s3:::<BUCKET-NAME-ACCOUNTA>/tpcc-native-backup.bak',    
     @overwrite_s3_backup_file=1,
     @type='FULL',
     @number_of_files=4;

For additional information, refer to Support for Transparent Data Encryption in SQL Server.

Extract the ciphertext-blob from the S3 metadata of the private key file

Complete the following steps:

Navigate to the S3 bucket from the previous step and copy the ciphertext-blob from the metadata of the private key file. This can be found under the tag x-amz-meta-rds-tde-pwd.

Share the KMS key (that is used to back up the TDE certificate) to the root user or any other user or role in account B that is going to perform the cryptographic operations.

The following is an example of an IAM policy using the root user:

{
    "Sid": "Allow an external account to use this KMS key",
    "Effect": "Allow",
    "Principal": {
        "AWS": [
        "arn:aws:iam::444455556666:root"
        ]
    },
    "Action": [
    "kms:Encrypt",
    "kms:Decrypt",
    "kms:ReEncrypt*",
    "kms:GenerateDataKey*",
    "kms:DescribeKey"
    ],
    "Resource": “<ARN-FOR-KMS-KEY>”
}

The following is an example of an IAM policy using a role:

{
    "Sid": "Allow an external account to use this KMS key",
    "Effect": "Allow",
    "Principal": {
        "AWS": "arn:aws:iam::444455556666:role/ExampleRole"
    },
    "Action": [
    "kms:Encrypt",
    "kms:Decrypt",
    "kms:ReEncrypt*",
    "kms:GenerateDataKey*",
    "kms:DescribeKey"
    ],
    "Resource": “<ARN-FOR-KMS-KEY>”
}

Copy the TDE certificate (*.cer) and private key (*.pvk) backup files from the account A S3 bucket to an S3 bucket in account B.

Decrypt the ciphertext from account A and extract the plaintext password

Decrypt the ciphertext received from account A using the AWS KMS key ID that you used to back up the TDE certificate. The following is a sample AWS Command Line Interface (AWS CLI) command to decrypt the ciphertext and get the plaintext password:

AWS kms decrypt —key-id  —ciphertext-blob   —output text —query Plaintext —region

For more information about decryption, refer to Decrypt.

Create a new AWS KMS key in account B and encrypt the key with a plaintext password

The next step is to create a new symmetric AWS KMS key in account B and encrypt the plaintext password that was decrypted in the previous step by using the AWS KMS key in account B. Use the credentials that have permissions on the AWS KMS key. The following command provides the ciphertext for this plaintext password that we decrypted previously:

aws kms encrypt —key-id <Provide KMS KeyID from Account-B> —plaintext "The output from the decrypt step" —output text —region <AWS Region>

For more information, refer to Symmetric CMK creation

Next, we navigate to the S3 bucket in account B where the certificate and private key is kept. We select the private key file (.pvk) and navigate to object actions and edit the metadata. Update the ciphertext value under the type User defined and tag x-amz-meta-rds-tde-pwd.

Restore the database and encrypt the AWS KMS key to Amazon RDS for SQL Server in account B

Before you restore the database, ensure that the RDS for SQL Server instance on account B has an option group with TDE and native backup and restore enabled.

Restore the updated TDE certificate into Amazon RDS for SQL Server on account B using the newly created AWS KMS key using following sample code:

EXECUTE msdb.dbo.rds_restore_tde_certificate
    @certificate_name='UserTDECertificate_FromAcct1',
    @certificate_file_s3_arn='arn:aws:s3:::<BUCKET-NAME-ACCOUNTB>/mssql-inst02.cer',
    @private_key_file_s3_arn='arn:aws:s3::: <BUCKET-NAME-ACCOUNTB>/mssql-inst02.pvk',
    @kms_password_key_arn='arn:aws:kms:us-east-1:<ACCOUNTB-ID>:key/mrk-<Key ID>';

Check if the user certificate is loaded with the following code:

SELECT * FROM msdb.dbo.rds_fn_list_user_tde_certificates();

After the TDE certificate is successfully restored and verified in Amazon RDS for SQL Server, we can proceed restoring the database backup. The following is an example to restore the backup of TDE enabled backup from S3 bucket in account B.

exec msdb.dbo.rds_ restore_database
    @ restore_db_name='tpcc',
    @s3_arn_to_restore_from='arn:aws:s3:::<BUCKET-NAME-ACCOUNTB>/tpcc',
    @with_norecovery=0,
    @type='FULL';

Clean up

To remove the AWS resources created during your testing, complete the following steps:

Sign in to the AWS Management Console.
Choose the Region where the RDS for SQL Server instances reside.
On the Amazon RDS console, choose Databases in the navigation pane.
Select the RDS instance that was created.
On the Actions menu, choose Delete.
On the Amazon S3 console, select the bucket that was created.
On the Actions menu, choose Delete.
On the AWS KMS console, select the KMS key you created.
Choose Schedule key deletion, enter a period between 7–30 days, then choose Schedule deletion.

Conclusion

Transparent data encryption is an inbuilt encryption mechanism commonly used in SQL Server to secure data. This post presented a solution for restoring a TDE-enabled database from one RDS for SQL Server instance to another instance residing in a different account.

Give this solution a try and leave any feedback or questions in the comments section. Stay tuned for more posts covering additional SQL Server migration solutions.

About the Authors

Alvaro Costa-Neto is a Database Specialist Solutions Architect for AWS, where he helps customers design and implement database solutions on the cloud. He has a passion for database technologies and has been working with them for more than 19 years, mostly with Microsoft SQL Server. He resides in Clermont, FL with his wife and two children, who share his love for aviation and traveling. When he is not working, he likes to host cookouts with his family and friends and explore new places.

Gopakumar Gopalakrishna Pillai is a Database Engineer at Amazon Web Services. He has worked on multiple database technologies and provided solutions on various customer issues within his 16 years of database journey thus far. He is focusing on providing optimal database solutions for RDS SQL server customers and leverages his experience to help customers build serverless applications. In free time he loves to explore new places.

Jeril Jose is Database Specialist Consultant with over 14 years of experience in Microsoft SQL Server and other database technologies. He helps customers architect, migrate and optimize their database solutions to AWS. Prior to joining AWS, he supported production and mission-critical database implementation across financial and retail segments.

AWS Database Blog