Do I need to back up the SQL Server Encryption Password and Symmetric Key?

Question

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?

Answer 1

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';
GO

CREATE SYMMETRIC KEY CreditCards_Key_01
WITH 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_256
ENCRYPTION BY CERTIFICATE CreditCards;
GO

OPEN SYMMETRIC KEY CreditCards_Key_01
   DECRYPTION BY CERTIFICATE CreditCards

UPDATE MyTable
SET 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!

Answer 2

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.