How to encrypt data in sql server and decrypt it in .net apps

Question

I want to encrypt some passwords in sql server and have a c# app decrypt them.

Obviously I can create an SP to decrypt the required password for me and pass this to the c# app, but this means sending the plaintext password over network.

So I want to be able to encrypt my password in sql server (using passphrase, certificate etc), which can be passed around to my c# apps which will then know how to decrypt it and use it.

I think this must be possible (maybe using certificates) but not really sure where to start.

Can use .net 4 and sql server 2008 if there are new approaches to this.

Thanks in advance for your advice.

Answer 1

Never encrypt the passwords!

Passwords should be salted and hashed - this is rule number one when it comes to system security.

Answer 2

We have created this functionality, an assembly that is used in both code and deployed on the server. However, we have run into memory pressure issues. The server is a dual quad core server with 24 gb of memory, 64 bit os and 32 bit sql server. AWE is enabled, sql's process is prioritized and 19gb of the memory is allocated to sql. It is our only assembly and we get tons of errors in the log file. I am a big fan of extending systems, however, this seems like a hack. Why do we have use one or the other? Why not both?

Answer 3

Could you create an encrypt/decrypt assembly in .NET (2.0) and deploy that to SQL Server? That's the only way I see how you could have the same algorithm/mechanism to encrypt/decrypt your data.

Create a .NET assembly with the encryption/decryption, deploy it to SQL Server, use it there to encrypt the data, and use the same assembly / same .NET code to decrypt the data in your .NET app.