We're going to be implementing encrypted backups in SQL 2014 soon and I'd like to know the gotchas or lessons encountered by anyone else who has done the same. I've read up at MSDN and elsewhere, created my master key and cert for backups in master db (and backed them up!). We do prod-to-test refreshes occasionally so I'll create the prod cert there from the backup file. None of our databases are TDE.

What do you recommend maintaining the cert & key backup files and keeping them secure? What kind of problems have you had long term with administering the backups or restoring them during DR?

Just looking for tips to avoid the arrows that inevitably come with something new & different. Paranoia level is high where recoverability is concerned.


Assuming you have the process down tight (as in scripted, tested, re-tested, and tested with the newest and most junior persons on the team) the only other area that I seen people slip on is accessibility of the key. Most people get that they should not store it in the same place as the backup files or even transport them together.

What is sometimes missed is whether the keys/certs are as well protected and accessible in a disaster. I've encountered users asking for some built-in backdoor from Microsoft (doesn't exist) or seek help to hack their own DBs because the key was lost along with a major incident at their primary data center (key/cert backups were in a secure administrative server that was not replicated to the DR site).

As for keeping them secure and fresh, the former is no different from storing other valuable data. Place in a secure host, highly restrict access (JIT if possible), all access logged in a secure tamper evident location (non-repudiation). Rotation process and mechanism must be automated to include the backup and DR sites. Everything validated by a different party from the one that executed on the tasks. Really no rocket science here. Things fail when humans think the tasks are trivial and they can do it in their sleep. Automation that has logs, monitors and alerts built-in will significantly reduce your risks.

  • Oh yeah, it's gonna get tested extensively. Current plan is for the key & cert backups to live forever on a secure disk on a different server and replicate to our DR site. I'll probably also create a scheduled backup of keys & certs to ensure at least a latest copy in case a key is regenerated without my knowledge/involvement. – machine2473 Jun 21 '16 at 20:38

SQLmojoe has good points in the answer.

2 things that you need to consider :

Takeaway is make sure you test your restores with all possible test cases and see how you achieve your company's RTO and RPO. Make sure you script out the restores, so it becomes just a matter of plugging-in some variables to restore encrypted database backups and get up quickly.

  • Sounds good, thanks. I'll be creating certs with expiry of 12/31/2999 so hopefully that'll keep the need for renewal at bay for awhile. The plan is to keep all key & cert backup files forever. – machine2473 Jun 21 '16 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.