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

Empty Column ??

I am having some weird issues that I cannot figure out.
- When I right click on a table using Enterprise Manager and return all
rows I see data except that one column is showing nothing. From in
that window I do a IS NULL query verify that there is in fact data in
the column and a LENGTH to verify it's length.
- If I open up the exact same database from within EM and do a select
ont he table it returns all rows and the column is no longer empty but
is showing the correct values.
I am using SA login for both tools. The column datatyype is VARCHAR
(4000), The tools are local and connection to a DB on another server.
There is no padding whatsoever.
Please help me
Ok I did a RTRIM ont he column and now everything is fine, seems there
were some trailing spaces. WHat was weird though was that in my friend
EM he could see the column fine even when I couldn't. Any setting that
would cause this?