question

BHARDWAJBhuvendra-3041 avatar image
0 Votes"
BHARDWAJBhuvendra-3041 asked ErlandSommarskog commented

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

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?

sql-server-generalwindows-apisql-server-integration-servicessql-server-migrationsql-server-migration-assistant
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered ErlandSommarskog commented

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.

· 6
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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.

0 Votes 0 ·

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.

0 Votes 0 ·

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.


0 Votes 0 ·
Show more comments
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @BHARDWAJBhuvendra-3041,

Welcome to Microsoft Q&A!

I enabled EKM for my database, and use below T-SQL to update the GUID:

 UPDATE EnglishStudents1 SET id = NEWID();

99764-1.png

Please let me know if I had misunderstanding your question.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.




1.png (11.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BHARDWAJBhuvendra-3041 avatar image
0 Votes"
BHARDWAJBhuvendra-3041 answered ErlandSommarskog commented

I think, I was not clear. So basically below is the scenerio :

• I have created a key Key_A on an External Key Manager via Cryptographic_Provider_A installed on an MS SQL Server.
Key_A is used to perform column encryption as well as TDE.
• Now, after deleting the credential & cryptographic provider, a new cryptographic provider Cryptographic_Provider_B is installed on the same system.
• Now if I try to use the Key_A to decrypt Database_A and decrypt Column data, I get an error that the cryptographic provider with which the key was created, and the one that is installed on the system have different GUIDs.

Question : Is it possible to manipulate the Key_A and change it's cryptographic_provider_guid from the GUID of Cryptographic_Provider_A to the GUID of Cryptographic_Provider_B ?








· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I have no experience of using EKM, but would there be any reason to assume that Provider_B would understand a key created on Provider_A, even if you were able to manipulate the key? (Which I strongly suspect that you can't.)

Anyway, what sort of key is this? With which command did you create it originally?

0 Votes 0 ·
CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

Hi @BHARDWAJBhuvendra-3041,

sorry for the late reply. I read this link:

Encryption keys for data and key encryption are created in transient key containers, and they must be exported from a provider before they are stored in the database. This approach enables key management that includes an encryption key hierarchy and key backup, to be handled by SQL Server.

I think you need to use a new key is generated by Cryptographic_Provider_B to decrypt it, because Key_A is mismatch the new cryptographic provider Cryptographic_Provider_B. And from this document, the SQLEKM provider reports problems in two ways:

Until the initialization of the EKM provider has finished errors are written to the Windows Event
Log. Open the Event Viewer on Windows and check the Application log for messages from
event source ”Utimaco EKM Provider”. Typical errors are that the path to the configuration file
is not defined, that the configuration file cannot be read, that the keystore file cannot be opened,
or that the log file could not be created/opened.
2. After the initialization, the log file is used for reporting. The log file and the log level need to be set
in the configuration file. Make sure to have at least log level 1 to see errors. Before contacting
the support try to reproduce the error with log level set to 5. After changing the log level you
need to restart the SQL Server service.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BHARDWAJBhuvendra-3041 avatar image
0 Votes"
BHARDWAJBhuvendra-3041 answered BHARDWAJBhuvendra-3041 commented

Here, Cryptographic_Provider_B has to use key created by Cryptographic_Provider_A only. This is because in TDE the key which is used to encrypt the DEK has to decrypt with the same key.
So we can't create the new key. Cryptographic_Provider_A had to use key created by Cryptographic_Provider_B, but the guid of both the provider were different so the key is not available to other provider.

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BHARDWAJBhuvendra-3041, I did not see your reply for the advise from Erland. Any update for this?

0 Votes 0 ·

@CarrinWu-MSFT I will try the suggested thing today and update accordingly.

0 Votes 0 ·
way0utwest avatar image
0 Votes"
way0utwest answered

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://docs.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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.