In my latest Webcast on SQL Server 2005 Security one of the questions that came up was:
“If some fields of your table are encrypted and you are suspicious that the key has been revealed can you re-encrypt all the fields with the regenerated key”?
Currently, there is no easy way to manage a key lifetime due to the complexity of managing the binding of the keys with the data they are protecting.
Thus, the application writer needs to keep track of everything that is encrypted with any given key, and in case it is necessary to regenerate a key, here are a series of steps that will probably help on SQL Server 2005:
Create a temp key
Open the old and the temp key
Decrypt (old key) the data and replace it using the temp key
Close the old key
Create a new key with the same name
Open the new key
Decrypt (temp key) the data and replace it using the new key
Close both keys
Drop the temp key
Unfortunately, this can be potentially error-prone (i.e. potential data loss if any column was omitted or failed to be decrypted on steps 3 or 7).
We are currently looking at developing a tool that will gather the binding information and make this more of transparent solution.