Sql server Encryption or security permissions

Question

Just started looking into encryption using keys and certificates in sql server 2005/08 and although it looks very good I'm not too sure why I should use it over sql server security permissioning.

For example, I have a table with sensitive data in, such as user-name/passwords. I can either encrypt the data using say ENCRYPTBYCERT, or simply leave as plain text and just apply permissions to the table for authorised users.

I won't be transferring this data over the internet, just accessing internally over the network.

Are there any other reasons to use encryption?

Answer 1

Well it provides more security if you encrypt at the DB level. It's tied to the machine for a start, so you can't (for example) backup the database, restore it somewhere else, and have at it. If you also encrypt the keys (in a web.config encrypted section for example), then you have just defended against several different potential attacks.

So if you were storing customer data (protected by privacy law), or CC data (obviously with due regard to PCI DSS), then encrypting it like this protects it, for example, from your own support staff.

And then... if you're assuming that your other security is rock solid, bad people will never be able to get access to the data, but if they did, and if it was encrypted, they're still getting nowhere.