Migrate TDE-enabled SQL Server databases to Amazon RDS for SQL Server

Amazon Relational Database Service (Amazon RDS) for SQL Server now supports the direct migration of transparent database encryption (TDE)-enabled databases by using the native backup and restore feature. Previously, to migrate a TDE-enabled database from on premises to Amazon RDS for SQL Server, you had to disable the TDE at your on-premises (source) database and then perform a native backup and restore of the given database to an RDS for SQL Server (target) instance.

In this post, we show you how to migrate a TDE-enabled database from on premises or SQL Server on Amazon Elastic Compute Cloud (Amazon EC2) to Amazon RDS for SQL Server.

Overview of transparent database encryption

Transparent database encryption (TDE) protects data at rest, which includes data and log files. The encryption of a database file is done at the page level. The pages in an encrypted database are encrypted before they’re written to disk and are decrypted when read into memory. TDE does real-time I/O encryption and decryption of data and log files. The encryption uses a database encryption key (DEK). The database boot record stores the key for availability during recovery. The DEK is a symmetric key. It’s secured by a certificate that the server’s primary database stores or by an asymmetric key that an EKM module protects. The following diagram illustrates the encryption hierarchy.

Figure 1: Encryption hierarchy (Source: Microsoft)

Solution overview

For your initial setup, you need a SQL Server on premises or on Amazon EC2 (source) and an RDS for SQL Server instance (target), as shown in the following figure 2.

Figure 2: Initial Setup

To implement the solution, you complete the following high-level steps:

Create Amazon Simple Storage Service (Amazon S3) buckets.
Create an AWS Identity and Access Management (IAM) role to access the S3 buckets.
Create a symmetric AWS Key Management Service (AWS KMS) key.
Create an option group for Amazon RDS for SQL Server.
Add the backup and restore option to the option group.
Add the TDE option to the option group.
Create an RDS for SQL Server instance.
Create an EC2 instance with SQL Server (if you don’t already have one).
Migrate the TDE-enabled database from self-managed SQL Server to Amazon RDS for SQL Server.

We also discuss how to drop the restored TDE certificate, if it’s no longer needed.

Prerequisites

Before we begin, we assume that you have the following prerequisites:

An understanding of TDE and migration of TDE-enabled databases
The AWS Command Line Interface (AWS CLI) installed and configured
An EC2 instance with SQL Server installed (source) and an RDS for SQL Server instance (target)
Install SQL Server Management Studio (SSMS) and make sure it has access to both SQL Server Instances.

For more information, refer to Creating a Microsoft SQL Server DB instance and connecting to it, Tutorial: Get started with Amazon EC2 Windows instances, and Transparent data encryption (TDE).

Because this solution involves AWS resource setup and utilization, it will incur costs on your account. Refer to AWS Pricing for more information.

We strongly recommend that you set this up in a non-production instance and run end-to-end validations before you implement this solution in a production environment.

Create S3 buckets

As a security best practice, we suggest creating two separate Amazon S3 buckets: one for your database backups and restores, and another bucket for storing TDE certificate and private key files. For this post, we create the buckets tde-poc-dkt and tde-poc-db. Replace these with your own values. You must create these buckets in the same Region as your RDS instance. For instructions, refer to Creating a bucket.

Create an IAM role to access the buckets

If you already have an existing IAM role, you can use that, or you can create a new IAM role manually. If you want to create a new IAM role manually, use the following example. Use the same trust relationships and permission policy for an existing role. For this post, we create a role called rds-sqlserver-tde-role. For more information about creating a role, refer to Creating a role to delegate permissions to an AWS service.

For this implementation, we create a new role and add the following trusted entity in the code block for the custom trust policy. Next, you create a customer-managed policy using the following sample Amazon S3 permission policy listed within the IAM role itself.

The following code is an example trust relationship for backup and restore of the certificate, private key, and database files:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Principal": {
                "Service": "rds.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

