Logging program access to SQL server

[Jusenkyo]

Hello all

I have a SQL server with about 10 databases on it, and around 200 users accessing them through MS Access front-ends.

A few of the users have worked out that they can create a new Access front end, create a link table to SQL server, and run their own queries/update tables from their PCs.

Is there anyway to record if someone is accessing tables on the server outside of the access front ends?

Cheers
J

 

[Corran007]

Accessing sqlserver is accessing sqlserver. its up to the client to ensure things like this dont happen. if a user has select access to table x, then they can do whatever select they want on it. This is one reason why some dont give direct table access to anything. they only give access to views or stored procedures.

Views might be your answer. i dont know access but i think it can link to views. if so, create views for each select statment they need, grant access to those views, then revoke access to the tables. that way they could still write queries, but they would be limited to what was in the views.

 

[Jusenkyo]

The problem that we have is that we want to allow people to access and change the data, but only through MS Access. Which is obviously a very difficult task, if we have to change the way things are currently running.

So my idea is to find some way of recording every access to the sql server, the program that is doing it, and how they are doing it.

Theres got to be a way!

 

[Corran007]

you can record who connects in via security auditing. but that will not tell you the program doing it or how. SQL server does not care what program. It just accepts commands from valid users.

 

[Jusenkyo]

So is there any program we can fit onto the server that will do this?

 

[JamesLean]

1) Check out the Current Activity window in EM - there is an Application column that shows the application the process has come from.

2) However, you say that your app is built in Access and that your users are using Access manually to connect and query the server. If so, it's going to be hard to differentiate between the two as they will both have Access in the application column!

--James

 

[SQLSister]

Take Access off the users computers and give them only a run time version of the programs they need.

Do all queries in the offical Access front end through stored procedures. Give permissions only to the stored procedures and not to the base tables. THen if they connect elsewhere they can't do anything!

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[Corran007]

1. they proably use access for other things so it proably wouldnt work

2. would proably work but when i sugested view, he said "The problem that we have is that we want to allow people to access and change the data, but only through MS Access. Which is obviously a very difficult task, if we have to change the way things are currently running."

It sounds like he needs something without changing the current way things are done. but i dont know of anything. The problem comes from the way things are currently done.

Jusenkyo, they only way to get the security you want will be to rewrite it so the users do not have direct table access only through stored procedures or views. Which would require a rewrite of the front end.

 

[Jusenkyo]

Cheers for that Corran.

I have already had ideas about changing the way we do things currently to only allow access to views and sp's. The problem is, we have alot of pure access databases with links to the SQL server, that would be very time consuming to rewrite.
So I thought there might be a way to leave things as they are, as you pointed out, and just record what was accessing what.

I thought that someone must have a program somewhere to do this?!

Never mind, back to the drawing board.
Cheers anyway!

 

[Jusenkyo]

Ive had a thought.

What about if we modify the connection string in our access front ends, so that they are different from the one you would get if just linking to SQL server manually.
If we could capture these strings, we could find out which are incorrect?

Ok, thought over.