I am considering encrypting my database and/or encrypting only certain columns in a few tables. Is it worth the time? I mean, how much of a burden would it be on someone if they were to get a hold of my encrypted database? Has anyone even found a way to break this?
If you are interested in using column level encryption in SQL Server 2005 and higher I have a bunch of sample code of how to use the built in encryption features in SQL Server to secure sensitive data. The code is all available at http://sqlcrypto.codeplex.com/
In the code I show how to encrypt sensitive data and ensure that only authorized users can decrypt it. I take advantage of the built in key management functionality and have strong randomly generated symmetric AES key managed by the database engine and those AES keys are then secured with certificates. Database roles control who has access to which keys and with the built in SQL Server encryption you can even use passphrases to additionally secure keys or certificates so that you don't even have to trust your DBA if the data is that highly sensitive. Having said that, if you don't trust your DBA then you have other problems :)
Since SQL Server does the right things with encryption (ensuring unique IVs) you cannot search encrypted columns without doing a table scan and decrypting every row to see if there is a match. I provide some sample code for building an HMAC of encrypted data so that you can at least do a basic search without leaking cleartext information.
I also have examples of how to store salted, stretched hashes of passwords using both the built in hashing algorithms (which are insufficient for production use) and a SQLCLR implementation of the bCrypt hashing algorithm for much better password storage.
There is also some sample code to show the use of transparent data encryption to encrypt the full contents of your data and log files on disk.
Some of the advantages to using the built in encryption are:
As mentioned, encrypted databases are computationally more expensive, not to mention the programming effort.
A few suggestions
And other more obvious vulnerabilities. Hardening your outer shell seems like a more economical investment.
Encryption is a potentially valuable subsequent line of defense / liability reduction.
and you may find you are missing something, like
But you should still make a best effort.
Ideally, your computers would only give out what is permitted, and encryption of storage would be pointless. This is rarely the case.
Note that this answer only focuses on direct hacking, and not physical access, or access through one of your sysadmin's PCs.
Cryptography is often not the problem. In most cases encrypting the entire database is almost certainly a waste of time.
There are often very important values that should be encrypted. An a value that is often overlooked is the session id, csrf tokens, the password salt and forgotten password token. If you are foolish enough to store your session id in the database then an hacker with a SQL Injection flaw can pull this value out and login without needing to crack a password hash.
As other users already said, encrypting the whole database is probably overkill, meaning that you would lose too much performance while gaining little extra security, since you would encrypt fields which is not necessary to encrypt.
Think about it this way: imagine that an attacker has managed to access all the data in your database thanks to an SQL injection. Which information should the attacker absolutely not see and which information is instead not so important, meaning that even if he could read it, he couldn't do anything with it? An example of the first type would be the users' passwords, and one of the many examples of the second type could be the user's favourite color, if for some reason you need to store this information in the database :D
So, encrypting the whole database is not worth the time and the performance loss, but there are some fields that absolutely need to be encrypted (passwords, credit card numbers, SSNs, session IDs and you could also add personal user information to this list, like phone numbers etc, your users would be grateful for this :) ).
Don't forget that if the attacker has access to your database, he could eventually get access to all the information in it even if some fields have been encrypted, he just needs more time to do it. And if you have very important information in your database, this extra time is extremely important.
And finally a suggestion: invest time into preventing SQL injection attacks. This is probably the most used technique with which an attacker can illegitimately have access to your database.
It depends. Remember that attackers will always target the weakest security link in your system. Do you think unencrypted database data is the weakest link in your system? It seems somewhat unlikely. While it will certainly provide extra protection, your time is probably better spent protecting other parts of your system, that are more likely to be the security weakest link.
Also, keep in mind that encrypting a DB is generally pretty expensive, computationally speaking. If your DB serves a lot of data to a lot of clients, this could really slow down your system.
protected by Community♦ Oct 28 '14 at 20:07
Thank you for your interest in this question.
Because it has attracted low-quality answers, posting an answer now requires 10 reputation on this site.
Would you like to answer one of these unanswered questions instead?