The following is the example permissions policy for backup and restore of the certificate, private key, and database files:

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "s3:ListAllMyBuckets",
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketLocation",
                "s3:GetBucketACL"
            ],
            "Resource": [
                "arn:aws:s3:::tde-poc-bkt",
                "arn:aws:s3:::tde-poc-db"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:GetObject",
                "s3:PutObject",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload"
            ],
            "Resource": [
                "arn:aws:s3:::tde-poc-bkt/*",
                "arn:aws:s3:::tde-poc-db/*"
            ]
        }
    ]
}

Create a symmetric KMS key

Create a symmetric key in the same Region as your RDS instance. For instructions, refer to Creating symmetric encryption KMS keys.

Choose the following options when creating the key:

Key type – Symmetric
Key usage – Encrypt and decrypt
Key administrators – Add the IAM role you created
Key usage permissions – Add the IAM role you created

Create an option group for Amazon RDS for SQL Server

Use the following AWS CLI command to create an option group for your RDS instance. In the following example, we use my-opt-grp as the name and SQL Server version 15.00. You can replace the parameters according to your environment.

aws rds create-option-group \
--option-group-name my-opt-grp \
--engine-name sqlserver-ee --major-engine-version 15.00 --region us-west-2 \
--option-group-description "Native Backup/Restore and TDE SQL Server EE 2019"

Add the backup and restore option to the option group

Use the following AWS CLI command to add the native backup and restore option to the option group you created. Replace the parameters and the IAM role ARN according to your environment.

aws rds add-option-to-option-group --option-group-name my-opt-grp --apply-immediately \
--options "OptionName=SQLSERVER_BACKUP_RESTORE,OptionSettings=[{Name=IAM_ROLE_ARN,Value=arn:aws:iam::<aws account id>:role/rds-sqlserver-tde-role}]"

Add the TDE option to the option group

Use the following AWS CLI command to add the TDE option to the option group. Replace the parameters according to your environment.

aws rds add-option-to-option-group \
--option-group-name my-opt-grp --apply-immediately \
--options OptionName=TDE --region us-west-2

Create an RDS for SQL Server instance

Use the following AWS CLI command to create an RDS instance that acts as the target of the database instance for migration. The option group you created earlier is attached to the instance. The instance name for this post is rds-sqlserver-tde. In the following example, we have specified the database subnet group (rds-subnet-group) and security group (sg-8f776cff) from our environment. You must replace it with your database subnet group name and security group ID.

aws rds create-db-instance --db-instance-identifier rds-sqlserver-tde \
--engine sqlserver-ee --engine-version 15.00 --db-instance-class db.m5.xlarge \
--master-username master --master-user-password password --allocated-storage 200 --license-model license-included \
--option-group-name my-opt-grp --vpc-security-group-ids sg-8f776cff --region us-west-2 \
--db-subnet-group-name rds-subnet-group

For more information, refer to Creating a Microsoft SQL Server DB instance and connecting to it.

Create an EC2 instance with SQL Server

Use the following AWS CLI command to create an EC2 instance that is the source of the TDE-enabled database. In the following example, we use the security group (sg-0756aba050ab92164), subnet ID (subnet-6c7a3e44), AMI ID (ami-066f0531ae091f886) and key name (AB3-US-West-2) from our environment. Replace these with your own values.

aws ec2 run-instances --image-id ami-066f0531ae091f886 --count 1 \
instance-type m5a.xlarge --key-name AB3-US-West-2 --security-group-ids sg-0756aba050ab92164  \
--subnet-id subnet-6c7a3e44 --region us-west-2

To create your instance on the Amazon EC2 console, refer to Create your EC2 resources and launch your EC2 instance.

Migrate your TDE-enabled database from self-managed SQL Server to Amazon RDS for SQL Server

To migrate your database, complete the following steps:

Connect to the SQL Server on the EC2 instance you created earlier using SQL Server Management Studio (SSMS).

Use the following T-SQL command to create the database master key (DMK), certificate, and database encryption key, and encrypt the user database on premises or Amazon EC2. Change the command variables according to your environment. In the following example, we migrate the database onpremdb.

