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.