Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?
share|improve this question

2 Answers 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.

share|improve this answer

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!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.