USE master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='<secret_password>';
GO

CREATE CERTIFICATE TDE_Cert
WITH
SUBJECT='Database_Encryption';
GO

use onpremdb
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO

ALTER DATABASE onpremdb SET ENCRYPTION ON;
GO

Use the following T-SQL command to verify the encryption status of your database. Encryption state = 3 indicates that the database is fully encrypted.
```
SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,percent_complete,encryptor_thumbprint, encryptor_type
FROM sys.dm_database_encryption_keys
```
Before taking the certificate backup, we need to generate a data key. To restore a TDE certificate that was backed up on an on-premises instance to an RDS for SQL Server instance, you must use a data key generated using your KMS key to back up the private key and save the ciphertext to the Amazon S3 metadata of the private key file under the tag x-amz-meta-rds-tde-pwd.

Use the following AWS CLI command to generate the data key required before taking the certificate backup. Locate your AWS KMS key ID on the AWS KMS console.

aws kms generate-data-key --key-id eee4d613-4871-4cee-87d8-a54ad8d5251b \ 
--key-spec AES_256 --region us-west-2

--Note: Example output from the command above
{
"Plaintext": "Gve+VE1JV8aufhDkPKeWceEt+vZaTYZCH25/OLPo+a4=", 
"KeyId": "arn:aws:kms:us-west-2:289799745972:key/eee4d613-4871-4cee-87d8-a54ad8d5251b", 
"CiphertextBlob": "AQIDAHiojOaIcTtQGK4gqEf6QcYuVUutMiW0y52tG63GqOknSgF0ZtnfmfqadzhfWUlRSx15AAAAfjB8BgkqhkiG9w0BBwagbzBtAgEAMGgGCSqGSIb3DQEHATAeBglghkgBZQMEAS4wEQQM/U3+BkE/yN5Jf0ckAgEQgDtoG9coTwG7bnPGx3c4hND8LAc4LEPnxd2BLQdJDOjqXU0LgudNfVUw4xNfWy2XCDgmSYV0nHU9EVbwkQ=="
}

Take a backup of your TDE certificate and private key using the following T-SQL command. Replace the password field with the data key you generated earlier (plaintext). Replace the backup file paths with values that match your environment. Use certificatename.cer and privatekey.pvk as the names of your TDE certificate and private key files.

use master 

BACKUP CERTIFICATE TDE_Cert TO FILE= 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\certificatename.cer'
WITH PRIVATE KEY (
FILE= 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\privatekey.pvk',
ENCRYPTION BY PASSWORD= 'Gve+VE1JV8aufhDkPKeWceEt+vZaTYZCH25/OLPo+a4=')

Note : Encryption password was generated in aws kms generate-data-key step above

Take a backup of your TDE-enabled database using the following T-SQL command. Replace the database name (onpremdb) with your database name.
```
BACKUP DATABASE onpremdb to DISK='C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\onpremdb.bak' with NOSKIP, NOFORMAT;
```
Next, you upload the certificate, private key, and database backup to the S3 buckets you created earlier. For more information on how to upload the files to Amazon S3 and edit the metadata, refer to Uploading objects.
Upload the database backup file to the tde-poc-db bucket or user defined bucket name.
Upload the certificate backup file (certificatename.cer) to the tde-poc-bkt bucket or user defined bucket name.
Upload the private key backup file (privatekey.pvk) to the tde-poc-bkt bucket or user defined bucket name.
Edit the metadata of the private key backup file and choose to add metadata with type as user defined, key name as x-amz-meta-rds-tde-pwd, and value as the KMS key CipertextBlob value from earlier.

Restore the TDE certificate and private key to Amazon RDS for SQL Server using the following Amazon RDS stored procedure. In this example code, we use the values of our environment. You must change the parameters according to your own environment. Note that we have appended UserTDECertificate_ to the name of the certificate. You can obtain the KMS key ARN from the AWS KMS console.

