Back up Encryption for SQL Server

Nowadays security breaches happen way to frequently. As DBA’s we should all take extra care with sensitive data and ensure we are encrypting correctly. But some of us forget to take it a step farther and don’t encrypt our backups. For those working in an ultra data sensitive environment I highly recommend you go this extra step.

As of SQL 2014, SQL Server now has the ability to encrypt your data during the backup process. Not only that but they have made the process simple. Prior to 2014, the only way to encrypt backups was Transparent Data Encryption (TDE), and that requires Enterprise Edition which made this out of reach for most.

First steps you must create a MASTER KEY and a Certificate ( or asymmetric key). You will notice that the Certificate has an expiration date. If this expires don’t fret, you will still be able to restore you backup however you will not be able to perform new backups until you create a new certificate. Depending on your environment you may have to have a routine process of managing your certificates and renewals. If you don’t, take care in what you set this date to to avoid this.

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

USE Master; 
GO
CREATE CERTIFICATE ExampleCert1  
   WITH SUBJECT = 'This is a Test',  
   EXPIRY_DATE = '20200831'; 
GO

SELECT C.name,C.certificate_id,C.pvt_key_encryption_type_desc,C.subject,C.start_date,C.expiry_date
 FROM sys.certificates AS C
 WHERE C.name = 'ExampleCert1';

use [Master]
GO
GRANT VIEW DEFINITION ON CERTIFICATE::[ExampleCert1] TO JoeShmo
GO

Under the MASTER Database go to Security then User.

Choose the User you want to grant permission to then go to properties.

Click on Securables, you will note its blank. You need to choose search.

Choose CERTIFICATES

Check the Certificate you are grant rights to and check GRANT for VIEW DEFINITION

Now try backing up your database. Note the Encryption option and the server certificate name.

BACKUP DATABASE [AdventureWorks2014] 
TO DISK = N'C:\temp\AWEncrypt.bak' 
WITH 
  COMPRESSION, 
  ENCRYPTION  
   ( 
   ALGORITHM = AES_256, 
   SERVER CERTIFICATE = [ExampleCert1] 
   ), 
  STATS = 10 
GO

If you don’t have the correct permission, you will see this error when you try to perform the back up.

Now that you have a backup we need to see if we can restore it. But first this is important, so I am going to quote Microsoft directly here from MSDN, without the key and certificate YOU CANNOT RESTORE!

“Create a backup of the encryption certificate and keys to a location other than your local machine where the instance is installed. To account for disaster recovery scenarios, consider storing a backup of the certificate or key to an off-site location. You cannot restore an encrypted backup without the certificate used to encrypt the backup.”

Restoring is just a normal restore as long as the key and certificate are present on that server.

USE [master]

RESTORE DATABASE [AdventureWorks2014]
FROM DISK = N'C:\temp\AWEncrypt.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 5
GO