As discussed in the last post, with TDE (Transparent Data Encryption), you generally do not need to change the Database Encryption Key (DEK) as it is separately protected by a certificate in the master database and you can rotate the certificate periodically.
Rotating TDE Certificates without re-encrypting data
As also mentioned, if you did need to change the DEK then this will be a resource intensive operation as SQL Server will need to decrypt all your existing data using the existing DEK value, then re-encrypt it with the new one.
Nonetheless, there may be scenarios when you wish to do this. There could be a security breach where you don’t feel confident unless all the keys are updated. Or you might need to support a higher level of encryption than had been previously used.
More commonly, you may publish a database from a development environment and wish to regenerate the keys so that there are unique to your production environment.
In either case, the process is simple and transparent from your point of view, you issue the ALTER DATABASE ENCRYPTION KEY command, specifying REGENERATE and the encryption algorithm you wish to use:
ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_256;
In the previous post I talked about rotating the certificate, and it’s worth noting that you can perform both these actions at the same time e.g.
ALTER DATABASE ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyTDECert_with_longevity
Where you have a lot of data in the database this could take a while to complete. You can check the status of the operation by looking in the dm_database_encryption_keys view:
SELECT k.encryption_state, k.create_date, k.regenerate_date,k.percent_complete
FROM sys.dm_database_encryption_keys k
INNER JOIN sys.databases d
ON k.database_id = d.database_id
WHERE d.name = ‘TestTDE’
Note that the encryption_state is set to 4 which means “key change in progress”.
In a brief test I compared the time taken to change the key with the time taken to encrypt a fresh un-encrypted database. Surprisingly it was about the same, I had expected it would be longer due to the need to decrypt and re-encrypt each data item.
Hi Matt,
I am using the EKM provider (HSM appliance) to enable the TDE on sql server. In their documentation they mentioned the below steps to rotate the Keys for Transparent Database Encryption after creating the new asymmetric key on master database using the EKM Provider
SCRIPT:-1
Use TDE_Database;
ALTER DATABASE ENCRYPTION KEY
REGENERATE
WITH ALGORITHM = AES_128
SCRIPT:-2
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY SQL_EKM_RSA_2048_Key_TDE_Rot;
go
My question is that do i need to run the both SCRIPT (1 and 2) to complete the key rotation on user database ?
I believe the script 1 is to regenerate the key with same asymmetric key on master database.
Please help if i need to run both script or just script # :- 2 ?
Hi Ravi, it depends on what you are trying to achieve. If you want to rotate the actual key that is used to encrypt the data then that is what the first script achieves. It does howver require SQL Server to decrypt all the data with the old key, then re-encrypt with the new which is a time consuming and resource heavy operation.
If you just want to rotate the asymmetric key that protectes the Database Encryption Key (which in most cases should be sufficient) then you can just run the second script.
Thanks Matt for your quick reply and explaining in details. Now it’s clear.