I talked previously about why we have each of the layers in the encryption hierarchy used to support TDE (Transparent Data Encryption).
Understanding Keys and Certificates with Transparent Data Encryption (TDE)
At the time I mused about why we need the encryption key in the database itself, rather than just encrypting with the keys associated with the certificate stored in the master database. I mentioned one reason – that these keys are intended for asymmetric encryption which has a higher overhead (the DEK is a symmetric key).
In a light-bulb moment, the more important reason has come to me since, and that is about key rotation.
In any form of encryption, it is possible that at some point an individual could gain access to the keys. For example, administrators of your SQL Server instance can freely make a backup of the certificate with a password they choose which can be used to encrypt it. Whilst that in itself is not a problem, it is good practice to periodically refresh those keys so that if someone gained access to an old copy it would be of no use to them. Or if you are aware that there has been a potential breach of security, you can quickly replace your keys.
If you have any form of security certification in your organisation, then it is likely it will be stipulated that you must change these in a defined timescale- e.g. annually.
However, this forms a problem if you want to change the key that is used to actually encrypt the data, to change that you would need to decrypt all the data using the old key and re-encrypt it using the old one. That would be an intensive activity, but also could lead to vulnerability while the process was underway.
As a result, many forms of encryption use separation between the actual key that is used for the encryption, and another object that is used to protect that key. You can back up the latter separately outside the database to ensure you can recover your data in case of a failure.
With TDE we have the database encryption key (DEK) – the key that is actually used to encrypt/decrypt the data, stored encrypted in the database itself. In the normal run of things, we never access this key directly and never store it anywhere else, so it should be fairly safe, i.e. we should rarely have to worry about replacing it – though the functionality exists to allow us to do so if we require.
Then we have the certificate which is used to encrypt that key – without the certificate the key is useless and TDE can’t work. The certificate we keep a password protected backup of, so is a bit more vulnerable/public.
Due to that, certificates have an expiry date. This date is a reminder to us that, as a good practice, we should create a new certificate and use that going forward before the existing one expires. TDE doesn’t stop working if the certificate expires, it is up to you to monitor your certificates and replace them when they come to the end of their life.
If you think about it, we might not like it if TDE did enforce this, it would be a big impact if we suddenly could no longer read or write to our database. Of course, MS could probably implement functionality to lock us out, but still allow us to use the certificate for rotating the key, but still it would probably upset a lot of people and be less than “transparent”.
If we look at the sys.certificates view we can find our TDE certificate and examine the expiry date:
SELECT name, subject, expiry_date
FROM sys.certificates
WHERE name = ‘MyTDECert’;
I didn’t specify an expiry date for this certificate when I created it, so it was automatically given one that was a year in the future.
Let’s create a new certificate, and this time we’ll specify a longer expiry. Then we rotate the encryption to use that one.
CREATE CERTIFICATE MyTDECert_with_longevity
WITH SUBJECT = ‘Certificate used for TDE in the TestTDE database for years to come’,
EXPIRY_DATE = ‘20201231’;
And let’s have a look at the values that has in the store:
What do we mean by rotation?
Rotation is the process of moving from the old certificate to the new one. In this case all that happens is that the encrypted value of the database encryption key (stored in our TDE protected database) is decrypted with the old certificate, re-encrypted with the new certificate, and that new encrypted value is stored in the database, overwriting the old one.
The Key value itself hasn’t changed, just the object protecting it, and as such we can still read/write data from the database without any additional work.
To rotate the certificate used is as simple as:
USE TestTDE;
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE MyTDECert_with_longevity;
And you’re done. The operation is almost instantaneous, shouldn’t require any database down time, or create additional overhead on your server. Though in production I’d still do it when things are quiet just in case anything goes wrong!
Hi
This needs to be clarified a little further
[quote]What do we mean by rotation?
Rotation is the process of moving from the old certificate to the new one. In this case all that happens is that the encrypted value of the database encryption key (stored in our TDE protected database) is decrypted with the old certificate, re-encrypted with the new certificate, and that new encrypted value is stored in the database, overwriting the old one.
The Key value itself hasn’t changed, just the object protecting it, and as such we can still read/write data from the database without any additional work.[/quote]
The operation appears instantaneous much as it does when first applying TDE.
When rotating, A background process will consume all database pages and re encrypt them with the new DEK.
The old and new are stored in the database boot page
Hi Perry,
Thanks for the input.
My point is that when just altering the key to use a different certificate, the underlying unencrypted value of the DEK doesn’t change, so the database pages don’t need to be re-encrypted – it’s similar to key rotation with Always Encrypted in that manner (which is why I used the term rotation). I tested this when working on the post, and have double checked after seeing your comment. The database remains at encrypted state (in sys.dm_database_encryption_keys) and monitoring in perfmon I can see that there is no background processor or disk activity.
This contrasts with where you use the REGENERATE command to create a new DEK – in which case you can see that the database goes into the state “Key Change in Progress” and you can see processor and disk being consumed until the operation (i.e. the re-encryption of the pages with the new key) is complete.
Hi Matthew. Thank you for the write up it has been helpful for us. I am thinking about a case with backups and would like to hear your take on it.
From my understanding the DEK is stored encrypted with a database backup. When the keys are rotated the key used to encrypt the DEK on an older backup may not be current. How is this backup restored?
It seems old keys are kept in the sys.certificate table and may be used for those older backups, but what if you are restoring from a new server or simply the old DEK encryption certificate is not available? Is that backup toast or are we able to say, restore using `this` DEK instead? Thinking we can use the current database DEK which we can decrypt correctly (with the assumption it is the same as the one used to encrypt the data in the backup)
Thank you!
Brett
Hi Brett.
Thanks for the comment, you raise a good point to consider. When restoring a backup of a TDE protected database, you must have the certificate in place that was valid at the point the backup was taken. The restore process explicitly checks this so you get an error if that’s not the case.
This is because the DEK in the backup has been encrypted with that certificate. The thumbprint of the certificate used is also stored in the backup so SQL can make sure it uses the right one to attempt to decrypt the key (or give you the error if it doesn’t exist).
Sorry for the slow reply – have been on vacation.
Regards,
Matthew.
Hi Matthew
This article was helpful for my. Now I have a new question, and it is related to Always On Availability groups. I have a database on Availability Groups, and this database is already encrypted. For enable TDE on that database for the first time, it was required to restore the Certificate using the Private Key from a backup on all the AG nodes, and then, enable the TDE feature.
Do you have any advice for the key rotation on this circunstancies?
That’s an interesting question, I don’t know the answer off the top off my head, but if I get a chance I’ll look into it and send you a response.
You will have to install the new cert on all nodes before rotating. If not the replicas will not synchronize after you rotate on primary
Hi Matthew
Regarding TDE tou have stated this wrt to rotating DEK:
we should rarely have to worry about replacing it – though the functionality exists to allow us to do so if we require.
Can you please share how?
https://matthewmcgiffen.com/2018/04/03/tde-regenerating-the-database-encryption-key/
Hi Matthew,
Any suggestion to replace the expired cert of more than one databases at one time?
Well, you just have to do it for each one. You can automate scripting it out if you know how to query the system objects.