April 15, 2012 at 10:08 am
dear friends,
I need to let my customer to access data in sql database via only my application. I dont want any passwords to be observed by profiler or etc while my application connects. if possible I dont want them, even with management studio, see or manipulate data. import or export data nor schema.
I read a lot about encryption, users, etc.. but what do you suggest ? it seems that playing with user rights is best solution. but even server administrator must not see data nor change it. as you can imagine my application always should have access to data and the schema.
what do you suggest. I got lost while playing with user roles. can you tell necessary steps to make a user to access only one database ? looks like everybody knows sql security very well but I'm new to it. I never had problems about security until now.
best regards...
April 15, 2012 at 11:03 am
About the only way to ensure that the server admin can't access the data is to encrypt the data outside of the database (in the application). Any database encryption (other than encryptbypassphrase) is dependant on the SQL security hierarchy and hence the sysadmin will always be able to decrypt data.
Similarly, you cannot keep a sysadmin out of the database, nor can you prevent a sysadmin from seeing or changing schema.
If this is an app for clients, the only way to keep the DB structure secure is to host the DB on your own servers. If you give the database to a 3rd party you are giving them full control.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 15, 2012 at 11:24 am
dear friends,
what about if I create a user, disable windows users privileges and not allow them to login sql server, disable sa and only give owner privileges to my new account ? imagine in users list, there's only one user. no one would login with to sql server with windows accounts.
also if I enable database encryption someone cannot restore database to another computer, right ?
also may I ask if there exists a way that can do the followings :
- with management studio users can change schema, no problem
- insert, update, delete as usual, no problem
- but when execute sql statements, some critical columns like customer name, product name would be shown as encrypted.
- I would use "INSERT INTO tablename (productname,...) VALUES ('screwdriver',...) and sql server automatically encrypt column productname and store into database.
- only with my application I should run proper queries.
I tried some samples about column encryption but I could not find howto get query without changing my application's queries (which are too much to change. also changing queries means testing them. especially views are so many)
I need something like "send password or key, execute sql and get the decrypted results". if "I don't send proper password first, then encrypted data returns to me".
is that possible ?
April 15, 2012 at 11:30 am
Column encryption requires changing the application. No alternatives, and column encryption will not protect against someone who has sa rights.
You cannot keep a windows admin out of SQL Server. You can slow them down (which is what your steps will do), but that's all. If someone wants in, they will get in. Also, who's going to do all the maintenance? Backups, consistency checks, index rebuilds, etc?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 16, 2012 at 5:05 pm
Unless you are talking SQL Server 2000, if I am a Windows admin I can get right in by putting SQL Server in single-user mode from the command line. If you are talking SQL Server 2000, as a Windows admin then I can hack the service account and password in less than 10 seconds using a readily available tool and log in as that. So you can't keep them out. If you do, in less than 5 minutes they are in if they know how to use Google.
That gets back to what Gail is talking about with respect to encryption. If you want to protect it such that an admin on the SQL Server cannot see the data unencrypted, then you must encrypt at the application level. Otherwise, I can use a debugger to see the unencrypted data in memory. This is known. With that said, if I have admin rights over the application server, I can see the data unencrypted on that server. At some point you're going to have to trust someone.
If you can't trust your customers, then you're going to have to go a solution you host yourself. This, by the way, rules out cloud solutions like Azure, BTW.
K. Brian Kelley
@kbriankelley
April 17, 2012 at 1:28 am
thanks for answering my question.
here's the real situation... my customer is pharmaceutical company and recently had a foreign partner who has IT department in india. these companies are very sensitive about their own data. they want me to protect data and let this partner access data only via appliciation, even they got backup files or remote access to server.
so, as I understand the only solution is use column base encryption to hide data from eyes and change application level codes.
but this means, any IT department guy can learn critical data about company right ? project details, salaries, anything. in the era of sql2012 this seems me some kind of strange. customer or programmer should have chance to choose "hide data from eyes" somehow.
anyway, thanks for your posts.
April 17, 2012 at 4:19 am
aykut canturk (4/17/2012)
so, as I understand the only solution is use column base encryption to hide data from eyes and change application level codes.
If you mean SQL-based column encryption, that's not a solution. Read Brian's solution again
but this means, any IT department guy can learn critical data about company right ?
No, not any IT guy, just the trusted administrators (who should only have that permission if they are trusted not to snoop). It is possible to prevent even them looking at the data, as both Brian and I said, encrypt in the application, make sure that the outsource company has no access at all to the application server and make sure you store the key securely where they have no access.
Also, if you don't trust an outsourced IT company, then your IT should not be outsourced to that company, it's as simple as that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 17, 2012 at 8:46 am
+1 to everything Gail said.
K. Brian Kelley
@kbriankelley
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply