Encryption from c# application

Question

SITUATION: I need to make encryption happen between my remote database and my c# application. I don't know what I'm doing (never done any encryption before) and all the stuff I found on the web was for asp.net and dealt with the web.config file.

SOME RELEVANT DATA: My connection string contains password info for SQL server authentication, there is a select, and a delete statement. Those three things will need to be encrypted.

I am using SQL Server 2008, Visual Studio 2008, and C#.

I doubt this is relevant but this is taking place inside a windows service. So far, anything SQL related that works in winForms has worked for me in services, so any help that is winForms related is appreciated, too.

WHAT I NEED HELP ON:

1. Any references on encryption that aren't restricted to asp.net would be greatly appreciated. :)

2. I see the MSDN page, and it seems like a decent place to start, but I am a little confused. It seems like this is the way 2 applications would send a file to each other, rather than sending something encrypted to SQL Server? Using this, I don't see how SQL Server would know how to decrypt it?

3. Am I way out in left field looking in System.Security.Cryptography? Is there some way to specify encryption within System.Data.SqlClient or am I going to have to resort to messing around making stored procedures on the remote server?

Thank you in advance!!! :)

SUMMARY:

Thank you all for setting me on the right path, it was difficult to choose just one answer! :)

I've concluded that I need to use SSL, and while I yet have a lot of confusion about the how-tos, I know that this requires a certificate, and that once the certificate is set up, the client can request encryption by asking for "Encrypt=yes" in the connection string. Luckily I believe I already have one I can use.

Another thing to note - TDE is the consensus on what is good for encrypting data that is just sitting in the database, while SSL is what to use for transmitting encrypted data.

Here were a couple links I found the most helpful:

Link

http://support.microsoft.com/default.aspx?scid=kb;en-us;316898

Answer 1

What you need is to protect the traffic between your application and the SQL Server. For this, simply follow the steps described in Encrypting Connections to SQL Server.

Next thing is that if you do store sensitive data, you want to store it encrypted in the database to protect against accidental media loss. The best solution, by far, is to use Transparent Database Encryption.

Neither of these solutions require any single line of code change in your application. They are both deployment time, administrator controlled settings. Trying to roll your own solution for cryptography will get you nowhere fast. It is extremely easy to screw up royally in cryptography, and you won't even know it. It is much easier - and far better - to satisfy the requirements without changing your application by simply leveraging encryption features provided by SQL (connection TLS, storage TDE).

Answer 2

For the encryption between the app and the DB, your best bet is to use an SSL certificate at the SQL Server level. Here is a Microsoft KB article on it (for an older version of SQL Server). http://support.microsoft.com/kb/316898 This will protect against sniffers.

We did this with our credit card application form, and it was very straightforward.

This Microsoft.com search will help you with newer versions of SQL Server.

And this article will help with SQL Server 2008. http://msdn.microsoft.com/en-us/library/cc278098.aspx