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.