TDE with AKV

Question

We want to introduce TDE on our IaaS infrastructure (VM machine with SQL Server 2019 Standard Edition).
We studied the implementation procedure using SQL Master Key plus certificate and it’s clear and now we go ahead with Azure Key Vault in order to use on-cloud Key to manage the TDE.
We are able to create the key, to create credential, to create an account and finally to encrypt the DB. It works like a charm.

Now we are investigating how to rotate that key in order to write down some security protocol to mantain security.
In AKV I found the function “+ New Version” of the key.

It works great… It generates a new version and move the other key to Older version like the picture below.
The new version key is ad95… the last was 1c98, it is still enable in order to consent me to re-encrypt the DEK in the next step…

After I do this, if I upgrade the encryption of my DEK in SQL Server DB It works.

USE <DBNAME>;
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER ASYMMETRIC KEY EKMSQLASYKey;

This T-SQL works. Now I can disable the older key (1c98 ones).

Ok. Now I’ll try to describe my issue.
Let suppose that, before generate a new version of the key, I hade made a backup of the DB using the key “1c98…”.
After I create a New Version (ad95..) I cannot recover the backup anymore. I don’t know how to tell SQL server the key to use.
I’ve tried to leave only the old key enabled but It does not work.
My question: I want to know if I can use this procedure to rotate the key and if there is some method to use the older version to restore bk.

Actually, I have to generate e new key, create a new credential and a new account in SQL Server and re-encrypt the dek leaving untouched the old credential/account in order to be able to restore the old backup made with the older key.

Regards,
Graziano.

Answer 1

@Graziano Tartari ,

Here's what happens, by default, when you create your master key, you specify the name of the key under "WITH_PROVIDER_KEY_NAME = 'ContosoKey'". As you can see, that's not specifying a key version which basically means "always call the latest available". The issue with that is that if at the moment you created the master key, the latest version was v1, the backup will be done with ContosoKey v1, and when you create a v2, then v2 becomes the latest available version and that won't let you restore the backup, Ideally, you should be able to point the restore operation to a specific key's version, however, that's doesn't seem to be possible and backing up the key to later on restore it in AKV won't work if you have purge protection turned on in your Key Vault.

I would say that a good workaround would be to create a master key-specific version per DB, I mean, you can create the master key like this:

CREATE ASYMMETRIC KEY [EKM_CMK_799ac65f4c2f4f29bb1adf10b8f627d9]
FROM PROVIDER [AzureKeyVault_EKM]
WITH PROVIDER_KEY_NAME = 'ContosoKey/799ac65f4c2f4f29bb6adf10b8f625d8',
CREATION_DISPOSITION = OPEN_EXISTING;

Where '/799ac65f4c2f4f29bb6adf10b8f625d8' represents the specific version of the key that will be used. With this, it doesn't matter if you update the Key with a new version, it will always attempt to use 799ac65f4c2f4f29bb6adf10b8f625d8 version for DB operations such as recovering a backup or at least, that's my theory (yet to be tested).

Answer 2

An upgrade.

A question solved about restoring the encrypted backup on another DBMS.
I have tryed to manage a disaster recovery supposing to loss my SQL server but having my DB Backup and AKV key.
I have deployed a new instance of SQL Server 2019 and connected to AKV.

I've tried to follow MS guide here https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/use-sql-server-connector-with-sql-encryption-features?view=sql-server-ver15 but I've found many mistakes...
In this guide it seems that the mapping of the vault asymmetric key was not important. However is the central topic. It talks about thumbrint of the vault key but this thumbprint is not important and not visible. The focus is on the thumbprint of the mapping key in SQL server. This thumbprint must be the same as the one the source SQL Server created. Both asymmetric key in source and destination must have the same thumbprint in order to restore the DB and you can fire the target putting the DBMS in single user mode and change this thumbprint in order to fit the backup thumbprint.

In the guide I've read also that I need a different vault key in order to backup the DB because I cannot use the same key used by TDE. This is not true. I can use the same key. A key is a stupid things... It cannot know if I have used it to encrypt or to backup. It does not makes sense...

I have mapped the asymmetric key from the AKV key (the same key of the TDE and backup).
I have recreated a credential and a login.

At this point SQL Server restore returned me a Thumbprint problem of the asykey.

Ok. I have changed the thumbprint of the asykey in order to fit the tb of the original key. I have done it with success.

Then, I have tried to restore the DB and it works like a charm. I have got my encrypted DB on a new SQL Server instance.

PS: now it remains to solve the first point of the question, using new version feature to restore backup. If it is possible...

Regards,
Graziano.