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:

http://blogs.msdn.com/b/sql_protocols/archive/2005/10/04/476705.aspx

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

Answer 1

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

Answer 2

I'm a little bit confused about your request. Do you need to encrypt the data locally, or do you want to encrypt the connection between your app and the server? Or the data stored in the database tables? Or a combination of those?

Anyways, for the encryption of the connection (which includes authentication), you may want to have a look at the channel encryption feature of SQL Server. http://blogs.msdn.com/b/sql_protocols/archive/2005/10/04/476705.aspx

For local files or connection strings as well as binary data encryption (which may also be stored in its encrypted form in the DB), looking into the System.Security.Cryptography namespace is the way to go.

Answer 3

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. Is is extremely easy to screw up royally in cryptography, and you won't even know it. Is it 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).