Link to home
Start Free TrialLog in
Avatar of davidcahan
davidcahanFlag for United States of America

asked on

Master Key Encryption and Certificate Encryption Errors

I'm trying to create an encryption certificate using my  Master Key.   But first I'm trying to recreate the encryption because I am on a new db server.   I backed up and restored all my DB's to this new server.  normally to recreate the encryption all i would do is run this command:


OPEN MASTER KEY DECRYPTION BY PASSWORD = 'foobar'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

However, this is giving me the error:

Msg 15466, Level 16, State 2, Line 3
An error occurred during decryption.

When i try to create a certificate using this command but not running the above command:

CREATE CERTIFICATE PMRCertificate  WITH SUBJECT ='This is PMRs Cert'

I get this error:
Please create a master key in the database or open the master key in the session before performing this operation

However, if i run the second comman immediately after the first (even though the first gives me the error:  An error occurred during decryption.) then i am able to create the cert.  I can even encrypt using that cert into a varbinary(128) field.  however, when i go to decrypt it comes back NULL.  

So, what the heck am i doing wrong and what do i need to do to fix this whole mess.  I really don't want to resort to doing the encryption via a hardcoded password (ie EncryptByPassword)


ASKER CERTIFIED SOLUTION
Avatar of RiteshShah
RiteshShah
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial