Master Key Encryption and Certificate Encryption Errors
Posted on 2009-04-22
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)