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

Question

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?

Answer 1

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.

Answer 2

Hi @BHARDWAJ Bhuvendra ,

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();  

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.

Answer 3

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 ?

Answer 4

Hi @BHARDWAJ Bhuvendra ,

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:

1. 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.

Answer 5

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.