Simple Example of SQL Server Backup Encryption

The estimated reading time for this post is 4 minutes

Encrypting the database backups help to secure the data. Today, as we get closer and closer to cloud solutions including backup files, every day, backup encryption becomes more and more necessary. SQL Server 2014 introduces the new feature to encrypt backups natively. Today we are here with another simple solution to demonstrate the full path of encrypting a backup on our source server and restoring it on the target server.

Create an Encrypted Backup

We start with our source server. To create an encrypted backup, there are few prerequisites that you need to set up before creating an encrypted backup.

Create a Database Master Key for the master database

In first step we need database master key or DMK on master database. To check whether the DMK exists or not we can use the following query:-

USE [master];
GO
SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101;

If the DMK does not exists, then, the following script creates a DMK for the master database:-

USE [master];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'SourceServerPassword'
GO

In a production environment, you need to set the strong password to secure the database master key.

Create a Certificate or an Asymmetric Key

When you want to take an encrypted backup, you need to specify an encryptor which can be a certificate or an asymmetric key. In this example, I am going to create a certificate. This code creates a certificate on the master database:

USE [master]
GO
CREATE CERTIFICATE [BackupEncryptCert]
   WITH SUBJECT = 'backup encryption certificate',  
   EXPIRY_DATE = '20220312'; 
GO 

If you do not specify the EXPIRY_DATE, it set to a date one year after creating the certificate or START_DATE (if specified). Backup encryption using a certificate checks the expiration date and will not allow to create a new encrypted backup with an expired certificate. However, you still are able to restore a backup by using expired certificate.

To encrypt a backup by using an asymmetric key, only asymmetric key residing in the Extensible Key Management (EKM) provider are supported.

Backup Certificate

After creating the certificate, it is too important to export the certificate and keep the file in a safe location. without certificate you will not be able to restore the encrypted backups using the certificate on other instances and in some circumstances even in the same instance. It is advisable to back up the certificate to a different location than the backup files location.

BACKUP CERTIFICATE [BackupEncryptCert]
   TO FILE = 'F:\keys\BackupEncryptCert.cer'
   WITH PRIVATE KEY(
      FILE='F:\keys\BackupEncryptCert.ppk',
      ENCRYPTION BY PASSWORD ='PrivateKeyPassword'
   )

use the strong password to encrypt certificate’s private key and save it secure as it will be required during restoring the certificate.

Create an Encrypted Database Backup

Now, you are able to take an encrypted backup. To encrypt a backup, you need to specify the certificate or asymmetric key as the encryptor, and an algorithm that can be one of the followings: AES_128, AES_192, AES_256, TRIPLE_DES_3KEY. Below example, we have used the BackupEncryptCert certificate as the encyptor and AES_256 for the algorithm to encrypt the MYTestDB database backup.

BACKUP DATABASE [MYTestDB] 
TO DISK = N'D:\Backups\MyTestDB_Encrypted.bak' 
WITH 
  COMPRESSION, 
  ENCRYPTION  
   ( 
   ALGORITHM = AES_256, 
   SERVER CERTIFICATE = BackupEncryptCert 
   ), 
  STATS = 10 
GO 

Note that appending an existing backup set is not supported for encrypted backups. So, you need to take each encrypted backup to a new backup set.

Restore an Encrypted Backup

Now, we move to our target server. To restore an encrypted backup, you need to have a copy of backup file, certificate file, and its private key file on the target server. You also need to set up encryption prerequisites before restoring an encrypted backup.

Create a Database Master Key for the master database

Same as what we have done for the source server in the first step, we need DMK on master database on target server. To check whether the DMK exists or not we can use this query:-

USE [master];
GO
SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101;

If the DMK does not exists, then, this script creates a DMK for the master database:-

USE [master];
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'TargetServerPassword'
GO

Restore the Participant Certificate or Asymmetric Key

When you want to create a certificate from existing file, ensure that you have copied all files which are certificate file and its private key file into the target server. And then, make sure the SQL Server startup account has permission to access to the files. We already copied the files from the source server to the target server. This script creates the certificate from the file:-

USE [master]
GO
CREATE CERTIFICATE [BackupEncryptCert]
   FROM FILE = 'D:\keys\BackupEncryptCert.cer'
   WITH PRIVATE KEY(
      FILE ='D:\keys\BackupEncryptCert.ppk',
      DECRYPTION BY PASSWORD='PrivateKeyPassword'
   )

the decryption password should be same as encryption password during backup certificate. Otherwise, you are not able to restore the certificate.

Restore the Encrypted Backup

Finally, it is time to restore the encrypted backup. After you restored the intended certificate, restore encrypted backup is as simple as restore typical backups. This script restore the backup to the target instance:-

RESTORE DATABASE [MYTestDB] 
FROM DISK = N'D:\Backups\MyTestDB_Encrypted.bak'
WITH 
       MOVE 'test' TO 'D:\MSSQL\DATA\test.mdf',
       MOVE 'testlog' TO 'D:\MSSQL\DATA\testlog.ldf',
       STATS = 10 
GO

Hamide B. Abshuri

Hamide B. Abshuri is SQL Server Database Developer and Administrator with extensive of working experience. She started work with SQL Server since year 2015. She is currently Microsoft Certified Professional and Microsoft Certified Solutions Associate: SQL Server 2016 Database Developer.

More Posts - Website


Leave a Comment

avatar
1000
  Subscribe  
Notify of