Copy SQL Server database to same server & decrypt by symmetric key return null

Question

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?

Answer 1

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.

Answer 2

The issue was that the column wasn't long enough to hold the encrypted data. It was weird, because it was a copy.. I increased the encrypted column size and all was resolved.