Have you ever wondered how to change the keys for the encryption for an already encrypted database. For this you can use “Key Rotation”. Key rotation is the process of decrypting data with the old encryption key and encrypting the data with the new encryption key.
But how to use the Key Rotation with SQL Server 2005??
Below are specified a few details as to how you can go ahead and use the Key Rotation to change the key with which the data has been encrypted.
You can add an encryption by a temporary new certificate.
Drop the old encryption and the old certificate.
Create a new certificate with the old name, add an encryption by it.
Finally drop the encryption made with the temporary certificate and drop the temporary certificate as well.
And with this you have the data encrypted with a new key and the old key has been dropped.
You can refer to this blog by Laurentiu Cristofor of the Microsoft SQL Server Security team for more details and the alternatives as well.
SE, Microsoft SQL Server