DECRYPTBYPASSPHRASE doesn't work after creating them with EncryptByPassPhrase
I have a table:
CREATE TABLE TempHashedValues(
HashedValues varbinary(200)
)
Now I insert the encrypted values into it, so they can be used later:
Insert into TempHashedValues values ( EncryptByPassPhrase('key', 'SecretEncoded' ))
Now when I try to decrypt them using the same key:
Select TOP 1 DECRYPTBYPASSPHRASE('key',HashedValues) from TempHashedValues
I am just returning a binary value, not the value I encrypted !!
What am I missing?
source to share
As stated here http://sqlity.net/en/2530/decryptbypassphrase/
ENCRYPTBYPASSPHRASE
returns the encrypted value as datatype VARBINARY(8000)
. This data type, with the exception of, for example, SQL_VARIANT, does not carry any information about the original data type. Hence DECRYPTBYPASSPHRASE
also returns a value VARBINARY(8000)
. You have to throw it:
Select TOP 1 (CAST(DECRYPTBYPASSPHRASE('key',HashedValues) AS VARCHAR(8000))) from TempHashedValues
source to share