There is one migration activity going on in which we are migrating from one provider to a different provider with different GUID. Now we need to restore the live data whose key is associated with one GUID. Is there any way by which GUID of the key can be updated?

Is the guid you are talking about master.sys.asymmetric_keys.cryptographic_provider_guid? The only way to change that would be to change the system catalog directly, but I am not going to say how to do that, because it would render you installation unsupported. And most likely it would not work. You need to rotate your TDE key. If you don't want to decrypt it before migrating to the new server, you will need to have both providers in installed for this operation. With the same caveat as before: I have never worked with EKM. What I'm saying is based on common sense and reading between the lines in the syntax.

This is a little confusing, but let me put a few thoughts here, and ask a couple questions. For TDE, we do not get to separate the DEK from the database. This is in the database, and has no backup/restore. This cannot be used for other purposes. There is a certificate, which can be in master (sys.certificates) or in an EKM provider. This key is used to encrypt/decrypt the DEK key. If you want to change this certificate to a new one, you perform key rotation. an example here: https://learn.microsoft.com/en-us/azure/azure-sql/database/transparent-data-encryption-byok-key-rotation?tabs=azure-powershell This allow you to decrypt the DEK key from EKM A, and then re-encrypt it with EKM B. For column level encryption, do you have the key as a certificate in EKM provider A? If so, you would need to decrypt the data or keys in use and re-encrypt them with EKM B in the same manner.

SQLEKM| Can GUID associated with key be updated with new GUID

Accepted answer

Erland Sommarskog 107.2K Reputation points

2021-05-28T21:19:26.217+00:00

Is the guid you are talking about master.sys.asymmetric_keys.cryptographic_provider_guid?

The only way to change that would be to change the system catalog directly, but I am not going to say how to do that, because it would render you installation unsupported. And most likely it would not work.

You need to rotate your TDE key. If you don't want to decrypt it before migrating to the new server, you will need to have both providers in installed for this operation.

With the same caveat as before: I have never worked with EKM. What I'm saying is based on common sense and reading between the lines in the syntax.
Please sign in to rate this answer.
BHARDWAJ Bhuvendra 21 Reputation points

2021-06-07T04:42:07.667+00:00

It worked for TDE by rotating the keys form provider A to provider B.
But how we should migrate for column level encryption which is using symmetric as well as asymmetric keys for encryption/decryption.

Erland Sommarskog 107.2K Reputation points

2021-06-07T21:18:04.173+00:00

For a symmetric key, you would need to use ALTER SYMMETRIC KEY which has options to drop a key and add a new one.

As I understand the syntax, an asymmetric key can only be protected by a password, so that does not seem to be a problem.

BHARDWAJ Bhuvendra 21 Reputation points

2021-06-08T09:28:18.163+00:00

We uses ENCRYPTBYKEY and ENCRYPTBYASYMKEY for symmetric and asymmetric keys encryption.

Is this the same Alter commands works for this too??

Also if this is possible then what is the state of data? Is it in decrypted state or in encrypted state while we are rotating the keys.

Erland Sommarskog 107.2K Reputation points

2021-06-08T21:05:47.573+00:00

For a symmetric key, you change the encryption of the key, but the key itself does not change. Thus, data is not affected.

For ENCRYPTBYASYMKEY do you use a key that comes from the EKM provider? In that case, I think you need to decrypt the data with the old key and encrypt it with the new key.

BHARDWAJ Bhuvendra 21 Reputation points

2021-06-09T11:10:38.35+00:00

Yes we were using keys from EKM provider. So there is no ways by which we can rotate the Symmetric/Asymmetric keys for Column level Encryption without decrypting the data?

Erland Sommarskog 107.2K Reputation points

2021-06-09T21:05:15.543+00:00

If you have something encrypted with key A and and want to have it encrypted with key B instead, how would you be able to that without decrypting the data with key A first?

But note that you can perform the operation in a transaction, so that the de-encrypted data is never available.

And note that this only applies to the data that was directly encrypted with the EKM key. If you have data that is encrypted with symmetric keys encrypted by the EKM keys, you don't need to decrypt the data, only the keys. And ALTER SYMMETRIC KEY seems to do that for you.
Sign in to comment

Share via

SQLEKM| Can GUID associated with key be updated with new GUID

5 additional answers