sql server vs .net encryption

Question

I have to store a small number of database fields (~3) in encrypted form in database tables.

In general, is it better to encrypt/decrypt data within an application using .net cryptography or encrypt/decrypt data in a database using symmetric key encryption? What are the pros/cons of the two methods?

Here is my Environment:
Application - intranet web application
Development Platform - Visual studio 2010, ASP.Net, .Net Framework 3.5
Server Operating System - Windows Server 2008
Database - SQL Server 2008

Answer 1

This is a great question.

My take on it is to let the part responsible for storage of the data to also be responsible for any encryption of it. In this case, let SQL server manage the encryption.

The primary reason is that we rarely build stand alone applications anymore. It's more common to have multiple apps utilize the same database backend. Further, it's more common to replace the tech used to build an app than it is to replace the database engine underneath it.

If you do it in code, then every app that uses the data store would have to use it's own encryption/decryption library to get access to the data. If done in sql server, then the apps can be pretty ignorant of the process while you maintain the same level of protection.

Along with this, you should leverage the ability to encrypt connections to the SQL server, which would handle keeping the data encrypted while it's in motion between the web/app server and the database. Also you should have SSL enabled between the browser and your web site to ensure the data is never decrypted between the browser -> web server -> database server.

Hope that helps.

Answer 2

For internal applications where you are not worried about "investigators" from the Internet, I prefer to do the encryption at the database. That way if something goes south with your application (for whatever reason) you are not reliant on that app to get the data back. Your app also, then, does not have the dependency on the encryption mechanisms or the overhead of performing the application. It can assume that it will be taken care of at the database making the code more maintainable.

It is also more convenient for apps that have multiple interfaces or gateways. The data is encrypted / decrypted centrally. What you would need to in that case, though, is make sure the data is being passed to / from the database via a secure tunnel (SSL or VPN or something similar), otherwise it doesn't mean a thing to encrypt the data at all; it can be easily intercepted with a standard network sniffer.

For shared hosting environments I prefer to do encryption within the app for security purposes. In that situation I control the keys and not the host provider.