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. Specify the encryption algorithm and the encryptor for the backup. This creates an encrypted backup file.

Note: This feature isn't the same as Transparent Data Encryption (TDE) encryption. For more information, see Support for Transparent Data Encryption in SQL Server.

Resolution

To restore a local or Microsoft Azure encrypted backup to Amazon RDS, you must have a backup of the server certificate. Without the certificate, you can't restore the backup to Amazon RDS. For more information, see Backup encryption on the Microsoft website.

Restore local encrypted backups

Before you begin to restore an encrypted backup, make sure that you've created the following resources:

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

1. Use the following script in your on-premises account to create a primary database key:

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

   For more information, see CREATE MASTER KEY (Transact-SQL) on the Microsoft website.

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

   CREATE CERTIFICATE TDE_CertWITH 
   SUBJECT='Database_Encryption';
   GO

   For more information, see CREATE CERTIFICATE (Transact-SQL) on the Microsoft website.

3. Use the following script to start a native backup:

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

   Note: It's a best practice to turn on compression for Amazon RDS restores. For more information, see Backup compression (SQL Server) on the Microsoft website.

4. Use the AWS Management Console to upload the encrypted backup to the S3 bucket tde-db.

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';

Restore Microsoft Azure encrypted backups

Before you begin to restore an encrypted backup to a Microsoft container, make sure that you've created the following resources:

• An 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 an Azure storage account on the Microsoft website.

2. Create a credential based on the shared access signature. For more information, see Create SAS tokens for your storage containers on the Microsoft 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 backup restores directly from Microsoft Azure storage containers. You must download the backup to an intermediary system.

4. Use the AWS Management Console to upload the encrypted backup to the S3 bucket tde-db.

5. 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'

Note: If you received errors, then see What are common issues that might occur when using native backup and restore in RDS for SQL Server?

Related information

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

How do I restore a backup file encrypted with AWS Key Management Service (AWS KMS) in RDS for SQL Server from an on-premises environment?