If I encrypt data using ENCRYPTBYCERT in sql server 2005/08, what happens if the certificate used to encrypt it with is dropped from the database?
The data is still encrypted, but the certificate is no longer there so can't be decrypted. What do I do? How do I get the original plaintext back? Recreating the certificate with the same password etc doesn't work.
If it's not possible and the data is lost, how do I prevent this from happening.
Many thanks.
Certificates are used to encrypt symmetric keys, not data. see Encryption Hierarchy. Attempting to drop the certificate while there are still symmetric keys encrypted with the certificate is blocked.
Good practices guidelines require that safe backup or escrow copies are kept for any cryptographic key generated, symmetric or asymmetric. This is part of the normal operations of any shop that cares to restore the data in case of a accidental operational key loss.