How do I turn TDE off or on in my RDS for SQL Server instance and how do I troubleshoot common errors with it?

Short description

TDE protects data at rest by encrypting the database’s physical files, such as the data (.mdf and .ndf) and transaction log file (.ldf). When TDE is turned on, TempDB is automatically encrypted and is used by all user-defined databases to store or process temporary objects.

Resolution

Turn on TDE

To turn on TDE in your instance, do the following:

1. Confirm that TDE is supported for your DB instance’s current DB engine version.
2. Turn on TDE for RDS for SQL Server.
3. Encrypt the data in your database.

Note: The certificate is automatically created when you add the TDE option in the option group and associate it with the DB instance. The certificate is also created automatically if you modify the already associated option group and add the TDE option to it. You don't have to create the TDE certificate manually on the DB instance.

Turn off TDE

For information on how to turn off TDE, see Turning off TDE for RDS for SQL Server.

Note: After turning off TDE on the database, you must reboot the DB instance to remove encryption for TempDB.

Troubleshoot common errors

Error: "Cannot find server certificate with thumbprint '0x56CCEA7170BD5AFB02EB08C674XXXXXXXXXXXXXX'. RESTORE DATABASE is terminating abnormally."

This error occurs when restoring a backup file with a TDE-encrypted source database to an RDS for SQL Server instance other than the original SQL Server instance. To restore the database, the TDE certificate of the source SQL Server instance must be imported to the destination RDS for SQL Server DB instance.

For more information on backing up and restoring TDE certificates, see the following:

• Backing up and restoring TDE certificates on RDS for SQL Server
• Backing up and restoring TDE certificates for on-premises databases
• Migrate TDE-enabled SQL Server databases to Amazon RDS for SQL Server

Error - Msg 50000, Level 16, State 1, Procedure msdb.dbo.rds_restore_tde_certificate, Line 91 [Batch Start Line 0] TDE certificate restore isn't supported on Multi-AZ DB instances.

This error occurs when restoring a TDE certificate on a Multi-AZ DB instance. TDE certificate backup and restore aren't supported on Multi-AZ DB instances.

For more on information, see Limitations in Backing up and restoring TDE certificates on RDS for SQL Server.

To avoid this error, turn off Multi-AZ deployment on your DB instance. Then, restore the TDE certificate on the RDS DB instance.

Error - Task execution has started. Task has been aborted. Private key password not found in S3 metadata.

This error occurs when importing user TDE certificates from an Amazon Simple Storage Service (Amazon S3) bucket with incorrect metadata in the private key.

To resolve this issue, in your S3 certificate bucket, update the following tags in the private key backup file's metadata:

• Key: x-amz-meta-rds-tde-pwd
• Value: The CiphertextBlob value from generating the data key

Error - Task has been aborted. Error verifying S3 bucket security. The associated IAM role does not have permission to access the specified S3 bucket.

This error occurs when backing up or restoring the TDE certificate with an AWS Identity and Access Management (IAM) role that is missing required permissions.

To resolve this issue, verify that the IAM role is both a user and an administrator for the AWS Key Management Service (AWS KMS) key. In addition to the permissions required for SQL Server native backup and restore, the IAM role also requires the following permissions:

• s3:GetBucketACL, s3:GetBucketLocation, and s3:ListBucket on the S3 bucket resource
• s3:ListAllMyBuckets on the * resource

For more information, see the Prerequisites for Backing up and restoring TDE certificates on RDS for SQL Server.

---