Encryption and Decryption by Key

I am trying to use the encryptionbykey and decryptionbykey from the database to return a key that is used in a web application as a querystring. I can encrpyt fine. But when the value is passed back to a function as as varchar and convert it to a varbinary it does not decrypt. Below is an example:

CREATE SYMMETRIC KEY DummyKey

WITH ALGORITHM = DESX

ENCRYPTION BY PASSWORD = N'd7mmy';

GO

DECLARE @.UnsubscribeURL varchar(300)

DECLARE @.OriginalUserKey varchar(250)

DECLARE @.EncryptedUserKey varchar(250)

-- Get Unsubscribe URL

SELECT @.UnsubscribeURL = 'http://hrowdn01.paychex.com/secure/hronlineApplication/unsubscribe.aspx?UserKey='

-- Create the user key (Company_Id + '_' + Employee_Id)

SELECT @.OriginalUserKey = CONVERT(varchar, 161) + '_' + CONVERT(varchar, 3381)

-- Open Encryption key

OPEN SYMMETRIC KEY DummyKey

DECRYPTION BY PASSWORD = N'd7mmy'

-- Encrypt the user key

SELECT @.EncryptedUserKey = master.dbo.fn_varbintohexstr(EncryptByKey(Key_GUID('DummyKey'),@.OriginalUserKey))

--EXEC master..xp_blowfishencrypt @.OriginalUserKey, @.key, @.EncryptedUserKey OUTPUT

CLOSE SYMMETRIC KEY DummyKey

-- Finish creating the unsubscribe URL

SET @.UnsubscribeURL = @.UnsubscribeURL + @.EncryptedUserKey

SELECT @.UnsubscribeURL

--New function would be here

DECLARE @.decrypted_str VARBINARY(MAX)

DECLARE @.DecryptedUserKey varchar(MAX)

OPEN SYMMETRIC KEY DummyKey

DECRYPTION BY PASSWORD = N'd7mmy'

SET @.decrypted_str = CONVERT(varbinary(max),'0x002da862c3f37f449936e9a3eabc83340100000007f5728f4a1ff60d6a08a3ee30dd7d8626551f0da25d14719f4e81a00147a2d9')

SET @.DecryptedUserKey = DecryptByKey(@.decrypted_str)

-- display decrypted text

SELECT @.DecryptedUserKey AS PlainText;

-- close and drop the key

CLOSE SYMMETRIC KEY DummyKey

DROP SYMMETRIC KEY DummyKey

Thanks,

J

Jason,

I've been playing around with this a little bit.

I don't have an answer.

But from what i can tell, you're going to need a function that reverses the process of

master.dbo.fn_varbintohexstr().

It appears that just recasting that output to varbinary isn't doing what you need.

sql