I'm working in a project, where the client is afraid their own employees (ie.: junior I.T. guys who the client does not trust) querying the database to get sensitive information.
The database is a new SQL Server database, so I have some kind of freedom.
Googling a bit, I landed here at SQL Server Transparent Data Encryption, and wondered if that what I need, and if it is not…
What are the best practices to encrypt all the columns, in all the tables, in a database in order to prevent users for querying the database?
Best Answer
Your problem boils down to access control.
The first defense I'd propose is to simply deny access to the untrusted users. If they can't get into the database, they can't query the database and get at the sensitive data.
If they must be allowed to access the database server, you can look at either explicitly granting them read permission to the tables they must access to perform their job tasks. Alternatively, you can leave their current permissions alone and simply revoke their ability to query tables or columns within those tables with sensitive data.
The reasons I'd start with this approach is that there is overhead to encryption. There is key management as well as the CPU cost of encrypting/decrypting the data. As I understand it, there's also going to be a performance cost of looking up data as the encrypted data if it's in an index, the encrypted value will be indexed and not the source value. The net result is that 2013-06-11 and 2013-06-12 could have been stored in contiguous disk locations but once they're encrypted they could be on opposite ends of the disk and your simple range query which used to perform well now sucks hind teat.
All of that said, Steve Jones had a good presentation on the Encryption Primer. I used some of that content plus this article on encryption in the DB but not in the cube to get our encryption stuff up and running.
As others have pointed out in the comments and other answers, despite having Encryption as part of its name, TDE, isn't going to protect the data in the database itself. The purpose of TDE is to protect your backups from unauthorized access. It has nothing to do with encrypted packets flowing across the network or automagically creating PCI compliance. It simply ensures that if our offsite backup tapes are lost, stolen or simply snooped, people will not be able to use them without the key.
The other point to access control that I failed to mention is physical access. If the concern is the junior admin accessing data they shouldn't, then in addition to denying them access to the database, don't overlook basics of preventing them from accessing the machine itself---either through remote desktop or physically logging onto the machine. Once they are able to get onto the box, there's nothing preventing them from restarting SQL Server into single user mode and removing your access controls.