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.
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.
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.
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-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:
The following is the example permissions policy for backup and restore of the certificate, private key, and database files:
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.
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.
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.
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.
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.
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
- Use the following T-SQL command to verify the encryption status of your database. Encryption state = 3 indicates that the database is fully encrypted.
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
- 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.
- 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
privatekey.pvkas the names of your TDE certificate and private key files.
- Take a backup of your TDE-enabled database using the following T-SQL command. Replace the database name (
onpremdb) with your database name.
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-dbbucket or user defined bucket name.
- Upload the certificate backup file (
certificatename.cer) to the
tde-poc-bktbucket or user defined bucket name.
- Upload the private key backup file (
privatekey.pvk) to the
tde-poc-bktbucket 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
CipertextBlobvalue 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.
- Use the following Amazon RDS T-SQL function to list the restored user TDE certificate:
- 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
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:
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:
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.
delete meto 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.
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.