How to prevent users from connecting to DB using MS-Access?
-
Using SQL2K.
We've discovered, to our chagrin, that our users are connected to our production server and databases using MS-Access, and integrated security.
How can this be prevented?
Thanks,
Paul
-
If they have rights to login to the server and access to the database there is no way I know of to keep them from using any ODBC/OLE DB aware product to access the data. You may be able to use a firewall to block those applications, but a savvy user will know how to change the application name in the connection string.
This is why I recommend only granting access to stored procedures and views (and I prefer not to allow direct view access) and not base tables. This flies in the face of the ORM crowd.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
stating the obvious, you've got to disable their access.
Here's how:
from Enterprise Manager, right click on the server, and expand the "Security Folder" so you can see "Logins"
You might see BUILTIN\Administrators there for example...if you right click and go to properties, the first page will show two option buttons...Grant access and Deny access....swich it to deny.
repeat for any logins that come from the domain...they have slashes in their names.
after that, you can add and restrict other logins as you see fit.
I assume that one specific app is used to connect...create an application role for the app and use that to connect instead.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks for the quick replies.
OK, more info:
These same users are members of an Active Directory group (IMS_Live), which is granted DB access.
They are to use only one application (IMS) to connect to the server and DB.
So, are you saying, create the application role for IMS, and only allow that to connect to the server, and revoke the DB access from the IMS_Live AD group?
P
-
I always forget about Application Roles. Probably a better a way to go if you can change the application to use it.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
with an application role, the end users will not know or need the password....the IMS application would login using that instead of passing their Windows credentials...Like Jack said, this depends on being able to change the ConnectionString of your default application. once that is done, disabling the AD groups going to prevent the users from connecting with anything else.
you might create a readonly group that has access for the few people that legitimately need to hit the database outside of your app (report writers?)., but read onyl access wouldn't be a bad thing in my opinion.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
And furthermore, assuming we can switch to app.sec., can we still determine their login name (SUSER_SNAME()) at the server?'
The auditing requires that we know who's done what.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply