0

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?

2 Answers 2

0

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.

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.