I have a SQL Server 2012 database that I backed up and restored to the same server and instance. When I attempt to use DecryptByKey it always returns null.
Since it is the same server, do I need to :
OPEN MASTER KEY DECRYPTION BY PASSWORD = '...'; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
I have tried several things:
I dropped the symmetric key, certificate and master key. I recreated them and added some data to a table. The encryption portion works but the decryption still fails. I'm using the following code to create my encryption objects:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mypass' CREATE CERTIFICATE SomeCert WITH SUBJECT = 'SomeSubject' CREATE SYMMETRIC KEY SomeKey WITH ALGORITHM = TRIPLE_DES ENCRYPTION BY CERTIFICATE SomeCert
I then encrypt and decrypt like so:
OPEN SYMMETRIC KEY CC_Key DECRYPTION BY CERTIFICATE DB_CC_Cert; UPDATE Table SET Column = ENCRYPTBYKEY(KEY_GUID('SomeKey'), @CCNum WHERE SomeColumn = 1; CLOSE SYMMETRIC KEY CC_Key; OPEN SYMMETRIC KEY CC_Key DECRYPTION BY CERTIFICATE DB_CC_Cert; SELECT CONVERT(VARCHAR(50),DECRYPTBYKEY(Column)) AS Column FROM Table CLOSE SYMMETRIC KEY CC_Key;
This select query always returns null. Would could be the issue?