Back up Encryption

, 2018-09-11 (first published: )

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; 


USE Master; 
   WITH SUBJECT = 'This is a Test',  
   EXPIRY_DATE = '20200831'; 

To verify it created you can run the below.

 FROM sys.certificates AS C
 WHERE = '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]

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 its blank. You need to choose search.


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' 
   ALGORITHM = AES_256, 
   SERVER CERTIFICATE = [ExampleCert1] 
  STATS = 10 

Or using GUI

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

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 safe guard them. If you don’t have them you can’t restore (it’s worth repeating).  So take the extra step if you can and make your data even more secure by encrypting you backups.





Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.


1,567 reads

Networking - Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I'd like to talk about social networking. We'll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let...


1,530 reads

Speaking at Community Events - More Thoughts

Last week I posted Speaking at Community Events - Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I've got a few more thoughts on the topic this week, and I look forward to your comments.


360 reads