How can I prevent MS Access from connecting to my MS SQL Server database?

Question

MS Access causes many performance problems when it is used to connect to SQL Server. How can I prevent MS Access from connecting to my MS SQL Server database?

Doesn't look as though APP_NAME() gets passed into logon triggers — Martin Smith
– Martin Smith, Commented Mar 28, 2011 at 13:27
What specific performance problems does Access allegedly cause? — David-W-Fenton
– David-W-Fenton, Commented Mar 30, 2011 at 21:26
David, the users are using Access to run reports against our transaction DB. They should only run reports against the replicated reporting DB. — Tarzan
– Tarzan, Commented Apr 1, 2011 at 21:59

HLGEM · Accepted Answer · 2011-03-28 13:23:39Z

3

Take away the rights to the user it connects as.

answered Mar 28, 2011 at 13:23

HLGEM

97k15 gold badges120 silver badges191 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tarzan Over a year ago

I love simple solution. Thanks, HLGEM!

Community · Accepted Answer · 2017-05-23 12:11:53Z

2

You have users with Access who also have rights to SQL Server? But you'd prefer they didn't use that tool?

You could look in dm_exec_sessions in a logon trigger and stop users whose program name is whatever Access sends: Is it possible to deny access to SQL Server from specific programs?

This can work, but isn't proof against a savvy or malicious user, since the Program Name is just provided when a connection is made.

Ideally, you'd want to restrict their rights so that whatever objects they do have access to, there isn't very much possibility for them to misuse them. i.e. no direct table access, any views don't select over a "for all time" set of data etc.

edited May 23, 2017 at 12:11

CommunityBot

11 silver badge

answered Mar 28, 2011 at 14:09

Cade Roux

90.1k42 gold badges186 silver badges268 bronze badges

Comments

reiniero · Accepted Answer · 2011-03-29 12:59:03Z

1

Agreed with Cade Roux. If you take away the possibility to use Access, they might use the MS query tool or Excel... It's better to restrict their rights to data or otherwise tackle the root cause of your problems (user education? lack of better tools?) than just restricting use of Access...

You could use SQL Server tracing to track down users running e.g. long running queries and suggest alternatives. Or educate them. Gently ;)

answered Mar 29, 2011 at 12:59

reiniero

4286 silver badges14 bronze badges

Collectives™ on Stack Overflow

How can I prevent MS Access from connecting to my MS SQL Server database?

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related