• State Not Answered
  • Replies 6 replies
  • Subscribers 95 subscribers
  • Views 11254 views
  • Users 0 members are here
Options
Related

Decrypting the database: Encryption error. The data was probably encrypted with the wrong public key.

ryoma ninomiya
over 4 years ago

Hi people,

I'm having trouble decrypting a database which was originally encrypted on another SQL server. The error message I get during the decryption is in the title, these are the steps I took so far:

  1.  Encrypt DB on SQL Server 1
  2. Create Backup of DB on SQL Server 1
  3. Migrate DB to SQL Server 2
  4. Clean personnel data from DB per SQL on SQL Server 2
  5.  Attempt to re-encrypt the DB on SQL Server 2 (because the JobServer was complaining about encryption)
  6. Get error message

We are attempting this because we want to make the current production environment in to a development environment. Transport packages were not imported in the intended order due to emergency changes and human errors, so we'd like to correct discrepancies between our environments by turning our PROD to DEV. I have attempted this in the past by creating a transport package containing of all customisations from PROD and importing this into a clean installed environment. This procedure took a considerable amount of time (with multiple attempts), so this time I though "hey let's just clean PROD data" and here we are.

My questions are the following:

  • Is there still a way to decrypt the DB? Or is the public key created using a combination of SQL Server ID and DB ID?
  • Has anyone figured our a best practice for ridding an environment of personnel data?

Thank you in advance!

Ryu

  • 0 over 4 years ago

    Hi Ryu,
    asymmetrical encryption is used.
    During step #1 a public key and a private key file is created. The public key is stored in the database for data encryption. The wizard instructs you to store the private key file somewhere save. If it is lost your encrypted data cannot be decrypted.
    there are two options here.
    option 1) use the private key file to decrypt the restored database prior to step #3 using CryptoConfig.exe. The jobqueue must be empty! (If not there might remain processes that contain encrypted parameters)
    option 2) skip step #5 and provide the private key file to the jobservices in the new environment.


    best regards,
    Andreas

  • 0 over 4 years ago in reply to Andreas Finsterbusch

    Hi Andreas,

    thank you for your response.

    Option 1) Yes I think this was the easiest way forward 

    Option 2) I have provided the private key created in step #1 to the job services of the new environment. I got a similar error as below which is what lead me to attempt to decrypt the DB. Do you think I perhaps made a mistake in the job service configuration?

    [TimeStamp] Error occurred in ExecutionSlot (thread: Main Loop):
    [821053] Could not decode parameter 'ConnectionString'.
        [809014] Encryption error. The data was probably encrypted with the wrong public key.
        [System.Security.Cryptography.CryptographicException] The parameter is incorrect.

  • 0 over 4 years ago in reply to Andreas Finsterbusch

    Hi Andreas,

    so I'm attempting a new run at this using option 1. But then it came to mind, wouldn't the public key become invalid as soon as I rename the database to something else? 

    1. Let's say I have a DB called OneIM in SQL Server 1
    2. I take a backup of this DB and re-create this DB one SQL Server as OneIM2

    Since the connection parameter would change as soon as I rename the DB, wouldn't this invalidate the public key? 

  • 0 over 4 years ago in reply to ryoma ninomiya

    Hi Ryu,

    the public key is independent from server name, database name, dbid and so on! The public key is stored in DialogDatabase.PublicKey.
    If the public key would be generated from server name, database name, and so on the private key also would. Which would render the hole encryption futile.

    Does the jobservice log contain a message stating that the provided private key was consumed? Copy the key into the jobservieces directors prior to starting/restarting the Jobservice.

  • 0 over 4 years ago in reply to Andreas Finsterbusch

    Hi Andreas,

    thank you for the clarification regarding the public key. Short answer: Yes, the job service log reports that the private key was moved to the key container. I'm still receiving the "cannot decode connection string" error, and the database is not decryptable. I have attempted procedure again as follows.

    1. Take fresh copy of DB on SQL Server 1
    2. Followed this guide to restore DB in SQL Server 2
    3. Configured Job Server and Service
    4. Get Error: "Could not decode parameter 'ConnectionString'. Encryption error. The data was probably encrypted with the wrong public key"
    5. Attempt to decrypt DB
    6. Get Error: "Error converting configuration parameter 'Common\MailNotification\Encrypt\ConnectPassword'. Encryption error. The data was probably encrypted with the wrong public key
    7. Reconfigured DB connection string via Designer
    8. Repeat step 4 - 6 > Same result
    9. Repeat step 3 - 6 trying different combinations > Same result
    I think I may have stumbled on a support case here. If anyone else if experiencing this problem, do let me know.

    Details to Step 4)

    2019-10-17 11:14:03 +02:00 - Warning: Moving private key to the key container.
    2019-10-17 11:14:03 +02:00 - Warning: Constrain access to encrypted configuration properties on the service account.
    2019-10-17 11:14:04 +02:00 - Warning: Starting queue \IAM811.
    2019-10-17 11:14:04 +02:00 - Info: Requesting process steps for queue \IAM811.
    <...Getting Job to execute...>
    2019-10-17 11:14:05 +02:00 - Warning: Stopped querying tasks for queue \IAM811.
    2019-10-17 11:14:05 +02:00 - Error occurred in ExecutionSlot (thread: Main Loop):
    [821053] Could not decode parameter 'ConnectionString'.
        [809014] Encryption error. The data was probably encrypted with the wrong public key.
        [System.Security.Cryptography.CryptographicException] The parameter is incorrect.
               at VI.JobService.ExecutionSlot.Execute(Job job)
               at VI.JobService.ExecutionSlot._DecryptParameters(JobParameters parms)
               <...Inner Exception...>
     
    Details to Step 6)
    The decryption seems to be successful (because the bar showing "Entries Converted / Total Entry Number" does seem to increase with time) up until the system attempts to decrypt the configparm 'Common\MailNotification\Encrypt\ConnectPassword'.
  • 0 over 4 years ago in reply to ryoma ninomiya

    Hi Ryu,

    the encryption and decryption by CryptoConfig only takes columns marked as encrypted into account.
    Configparms with a manually inserted, already encrypted value will not be decrypted. The same goes for parameters of Processes that are already in Jobqueue.
    Empty your Jobqueue prior to the decryption. Replace all encryptet values in DialogConfigparm with plaintext values.