SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Administration (2000)
 Connection Chaos
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

elwoos
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 12/13/2004 :  05:19:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 12/13/2004 :  05:23:26  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 12/13/2004 :  05:26:48  Show Profile  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 12/13/2004 :  05:53:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 12/13/2004 :  06:04:30  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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

United Kingdom
22431 Posts

Posted - 12/13/2004 :  10:52:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 12/14/2004 :  05:39:10  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 12/14/2004 :  22:01:40  Show Profile  Send jen a Yahoo! Message  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2050 Posts

Posted - 12/15/2004 :  04:10:40  Show Profile  Reply with Quote
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

Edited by - elwoos on 12/15/2004 04:25:42
Go to Top of Page

robvolk
Most Valuable Yak

USA
15688 Posts

Posted - 12/15/2004 :  08:14:34  Show Profile  Visit robvolk's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

Sweden
4110 Posts

Posted - 12/15/2004 :  22:09:53  Show Profile  Send jen a Yahoo! Message  Reply with Quote
that sounds like a game plan, i like it, simple yet effective

--------------------
keeping it simple...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000