CODEWITHSUNDEEP
sql-server-2012encryption-symmetric
drizzie
drizzie

Reputation: 3361

DecryptByKey returns null SQL Server 2012

I am trying to decryptbykey and it returns null.

--CREATE MASTER KEY ENCRYPTION
--BY PASSWORD = 'Password1'

--CREATE CERTIFICATE MyCert
--WITH SUBJECT = 'MyCert'

--CREATE SYMMETRIC KEY MyKey
--WITH ALGORITHM = TRIPLE_DES ENCRYPTION
--BY CERTIFICATE MyKey

OPEN SYMMETRIC KEY MyKey DECRYPTION
BY CERTIFICATE MyCert;

INSERT INTO sometable (ENCRYPTBYKEY(KEY_GUID('MyKey'),'12345'));

CLOSE SYMMETRIC KEY MyKey;

OPEN SYMMETRIC KEY MyKey DECRYPTION
BY CERTIFICATE MyCert;

SELECT
    CONVERT(VARCHAR(50), DECRYPTBYKEY(some_column))
FROM sometable;

CLOSE SYMMETRIC KEY MyKey;

The column is defined as varbinary(256).

The result is null. How do I change this to get the decrypted value instead of null? Or what are some of the possible pitfalls or things to check?

Upvotes: 3

Views: 8432

Answers (2)

Roman Pokrovskij
Roman Pokrovskij

Reputation: 9776

With my sample sometable this script works well. So please include your table definition.

Then I suppose you should test IF KEY_GUID('MyKey') IS NOT NULL..

Upvotes: 2

drizzie
drizzie

Reputation: 3361

The issue was the column in the table wasn't large enough to store the data. So it was truncating without error.

I increased the column size and the issue was resolved.

Upvotes: 3

Related Questions