1

good morning,

Perform a migration of an On Premise database:

Microsoft SQL Server 2005 - 9.00.5000.00 (X64) Dec 10, 2010 10:38:40 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)

An AWS:

Microsoft SQL Server 2019 (RTM-CU16-GDR) (KB5014353) - 15.0.4236.7 (X64) May 29, 2022 15:55:47 Copyright (C) 2019 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Datacenter 10.0 < X64> (Build 14393: ) (Hypervisor)

The whole theme works for me but I have a problem, there is a database that uses the symmetric recorder at the column level, these are the symmetric keys on the old server: enter image description here

And this is the configuration on the new server:

enter image description here

The problem is when I try to decrypt, because of the encryption of the MasterKey in AES256 it does not allow me to do it.

My questions are: is there a way to change the engraving to be with the TRIPLE_DES on the new server?

Is there a way to change the encryption to be with AES256 on the old server? With this, it would generate a backup copy again and restore, ending that there would be no problem

The only option is to decrypt on the old server, generate backup and re-encrypt on the new server?

Thank you all so much for reading.

Improve this question
7
  • Looks you you did some type of import/export of the data and not a backup/restore of the database, correct?
    – Sean Gallardy
    Commented May 30, 2023 at 21:25
  • Perform a backup and restore of a database.
    – Omar Noa
    Commented May 30, 2023 at 22:18
  • Then why did your symmetric key id change for the SK_ key if you did a backup restore?
    – Sean Gallardy
    Commented May 30, 2023 at 22:40
  • In the attempt to try to fix the problem, I regenerated the key and the certificate, but quickly perform the test and I will restore the DB, and they are migrated with the same symmetric key id as in the origin, but it continues to show me the data without decryption.
    – Omar Noa
    Commented May 30, 2023 at 23:11
  • So really the issue isn't with the database master key, it's with the service master key. Open the database master key by password and then add encryption by the service master key.
    – Sean Gallardy
    Commented May 30, 2023 at 23:12

1 Answer 1

Reset to default
2

The problem is when I try to decrypt, because of the encryption of the MasterKey in AES256 it does not allow me to do it.

This isn't the problem. The problem is the database was restored but the database master key is encrypted by the older service master key. Open the database master key by password and then add encryption by the service master key.

I already did it, but nothing happens: OPEN MASTER KEY DECRYPTION BY PASSWORD = 'thepassishere'; GO ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; GO Information keeps showing up NULL

This is because you've changed the encryption key, previously: "In the attempt to try to fix the problem, I regenerated the key and the certificate [...]"

You'll want to restore the database, open the master key, add encryption by the service master key. Don't drop or change any other items.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.