0

Hi All,

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.

Thanks!

Fost

2
Contributors
1
Reply
2
Views
4 Years
Discussion Span
Last Post by buddylee17
0

The master database has a scalar function, fn_varbintohexstr, for this.

Example:

DECLARE @text varchar(256), @varbinary varbinary(256);
SET @text = 'A1B2C3';
SET @varbinary = cast(@text as varbinary(256));

SELECT 
     @text OriginalString
    , sys.fn_varbintohexstr(@varbinary) HexString /* returns nvarchar(max)*/
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.