Stored Procedure to Back Up Encryption Objects

Hello world!  For anyone that may see this, my name is Clint, and I have been working in enterprise-level IT for nearly a decade – mostly on the database side of things.  My buddy, Mr. Julius, has been kind enough to let me post on this blog, so I hope I do it justice.  I plan to share some cool things here.  Enough introduction.  On with it!

This link will take you to a stored procedure that I wrote to back up encryption objects, specifically Service Master Keys, Database Master Keys, and Certificates.  I am relatively new to working with SQL Server encryption, so I’m  not sure how this proc will evolve, but for now, it backs up all the encryption objects I need to be concerned with.

Why should we back up these objects?  Well, I won’t describe how SQL Server column-level encryption works – there are plenty of sites dedicated to that, and it would take a lot of typing.  In a nutshell, it’s because of this: if you lose one, or more, of these objects for any reason, you risk losing the ability to decrypt your data (which would basically mean it’s lost forever).  Therefore, you need the entire hierarchy of your encryption objects to be intact to have viable, encrypted data.  Some examples of why these objects may need to be backed up include:

1. Service Master Keys are unique to a SQL Server Instance, so if you want the same key on a separate instance, you must manually restore to it.  These are not carried over in a DB backup/restore operation.
2. Database Master Keys are re-encrypted when you restore a DB to SQL Server instance with a different Service Master Key, causing the “downstream” objects to also be re-encrypted (i.e. unreadable).   Therefore, even thought these are carried over when you do a backup/restore, if the Service Master Key was different upon restore, you won’t get the desired results.
3. Certificates can be used to open/close keys (which, in turn, do the actual encryption/decryption).  One certificate can control many keys.  I believe, if you lose the certificate, but still have the keys, you can create a new certificate to control the keys…I am not sure about this!  I still have some testing to do, so I will update this post when I have definitive info.  Either way, to be safe, I back them up anyway.

The above list is not all-inclusive.  There may be many more reasons why you need to backup/restore the objects.

It seems crazy to me that there is not an “out of the box” way to back up these objects through the GUI, and/or schedule it, considering the MSDN page on Service Master Keys says it should be one of your first admin tasks performed on the server.  Maybe it’s a security concern to have something built-in to the product like that.  Who knows?

Some references are below in the event you want to dig a little deeper.   Hopefully this will prove helpful to someone besides me.  Later!

-Clint

References

• Microsoft:
  • Backup SMK – https://msdn.microsoft.com/en-us/library/ms190337.aspx
  • Restore SMK – https://msdn.microsoft.com/en-us/library/ms187972.aspx
  • Backup DMK- https://msdn.microsoft.com/en-us/library/ms174387.aspx
  • Restore DMK – https://msdn.microsoft.com/en-us/library/ms186336.aspx
  • Backup Certificate – https://msdn.microsoft.com/en-us/library/ms178578.aspx
  • Create Certificate (there is no Restore command) – https://msdn.microsoft.com/en-us/library/ms187798.aspx
• Non-Microsoft
  • http://www.sqlservercentral.com/blogs/steve_jones/2011/12/26/backing-up-the-service-master-key/
  • http://dba.stackexchange.com/questions/73926/why-do-i-need-to-backup-the-database-master-key
  • http://www.sqlservercentral.com/blogs/steve_jones/2012/02/27/backing-up-a-certificate/