Dan Sellers's WebLog

Regenerating Keys in SQL Server 2005

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:

1. Create a temp key
2. Open the old and the temp key
3. Decrypt (old key) the data and replace it using the temp key
4. Close the old key
5. Create a new key with the same name
6. Open the new key
7. Decrypt (temp key) the data and replace it using the new key
8. Close both keys
9. 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.