How to Restore to AWS RDS SQL Server from TDE Enabled SQL DB(on-premises) backup file stored in S3

All we need is an easy explanation of the problem, so here it is.

I have a on-premises TDE enabled SQL DB backup file available in S3 along with Cert and Pvt Key files.
How to restore in AWS RDS SQL DB by utilizing the same master key password provided during source db backup?

When I tried to run the below query in RDS, getting error as ‘User doesnot have permission to perform this action’

`CREATE MASTER KEY ENCRYPTION 
BY PASSWORD='[email protected]'`

The aws website provides below SP for restoration of TDE enabled SQL DB

`EXECUTE msdb.dbo.rds_restore_tde_certificate
@certificate_name='UserTDECertificate_certificate_name'
, @certificate_file_s3_arn='arn:aws:s3:::bucket_name/certificate_file_name.cer'
, @private_key_file_s3_arn='arn:aws:s3:::bucket_name/key_file_name.pvk'
, @kms_password_key_arn='arn:aws:kms:region:account-id:key/key-id'`

@kms_password_key_arn – The ARN of the symmetric KMS key used to encrypt the private key password.

Just wanted to know from where/how we will get value of the parameter @kms_password_key_arn with same password (‘[email protected]’) used during .bak file creation when we are unable to run/create masterkey encryption query in RDS.

Any suggestions would be appreciated.
Thanks in Advance!

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

First, you need to follow the instructions for backing up a TDE certificate on an on-premise SQL Server for use in RDS – https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.TDE.html#TDE.BackupRestoreOnPrem

Then you need to follow the instructions for restoring a TDE certificate from S3 – https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.SQLServer.Options.TDE.html#TDE.BackupRestoreRDS

The output from Step 1 in the first task contains the key id information for use in the second task.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply