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
To verify it 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 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
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 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 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.
