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

Question

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='Strong11111@Password'`

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 ('Strong11111@Password') 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!

Answer 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.