September 1, 2020 at 7:35 pm
Hello All,
Long story short, I am in a situation of inheriting ownership of a set of databases using symmetric keys (legacy servers).
Time to upgrade / migrate to new servers. I will admit I am not the best regarding dealing with encryption but I am 99% sure the legacy documentation I am working off of for the pw's for the symmetric key source and identity value are wrong.
I:
Restored / upgraded an example database to the new server
Created a new service master key
first test...I just dropped the key, cert, database master key.
Then I used the legacy script to add the database master key, cert, and key back...then ran a query to decrypt and boom...nothing.
Was surprised/Shocked/WTF!
Lots of searching/alternative tests no avail...so I am stuck...or so I thought.
I re-restored the database again, threw a hail mary off of some info I found online about a similar situation to attempt to open the database master key...and Add encryption by service master key
Boom...it appears to have worked and I can query data like I was expecting too initially!!!
Code
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Documented_Password_I_Had_For_Database_Master_Key'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
CLOSE MASTER KEY
SO...now that it looks like I have some light at the end of the tunnel...is there any way to 'swap' out the unknown cert / key passwords that don't seem to be working or am I basically back in the same boat?
Again, it looks like the same methodology was used throughout all the database participating in encryption that I am attempting to move / upgrade.
The Database Master keys seem to be all the same it is just the Key_Source and / or Identity_Value passwords are incorrect in the legacy documentation.
So while I am better off than I was about an hour ago...I am not sure what options I have to safeguard myself to get a cert / key password that is valid.
Any advice is greatly appreciated.
Thanks,
Lee
September 2, 2020 at 5:41 pm
The hierarchy is that one key encrypts another. So the SMK (service master key) encrypts the DMK and allows it to open. The DMK, once open, allows you to open the cert , which allows you to open the symmetric key. Your key should require you to open it, with the code to then open the cert with a password.
In general, when you restore to a new instance, you need to link the DMK to the SMK, which your code above does.
The cert can be backed up and restored with the BACKUP CERT and CREATE CERT DDL. The sym keys cannot. You need the identity and source values. What you can do is recreate a new sym key, linked to the cert, and then decrypt/encrypt the data with this new key. Doc the values used.
September 4, 2020 at 9:18 am
I am iterested too and I will wait for some expert's answers here
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply