2

We use SQL Server 2012 Enterprise Edition and Transparent Data Encryption (TDE). Microsoft have a command to let you change the encryption on the database "ALTER DATABASE ENCRYPTION KEY". The docs page helpfully gives you the command, but it doesn't detail the process. I posted this question a the bottom of the page, but I thought that I might get a quicker answer here.

When I run this command, is the data left unencrypted on the disk at any point? i.e. Does this command first unencrypt all the data using the old key/certificate and then re-encrypt it using the new one, which would mean that the data is unencrypted at some point in the process. OR, is the process a pipe that takes in the data encrypted in the old key and spits out the data encrypted with the new key and the data is never stored on the disk unencrypted at any point.

Bonus question, what happens to the log file during the key change?

MS docs https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-encryption-key-transact-sql

2

Generally, for best results on Stack, you want to break each question up into its own question, but here we go:

Q: When I run this command, is the data left unencrypted on the disk at any point?

Not by the key change process, but be aware that filestream and replication data aren't encrypted regardless. For more details, check Books Online's section on TDE.

Q: i.e. Does this command first unencrypt all the data using the old key/certificate and then re-encrypt it using the new one, which would mean that the data is unencrypted at some point in the process.

No. For more details, see Microsoft's post on encryption key management.

Q: Bonus question, what happens to the log file during the key change?

Changes to the database are logged operations, so you'll need to watch the size and speed of your transaction log just like you did when you first applied TDE to the database.

  • Awesome, thanks Brent. Understood about replication, filestream etc. The question arose during a security audit around key management and rotation and I didn't have an answer. I pinned the log question to the encryption question as I wanted to know how the log file would be encrypted when you change the key. Reading the blog post that you link to, encryption is done on a page by page basis with the old key retained to decrypt those pages still encrypted by the old key. So both log and data files would use the new key going forwards. – Gavin Harris Apr 19 '17 at 12:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.