Backup Encryption In SQL Server

Nowadays security breaches happen way too frequently. As DBA 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 further 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, 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 your 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 about what date you set 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

To verify it's created you can run the below.

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

After that is done you’re ready to create your encrypted backup. Before you do you must however make sure you have the VIEW DEFINITION permission for the Certificate.

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

Using GUI

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 it's blank. You need to choose search.

Choose CERTIFICATES

Check the Certificate you are granted 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

Or using GUI

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

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 in 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

It’s a pretty simple process to ensure your backups are encrypted. The tricky part is to make sure you back up the Master Key and Certificate and safeguard them. If you don’t have them you can't restore them (it's worth repeating). So take the extra step if you can and make your data even more secure by encrypting your backups.