7

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?

Improve this question
3
  • I have used the encryption, but the problem with it is the key? who hold it. you will have slight overhead on it. another way is to use views and only present the data they need to have.
    – user24878
    Jun 12, 2013 at 18:57
  • 6
    Is the concern that the "Junior IT guys" will have access to the underlying data files and will try to parse data from them? Or is the concern that they will log in to the SQL Server database and run queries? If the issue is running queries, why do these untrusted users have database accounts to begin with? Can you simply not create accounts for them? Or not grant their accounts privileges to query tables with sensitive information?
    – Justin Cave
    Jun 12, 2013 at 19:00
  • This question has been asked and answered a bunch of times at Security SE. In general, the answer is this is not recommended. You may be interested in Full Drive Encryption, though.
    – Deer Hunter
    Jun 12, 2013 at 20:02

3 Answers 3

Reset to default
17

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.

Improve this answer
5
  • 4
    In my experience, database servers usually have plenty of unused CPU, so the tradeoff of more CPU activity for encryption is acceptable. THe most important point, however, which is repeated in posts below, is that TDE only protects data while it is at rest on the hard disk. It is transparent to normal database operations. The correct answer to your problem is "access control" and "only hire people you trust".
    – Greenstone Walker
    Jun 12, 2013 at 22:40
  • 3
    @GreenstoneWalker Agreed on all points. One thing we must begin thinking about though is the CPU usage for SQL Server as from 2012 forward it has per core licensing so the more CPU intensive activities it is responsible for, the more it's going to cost.
    – billinkc
    Jun 13, 2013 at 2:07
  • good point. Another point, mentioned to me by a coworker, is that assuming a machine has CPU cycles to spare is a bad assumption in the age of virtualisation. VM guests will be given the cycles they need and no more.
    – Greenstone Walker
    Jun 13, 2013 at 22:50
  • 1
    "The purpose of TDE is to protect your backups from unauthorized access.": that's not entirely correct. TDE does encrypt all the data (MDF and LDF files). Backups are encrypted simply as a side effect of being a copy of an already encrypted MDF/LDF. Accidental loss of the MDF/LDF files is safe becuase TDE ensures they are unreadable w/o a key. The gist is that TDE will also transparently decrypt the files on access so the data is available if access to the instance is granted.
    – Remus Rusanu
    Jun 14, 2013 at 7:25
  • 1
    It basically helps in case someone steals the discs - without using full disc encryption (bitlocker) which would do the same (sans backup encryption). VERY limited use case, but when one needs it... one needs it.
    – TomTom
    Jun 14, 2013 at 10:48
11

The only way to use encryption to protect the data against your own administrators/IT is when the user enters the decryption password him/herself, every time it queries the data. If your application presents the user with a password dialog and then issues an OPEN SYMMETRIC KEY ... DECRYPTION BY CERTIFICATE ... WTIHT PASSWORD ... (or some equivalent) to open the data encryption key(s), then and only then the data is protected cryptographically against unauthorized access: even if someone gets access to the data, it does not know the decryption key nor can it retrieve the key from somewhere on the server.

Every other scheme (including TDE) involves the key hierarchy to be opened by the back end engine and this implies the key is stored somewhere along that hierarchy rooted in the DPAPI. In all these case you have data only access control protection (GRANT/DENY/REVOKE), not cryptographic protection, since the decryption key is accessible to SQL Server itself and it will decrypt the data for you. These schemes protect against media loss, not against access. And they are very valuable, and widely deployed, as such.

Now going back to the first paragraph, you must understand the caveats:

  • no secret can by hidden on a system against an authorized administrator. The bar can be raised, but an administrator can always get to the secret if he wants to.
  • using column level encryption makes the data unsearcheable. Encrypting all columns in all tables will make the data unqueryable, that's sure... I really mean unqueryable by anyone including authorized users
  • having an application ask the end user for the decryption password on every access is seldom feasible

Between these three there is very very little benefits left in deploying encryption like you're attempting to. What really boils does to is what other have said: access control and trust. And remember that audit is available and can server as a deterrent.

Improve this answer
6

It is totally NOT what you neeed.

Assuming the servers stay online it wont help because it is transparent - and the junior admins "querying the database" must be able to connect and issue a sql statement (file access is blocked as long as the db server runs) and that - means the encryption wont "work".

Transparent Encryption helps for someone walking out (stealing) the disc with the data file ;)

Keep the non-trusted junior IT guys out of IT - simple like that. Or at least out of the database (no access, no password they know).

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.