Can I encrypt value in C# and use that with SQL Server 2005 symmetric encryption?

Question

To be more specific, if I create a symmetric key with a specific KEY_SOURCE and ALGORITHM (as described here), is there any way that I can set up the same key and algorithm in C# so that I can encrypt data in code, but have that data decrypted by the symmetric key in Sql Server?

From the research I've done so far, it seems that the IDENTITY_VALUE for the key is also baked into the cypher text, making things even more complex.

I'm thinking about just trying all the various ways I can think of, ie hashing the KEY_SOURCE using different hash algorithms for a key and trying different ways of encrypting the plain text until I get something that works. Or is that just futile?

Has anyone else done this, any pointers?

UPDATE

Just to clarify, I want to use NHibernate on the client side, but theres a bunch of stored procedures on the database side that still perform decryption.

Answer 1

Are you opposed to encrypting the data in SQL Server?

Not knowing the requirements of your application, from an architectural standpoint I would stick to encrypting & decrypting on the SQL side.

However, I can only speculate that one reason you'd want to do this is that you might not trust the transporation medium between your application & your SQL Server, and you want to encrypt the TSQL going across that channel in case it is intercepted.

If this is the case - I'd suggest looking into SSL HTTP endpoints in SQL Server, as those might be a viable option.