Proc having Key issues after moving DB from one server to another

Question

After moving a database from one server to another, we started having an issue:

> Msg 15581, Level 16, State 7, Procedure xxxxx, Line 24 [Batch Start Line 2]
Please create a master key in the database or open the master key in the session before performing this operation.

Msg 15315, Level 16, State 1, Procedure xxxxx, Line 62 [Batch Start Line 2]
The key '<CustomKeyName>' is not open. Please open the key before using it.

The procedure in question has:

 OPEN SYMMETRIC KEY MySymKey
 DECRYPTION BY CERTIFICATE MySymCert; 

I can verify that after moving the DB, I can see a symmetric key and a certificate by querying sys.symmetric_keys and sys.certificates.

We, yeah, don't have the master key pass. I'm curious if this process would work:

1. In the old server (in the DB scope):

ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD = 'a_cool_pass';

2. backup DB again

3. Retore DB in the new server again (with replace)

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'the_cool_Pass'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

would this be a proper solution for this situation?

OR, can I just drop the symetric_key and cert, and recreate?

edit: REGENERATING the master key pass in the old server, backup/restore into new, OPEN MASTER KEY DECRYPTION BY PASSWORD = got the issue The key is not encrypted using the specified decryptor. ( what I thought it was strange. I will try again tomorrow.

(would I need to do a backup restore of the certificate?

Answer 1

would this be a proper solution for this situation?

Yes. You don't have to regenerate master key on old server.

If it's open (by service master key) just add a password to decrypt

ALTER MASTER KEY ADD ENCRYPTION BY PASSWORD = 'a_cool_pass';

also after restore on new server, open it with the same password

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'a_cool_pass'

'a_cool_pass' and not 'the_cool_Pass'

can I just drop the symetric_key and cert, and recreate?

You will loose all data encrypted with key and cert.

EDIT.

would I need to do a backup restore of the certificate?

It is attached to your database so there is no need to do it, but trying to fix your issues you can run a backup of master key instead of backup/restore whole database from old server

• backup only master key from old database,
• restore it to new server,
• open the master key
• open symmetric key
• and decrypt data.