A good way to encrypt database fields?

Question

I've been asked to encrypt various db fields within the db.

Problem is that these fields need be decrypted after being read.

---

I'm using Django and SQL Server 2005.

Any good ideas?

Answer 1

See: Using Symmetric Encryption in a SQL Server 2005 Database

Answer 2

Yeah. Tell whoever told you to get real. Makes no / little sense. If it is about the stored values - enterprise edition 2008 can store encrypted DB files.

Otherwise, if you really need to (with all disadvantages) just encrypt them and store them as byte fields.

Answer 3

I had the same problem, and created the following solution: http://djangosnippets.org/snippets/2489/

I happened to use M2Crypto as the cipher engine, but that can be swapped out if desired.

As TomTom notes, doing this just raises the bar for an attacker rather than making hostile decryption impossible - in addition to accessing your database, they now also need to access wherever you store the passphrase that feeds into the key derivation function. However, by splitting the key from the data it is protecting in this way, you at least now have the option to further secure that key (e.g. with a key management server) to raise the bar yet higher. Defence in depth is a good strategy, but you also need to decide what constitutues overkill for a given application.

It's also a terrible idea to encrypt any field that might be useful for searching or sorting purposes (I only use this trick to store OAuth credentials for a web service that doesn't support proper tokenised OAuth connections).