Decryptbypassphrase Is Not Working After Creating Them With Encryptbypassphrase

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 TempHashed

Solution 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