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.
Best Answer
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.