8:41 - Thursday, 17 April 2014

Do I Need To Back Up The SQL Server Encryption Password And Symmetric Key?

I’m trying to setup SQL Server to use Cell-Level Encryption on some sensitive data. It seems straightforward per this example on the MSDN:

  1. Create the Master Key with a strong password.
  2. Create a Certificate.
  3. Create a Symmetric Key with the Certificate.
  4. Encrypt data as needed by using the EncryptByKey function like this: EncryptByKey(Key_GUID('SensitiveData_Key_01'), MySensitiveDataColumn)
  5. Decrypt data as needed by using the DecryptByKey function like this: CONVERT(varchar, DecryptByKeyAutoCert(cert_ID('MyCertName'), NULL, MySensitiveDataColumn))

So given the above usage, let’s say my server dies. I need to reinstall SQL Server on a completely new Windows machine (or VM), and restore my database from backup. If I restore the database backup will the encryption/decryption continue to work correctly?

If not, what data do I need to save/backup in order for me to be able to recover my data in the case of a catastrophic failure?

Judging from the diagrams on this other MSDN article about the Encryption Hierarchy, my guess is that I need to backup some or all of the following:

  1. Password for the Master Key
  2. Master Key?
  3. Certificate?
  4. Symmetrical Key?

After doing some more research I found this article on “Cloning” Symmetric Keys.

You can instruct the CREATE SYMMETRIC KEY function to generate the Symmetric Key using a KEY_SOURCE and IDENTITY_VALUE. You can regenerate the same key later on by passing the same values for the KEY_SOURCE and IDENTITY_VALUE.

Here is a quick example:

CREATE CERTIFICATE CreditCards   WITH SUBJECT = 'Customer Credit Card Numbers';GOCREATE SYMMETRIC KEY CreditCards_Key_01WITH KEY_SOURCE = 'A pass phrase from which to derive the key.',    IDENTITY_VALUE = 'An identity phrase from which to generate a GUID for tagging data that is encrypted with a temporary key',    ALGORITHM = AES_256ENCRYPTION BY CERTIFICATE CreditCards;GOOPEN SYMMETRIC KEY CreditCards_Key_01   DECRYPTION BY CERTIFICATE CreditCardsUPDATE MyTableSET EncryptedCreditCardNumber = EncryptByKey(Key_GUID('CreditCards_Key_01'), CreditCardNumber);GO

To quote Michael Coles from his article

“For my tastes, it would make more sense to require IDENTITY_VALUE and KEY_SOURCE options by default.”

Not sure why they didn’t do it that way, or at least highlight that in the example they have out there on the MSDN!

You should simulate your proposed disaster and confirm you can recover on a test machine. Whatever you need should become apparent pretty quickly during the restore process.