-- Add prefix "UserTDECertificate_" to @certificate_name

exec msdb.dbo.rds_restore_tde_certificate
 @certificate_name='UserTDECertificate_ec2_tde_cert',
 @certificate_file_s3_arn='arn:aws:s3:::tde-poc-bkt/certificatename.cer',
 @private_key_file_s3_arn='arn:aws:s3:::tde-poc-bkt/privatekey.pvk',
 @kms_password_key_arn='arn:aws:kms:us-west-2:<aws-account-id>:key/eee4d613-4871-4cee-87d8-a54ad8d5251b';

 
 Monitor Task: 

exec msdb.dbo.rds_task_status @task_id= <task id>;

Use the following Amazon RDS T-SQL function to list the restored user TDE certificate:
```
SELECT * FROM msdb.dbo.rds_fn_list_user_tde_certificates()
```
After you have restored the TDE certificate, you can restore the TDE-enabled database into your RDS for SQL Server instance using the Amazon RDS procedure rds_restore_database. Change the parameters according to your environment. You can verify the status of database restore using the Amazon RDS provided stored procedure rds_task_status.
```
exec msdb.dbo.rds_restore_database 
@restore_db_name='onpremdb',
@s3_arn_to_restore_from='arn:aws:s3:::tde-poc-db/onpremdb.bak';

exec msdb.dbo.rds_task_status @task_id= <task_id> ; 
```
For more information, refer to Using native backup and restore.
When the restoration is successful, you can verify if the restored database is encrypted using the following T-SQL command:
```
SELECT DB_NAME(database_id) AS DatabaseName, encryption_state, percent_complete,* 
FROM sys.dm_database_encryption_keys
```

Drop the restored TDE certificate

User TDE certiﬁcates (those preﬁxed with UserTDECertificate_) are used for restoring self-managed, TDE-enabled databases to Amazon RDS for SQL Server. After restoring the databases, and before making them available for use, Amazon RDS modiﬁes the TDE-enabled databases to use Amazon RDS-generated TDE certiﬁcates (those preﬁxed with RDSTDECertificate). User TDE certiﬁcates remain on the RDS for SQL Server DB instance unless you drop them using the rds_drop_tde_certificate stored procedure.

You can’t reuse a user TDE certiﬁcate for TDE encryption of any other database on your RDS for SQL Server DB instance. You can only use it to restore TDE-enabled databases from the source database instance that uses the same TDE certiﬁcate.

If you decide that the user TDE certificates are no longer required, you can drop them using the provided stored procedure.

The following example drops the user certificate UserTDECertificate_ec2_tde_cert from the RDS instance:

EXECUTE msdb.dbo.rds_drop_tde_certificate UserTDECertificate_ec2_tde_cert

Clean up the environment

To avoid future charges and remove all the components created while testing this use case, complete the following steps:

On the Amazon RDS console, choose Databases in the navigation pane.
Select the databases you set up and on the Actions menu, choose Delete.
Enter delete me to confirm deletion.
For more information about deleting an instance, refer to Deleting a DB instance.
On the Amazon EC2 console, locate the SQL Server on Amazon EC2 instance that you used as your source and delete the instance.
On the Amazon S3 console, locate the bucket you created earlier.
Empty the bucket, then delete the bucket.

Summary

In this post, you learned about how to migrate a TDE-enabled database from a self-managed SQL Server instance to Amazon RDS for SQL Server. This feature is useful if you want to migrate your existing databases to Amazon RDS for SQL Server without first having to decrypt them before taking a backup. It helps to save time and effort in your migration journey to AWS.

Try out this solution in your RDS for SQL Server instance 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 includes data analytics and visualization. In his spare time, he loves music and outdoor activities.

Sudarshan Roy is a Senior Database Specialist Cloud Solution Architect with World Wide AWS Database Services Organization (WWSO). He has led large scale Database Migration & Modernization engagements for Enterprise Customers and his passionate of solving complex migration challenges while moving database workload to AWS Cloud.

AWS Database Blog