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:

Create the Master Key with a strong password.

Create a Certificate.

Create a Symmetric Key with the Certificate.

Encrypt data as needed by using the EncryptByKey function like this: EncryptByKey(Key_GUID('SensitiveData_Key_01'), MySensitiveDataColumn)

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?

2 Answers
2

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.