CODEWITHSUNDEEP
sqlencryptionsql-server-2014password-encryption
Simsons
Simsons

Reputation: 12745

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 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?

Upvotes: 3

Views: 4625

Answers (1)

arisalsaila
arisalsaila

Reputation: 429

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

Upvotes: 3

Related Questions