DECRYPTBYPASSPHRASE is not working after creating them with EncryptByPassPhrase

Question

I have a table:

CREATE TABLE TempHashedValues(
HashedValues varbinary(200)
)

Now, I am inserting encrypted values to it using, so that could be used later:

Insert into TempHashedValues values ( EncryptByPassPhrase('key', 'SecretEncoded' ))

Now when I am trying to decrypt them using same key:

Select TOP 1 DECRYPTBYPASSPHRASE('key',HashedValues) from  TempHashedValues

I am just getting the binary value back , not the value I encrypted !!

What am I missing?

Answer 1

As stated here http://sqlity.net/en/2530/decryptbypassphrase/ ENCRYPTBYPASSPHRASE returns the encrypted value as a VARBINARY(8000) data type. That data type, other than for example SQL_VARIANT does not carry any information about the originating data type. Therefore, the DECRYPTBYPASSPHRASE also returns a VARBINARY(8000) value. You have to cast it :

Select TOP 1 (CAST(DECRYPTBYPASSPHRASE('key',HashedValues) AS VARCHAR(8000))) from  TempHashedValues