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.
Best Answer
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.