Search code examples
sql-serverencryption-symmetric

Symmetric Key DecryptByKey returning clear text with '0x' prefix

I am inserting data into a table like this through an SSIS OLEDB command

OPEN SYMMETRIC KEY crypto_key
DECRYPTION BY CERTIFICATE crypto_cert;

insert into tmp_ssis_auth_load_detail values(EncryptByKey(Key_GUID('crypto_key'), ?))

CLOSE SYMMETRIC KEY crypto_key

When I try to decrypt the data with the following query(in SQL Management Studio)

OPEN SYMMETRIC KEY crypto_key
DECRYPTION BY CERTIFICATE crypto_cert;

select DecryptByKey(account_number) from tmp_ssis_auth_load_detail

CLOSE SYMMETRIC KEY crypto_key

I get the result as 0x4556808712810676 and when I convert it to varchar I get some garbage value like "EV€‡v". The original value I gave the input query was "4556808712810676" which is returned with "0x" prefix by the decrypt function.

If I run the insert in SQL Management Studio I am able to decrypt it successfully.

What am I doing wrong here?

Solution

  • The datatype of the column being sent from the previous step was initially "DT_STR". Changing it to "DT_TEXT" fixed the issue.