Blocking 3rd part app connectivity to SQL2005 server

Question

Blocking 3rd part app connectivity to SQL2005 server

Christopher Klein

SSCrazy

Points: 2648
More actions
August 5, 2008 at 1:13 pm

#382157

Hi there,
We are in the process of tightening down things and we have a number of users who will frequently use MSAccess to connect into our SQL databases. Mostly it is to write their own reports or in a few limited cases do direct data manipulation (which is why we want to change things).
Is there a tried and true method to keep everybody out of the system for direct data manipulation? I'm thinking that we remove all access except stored procedures, so the users are forced to go thru OUR code to do things.
thanks,
Chris

Viewing 5 posts - 1 through 4 (of 4 total)

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

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 1

You can write a logon trigger that checks the app signature. Only instead of checking for apps they're not supposed to be connecting with just check for yours and only allow them to connect if they're connecting with your app.

Of course you can't deny them access... I think you have to log them off or rollback if it doesn't meet the criteria, but the effect is the same. I've got some similar code written but it's back at the office. If you remind me tomorrow I'll dig it out for you.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Christopher Klein SSCrazy Points: 2648 More actions · Answer 2

if you have any code samples for those login triggers, that would be a good starting place I think.

thanks!

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 3

the best way is management

if this is policy than you should report every violation of it to the powers that be. the second best way is to use windows authentication to lock out anyone who is not supposed to be there

K. Brian Kelley SSC Guru Points: 114562 More actions · Answer 4

crever (8/5/2008)
You can write a logon trigger that checks the app signature. Only instead of checking for apps they're not supposed to be connecting with just check for yours and only allow them to connect if they're connecting with your app.
Of course you can't deny them access... I think you have to log them off or rollback if it doesn't meet the criteria, but the effect is the same. I've got some similar code written but it's back at the office. If you remind me tomorrow I'll dig it out for you.

You can rollback, but the app name can be forged using a File DSN. So if there's an organizational policy that says no access through MS Access, and they are willing to violate that, they'll eventually figure out the File DSN workaround. Also, they may go with Excel, etc., so you're in a losing battle.

Best strategy is to apply the Principle of Least Privilege. Give the users the rights they need and no more. Now, granted, if they need that sort of access, you're out of luck. But the problem isn't that they are using Access since any client will do. The problem is the permissions themselves.

K. Brian Kelley
@kbriankelley