Database Backup Encryption

Restoring encrypted database on another server (using Backup Encryption). Only works with SQL Server 2014 and later.

Backup

-- Database Backup

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Test1234';
GO

BACKUP MASTER KEY
TO FILE = 'D:\SQLServerBackups\TestEncryption\MASTER_KEY.key'
ENCRYPTION BY PASSWORD = 'Test1234';
GO

---

USE master
GO
CREATE CERTIFICATE TestDB_Backup_Certificate WITH SUBJECT = 'Test Encryption';
GO

BACKUP CERTIFICATE TestDB_Backup_Certificate
TO FILE = 'D:\SQLServerBackups\TestEncryption\CERTIFICATE.cer'
WITH PRIVATE KEY
(
    FILE = 'D:\SQLServerBackups\TestEncryption\MASTER_KEY.key'
   ,ENCRYPTION BY PASSWORD = 'Test1234'
);
GO

---

USE master
GO
BACKUP DATABASE [TestEncryptedDB] TO DISK = N'D:\SQLServerBackups\TestEncryption\TestEncryptedDB.bak'
WITH  
  FORMAT,
  COMPRESSION,  
  ENCRYPTION   
   (  
   ALGORITHM = AES_256,  
   SERVER CERTIFICATE = TestDB_Backup_Certificate 
   ),  
  STATS = 10 
GO

If a certificate is not backed up prior to backing up a database in T-SQL the following warning will be displayed in the results:

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

Restore

If restoring database to the same instance, then we do not need to re-create the certificate. We only need to re-create the certificate (using the .cer and .key files) if we are restoring the database to another instance.

-- Database Restore

-- If restore to another instance...
USE master
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Test1234';
GO

USE master
GO
CREATE CERTIFICATE TestDB_Backup_Certificate
    FROM FILE = 'D:\SQLServerBackups\TestEncryption\CERTIFICATE.cer'
     WITH PRIVATE KEY 
      ( 
        FILE = 'D:\SQLServerBackups\TestEncryption\MASTER_KEY.key' ,
        DECRYPTION BY PASSWORD = 'Test1234'
      ) 
GO

-- Drop Certificate
--DROP CERTIFICATE TestDB_Backup_Certificate

-- The restore process is as usual. You don't have to specify any extra options, but the certificate or key used to encrypt the backup must be available on the instance.
USE master
GO
RESTORE DATABASE [TestEncryptedDB]
FROM DISK = N'D:\SQLServerBackups\TestEncryption\TestEncryptedDB.bak'
WITH REPLACE
GO

If the certificate is not found on the database instance, you will get the following message… To solve this, recreate the certificate on the instance.

Msg 33111, Level 16, State 3, Line 72
Cannot find server certificate with thumbprint '0x19BE6079C37D4B833AC21CBBCC1385F4C9A394D4'.
Msg 3013, Level 16, State 1, Line 72
RESTORE DATABASE is terminating abnormally.

Queries

select name,thumbprint from sys.certificates
select name,is_master_key_encrypted_by_server from sys.databases

Note

• Database master key passwords do not need to match between instances
• When you backed up the certificate you added a private key and password, this private key is independent of the existing database master key.
• This certificate can now be created using the .cer, .key, and private key password on any other instance that has a database master key (as long as the service account has permissions as SQLPRODDBA mentions)

SQLITY post on backing up certificates

in SQL Server all keys are at all times protected by either a password or by another key in the encryption hierarchy. That extends to the backup files too. As backup files are intended to be stored of site, the SQL Server encryption hierarchy is not available to protect them. Therefore, we have to provide a password to protect the key.

SQL Server started having encrypted backups since SQL Server 2014. You can see the technical details here. This is a very good feature that allows you to encrypt the backups upon creation with different algorithms that will meet your security needs and requirements without having to use or pay for any third-party tool. Restoring the encrypted backups to its source server can be done without a hitch or change in process as with restoring normal backups. Some items to note and practices you might want to consider:

1. Make sure you created the certificate with the correct expiry date. Take note that renewing the certificate, if it expires or extending the certificate’s expiration date, changes the thumbprint of the certificate. This makes it invalid for the backups already created using it before the changes and you won’t be able to restore those backups.
2. Back up the certificate and keys and save it to a different location aside from the server where it was created. Take note that without the certificates, your backups will not be usable. It is best to keep it in an off-site location for your Disaster Recovery or Key Management providers.
3. For Availability groups, make sure your certificates are all on the node. You normally run your backups on one of the nodes but these backups are for all your AG replicas. Make sure the certificate is available on all the nodes so you are always ready to restore it on any of the nodes.

Sources:

https://dba.stackexchange.com/questions/149776/restoring-encrypted-database-on-another-server-using-backup-encryption

https://blog.pythian.com/restore-encrypted-backups/