Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite
1

I'm not a DBA but I've been asked to see if we can encrypt the data in sql server such that the admin, despite having access to SQL Server won't be able to see the data but application would still be able to query on it and read the data.

My limited knowledge of encryption tells me that if application encrypts the data then db won't be able to query it. If db encrypts the data then DBA can definitely read it.

So is there a way to prevent DBA from peeking into data but still be able to manage database?

share|improve this question
Only encryption from the application side will help you do that. In the database you will have to save only binary data in the appropriate tables/columns and use whatever encryption method will suit your style. – Marian Apr 25 '11 at 14:17

2 Answers

up vote 4 down vote accepted

You will need to encrypt the data within the application and store the encrypted values within the database. Things like TDE and storage level encryption will not meet your requirements.

share|improve this answer
But we can't query on encrypted data so that would not be much help isn't it? – Hasan Khan Apr 25 '11 at 4:31
1  
It depends on what data you encrypt. You have to find a balance between protection and functional. If you want to prevent the admin from seeing the data the only way to do this would be to encrypt the data within the table at the application layer. – mrdenny♦ Apr 25 '11 at 5:27
up vote 3 down vote

I think what you are referring to is cell-level encryption, but using this feature requires changing the application. TDE (transparent database encryption) is another option, this encrypts the files on the disk, but they are decrypted in-memory, so anyone with direct access to the machine and sufficient privileges to look into the block buffer cache directly could access the data.

An alternative approach is to simply use the db_backupoperator role so the account the DBA uses can backup the database, but has no rights to look at the data. In this scenario, you would want to have the sa password written down and locked in a safe in case you ever do really need it.

Tho', as an aside, if you are worried about your DBA stealing your data, your problems are organizational, not technical. I work in a regulated industry myself and DBAs and SAs are carefully vetted (e.g. annual background checks) and we store data in the DB in BLOBs encrypted to which the application has the keys (and no-one has logins on both the machine with the data and the machine with the keys). However, it is possible to take this strategy too far. On one of my current projects, I need to use LDAP for authentication. There is some problem, but there is no-one who is allowed to log into both the DB box and the LDAP box, so diagnosing the issue is very difficult - which for the business means, expensive.

share|improve this answer
Hmm, downvoted why exactly? – Gaius Apr 25 '11 at 10:25
db_backupoperator != DBA. PS: I didn't vote now. PPS: I hate the first part, but I agree with second part :-). This is not an IT issue, if you don't trust your DBA with your data...this is wrong on too many levels. – Marian Apr 25 '11 at 14:18
That's correct. So if you are worried about DBAs, instead of using the sa account for everything, create accounts without only the specific privileges to do day-to-day DBA work like backups. Some sites call this "database operator" instead of "database administrator". – Gaius Apr 25 '11 at 14:26

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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