Henry_Lee (4/13/2012)
Thanks for the reply Travis.I don't think we're talking about the same thing. It looks like you're referring to TDE. I was referring to column level encryption. I'm not familiar with TDE nor have I ever seen that error message you posted.
I use a Database Master Key to protect a Certificate which in turn protects a Symmetric Key, then that key is used to encrypt certain columns within certain tables.
I have intentionally dropped then recreated the keys / cert from the script. Additionally, I have restored the database to the same server as well as to different servers. I've tried a number of different scenarios and have yet to lose the ability to decrypt the data. In fact, you don't even need the Database Master Key and Certificate - so long as you can create the Symmetric Key using the same ALGORITHM, KEY_SOURCE and IDENTITY_VALUE.
Henry,
Sorry (you did not mention CLE in your post), but I got it, and you are correct. The main difference being is in TDE the Master Key and Certificate(s) are created in the Master database (hence the need for backup) In CLE(column-level encryption) the certificates and keys (Symmetric/Asymmetric) are created in the specific database and thus move with it.:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"