January 21, 2022 at 11:56 am
I have a database which contains a table with encrypted data.
I have restored this database onto a new instance (and upgraded from SQL2016 to SQL2019) but now, when I try to access the encrypted information, I get the error:
Msg 50000, Level 16, State 1, Procedure [database].[schema].[procedure], Line 27 [Batch Start Line 7]
Please create a master key in the database or open the master key in the session before performing this operation.
I have tried a BACKUP/RESTORE of the DMK from the original database but on the restored database I get the error:
Msg 15329, Level 16, State 30, Line 4
The current master key cannot be decrypted. If this is a database master key, you should attempt to open it in the session before performing this operation. The FORCE option can be used to ignore this error and continue the operation but the data encrypted by the old master key will be lost.
Obviously the FORCE option would lose the encrypted data - which would be a pain to copy and recreate from the source database.
I *believe* that the original DMK was encrypted by the service master key rather than a password (my BACKUP didn't require me to OPEN the master key first) so I'm stumped as to how I can update the DMK in the restored database.
Any ideas? Thanks.
January 21, 2022 at 7:44 pm
Have you tried opening the master key by password and adding encryption by service master key?
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply