Periodically change the symmetric key and certificate for a encrypted database in SQL Server?

4
votes

How to periodically change the symmetric key and certificate for a encrypted database in SQL Server? Encryption logic is Database Master Key -> Certificate -> Symmetric Key -> Decrypted Data. I would like to periodically change certificates / symmetric keys to ensure the symmetric keys / certificate is safe.

1
sql-serverencryptionmaintenance
what would you do with the data already encrypted with older symmetric key / certificate? – ughai
i dont want any changes /touch the encrypted data is it possible to change symmetric keys / certificate without touching the encrypted data? I just want to have a periodic maintenace to change the keys and certificates as a security best practise. – Srimant
once you change your existing keys, then older data will be encrypted with a different key and newer data with a different key. Now when running SELECT you would have issues when decrypting the data (unless you decrypt all data and then re-encrypt it using the new key). Generally these keys do not leave your database which are housed in secure location. – ughai
So the only possibility to change keys /certificate is to decrypt the entire set of data ,regenerate the keys/certificate and reencrypt.Pretty tedious process but looks like there is no alternative.That helps Microsoft should explicitly state that in key maintenance/recreate key process. – Srimant
you can change the certificate using the process mentioned in this thread blogs.msdn.com/b/lcris/archive/2006/03/13/550904.aspx – ughai

1 Answers

1
votes

If you're using TDE:

ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER ASYMMETRIC KEY your_key_encryption_key

will do the trick.

If your data is encrypted at the column level, you'll have to cycle through every row and update each with ENCRYPTBYKEY(key_guid('new_key'), DECRYPTBYKEY(data)).