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?

Answer 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 ;)

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

Answer 3

Take away the rights to the user it connects as.