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

BHARDWAJ Bhuvendra 21 Reputation points
2021-05-25T08:38:05.93+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,238 questions
Windows API - Win32
Windows API - Win32
A core set of Windows application programming interfaces (APIs) for desktop and server applications. Previously known as Win32 API.
2,352 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,404 questions
SQL Server Migration Assistant
SQL Server Migration Assistant
A Microsoft tool designed to automate database migration to SQL Server from Access, DB2, MySQL, Oracle, and SAP ASE.
474 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erland Sommarskog 98,636 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.


5 additional answers

Sort by: Most helpful
  1. CarrinWu-MSFT 6,841 Reputation points
    2021-05-26T07:09:56.51+00:00

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

    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.

    0 comments No comments

  2. BHARDWAJ Bhuvendra 21 Reputation points
    2021-05-26T07:26:00.567+00:00

    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 ?


  3. CarrinWu-MSFT 6,841 Reputation points
    2021-05-27T02:18:27.59+00:00

    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.

    0 comments No comments

  4. BHARDWAJ Bhuvendra 21 Reputation points
    2021-05-28T08:05:28.52+00:00

    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.