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
EncryptByKeyfunction like this:
- Decrypt data as needed by using the
DecryptByKeyfunction 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?
- Symmetrical Key?