Is there a way to decrypt a column when querying data from a SQL Server (2005/2008) database that has been encrypted using DES via .NET? If so, how? I have the key, but don't know how to use it within the context of a sql server query.
+3
A:
I would probably consider creating a CLR function in SQL Server to handle this decryption.
You can use this function just like any other UDF within SQL Server to decrypt on demand.
That way, its as simple as using regular .NET C# code to handle it exactly the way you would do it in your application itself.
InSane
2010-10-22 18:31:01
Yep, that's how I'd do it. Though, you should carefully consider the use case - is it absolutely necessary to to the decryption in the database, rather than in some service layer? Generally, the fewer times you have to pass the key around, the smaller the vulnerability footprint.
Dathan
2010-10-22 18:35:50
No it's health care related info, one way hashing won't do. I was leaning towards putting a service layer over it anyway, so in that case I don't even need to worry about SQL Server.
Saraz
2010-10-22 18:51:13