Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Connection Chaos

Author  Topic 

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-12-13 : 05:19:13
The system I look after uses ODBC for the client app to connect to the SQL Server (7). Some users have realised this and are using Excel etc to connect and write queries. This is all done with Windows authentication to the server. I would like to prevent these people from accessing like this but am not sure if it is even possible.

Does anyone have any suggestions?

thanks

steve

To alcohol ! The cause of - and solution to - all of life's problems

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-13 : 05:23:26
ha ha ha, it is possible.

just had this scenario happening here (time records, edits and deletes), so what i did is, i placed a trigger on the table and disconnected the users using @@spid if they're not "authorized" to access the objects using programs other than the "authorized" program.

it's probably the same logic, so you'll need to monitor the sysprocesses table




--------------------
keeping it simple...
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-12-13 : 05:26:48
Ooooh, thanks Jen, I'll take a look at that :)


steve



To alcohol ! The cause of - and solution to - all of life's problems
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-12-13 : 05:53:35
But they'll only be connecting to the SProcs they have execute permission for, so what's the problem which App. they connect from?

<BigG>

Kristen
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-13 : 06:04:30
problem is, if the queries are not in sprocs, there are systems that are too large to convert or bother converting to or too much hassle to convert (too many dependecies),

i guess long term solution is edit the system steve and use sprocs and udfs like what Kristen is suggesting at.

in our case, programmers share user information to other programmers, so it's like trying to contain the fire before it spreads. By disconnecting them, they'll be discouraged to try again.

--------------------
keeping it simple...
Go to Top of Page

Kristen
Test

22859 Posts

Posted - 2004-12-13 : 10:52:43
All joking apart, chaps and chapesses, using SProcs and NOT granting db_datareader / db_datawriter is a huge boon - loads of wasted time ermmmm ... "not wasted" :-)

Kristen
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-12-14 : 05:39:10
Don't get me wrong here guys I agree with you absolutely (and appreciate the advice)

The system I have to babysit is NOT written by me. It is essentially a database that was originally converted from a hierarchical database system (didn't see anyone mention this one in my survey!) into MS Access. It was then pushed through the Access upsizing wizard to get it onto SQL Server. i.e. SQL Server is largely being used as a glorified version of Access - NO Security - in fact, if I tried to put some into place it would probably break the app or at best would deny legitimate access or break the support contract.

Essentially what I have is tables linked through to an MS Access front end. And that's it. Anyone can break into the app (deliberately or by accident) and have whatever access they like to the underlying data (I can hear the gasps of horror as I write).

And if you think that's bad, this thing isn't even in FIRST normal form never mind any other - now I know there are sometimes good reasons for denormalising but believe me they don't apply in this case. This supplier even wrote their own record locking system - the icing on the cake being - yes, you've guessed it - it requires user input to complete the process! And as for relationships - pah! who needs 'em.

Apologies, I'll try to stop ranting now. Anyway the bottom line is that because of the way the system is constructed pretty much anyone can access this data should they choose to and have a little knowledge. One of the local favourites is Excel, we also have a few arbitrary Access databases doing all sorts of things. I've run out of fingers and toes to plug these gaps with so have now made sure these users are trained in SQL and have become more familiar with Query Analyser. One of my problems now is making sure that they dont' turn it to point at the production database and that they desist from using Excel, Access etc which is what my question was about.

I suppose in an 'ideal' version of my world I would only like certain apps (in conjunction with certain users) to be able to access the production system. I'd like to be able to tell the Server that it was the main app that was accessing the data rather than any old Access file but am not sure how to - just to add to the joy we are using Access 97 and ODBC links (remember them anyone?)

Still, at least the network admins are quiet at the moment, though one of them did come to me the other day and announce that it had taken a week to sort out a problem with a Transaction log growing too large. He didn't look too happy when I said he should have asked me

steve

To alcohol ! The cause of - and solution to - all of life's problems
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-14 : 22:01:40
ouch...

you should try to migrate that server to a more secured state, if not normal. Rome was not built in one day, if they desist then you're not accountable.

In the end, they'll ask you and my guess is that they'll gonna blame you for not being proactive.

--------------------
keeping it simple...
Go to Top of Page

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-12-15 : 04:10:40
Jen

I can't disagree with you but it needs a lot of work from the supplier who refuses to acknowledge that there is a risk - and to be honest wouldn't have the knowledge to improve it much anyway. I've offered to advise him but he isn't interested. It's a one man and his dog outfit and he has a near monopoly - why should he change his system when most people who use it will not realise the rick and even if they do have little alternative. He only discovered that there are such things as Views and Stored Procedures when I mentioned them to him!

The consolation is that he is therefore culpable - I just need to keep reminding people here that they have been warned about this risk (and I have a trigger that logs significant changes to data)

thanks

steve

To alcohol ! The cause of - and solution to - all of life's problems
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-12-15 : 08:14:34
I hope you didn't tell people about the trigger...that way when they FUBAR something you can nod your head sagely saying "I f---ing told ya so! Let me see if I can actually rescue something..." and go off for a couple hours and surf the web, then just run your handy data recovery script and save the day!
Go to Top of Page

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-15 : 22:09:53
that sounds like a game plan, i like it, simple yet effective

--------------------
keeping it simple...
Go to Top of Page
   

- Advertisement -