Best way to encrypt certain fields in SQL Server 2008?

Question

I'm writing a .NET web app that will read and write information to a SQL Server 2008 database. Some of this information will be highly confidential in nature so I want to encrypt certain data elements.

I don't want to use TDE or any full-database encryption for performance reasons. My main concern is protecting this sensitive data as a last resort against a SQL injection or even a database server compromise.

What is the best way to do this to preserve performance? Is it faster to use the SQL Server 2008 encryption functions such as EncryptByKey, or would it be faster to encrypt and decrypt the data in the .NET web app itself? The app would be using a symmetric key stored in the secure web.config and store the encrypted values in the DB.

Answer 1

I have done data encryption inside web app using a third party library and I do think that it will be slower as compared to the one in SQL2008 EncryptByKey.

Answer 2

I have not used the database encryption, but my recommendation would do the encryption in the application level. Down the line if for whatever reasons your database needs change, the encryption logic will be in the application layer and not the database layer.

I am not exactly sure of the speeds between the two, but the maintenance of the project will be easier down the line if it is maintained in the application layer. As for encryption, this website is a good read for designing your encryption and decryption logic and salting.

Encrypt and Decrypt Data Using a Symmetric (Rijndael) Key

Answer 3

I've always followed the rule that if what you are trying to do is data related with no business rules then it should be done in the database, that way you don't have to worry about it again... for example if you were to write another application, you'd already have the encryption setup and not have to write any extra code. This include not needing to test again or introduce any new bugs.