Simple encrypted backups with SQL Server 2014, for lazy people

Let's say the database you want to back up is called Foobar. The intention is to encrypt the backups so that if they fall into the wrong hands then they cannot easily be read.

You will need to create two things: a secret master key, and a certificate.

Designate a "safe place" where you can keep protected copies of these things. If you lose them, you risk losing access to your database too, as the backups will be unreadable. (So, obviously, don't use the database server as your safe place, in case that server dies.)

Now create a master key on the specific database you want to protect (i.e. Foobar — not the system database called Master!). This involves specifying a password, which you should keep in the safe place. Do it like this:


Make a backup of the new master key, and keep it in the safe place. (It is not sufficient merely to remember the password you used to generate the key, because the key incorporates unique material that is randomly generated at the time of creation.) Do it like this (using the same password you used above):

BACKUP MASTER KEY TO FILE = 'c:\windows\temp\FoobarMasterKey.bak' ENCRYPTION BY PASSWORD = 'my_strong_password'

Now, on the Master database (not the Foobar database), create your encryption certificate. This will be (automatically, by default) encrypted with the master key created above. (Don't specify a password for the certificate: if you do, backups will fail because SQL Server only knows the master key, not your password. An alternative to using a certificate is using an asymmetric key, but that is seven shades of complicated, and we're trying to be lazy.) Do it like this:

USE Master; CREATE CERTIFICATE MyBackupEncryptor WITH SUBJECT = 'backup encryptor', EXPIRY_DATE = '01/01/2099'

If you don't specify an expiry date, it will default to 1 year from now, and on that day your backups will stop working. (I learned this the fun way.)

Make a backup of the new certificate, and keep it in the safe place:

BACKUP CERTIFICATE MyBackupEncryptor TO FILE = 'c:\windows\temp\MyBackupEncryptor_certificate.bak'

Now you can go and modify your backup plan (under Maintenance Plans in SQL Server Management Studio). You did have a backup plan, right? ...Oh, I'll wait.

Find the step that does the actual backup, and go to its Options tab. Tick the Backup encryption box, select the encryption algorithm (this must match the one that your certificate uses — the default seems to be AES 256), select your certificate, and save changes to the plan.

That's it! Run a backup to make sure it works. Then you can go home, open a beer, and watch the Midnight Patrol episode where their evil clones come out of a mirror. Evil Keiko is bae.