Copy SQL Server database to same server & decrypt by symmetric key return null 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?
Following worked for me on SQL Server 2008:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'master_key_password'
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY


For me it was essential to drop the encryption first, although the 'add' command should usually do this automatically.