I'm having a bit of an issue with a stored procedure I have written which encrypts a value using ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE.
The problem is these functions return Varbinary data types which I need to append to a Varchar data type.
Can anyone help with being able to display this as a Varchar data type without converting it? ie have a Varchar whose value is "0x010000004C4101804DD79FC3CA2258826140B2A3B66A0130F4AECB34" rather than the actual Varchar conversion of the Varbinary.
I'm not limited to using ENCRYPTBYPASSPHRASE if anyone knows of a more convenient method. Please bear in mind the values are stored in plaintext so encrypting the columns is the only no-no.