Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We just purchased a program that requires the users to have an account in the MS SQL server, with read/write access to the program's database.

My concern is that since these users will now have write access to the database, they could directly connect to the SQL server outside of the program's client and then mess with the data directly in the tables.

Is there anyway I can prevent access to the database while still allowing access via the client program?

Edit : SQL 2008 Express, can upgrade to SQL 2008 R2 Standard if needed.

Every workstation will need access due for people to log their hours/schedule. The workstations are locked down, so no one has osql, studio manager or anything like that which. However they could setup an ODBC datasource and then connect via Excel/Access.

Just thinking about this now, messing with the data is not the bigger concern anymore, there are privacy issues since everyone's rates of pay, etc will be in this system.

I agree it's a very poor design.

share|improve this question
1  
Which version of SQL Server? –  squillman Apr 12 '12 at 21:00
1  
You had better make sure you have binary logging turned on (or whatever MS calls it in MSSQL world). –  EEAA Apr 12 '12 at 21:12
4  
Can you get your money back? This sounds like a terribly-designed application. –  Joel E Salas Apr 12 '12 at 22:42
    
I wish I could get the money back (it wasn't cheap either). It was purchased without any consultation from me, but it's going to be a business critial piece of software for us. Payrole & Scheduling, hence the need for the security. –  me2011 Apr 13 '12 at 14:09
    
Your payroll/scheduling app requires every user to have write access to the database? That is the -opposite- of security. –  gparent Apr 13 '12 at 14:20
show 1 more comment

2 Answers

up vote 3 down vote accepted

No. If the users have read/write access to the database and they are able to connect to it not using the program, they could do something like UPDATE sometable SET attribute = NULL; and destroy your dataset, or make any arbitrary changes they want.

Unfortunately, SQL permissions do not have the capability to express the concept of normal vs. malicious changes made by people who otherwise have access, and I suspect denying them permission to update records would be somewhat self-defeating.

Much like Joel's comment, I'd be asking for a refund if this is a concern in your environment. Keep frequent backups and logs ;)

If you have a way to prevent logins not using the application (eg. by restricting connections to a single source, if your application runs through terminal services or citrix), you could definitely use that to improve security.

share|improve this answer
    
That's what I was figuring, thanks for the confirming it.....I'm wondering if I published the application in our Windows Terminal server, if it would still connect to the DB correctly. Then I could limit the connection to only the terminal server. If that works, it will mean we have to buy another bunch of terminal server licenses but for the security it would be worth it. –  me2011 Apr 13 '12 at 14:18
add comment

I'm a little late on this, and I agree that you should point the blamethrower at the application's developers and I'd put my concerns in writing and get someone with political power to understand the risks, but I hope to give you some more options.

If I were really stuck, I would look into creating a trigger for the LOGON event. In the trigger, I would find a way to discriminate between what I'll call a "legitimate" logon and an "illegitimate" logon and stop the illegitimate ones from completing. Legitimate logons would be users connecting to the proper database with the proper application, plus any administrative logins, job logins, etc. that you might need. I'd be very careful when writing this, since it seems like a good way to lock yourself out of the server. BOL says that LOGON TRIGGERS are available in SQL 2008, I'm pretty sure that they are available in Express.

The problem with this tactic is that you might find yourself playing "whack-a-mole", where you exclude Excel and Access, then someone figures out how to write a quick vb.net application that lets them get in, then you block that, then someone modifies the connection string to change the application name, etc. The more knowledgeable your users are, the more difficult this would be to stop them. If you've got developers, they might see it as a challenge. I'd say that anyone who is aggressively trying to find a way around security controls, even if those controls aren't perfect) is a problem. (If I lock the screen door on my home, it's pretty obvious that I don't want anyone to come in. If someone uses a pocket knife to slash the screen and step in, they have certainly done something wrong.)

Another thing to do is to simply run a query on the DMVs to find users who are not playing by the rules. You can get user, host and application name information from the system DMVs. If you run the query periodically (once a minute or so) and save the results in a table, you can have a look every day (or week) and then go rap the bad actors on the knuckles. Or have HR do it.

Another thing to try is, if you have anything that looks for and reports on long-running queries, you can look for "weird" queries. I actually caught someone doing something once, while reviewing logs for problem queries. Often, inexperienced users that are poking around will run inefficient queries that either read a lot of data or cause long-duration blocks. Sometimes, if the application has a certain, definite "style" to the way it write queries, you can pick out queries that are written by someone (or something) else. IOW, to use a very contrived example, there is a big difference between:

select * from salary where employee = "me"

and

select * from salary order by salary_amount

To sum up: Fixing the application would be best. Preventing logins might be OK. Trying to find violators after-the-fact might be all you can do.

share|improve this answer
    
I like the idea of DMVs, I'll have to look more into that. Any futher information you can quickly expand on for that? Haven't Googled it yet :) I'm going to do a call with the vendor next week and walk them though how easy it is to get in as a normal user and then tell them they need to fix this and I'm recommending against deployment until then. It will still be deployed but at least the issues will be down in writing. –  me2011 Apr 13 '12 at 15:20
    
+1, this a good solution to a bad problem. –  Jason Cumberland Apr 13 '12 at 17:52
    
The TS solution will help prevent the unauthorized disclosure or damage from occurring in the first place, though. I'd actually recommend both that and auditing, but this isn't an especially novel recommendation (eg. you should be able to audit no matter what). –  Falcon Momot Apr 13 '12 at 18:59
    
You could schedule a job to run a query like "select session_id, login_time, host_name, program_name,login_name from master.sys.dm_exec_sessions where session_id > 50" and store the results in a table, somewhere. I would tweak the WHERE clause to eliminate more "known good" things, like program_name like 'Microsoft SQL Server Management Studio%' first. This is easier to handle if the client application is well-behaved and actually fills in the "program_name" filed when it creates the database connection. –  darin strait Apr 16 '12 at 14:02
add comment

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.