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

{"value":"[Amazon Relational Database Service (Amazon RDS) for SQL Server](https://aws.amazon.com/rds/sqlserver)now supports the direct migration of [transparent database encryption (TDE)](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16)-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.\n\nIn this post, we show you how to migrate a TDE-enabled database from on premises or SQL Server on [Amazon Elastic Compute Cloud](http://aws.amazon.com/ec2) (Amazon EC2) to Amazon RDS for SQL Server.\n\n\n#### **Overview of transparent database encryption**\n\n\nTransparent 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.\n\n\n\nFigure 1: Encryption hierarchy (Source: Microsoft)\n\n\n#### **Solution overview**\n\n\nFor 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.\n\n\n\nFigure 2: Initial Setup\n\nTo implement the solution, you complete the following high-level steps:\n1. Create [Amazon Simple Storage Service](http://aws.amazon.com/s3) (Amazon S3) buckets.\n2. Create an [Amazon Web Services Identity and Access Management](http://aws.amazon.com/iam) (IAM) role to access the S3 buckets.\n3. Create a symmetric [Amazon Web Services Key Management Service](http://aws.amazon.com/kms) \n4. Create an option group for Amazon RDS for SQL Server.\n5. Add the backup and restore option to the option group.\n6. Add the TDE option to the option group.\n7. Create an RDS for SQL Server instance.\n8. Create an EC2 instance with SQL Server (if you don’t already have one).\n9. Migrate the TDE-enabled database from self-managed SQL Server to Amazon RDS for SQL Server.\n\nWe also discuss how to drop the restored TDE certificate, if it’s no longer needed.\n\n\n#### **Prerequisites**\n\n\nBefore we begin, we assume that you have the following prerequisites:\n\n- An understanding of TDE and migration of TDE-enabled databases \n- The [Amazon Web Services Command Line Interface](https://aws.amazon.com/cli/) (Amazon Web Services CLI) installed and configured\n- An EC2 instance with SQL Server installed (source) and an RDS for SQL Server instance (target)\n- Install SQL Server Management Studio (SSMS) and make sure it has access to both SQL Server Instances.\n\nFor more information, refer to [Creating a Microsoft SQL Server DB instance and connecting to it](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.CreatingConnecting.SQLServer.html), [_Get started with Amazon EC2 Windows instances](https://docs.aws.amazon.com/AWSEC2/latest/WindowsGuide/EC2_GetStarted.html), [and Transparent data encryption (TDE)](https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16).\nBecause this solution involves Amazon Web Services resource setup and utilization, it will incur costs on your account. Refer to Amazon Web Services Pricing for more information.\n\nWe 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.\n\n\n#### **Create S3 buckets**\n\n\nAs 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](https://docs.aws.amazon.com/AmazonS3/latest/userguide/create-bucket-overview.html).\n\n\n#### **Create an IAM role to access the buckets**\n\n\nIf 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 Amazon Web Services service](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-service.html).\n\nFor 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.\n\nThe following code is an example trust relationship for backup and restore of the certificate, private key, and database files:\n\n```\n{\n \"Version\": \"2012-10-17\",\n \"Statement\": [\n {\n \"Effect\": \"Allow\",\n \"Principal\": {\n \"Service\": \"rds.amazonaws.com\"\n },\n \"Action\": \"sts:AssumeRole\"\n }\n ]\n}\n```\n\nThe following is the example permissions policy for backup and restore of the certificate, private key, and database files:\n\n```\n{\n \"Version\": \"2012-10-17\",\n \"Statement\": [\n {\n \"Effect\": \"Allow\",\n \"Action\": \"s3:ListAllMyBuckets\",\n \"Resource\": \"*\"\n },\n {\n \"Effect\": \"Allow\",\n \"Action\": [\n \"s3:ListBucket\",\n \"s3:GetBucketLocation\",\n \"s3:GetBucketACL\"\n ],\n \"Resource\": [\n \"arn:aws:s3:::tde-poc-bkt\",\n \"arn:aws:s3:::tde-poc-db\"\n ]\n },\n {\n \"Effect\": \"Allow\",\n \"Action\": [\n \"s3:GetObject\",\n \"s3:PutObject\",\n \"s3:ListMultipartUploadParts\",\n \"s3:AbortMultipartUpload\"\n ],\n \"Resource\": [\n \"arn:aws:s3:::tde-poc-bkt/*\",\n \"arn:aws:s3:::tde-poc-db/*\"\n ]\n }\n ]\n}\n\n```\n\n\n\n#### **Create a symmetric KMS key**\n\n\nCreate a symmetric key in the same Region as your RDS instance. For instructions, refer to [Creating symmetric encryption KMS keys](https://docs.aws.amazon.com/kms/latest/developerguide/create-keys.html#create-symmetric-cmk).\n\nChoose the following options when creating the key:\n\n- Key type – Symmetric\n- Key usage – Encrypt and decrypt\n- Key administrators – Add the IAM role you created\n- Key usage permissions – Add the IAM role you created\n\n\n#### **Create an option group for Amazon RDS for SQL Server**\n\nUse the following Amazon Web Services 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.\n\n```\naws rds create-option-group \\\n--option-group-name my-opt-grp \\\n--engine-name sqlserver-ee --major-engine-version 15.00 --region us-west-2 \\\n--option-group-description \"Native Backup/Restore and TDE SQL Server EE 2019\" \n\n```\n\n#### **Add the backup and restore option to the option group**\n\n\nUse the following Amazon Web Services 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.\n\n```\naws rds add-option-to-option-group --option-group-name my-opt-grp --apply-immediately \\\n--options \"OptionName=SQLSERVER_BACKUP_RESTORE,OptionSettings=[{Name=IAM_ROLE_ARN,Value=arn:aws:iam::<aws account id>:role/rds-sqlserver-tde-role}]\" \n\n```\n\n\n#### **Add the TDE option to the option group**\n\n\nUse the following Amazon Web Services CLI command to add the TDE option to the option group. Replace the parameters according to your environment.\n\n```\naws rds add-option-to-option-group \\\n--option-group-name my-opt-grp --apply-immediately \\\n--options OptionName=TDE --region us-west-2\n\n```\n\n\n#### **Create an RDS for SQL Server instance**\n\n\nUse the following Amazon Web Services 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.\n\n```\naws rds create-db-instance --db-instance-identifier rds-sqlserver-tde \\\n--engine sqlserver-ee --engine-version 15.00 --db-instance-class db.m5.xlarge \\\n--master-username master --master-user-password password --allocated-storage 200 --license-model license-included \\\n--option-group-name my-opt-grp --vpc-security-group-ids sg-8f776cff --region us-west-2 \\\n--db-subnet-group-name rds-subnet-group\n```\n\nFor more information, refer to [ Creating a Microsoft SQL Server DB instance and connecting to it](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.CreatingConnecting.SQLServer.html).\n\n\n#### **Create an EC2 instance with SQL Server**\n\n\nUse the following Amazon Web Services 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.\n\n```\naws ec2 run-instances --image-id ami-066f0531ae091f886 --count 1 \\\ninstance-type m5a.xlarge --key-name AB3-US-West-2 --security-group-ids sg-0756aba050ab92164 \\\n--subnet-id subnet-6c7a3e44 --region us-west-2\n```\n\nTo create your instance on the Amazon EC2 console, refer to [Create your EC2 resources and launch your EC2 instance](https://docs.aws.amazon.com/efs/latest/ug/gs-step-one-create-ec2-resources.html).\n\n\n#### **Migrate your TDE-enabled database from self-managed SQL Server to Amazon RDS for SQL Server**\n\n\nTo migrate your database, complete the following steps:\n\n1. Connect to the SQL Server on the EC2 instance you created earlier using SQL Server Management Studio (SSMS).\n2. 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.\n\n```\nUSE master;\nGO\nCREATE MASTER KEY ENCRYPTION\nBY PASSWORD='<secret_password>';\nGO\n\nCREATE CERTIFICATE TDE_Cert\nWITH\nSUBJECT='Database_Encryption';\nGO\n\nuse onpremdb\nCREATE DATABASE ENCRYPTION KEY\nWITH ALGORITHM = AES_256\nENCRYPTION BY SERVER CERTIFICATE TDE_Cert;\nGO\n\nALTER DATABASE onpremdb SET ENCRYPTION ON;\nGO\n```\n\n3.Use the following T-SQL command to verify the encryption status of your database. Encryption state = 3 indicates that the database is fully encrypted.\n\n```\nSELECT DB_NAME(database_id) AS DatabaseName, encryption_state,percent_complete,encryptor_thumbprint, encryptor_type\nFROM sys.dm_database_encryption_keys\n\n```\n\nBefore 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.\n\n\n4.Use the following Amazon Web Services CLI command to generate the data key required before taking the certificate backup. Locate your Amazon Web Services KMS key ID on the Amazon Web Services KMS console.\n\n```\naws kms generate-data-key --key-id eee4d613-4871-4cee-87d8-a54ad8d5251b \\ \n--key-spec AES_256 --region us-west-2\n\n--Note: Example output from the command above\n{\n\"Plaintext\": \"Gve+VE1JV8aufhDkPKeWceEt+vZaTYZCH25/OLPo+a4=\", \n\"KeyId\": \"arn:aws:kms:us-west-2:289799745972:key/eee4d613-4871-4cee-87d8-a54ad8d5251b\", \n\"CiphertextBlob\": \"AQIDAHiojOaIcTtQGK4gqEf6QcYuVUutMiW0y52tG63GqOknSgF0ZtnfmfqadzhfWUlRSx15AAAAfjB8BgkqhkiG9w0BBwagbzBtAgEAMGgGCSqGSIb3DQEHATAeBglghkgBZQMEAS4wEQQM/U3+BkE/yN5Jf0ckAgEQgDtoG9coTwG7bnPGx3c4hND8LAc4LEPnxd2BLQdJDOjqXU0LgudNfVUw4xNfWy2XCDgmSYV0nHU9EVbwkQ==\"\n}\n```\n\n5.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.\n\n```\nuse master \n\nBACKUP CERTIFICATE TDE_Cert TO FILE= 'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.SQL2019\\MSSQL\\DATA\\certificatename.cer'\nWITH PRIVATE KEY (\nFILE= 'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.SQL2019\\MSSQL\\DATA\\privatekey.pvk',\nENCRYPTION BY PASSWORD= 'Gve+VE1JV8aufhDkPKeWceEt+vZaTYZCH25/OLPo+a4=')\n\nNote : Encryption password was generated in aws kms generate-data-key step above \n\n```\n\n6.Take a backup of your TDE-enabled database using the following T-SQL command. Replace the database name (onpremdb) with your database name.\n\n```\nBACKUP DATABASE onpremdb to DISK='C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\Backup\\onpremdb.bak' with NOSKIP, NOFORMAT;\n\n```\n\nNext, 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.\n\n7.Upload the database backup file to the tde-poc-db bucket or user defined bucket name.\n8.Upload the certificate backup file (certificatename.cer) to the tde-poc-bkt bucket or user defined bucket name.\n9.Upload the private key backup file (privatekey.pvk) to the tde-poc-bkt bucket or user defined bucket name.\n10.[Edit the metadata](https://docs.aws.amazon.com/AmazonS3/latest/userguide/add-object-metadata.html) 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.\n11.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 Amazon Web Services KMS console.\n\n```\n-- Add prefix \"UserTDECertificate_\" to @certificate_name\n\nexec msdb.dbo.rds_restore_tde_certificate\n @certificate_name='UserTDECertificate_ec2_tde_cert',\n @certificate_file_s3_arn='arn:aws:s3:::tde-poc-bkt/certificatename.cer',\n @private_key_file_s3_arn='arn:aws:s3:::tde-poc-bkt/privatekey.pvk',\n @kms_password_key_arn='arn:aws:kms:us-west-2:<aws-account-id>:key/eee4d613-4871-4cee-87d8-a54ad8d5251b';\n\n \n Monitor Task: \n\nexec msdb.dbo.rds_task_status @task_id= <task id>;\n```\n\n12.Use the following Amazon RDS T-SQL function to list the restored user TDE certificate:\n\n```\nSELECT * FROM msdb.dbo.rds_fn_list_user_tde_certificates()\n\n```\n\n\n13.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.\n\n```\nexec msdb.dbo.rds_restore_database \n@restore_db_name='onpremdb',\n@s3_arn_to_restore_from='arn:aws:s3:::tde-poc-db/onpremdb.bak';\n\nexec msdb.dbo.rds_task_status @task_id= <task_id> ; \n```\n\nFor more information, refer to Using native backup and restore.\n\n14.When the restoration is successful, you can verify if the restored database is encrypted using the following T-SQL command:\n\n```\nSELECT DB_NAME(database_id) AS DatabaseName, encryption_state, percent_complete,* \nFROM sys.dm_database_encryption_keys\n```\n\n\n\n#### **Drop the restored TDE certificate**\n\n\nUser TDE certificates (those prefixed 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 modifies the TDE-enabled databases to use Amazon RDS-generated TDE certificates (those prefixed with RDSTDECertificate). User TDE certificates remain on the RDS for SQL Server DB instance unless you drop them using the rds_drop_tde_certificate stored procedure.\n\nYou can’t reuse a user TDE certificate 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 certificate.\n\nIf you decide that the user TDE certificates are no longer required, you can drop them using the provided stored procedure.\n\nThe following example drops the user certificate UserTDECertificate_ec2_tde_cert from the RDS instance:\n\n```\nEXECUTE msdb.dbo.rds_drop_tde_certificate UserTDECertificate_ec2_tde_cert\n```\n\n\n\n#### **Clean up the environment**\n\n\nTo avoid future charges and remove all the components created while testing this use case, complete the following steps:\n\n1. On the Amazon RDS console, choose Databases in the navigation pane.\n2. Select the databases you set up and on the Actions menu, choose Delete.\n3. Enter delete me to confirm deletion.\nFor more information about deleting an instance, refer to [Deleting a DB instance](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_DeleteInstance.html).\n4. On the Amazon EC2 console, locate the SQL Server on Amazon EC2 instance that you used as your source and [delete the instance](https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/terminating-instances.html).\n5. On the Amazon S3 console, locate the bucket you created earlier.\n6. [Empty the bucket](https://docs.aws.amazon.com/AmazonS3/latest/userguide/empty-bucket.html), then [delete the bucket](https://docs.aws.amazon.com/AmazonS3/latest/userguide/delete-bucket.html).\n\n\n#### **Summary**\n\n\nn 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 Amazon Web Services.\n\nTry out this solution in your RDS for SQL Server instance and if you have any comments or questions, leave them in the comments section.\n\n\n#### **About the Authors**\n\n\n\n\n\n**Barry Ooi** is a Senior Database Specialist Solution Architect at Amazon Web Services. His expertise is in designing, building and implementing data platform using cloud native services for customers as part of their journey on Amazon Web Services. His areas of interest includes data analytics and visualization. In his spare time, he loves music and outdoor activities.\n\n\n\n**Sudarshan Roy** is a Senior Database Specialist Cloud Solution Architect with World Wide Amazon Web Services 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 Amazon Web Services Cloud.","render":"<p><a href=\"https://aws.amazon.com/rds/sqlserver\" target=\"_blank\">Amazon Relational Database Service (Amazon RDS) for SQL Server</a>now supports the direct migration of <a href=\"https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16\" target=\"_blank\">transparent database encryption (TDE)</a>-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.</p>\n<p>In this post, we show you how to migrate a TDE-enabled database from on premises or SQL Server on <a href=\"http://aws.amazon.com/ec2\" target=\"_blank\">Amazon Elastic Compute Cloud</a> (Amazon EC2) to Amazon RDS for SQL Server.</p>\n<h4><a id=\"Overview_of_transparent_database_encryption_5\"></a><strong>Overview of transparent database encryption</strong></h4>\n<p>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.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/aa3f486638044997b814ffa48fdb9f41_image.png\" alt=\"image.png\" /></p>\n<p>Figure 1: Encryption hierarchy (Source: Microsoft)</p>\n<h4><a id=\"Solution_overview_15\"></a><strong>Solution overview</strong></h4>\n<p>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.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/1b82ba8dd8c1480691640a444af3d7dc_image.png\" alt=\"image.png\" /></p>\n<p>Figure 2: Initial Setup</p>\n<p>To implement the solution, you complete the following high-level steps:</p>\n<ol>\n<li>Create <a href=\"http://aws.amazon.com/s3\" target=\"_blank\">Amazon Simple Storage Service</a> (Amazon S3) buckets.</li>\n<li>Create an <a href=\"http://aws.amazon.com/iam\" target=\"_blank\">Amazon Web Services Identity and Access Management</a> (IAM) role to access the S3 buckets.</li>\n<li>Create a symmetric <a href=\"http://aws.amazon.com/kms\" target=\"_blank\">Amazon Web Services Key Management Service</a></li>\n<li>Create an option group for Amazon RDS for SQL Server.</li>\n<li>Add the backup and restore option to the option group.</li>\n<li>Add the TDE option to the option group.</li>\n<li>Create an RDS for SQL Server instance.</li>\n<li>Create an EC2 instance with SQL Server (if you don’t already have one).</li>\n<li>Migrate the TDE-enabled database from self-managed SQL Server to Amazon RDS for SQL Server.</li>\n</ol>\n<p>We also discuss how to drop the restored TDE certificate, if it’s no longer needed.</p>\n<h4><a id=\"Prerequisites_38\"></a><strong>Prerequisites</strong></h4>\n<p>Before we begin, we assume that you have the following prerequisites:</p>\n<ul>\n<li>An understanding of TDE and migration of TDE-enabled databases</li>\n<li>The <a href=\"https://aws.amazon.com/cli/\" target=\"_blank\">Amazon Web Services Command Line Interface</a> (Amazon Web Services CLI) installed and configured</li>\n<li>An EC2 instance with SQL Server installed (source) and an RDS for SQL Server instance (target)</li>\n<li>Install SQL Server Management Studio (SSMS) and make sure it has access to both SQL Server Instances.</li>\n</ul>\n<p>For more information, refer to <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.CreatingConnecting.SQLServer.html\" target=\"_blank\">Creating a Microsoft SQL Server DB instance and connecting to it</a>, <a href=\"https://docs.aws.amazon.com/AWSEC2/latest/WindowsGuide/EC2_GetStarted.html\" target=\"_blank\">_Get started with Amazon EC2 Windows instances</a>, <a href=\"https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16\" target=\"_blank\">and Transparent data encryption (TDE)</a>.<br />\nBecause this solution involves Amazon Web Services resource setup and utilization, it will incur costs on your account. Refer to Amazon Web Services Pricing for more information.</p>\n<p>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.</p>\n<h4><a id=\"Create_S3_buckets_54\"></a><strong>Create S3 buckets</strong></h4>\n<p>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 <a href=\"https://docs.aws.amazon.com/AmazonS3/latest/userguide/create-bucket-overview.html\" target=\"_blank\">Creating a bucket</a>.</p>\n<h4><a id=\"Create_an_IAM_role_to_access_the_buckets_60\"></a><strong>Create an IAM role to access the buckets</strong></h4>\n<p>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 <a href=\"https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_create_for-service.html\" target=\"_blank\">Creating a role to delegate permissions to an Amazon Web Services service</a>.</p>\n<p>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.</p>\n<p>The following code is an example trust relationship for backup and restore of the certificate, private key, and database files:</p>\n<pre><code class=\"lang-\">{\n &quot;Version&quot;: &quot;2012-10-17&quot;,\n &quot;Statement&quot;: [\n {\n &quot;Effect&quot;: &quot;Allow&quot;,\n &quot;Principal&quot;: {\n &quot;Service&quot;: &quot;rds.amazonaws.com&quot;\n },\n &quot;Action&quot;: &quot;sts:AssumeRole&quot;\n }\n ]\n}\n</code></pre>\n<p>The following is the example permissions policy for backup and restore of the certificate, private key, and database files:</p>\n<pre><code class=\"lang-\">{\n &quot;Version&quot;: &quot;2012-10-17&quot;,\n &quot;Statement&quot;: [\n {\n &quot;Effect&quot;: &quot;Allow&quot;,\n &quot;Action&quot;: &quot;s3:ListAllMyBuckets&quot;,\n &quot;Resource&quot;: &quot;*&quot;\n },\n {\n &quot;Effect&quot;: &quot;Allow&quot;,\n &quot;Action&quot;: [\n &quot;s3:ListBucket&quot;,\n &quot;s3:GetBucketLocation&quot;,\n &quot;s3:GetBucketACL&quot;\n ],\n &quot;Resource&quot;: [\n &quot;arn:aws:s3:::tde-poc-bkt&quot;,\n &quot;arn:aws:s3:::tde-poc-db&quot;\n ]\n },\n {\n &quot;Effect&quot;: &quot;Allow&quot;,\n &quot;Action&quot;: [\n &quot;s3:GetObject&quot;,\n &quot;s3:PutObject&quot;,\n &quot;s3:ListMultipartUploadParts&quot;,\n &quot;s3:AbortMultipartUpload&quot;\n ],\n &quot;Resource&quot;: [\n &quot;arn:aws:s3:::tde-poc-bkt/*&quot;,\n &quot;arn:aws:s3:::tde-poc-db/*&quot;\n ]\n }\n ]\n}\n\n</code></pre>\n<h4><a id=\"Create_a_symmetric_KMS_key_127\"></a><strong>Create a symmetric KMS key</strong></h4>\n<p>Create a symmetric key in the same Region as your RDS instance. For instructions, refer to <a href=\"https://docs.aws.amazon.com/kms/latest/developerguide/create-keys.html#create-symmetric-cmk\" target=\"_blank\">Creating symmetric encryption KMS keys</a>.</p>\n<p>Choose the following options when creating the key:</p>\n<ul>\n<li>Key type – Symmetric</li>\n<li>Key usage – Encrypt and decrypt</li>\n<li>Key administrators – Add the IAM role you created</li>\n<li>Key usage permissions – Add the IAM role you created</li>\n</ul>\n<h4><a id=\"Create_an_option_group_for_Amazon_RDS_for_SQL_Server_140\"></a><strong>Create an option group for Amazon RDS for SQL Server</strong></h4>\n<p>Use the following Amazon Web Services 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.</p>\n<pre><code class=\"lang-\">aws rds create-option-group \\\n--option-group-name my-opt-grp \\\n--engine-name sqlserver-ee --major-engine-version 15.00 --region us-west-2 \\\n--option-group-description &quot;Native Backup/Restore and TDE SQL Server EE 2019&quot; \n\n</code></pre>\n<h4><a id=\"Add_the_backup_and_restore_option_to_the_option_group_152\"></a><strong>Add the backup and restore option to the option group</strong></h4>\n<p>Use the following Amazon Web Services 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.</p>\n<pre><code class=\"lang-\">aws rds add-option-to-option-group --option-group-name my-opt-grp --apply-immediately \\\n--options &quot;OptionName=SQLSERVER_BACKUP_RESTORE,OptionSettings=[{Name=IAM_ROLE_ARN,Value=arn:aws:iam::&lt;aws account id&gt;:role/rds-sqlserver-tde-role}]&quot; \n\n</code></pre>\n<h4><a id=\"Add_the_TDE_option_to_the_option_group_164\"></a><strong>Add the TDE option to the option group</strong></h4>\n<p>Use the following Amazon Web Services CLI command to add the TDE option to the option group. Replace the parameters according to your environment.</p>\n<pre><code class=\"lang-\">aws rds add-option-to-option-group \\\n--option-group-name my-opt-grp --apply-immediately \\\n--options OptionName=TDE --region us-west-2\n\n</code></pre>\n<h4><a id=\"Create_an_RDS_for_SQL_Server_instance_177\"></a><strong>Create an RDS for SQL Server instance</strong></h4>\n<p>Use the following Amazon Web Services 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.</p>\n<pre><code class=\"lang-\">aws rds create-db-instance --db-instance-identifier rds-sqlserver-tde \\\n--engine sqlserver-ee --engine-version 15.00 --db-instance-class db.m5.xlarge \\\n--master-username master --master-user-password password --allocated-storage 200 --license-model license-included \\\n--option-group-name my-opt-grp --vpc-security-group-ids sg-8f776cff --region us-west-2 \\\n--db-subnet-group-name rds-subnet-group\n</code></pre>\n<p>For more information, refer to <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_GettingStarted.CreatingConnecting.SQLServer.html\" target=\"_blank\"> Creating a Microsoft SQL Server DB instance and connecting to it</a>.</p>\n<h4><a id=\"Create_an_EC2_instance_with_SQL_Server_193\"></a><strong>Create an EC2 instance with SQL Server</strong></h4>\n<p>Use the following Amazon Web Services 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.</p>\n<pre><code class=\"lang-\">aws ec2 run-instances --image-id ami-066f0531ae091f886 --count 1 \\\ninstance-type m5a.xlarge --key-name AB3-US-West-2 --security-group-ids sg-0756aba050ab92164 \\\n--subnet-id subnet-6c7a3e44 --region us-west-2\n</code></pre>\n<p>To create your instance on the Amazon EC2 console, refer to <a href=\"https://docs.aws.amazon.com/efs/latest/ug/gs-step-one-create-ec2-resources.html\" target=\"_blank\">Create your EC2 resources and launch your EC2 instance</a>.</p>\n<h4><a id=\"Migrate_your_TDEenabled_database_from_selfmanaged_SQL_Server_to_Amazon_RDS_for_SQL_Server_207\"></a><strong>Migrate your TDE-enabled database from self-managed SQL Server to Amazon RDS for SQL Server</strong></h4>\n<p>To migrate your database, complete the following steps:</p>\n<ol>\n<li>Connect to the SQL Server on the EC2 instance you created earlier using SQL Server Management Studio (SSMS).</li>\n<li>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.</li>\n</ol>\n<pre><code class=\"lang-\">USE master;\nGO\nCREATE MASTER KEY ENCRYPTION\nBY PASSWORD='&lt;secret_password&gt;';\nGO\n\nCREATE CERTIFICATE TDE_Cert\nWITH\nSUBJECT='Database_Encryption';\nGO\n\nuse onpremdb\nCREATE DATABASE ENCRYPTION KEY\nWITH ALGORITHM = AES_256\nENCRYPTION BY SERVER CERTIFICATE TDE_Cert;\nGO\n\nALTER DATABASE onpremdb SET ENCRYPTION ON;\nGO\n</code></pre>\n<p>3.Use the following T-SQL command to verify the encryption status of your database. Encryption state = 3 indicates that the database is fully encrypted.</p>\n<pre><code class=\"lang-\">SELECT DB_NAME(database_id) AS DatabaseName, encryption_state,percent_complete,encryptor_thumbprint, encryptor_type\nFROM sys.dm_database_encryption_keys\n\n</code></pre>\n<p>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.</p>\n<p>4.Use the following Amazon Web Services CLI command to generate the data key required before taking the certificate backup. Locate your Amazon Web Services KMS key ID on the Amazon Web Services KMS console.</p>\n<pre><code class=\"lang-\">aws kms generate-data-key --key-id eee4d613-4871-4cee-87d8-a54ad8d5251b \\ \n--key-spec AES_256 --region us-west-2\n\n--Note: Example output from the command above\n{\n&quot;Plaintext&quot;: &quot;Gve+VE1JV8aufhDkPKeWceEt+vZaTYZCH25/OLPo+a4=&quot;, \n&quot;KeyId&quot;: &quot;arn:aws:kms:us-west-2:289799745972:key/eee4d613-4871-4cee-87d8-a54ad8d5251b&quot;, \n&quot;CiphertextBlob&quot;: &quot;AQIDAHiojOaIcTtQGK4gqEf6QcYuVUutMiW0y52tG63GqOknSgF0ZtnfmfqadzhfWUlRSx15AAAAfjB8BgkqhkiG9w0BBwagbzBtAgEAMGgGCSqGSIb3DQEHATAeBglghkgBZQMEAS4wEQQM/U3+BkE/yN5Jf0ckAgEQgDtoG9coTwG7bnPGx3c4hND8LAc4LEPnxd2BLQdJDOjqXU0LgudNfVUw4xNfWy2XCDgmSYV0nHU9EVbwkQ==&quot;\n}\n</code></pre>\n<p>5.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.</p>\n<pre><code class=\"lang-\">use master \n\nBACKUP CERTIFICATE TDE_Cert TO FILE= 'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.SQL2019\\MSSQL\\DATA\\certificatename.cer'\nWITH PRIVATE KEY (\nFILE= 'C:\\Program Files\\Microsoft SQL Server\\MSSQL15.SQL2019\\MSSQL\\DATA\\privatekey.pvk',\nENCRYPTION BY PASSWORD= 'Gve+VE1JV8aufhDkPKeWceEt+vZaTYZCH25/OLPo+a4=')\n\nNote : Encryption password was generated in aws kms generate-data-key step above \n\n</code></pre>\n<p>6.Take a backup of your TDE-enabled database using the following T-SQL command. Replace the database name (onpremdb) with your database name.</p>\n<pre><code class=\"lang-\">BACKUP DATABASE onpremdb to DISK='C:\\Program Files\\Microsoft SQL Server\\MSSQL15.MSSQLSERVER\\MSSQL\\Backup\\onpremdb.bak' with NOSKIP, NOFORMAT;\n\n</code></pre>\n<p>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.</p>\n<p>7.Upload the database backup file to the tde-poc-db bucket or user defined bucket name.<br />\n8.Upload the certificate backup file (certificatename.cer) to the tde-poc-bkt bucket or user defined bucket name.<br />\n9.Upload the private key backup file (privatekey.pvk) to the tde-poc-bkt bucket or user defined bucket name.<br />\n10.<a href=\"https://docs.aws.amazon.com/AmazonS3/latest/userguide/add-object-metadata.html\" target=\"_blank\">Edit the metadata</a> 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.<br />\n11.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 Amazon Web Services KMS console.</p>\n<pre><code class=\"lang-\">-- Add prefix &quot;UserTDECertificate_&quot; to @certificate_name\n\nexec msdb.dbo.rds_restore_tde_certificate\n @certificate_name='UserTDECertificate_ec2_tde_cert',\n @certificate_file_s3_arn='arn:aws:s3:::tde-poc-bkt/certificatename.cer',\n @private_key_file_s3_arn='arn:aws:s3:::tde-poc-bkt/privatekey.pvk',\n @kms_password_key_arn='arn:aws:kms:us-west-2:&lt;aws-account-id&gt;:key/eee4d613-4871-4cee-87d8-a54ad8d5251b';\n\n \n Monitor Task: \n\nexec msdb.dbo.rds_task_status @task_id= &lt;task id&gt;;\n</code></pre>\n<p>12.Use the following Amazon RDS T-SQL function to list the restored user TDE certificate:</p>\n<pre><code class=\"lang-\">SELECT * FROM msdb.dbo.rds_fn_list_user_tde_certificates()\n\n</code></pre>\n<p>13.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.</p>\n<pre><code class=\"lang-\">exec msdb.dbo.rds_restore_database \n@restore_db_name='onpremdb',\n@s3_arn_to_restore_from='arn:aws:s3:::tde-poc-db/onpremdb.bak';\n\nexec msdb.dbo.rds_task_status @task_id= &lt;task_id&gt; ; \n</code></pre>\n<p>For more information, refer to Using native backup and restore.</p>\n<p>14.When the restoration is successful, you can verify if the restored database is encrypted using the following T-SQL command:</p>\n<pre><code class=\"lang-\">SELECT DB_NAME(database_id) AS DatabaseName, encryption_state, percent_complete,* \nFROM sys.dm_database_encryption_keys\n</code></pre>\n<h4><a id=\"Drop_the_restored_TDE_certificate_335\"></a><strong>Drop the restored TDE certificate</strong></h4>\n<p>User TDE certificates (those prefixed 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 modifies the TDE-enabled databases to use Amazon RDS-generated TDE certificates (those prefixed with RDSTDECertificate). User TDE certificates remain on the RDS for SQL Server DB instance unless you drop them using the rds_drop_tde_certificate stored procedure.</p>\n<p>You can’t reuse a user TDE certificate 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 certificate.</p>\n<p>If you decide that the user TDE certificates are no longer required, you can drop them using the provided stored procedure.</p>\n<p>The following example drops the user certificate UserTDECertificate_ec2_tde_cert from the RDS instance:</p>\n<pre><code class=\"lang-\">EXECUTE msdb.dbo.rds_drop_tde_certificate UserTDECertificate_ec2_tde_cert\n</code></pre>\n<h4><a id=\"Clean_up_the_environment_352\"></a><strong>Clean up the environment</strong></h4>\n<p>To avoid future charges and remove all the components created while testing this use case, complete the following steps:</p>\n<ol>\n<li>On the Amazon RDS console, choose Databases in the navigation pane.</li>\n<li>Select the databases you set up and on the Actions menu, choose Delete.</li>\n<li>Enter delete me to confirm deletion.<br />\nFor more information about deleting an instance, refer to <a href=\"https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_DeleteInstance.html\" target=\"_blank\">Deleting a DB instance</a>.</li>\n<li>On the Amazon EC2 console, locate the SQL Server on Amazon EC2 instance that you used as your source and <a href=\"https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/terminating-instances.html\" target=\"_blank\">delete the instance</a>.</li>\n<li>On the Amazon S3 console, locate the bucket you created earlier.</li>\n<li><a href=\"https://docs.aws.amazon.com/AmazonS3/latest/userguide/empty-bucket.html\" target=\"_blank\">Empty the bucket</a>, then <a href=\"https://docs.aws.amazon.com/AmazonS3/latest/userguide/delete-bucket.html\" target=\"_blank\">delete the bucket</a>.</li>\n</ol>\n<h4><a id=\"Summary_366\"></a><strong>Summary</strong></h4>\n<p>n 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 Amazon Web Services.</p>\n<p>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.</p>\n<h4><a id=\"About_the_Authors_374\"></a><strong>About the Authors</strong></h4>\n<p><img src=\"https://dev-media.amazoncloud.cn/e0bda6b8bf194eda8281531f37021ef6_image.png\" alt=\"image.png\" /></p>\n<p><strong>Barry Ooi</strong> is a Senior Database Specialist Solution Architect at Amazon Web Services. His expertise is in designing, building and implementing data platform using cloud native services for customers as part of their journey on Amazon Web Services. His areas of interest includes data analytics and visualization. In his spare time, he loves music and outdoor activities.</p>\n<p><img src=\"https://dev-media.amazoncloud.cn/6f59511ddb034d0bbef3505685ac6358_image.png\" alt=\"image.png\" /></p>\n<p><strong>Sudarshan Roy</strong> is a Senior Database Specialist Cloud Solution Architect with World Wide Amazon Web Services Database Services Organization (WWSO). He has led large scale Database Migration &amp; Modernization engagements for Enterprise Customers and his passionate of solving complex migration challenges while moving database workload to Amazon Web Services Cloud.</p>\n"}