Best Practice for Database Encryption in SQL Server 2005

Question

I need to develop an application which stores data in a SQL Server 2005 database (the app itself will be either a WCF Service or an Asp.Net Web Service).

Now, this data is supremely confidential, and I need to have it stored in an encrypted form in the database.

So, I am wondering what the best practices are around this. I know that there is some encryption capabilities that SQL Server has in-built. Is there a 'for dummies' type of resource for this so that I can quickly get going.

Alternatively I was thinking that I could encrypt/decrypt in my C# code and not in the database - maybe have a layer which handles this just above the data access layer (is that a good idea)?

Answer 1

Look at this link for a good introduction with samples.

I think doing the data encryption in the application is better, because in that case the transferred data is already encrypted. Otherwise you have to use a secure channel between your app and the database server.

It depends on your needs, i would say.

Answer 2

Have you considered encrypting your data at the file-system level?

It's Windows 2008/Vista only, but it should give you what you need and it's what it's designed for.

Answer 3

Before you decide on an encryption method, you need to access what parts of the system are vulnerable. If the potential for unauthorized access to the database exists, does the same threat exist for your application? Someone could run your code through Reflector and determine what methods were being used to encrypt and decrypt. You can mitigate that exposure to some extent with the code obsfucators. If that concern is not a risk, then you may find it easier to encrypt your data at the application level.