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?
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:
- Password for the Master Key
- Master Key?
- Certificate?
- Symmetrical Key?
Best 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 aKEY_SOURCE
andIDENTITY_VALUE
. You can regenerate the same key later on by passing the same values for theKEY_SOURCE
andIDENTITY_VALUE
.Here is a quick example:
To quote Michael Coles from his article
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!