T-SQL DecryptByKey returns NULL with Column Value, but not with Column Name

Question

I have an encrypted varbinary(MAX) field in my DB called ACCT_FName_encrypt.

I can successfully decrypt this field with:

CONVERT(nvarchar(MAX), DecryptByKey(ACCT_FName_encrypt)) AS 'ACCT_FName_Denc'

But if I try to decrypt the actual value from the column, I get NULL:

CONVERT(nvarchar(MAX), DecryptByKey('0x001D25D87D3D8E49A97863ADC4958E790100000021E26DD2305384AE49EC9329EF2AF8758134F7C946EC9FE024805B8DF21472C4545D461DA9F2B7F96094C2AED09BF4A9')) AS 'ACCT_FName_Denc'

How can I get the decrypted value from the straight varbinary, without calling the field?

Some additional information... I've tried removing the CONVERT and just using the DecryptByKey, but that also results in NULL. I've also tried adding a STYLE to the CONVERT (0, 1, 2) but that still results in NULL. — nbardach, Oct 01 '19 at 17:16

score 4 · Accepted Answer · answered Oct 02 '19 at 05:39

It should not be passed as string and it needs to be cast to the original type after. Full, working example:

OPEN SYMMETRIC KEY StackOverflow
DECRYPTION BY PASSWORD = 'pass123_@pass123_@'
GO

DECLARE @ColumnValue NVARCHAR(MAX);
DECLARE @EncrpytionValue VARBINARY(8000);

SET @ColumnValue = REPLICATE (N'A', 12)

SET @EncrpytionValue = ENCRYPTBYKEY( KEY_GUID('StackOverflow'), @ColumnValue )

SELECT @EncrpytionValue

SELECT CONVERT(NVARCHAR(MAX), DECRYPTBYKEY(@EncrpytionValue));
SELECT CONVERT(NVARCHAR(MAX), DECRYPTBYKEY(0x00B08017838E6C48889DD12542E4C52002000000A8C910DA1CBFFE30E446358940177F03F912EE36FACF91FA2044BE5C75C9AA69BC15E6425DE52C2A193BA13AEDA90AE2276C244E56692B75CB2D4FDEC8D596F9));

--DROP SYMMETRIC KEY StackOverflow;

and in your code it will be just:

CONVERT(nvarchar(MAX), DecryptByKey(0x001D25D87D3D8E49A97863ADC4958E790100000021E26DD2305384AE49EC9329EF2AF8758134F7C946EC9FE024805B8DF21472C4545D461DA9F2B7F96094C2AED09BF4A9)) AS 'ACCT_FName_Denc'

score 0 · Answer 2 · answered Oct 01 '19 at 14:17

0

Try converting the value to Varbinary

select convert(varbinary, '0x001D25D87D3D8E49A97863ADC4958E790100000021E26DD2305384AE49EC9329EF2AF8758134F7C946EC9FE024805B8DF21472C4545D461DA9F2B7F96094C2AED09BF4A9')

answered Oct 01 '19 at 14:17

Elena López

99
2

T-SQL DecryptByKey returns NULL with Column Value, but not with Column Name

2 Answers2