July 21, 2016 at 4:47 am
Hi there,
Different question from me this time.
We have someone who is using an application (RStudio) and hammered our server so much with the querying that it filled the Tempdb (and that is the first time it has EVER happened).
So we would like to block them using this method. I have looked at a trigger, what we were originally going to do was set one up to block anything ODBC connection wise but I brought up that actually we have several processes that use ODBC so it would block us too.
I have seen triggers that chuck people off when they aren't connecting via certain methods, but not one to block certain applications. We aren't using Windows Firewall so we can't use this method.
My idea is this trigger:
CREATE TRIGGER trgUnauthApp
ON ALL SERVER
FOR LOGON
AS
IF APP_NAME() LIKE ('RStudio%')
BEGIN ROLLBACK
END
Not sure if this is too simple? I imagine what will happen is they will find another way in, but we will obviously tackle that.
This is read only access, so they are not writing anything to the DBs.
Thanks for assistance in advance. Karen
July 21, 2016 at 4:54 am
It'll do the job, but app names are spoofable.
But the thing is, this is a non-technical problem. Address it as a non-technical problem. Someone is doing something they shouldn't, probably out of ignorance. Rather than blocking them (which can just lead that person to complain to their manager that you're preventing them from doing their work), speak to them. Maybe they should be using your reporting server rather than production for their data analysis. Maybe they can make the query more efficient if someone helps them with that, etc, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 21, 2016 at 6:03 am
Gail is right - address the root cause of the problem, don't try to cover it up.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 21, 2016 at 7:07 am
Not going to argue with you (definitely preaching to the choir), sadly some of these people go rogue.
Thanks for the input, I had to put my DBA head back on for this one. My concern is they will just go and use another application, which doesn't get to the route of the issue.
July 21, 2016 at 1:14 pm
Hopefully their new app won't do dumb things. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
July 21, 2016 at 1:22 pm
I doubt it's anything to do with the app. RStudio doesn't have query designer features, R (the language) just connects to SQL using ODBC and passes over whatever query the user wrote/generated
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 22, 2016 at 3:21 am
GilaMonster (7/21/2016)
I doubt it's anything to do with the app. RStudio doesn't have query designer features, R (the language) just connects to SQL using ODBC and passes over whatever query the user wrote/generated
I suspect this is the issue, I have done some more analysis and their queries are taking a hefty amount of CPU and disk IO. Will go and have a chat with them, hence why I think them using another app will not get to the crux of the problem.
July 22, 2016 at 7:34 am
Kazmerelda (7/22/2016)
GilaMonster (7/21/2016)
I doubt it's anything to do with the app. RStudio doesn't have query designer features, R (the language) just connects to SQL using ODBC and passes over whatever query the user wrote/generatedI suspect this is the issue, I have done some more analysis and their queries are taking a hefty amount of CPU and disk IO. Will go and have a chat with them, hence why I think them using another app will not get to the crux of the problem.
You may be missing the point of the earlier advice. It's not "give them another app." It's give them the training they need to not kill your server.
Maybe they need to attend T-SQL courses. Maybe they have one or two projects they're trying to get answers for but have no idea how to write the queries and once they have their answers, they'll never bug the system again.
Find out what this person thinks (s)he is doing. What result (s)he is looking for. Then address it on a very basic level. Is this person not even supposed to have access like this? Is this person over his/her head in their job? Maybe (s)he is a power user (I have 1+ of those at my workplace) and needs to be redirected to a non-OLTP version of the database or a non-production version.
Now there's a thought. You could restore production down to non-prod, giving this person a db they can dink around with AND test your backups at the same time. Win-Win. Assuming you've checked all the other issues first, though.
Anyway, don't treat this as "give them another app" issue. Treat this as a "what does this person need to make both our lives easier" moment.
July 22, 2016 at 7:45 am
Brandie Tarvin (7/22/2016)
Kazmerelda (7/22/2016)
GilaMonster (7/21/2016)
I doubt it's anything to do with the app. RStudio doesn't have query designer features, R (the language) just connects to SQL using ODBC and passes over whatever query the user wrote/generatedI suspect this is the issue, I have done some more analysis and their queries are taking a hefty amount of CPU and disk IO. Will go and have a chat with them, hence why I think them using another app will not get to the crux of the problem.
You may be missing the point of the earlier advice. It's not "give them another app." It's give them the training they need to not kill your server.
Maybe they need to attend T-SQL courses. Maybe they have one or two projects they're trying to get answers for but have no idea how to write the queries and once they have their answers, they'll never bug the system again.
Find out what this person thinks (s)he is doing. What result (s)he is looking for. Then address it on a very basic level. Is this person not even supposed to have access like this? Is this person over his/her head in their job? Maybe (s)he is a power user (I have 1+ of those at my workplace) and needs to be redirected to a non-OLTP version of the database or a non-production version.
Now there's a thought. You could restore production down to non-prod, giving this person a db they can dink around with AND test your backups at the same time. Win-Win. Assuming you've checked all the other issues first, though.
Anyway, don't treat this as "give them another app" issue. Treat this as a "what does this person need to make both our lives easier" moment.
Hey Brandie,
Not disagreeing with you at all, perhaps it's not coming across that I do agree with what you are all saying, and I don't believe another app is going to help/blocking this access. I have examined all methods they are querying (they have MS as well), so yep will most definitely be talking to them about what they are doing and offer assistance where needed. It sounds like (as I am yet to chat to this person myself) that they are fairly new to this, we all had to start somewhere :).
And already one step ahead on a Dev environment which I am currently rebuilding and I have advocated they go there versus our prod server.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply