Encrypting a data

Hi,

I have creating a stored procedure in sql server 2005 to encrypt a parameter value that comes from an asp.net page. this value is of type varchar.

Now i have declared a paramater as varbinary to accept this value in the stored procedure. And encrypting it using symmetric key which is encrypted by certificate.

Now when i enter a value into the textbox in asp.net page. it shows

"implicit conversion from varchar to varbinary not possible" pls tell me what is the solution? do i have to convert the value in vb code while accessing the value of the textbox or do i have to convert the value while passing the value when call stored procedure.

pls tell me the solution so that i don't jave to change any coding in vb.

some solution in sql server itselt.

Thank you

Gaurav

If you're passing a varchar to the stored procedure, then rewrite the stored procedure to accept a varchar.

Thanks
Laurentiu

|||

But i have to encrypt that value - the value that is coming from a varchar parameter.

And encryption works on varbinary data type. so do i need to convert that parameter to varbinary in the stored procedure.

Infact i even tried that but it is showing the same result implicit conversion not allowed.

Pls give me a solution. where i could pass a varchar data type value to a stored procedure and encrypt it in the stored procdure.

Thanks

Gaurav

|||

You can encrypt varchar values; it is not true that encryption works only on varbinary data. See the BOL article for EncryptByKey:

http://msdn2.microsoft.com/en-us/library/ms174361(SQL.90).aspx

varchar is explicitly listed as an accepted type for the cleartext data, so you just need to rewrite your procedure to accept a varchar value. Converting the varchar data to varbinary in the procedure does not help, because the problem happens earlier when you pass the varchar value to the procedure that expects a varbinary. If you don't want to change the procedure code to expect a varchar, then you have to explicitly cast the varchar to varbinary before you pass it to the procedure.

For an example that encrypts varchar data, see http://blogs.msdn.com/lcris/archive/2005/12/16/504692.aspx. There are other examples on my blog that encrypt varchar data besides that one.

Thanks
Laurentiu