CODEWITHSUNDEEP
sql-servert-sqlencryptionencryption-symmetricvarbinary
nbardach
nbardach

Reputation: 175

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

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?

Upvotes: 2

Views: 6826

Answers (2)

gotqn
gotqn

Reputation: 43636

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'

Upvotes: 4

Elena López
Elena López

Reputation: 99

Try converting the value to Varbinary

select convert(varbinary, '0x001D25D87D3D8E49A97863ADC4958E790100000021E26DD2305384AE49EC9329EF2AF8758134F7C946EC9FE024805B8DF21472C4545D461DA9F2B7F96094C2AED09BF4A9')

Upvotes: 0

Related Questions