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.
SQLmojoe has good points in the answer.
2 things that you need to consider :
The cert that you used when taking backup should be preserved as is. The cert should not be changed or renewed (even if it is expired). Renewing the cert will change the cert thumbprint and you wont be able to restore the database backup.
The user account should have VIEW DEFINITION permissions on the certificate or the asymmetric key used to encrypt during backup.
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.