How do I restore an encrypted backup file or encrypted Microsoft Azure backup in RDS for SQL Server from an on-premises environment?
I want to restore an encrypted backup file or encrypted Microsoft Azure backup between an on-premises environment and Amazon Relational Database Service (Amazon RDS) for Microsoft SQL Server instance. How can I do this using the native backup and restore feature?
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?
Relevant content
- asked 2 years agolg...
- asked a year agolg...
- asked 8 months agolg...
- AWS OFFICIALUpdated 8 months ago
- AWS OFFICIALUpdated 5 months ago
- AWS OFFICIALUpdated 2 years ago