x

Encrypt data so that DBA cannot access it

Once again I have to prostrate myself before your collective intelligence.

Our company needs to create a database for the HR department. One of the fields will be salary that will be restricted to only one or two people. As the DBA has sysadmin permissions they can bypass most encryption. Is there an encryption scheme that would meet my design needs? A brief outline would be fine, I can flesh out the details.

P.S. Before you ask I did consider a scheme that would let me have a little peek at the juicy information but I am too honest for that

Many thanks

more ▼

asked Mar 02, 2012 at 11:59 AM in Default

half fast dba gravatar image

half fast dba
152 8 9 10

(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

Someone, somewhere needs to hold the keys to the fort.

If you need to encrypt data in such a way that the person responsible for the data has no way of breaking it, then that suggests a lack of trust. And if you don't trust your DBA, then either the wrong people have DBA rights, or the DBA is in the wrong job.

Lets assume your application encrypts before it gets to the database - then all is good, the DBA has no access to the encryption algorithm, but the application developers do.

more ▼

answered Mar 02, 2012 at 12:16 PM

Kev Riley gravatar image

Kev Riley ♦♦
53k 47 49 76

This is exactly why we have a 3rd party maintain our salary information.
Mar 02, 2012 at 07:35 PM KenJ
+1 Thats one way of doing it.
Mar 02, 2012 at 07:38 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left

I have to agree with Kev, someone will hold the keys to the fort.

However, there are ways to make that chosen someone neither the DBA nor the application designer. The application designer obviously knows the algorithm that is being used, but that does not necessarily mean they know the decryption key. So, your application could be set to encrypt all data going into that field with a key supplied by the HR person and the data is then meaningless to everyone including both the application designer and the DBA without that key.

As Kev Pointed out, TDE will not do what you want. But, set up properly, cell level encryption can do it. Technet has a brief overview here: [Database Encryption in SQL Server 2008 Enterprise Edition][1] However setting up cell level encryption can be a bit tricky, managing it even harder.

So, what you are asking for can be done and in a couple of ways, but it comes with a laundry list of caveats:

  1. Someone with the right permissions can probably still bypass the encryption indirectly, it just takes more work. Someone with access to the system could install a key-logger, this would let them record every keystroke put in, to include the password when HR puts it in. (Note there are some ways to make a keylogger less effective, but they are difficult and way beyond the scope of the question).
  2. You still need to trust the application developer and/or DBA to set things up right. It is easy to deliberately make encryption worthless (the DBA could have a hidden trigger that also puts an unenctrypted copy of all data going through cell level data to an unencrypted column, the application developer could have it secretly email him the data as HR puts it in, etc...)
  3. Even if they don't deliberately put a backdoor in the software, encryption is hard to do right so they could easily make a mistake that make the encryption relatively easy to break.
  4. You of course have to trust the end user to handle the security right. Most security breaches in the real world are achieved through social engineering, not technical skill. For instance, many users will hand over their passwords just for the asking. Many users will write the password down and leave it at their desk.
  5. And finally, encryption always comes with a performance price
[1]: http://msdn.microsoft.com/en-us/library/cc278098(v=sql.100).aspx#_Toc189384682
more ▼

answered Mar 02, 2012 at 05:47 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

(comments are locked)
10|1200 characters needed characters left

I agree with Kev Riley. At the end there would always be a backdoor for someone. Either at application level OR at Database level. At the best you can do is to encrypt the field with a password protected key that is unknown to DBAs. But that can be easily sniffed while the application is encrypting/decrypting it. That seems to be more dangerous as more persons could get involved in it.

There are other options like Encryption File System and BIT Locker, but that are of bigger magnitude and hard to handled. Besides, there could still be some gothas like backup/restore, detach/attach etc. Although TDE is there, but you would find yourself in more hassle to control such environment.
more ▼

answered Mar 02, 2012 at 12:44 PM

Sacred Jewel gravatar image

Sacred Jewel
1.7k 2 4 5

(comments are locked)
10|1200 characters needed characters left
You can use TDE (Transparent Data Encryption) technique to do this.
more ▼

answered Mar 02, 2012 at 12:19 PM

HayathKhan gravatar image

HayathKhan
1

TDE encrypts data at the file level, i.e. data at rest. It only prevents someone from removing/copying the files and attempting to reattach them to another SQL instance that doesn't have the right certificate/key. It will not stop a DBA (or anyone with rights to read data) from reading the data in the tables on the 'correct' server.
Mar 02, 2012 at 12:28 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
There is a need for policy then technology.
more ▼

answered Mar 02, 2012 at 03:09 PM

inder gravatar image

inder
211 17 19 22

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x38

asked: Mar 02, 2012 at 11:59 AM

Seen: 1585 times

Last Updated: Mar 02, 2012 at 07:38 PM