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

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.