How to prevent Excel users from connecting to SQL Server

Question

How to prevent Excel users from connecting to SQL Server

Mohit Nayyar

Ten Centuries

Points: 1008
More actions
October 11, 2004 at 12:27 am

#108931

How can I disable the application level users in MS SQL Server.
Like I don't want any users to connect to my SQL Server using MS Access or Excel. I cannot change the password etc. They are the administrators, so how can we implement this?
Mohit Nayyar

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com/
"If I am destined to fail, then I do have a purpose in my life, To fail my destiny"

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Steve Oberholzer SSC Enthusiast Points: 116 More actions · Answer 1

I'm sitting with a similar problem, but I think they are related. I've got users that connect helter skelter to the database using ISQLW. What I did notice was that when a user logs on, that information is stored in master.dbo.sysprocesses, and this is the table that the SP_WHO or SP_WHO2 stored procs query. It returns information such as WHO is logged in, from what WORKSTATION, what APPLICATION opened the connection, etc.

What I wanted to do was to write a TRIGGER on this table, but SQL Server doesn't allow me to do this. If you could somehow write a trigger on this table, you could pick up, WHO is connected, WHAT application opened the connection, etc. You could possibly also issue KILL statements to kill the SPIDs of the offending connections?

Steve Oberholzer SSC Enthusiast Points: 116 More actions · Answer 2

I'm sitting with a similar problem, but I think they are related. I've got users that connect helter skelter to the database using ISQLW. What I did notice was that when a user logs on, that information is stored in master.dbo.sysprocesses, and this is the table that the SP_WHO or SP_WHO2 stored procs query. It returns information such as WHO is logged in, from what WORKSTATION, what APPLICATION opened the connection, etc.

What I wanted to do was to write a TRIGGER on this table, but SQL Server doesn't allow me to do this. If you could somehow write a trigger on this table, you could pick up, WHO is connected, WHAT application opened the connection, etc. You could possibly also issue KILL statements to kill the SPIDs of the offending connections?

Mohit Nayyar Ten Centuries Points: 1008 More actions · Answer 3

Hi Steve

Ya, the solution is great, even I thought the same thing. But we should not play with system tables. I am still searching and let you know.

Otherwise we will go ahead with trigger.

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com/
"If I am destined to fail, then I do have a purpose in my life, To fail my destiny"

David.Poole SSC Guru Points: 76035 More actions · Answer 4

If your users are administrator then you have big problems.

If you want to block Access and Excel usage then what rights should they legitimately hold?

What logon are they using? Why not deny them rights within the database?

LinkedIn Profile

Mohit Nayyar Ten Centuries Points: 1008 More actions · Answer 5

Thanks David

They all are the administrator or in someway related to top level management. They use excel because of easyness but it creates lots of problem for production environment.

We don't want them to connect to SQL server using Access/Excel. Is it possible? I don't want to play with "sysProcesses".

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com/
"If I am destined to fail, then I do have a purpose in my life, To fail my destiny"

Chiefley1 SSC Enthusiast Points: 148 More actions · Answer 6

Mohit,

I feel your pain. This is not a technical problem at all. I think its a political one. One common solution to this problem is to highly restrict access to the production machine, but copy some or all of the production DB to another more accessible machine every night. The next day managers, etc, can mine the offline copy for whatever they need. DTS would be a good tool for this.

Chiefley

Gé Brander SSCarpal Tunnel Points: 4353 More actions · Answer 7

Go with application roles for connecting to the database from your applications. That way, only the applications are able to connect, and no other tool like Excel or Access.

It is a big change in developing applications that use databases, but it is a solution.

Mohit Nayyar Ten Centuries Points: 1008 More actions · Answer 8

Thanks guys

But We cannot go ahead with application roles as more than 10 different applications uses this db and they are legacy apps.

The DTS option is great, but what if we have more than 20 major tables with million rows each with 50-70 other required tables for management reporting.

Thanks a LOT.

Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com/
"If I am destined to fail, then I do have a purpose in my life, To fail my destiny"

Vladan SSC-Insane Points: 22072 More actions · Answer 9

I agree with Chiefley that it is a political problem... did you try to explain them, that using outside tools like Access and Excel can create serious problems? They should understand, that certain things are dangerous or at least able to create serious production problems, and should better be avoided.

Of course they must have something to work with, to help them make their decisions etc. - but not necessarily a production DB. On the other hand, they are not interested in each order (or other record) separately. What they need is overview. When we realized, how much various controlling dept. queries block production database, we had a serious talk with them. Result was, that we got enough material about their requirements to analyse the situation, and to create what they really needed - data warehouse, or whatever you want to call it. A few tables in another DB, which store monthly or weekly sums by various criteria (group by are rather complicated ones, so some of the "sum" tables have in fact 10000+ lines each month/week). Tables are updated every night with a job, are quick to query and hold almost everything they need. Since we added web interface, that allows them to create queries on these tables easily, we have no problems.

HTH, Vladan

robertk SSCommitted Points: 1608 More actions · Answer 10

Definitely a political problem. I work in an environment where the top level managers used to have network admin rights. After we explained to them the need to remove these rights, the fact that they can accidentally delete mission-critical files and data, they did not want to have this responsibility on their heads. Most of them complied without too much complaint.

Part of being a DBA is occasionally fighting these political battles. Find a political cure, not a technical one, it'll be your butt that gets fried in the long run if you don't.

Chiefley1 SSC Enthusiast Points: 148 More actions · Answer 11

Mohit,

I see your point about the volume of data and all the custom apps. Since all those apps are already written to use the schema of the production database, maybe DTS is unnecessary. You dont need any transformations, all you really need is a reasonably up to date copy of the DB on another machine. One way is to copy the nightly backup of the production DB to another machine each night and restore it. Another way is to ship the backups once a week and use one-way replication to keep the public machine up to date between the backups.

The first way is simple, the second keeps the data very current even during the day.

Chiefley