How do I restore an encrypted backup file or encrypted Microsoft Azure backup in RDS for SQL Server from an on-premises environment?

Short description

Backup encryption is available in SQL Server 2014 (12.x) and later. You can specify the encryption algorithm and the encryptor (a certificate or asymmetric key) when creating a backup. This creates an encrypted backup file.

Note: This feature is not the same as TDE encryption.

Resolution

Encrypted backups

Note: Before starting the following resolution steps, make sure that you have created the following:

• An Amazon Simple Storage Service (Amazon S3) bucket.
• An Amazon RDS instance configured with TDE and SQLSERVER_BACKUP_RESTORE in the option group.

1.    Create a master key using the following script in your on-premises account:

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='InsertStrongPasswordHere';
GO

2.    Create a certificate protected by the master key in your on-premises account:

CREATE CERTIFICATE TDE_Cert
WITH 
SUBJECT='Database_Encryption';
GO

3.    Start a native backup using the following script.

Note:Turn on compression to avoid issues while restoring to RDS.

BACKUP DATABASE [TestDB] TO DISK =N'E:\Backup\EncryptedBackupFromOnpremise.bak' WITH COMPRESSION,
ENCRYPTION( ALGORITHM = AES_256, SERVER CERTIFICATE = [TDE_Cert] ), STATS = 10
GO

4.    Upload the encrypted backup to the S3 bucket tde-db, using the AWS Management Console.

5.    Run the following command to restore the database:

exec msdb.dbo.rds_restore_database 
@restore_db_name='onpremdb',
@s3_arn_to_restore_from='arn:aws:s3:::tde-db/EncryptedBackupFromOnpremise.bak';

Encrypted Microsoft Azure backups

Note: This resolution involves similar steps as the preceding sections. However, the encrypted backup is initiated to the Microsoft Azure backup container instead of local storage. Before starting the following resolution steps, make sure that you have created the following:

• An Amazon Simple Storage Service (Amazon S3) bucket.
• An RDS instance configured with TDE and SQLSERVER_BACKUP_RESTORE option group.
• An on-premises account configured with certificates to encrypt the backup.
• An active Microsoft Azure account.

1.    Create a storage account in Microsoft Azure. For more information, see Create a storage account in the Microsoft documentation website.

2.    Create a credential based on the shared access signature. For more information, see Create SAS tokens for your storage containers in the Microsoft documentation website.

USE master  
CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] 
  -- this name must match the container path, start with https and must not contain a forward slash at the end
WITH IDENTITY='SHARED ACCESS SIGNATURE' 
  -- this is a mandatory string and should not be changed   
 , SECRET = 'sharedaccesssignature'
GO

3.    Start a native encrypted backup to the Microsoft Azure storage:

BACKUP DATABASE [TestDB] TO  URL = N'https://backuptestsanketh.blob.core.windows.net/backup/TestDB_backup_2022_09_29_112330.bak' 
WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'BackupEnc-Full Database Backup', NOSKIP, NOREWIND, NOUNLOAD, COMPRESSION, 
ENCRYPTION(ALGORITHM = AES_128, SERVER CERTIFICATE = [On_PremTDE_Cert]),  STATS = 10
GO

Note: Amazon RDS doesn't support restoring backups directly from Microsoft Azure storage containers. So, you must download the backup to an intermediary system.

4.    Upload the backup file to the S3 bucket (tde-db).

5.    To backup and restore the private key and certificate to the RDS instance, follow the steps in the Resolve the "Cannot find server certificate with thumbprint" error section.

6.     After the prerequisites are in place, run the following command to restore the encrypted backup:

exec msdb.dbo.rds_restore_database
@restore_db_name='AzureBackupEncrypted',
@s3_arn_to_restore_from='arn:aws:s3:::tde-db/backup/azure-encrypted.bak'

Resolve the "Cannot find server certificate with thumbprint" error

Restoring encrypted backups in another environment where certificates aren't configured results in the following error:

Cannot find server certificate with thumbprint '0xBA01F2AF71C3FB4A92C5F69BDE3EC8DB318D7D0E'

To avoid the preceding error, do the following:

1.    Create a new AWS Key Management Service (AWS KMS) key in the AWS account where you'll be performing the native restoration.

2.    Generate an AWS KMS data key to initiate backup of the certificate:

aws kms generate-data-key --key-id KMS-Key-ID --key-spec AES_256 --region us-west-2

The following is example output from the preceding command:

{
"Plaintext": "Gve+VE1JV8aufhDkPKeWceEt+vZaTYZCH25/OLPo+a4=", 
"KeyId": "arn:aws:kms:us-west-2:289799745972:key/eee4d613-4871-4cee-87d8-a54ad8d5251b", 
"CiphertextBlob": "AQIDAHiojOaIcTtQGK4gqEf6QcYuVUutMiW0y52tG63GqOknSgF0ZtnfmfqadzhfWUlRSx15AAAAfjB8BgkqhkiG9w0BBwagbzBtAgEAMGgGCSqGSIb3DQEHATAeBglghkgBZQMEAS4wEQQM/U3+BkE/yN5Jf0ckAgEQgDtoG9coTwG7bnPGx3c4hND8LAc4LEPnxd2BLQdJDOjqXU0LgudNfVUw4xNfWy2XCDgmSYV0nHU9EVbwkQ=="
}

3.    Start a backup of the certificate and private key in the source instance (on-premises or EC2 instance):

use master 
BACKUP CERTIFICATE TDE_Cert TO
FILE= 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\certificatename.cer'
WITH PRIVATE KEY (
FILE= 'C:\Program Files\Microsoft SQL Server\MSSQL15.SQL2019\MSSQL\DATA\privatekey.pvk',
ENCRYPTION BY PASSWORD='Gve+VE1JV8aufhDkPKeWceEt+vZaTYZCH25/OLPo+a4=')

4.    Upload the backup certificate file to the S3 bucket tde-bkt.

5.    Upload the private key file to the S3 bucket tde-bkt. After uploading, edit the metadata of the private key file, and then specify the following values:

• Key: x-amz-meta-rds-tde-pwd
• Value: CipertexBlob obtained in step 2.

6.    Restore the certificate in the RDS instance:

exec
msdb.dbo.rds_restore_tde_certificate
@certificate_name='UserTDECertificate_ec2_tde_cert',
@certificate_file_s3_arn='arn:aws:s3:::tde-bkt/certificatename.cer',
@private_key_file_s3_arn='arn:aws:s3:::tde-bkt/privatekey.pvk',
@kms_password_key_arn='arn:aws:kms:us-west-2:aws-account-id:key/key-id';

7.    Run the following command to restore the database:

exec msdb.dbo.rds_restore_database 
@restore_db_name='onpremdb',
@s3_arn_to_restore_from='arn:aws:s3:::tde-db/EncryptedBackupFromOnpremise.bak';

---

Related information

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

How do I restore a KMS-encrypted backup file in RDS for SQL Server from an on-premises environment?