How to allow certain applications (not users) to hit the database

Question

How to allow certain applications (not users) to hit the database

Bullfrog

SSC Eights!

Points: 895
More actions
August 4, 2009 at 10:54 am

#224589

Hello:
I have a few power users that have been using SQL Server Mgm't. Studio from their client desktops. They are restricted to read-only for only one database. All of this is well.
However, I've noticed that they are using their privileges to hit the database via MS-Access on their desktop. This is not good. The original intent was for them to do infrequent troubleshooting of the data. However, they are developing a full blown MS-Access project using linked tables.
Can I restrict certain user ID's to only hit the database using only certain tools? I'd like to maintain unique id's per power user versus one generic "poweruser" account.
Thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

you could use a LOGON TRIGGER, and test the app_name() = 'Microsoft SQL Server Management Studio Express - Query', and deny the logon attempt if it is not in a selected group of values.

; i don't have a specific example handy,i'll google a bit and see if i can find a decent example to paste.

-=Edit=-

my google-fu is strong.

here's an example where it is specifically denying if the word "excel" exists in app_name:

http://www.vandeputte.org/2006/11/sql-server-logon-triggers-part-2.html

you could use that as a model to deny if access is in the string, or the opposite and make them only be allowed to use SSMS.

Remember a smart developer can create a connection string that specifies a phoney app name in it, regardless of the "real" program using it, but for the other 99% of the peopel, this would work fine.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Jack Corbett SSC Guru Points: 184394 More actions · Answer 2

I don't know if you can change it in Excel or Access, but you need to be careful with APP_NAME() as you can change it in your connection string.

Data Source=SqlServer;Initial Catalog=DatabaseName;Integrated Security=SSPI;Application Name=NotAccess

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

rgoerwit SSC Enthusiast Points: 188 More actions · Answer 3

Taking a somewhat different tack here, I'd just note that summarily turning off access is usually bad form. The correct approach (which you may well have tried, so I don't mean this as a cricism) is to discuss and agree to rules of engagement, and try to understand what's motivated your users to move to Access. Presumably they have some need that led them in this direction. Think of it as your responsibility to either change your thinking and accommodate, or, alternatively, to help your users find another way to do what they want to do that will work for them.

As noted, you probably have tried this, so again I don't mean it as a critique. I write this "just in case." There are, sadly, a number of DBAs and sysadmins who just sit in the back room and piss people off.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

rgoerwit (8/6/2009)
Taking a somewhat different tack here, I'd just note that summarily turning off access is usually bad form. The correct approach (which you may well have tried, so I don't mean this as a cricism) is to discuss and agree to rules of engagement, and try to understand what's motivated your users to move to Access. Presumably they have some need that led them in this direction. Think of it as your responsibility to either change your thinking and accommodate, or, alternatively, to help your users find another way to do what they want to do that will work for them.
As noted, you probably have tried this, so again I don't mean it as a critique. I write this "just in case." There are, sadly, a number of DBAs and sysadmins who just sit in the back room and piss people off.

Very good point. People use Access and Excel because the applications they use don't meet their job needs. See if there is a way to get those needs met.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Bullfrog SSC Eights! Points: 895 More actions · Answer 5

Yes, I agree that is the most logical approach. I'm kind of caught in the middle. Most users are and have been content for a while. Unfortunately, when these few go in through Access they can bring the server to its knees and then all of the other users complain. I think it comes down to what you're most familiar with and these few tend to gravitate toward Access.

Thanks for the replies!

rgoerwit SSC Enthusiast Points: 188 More actions · Answer 6

Being caught in the middle is hard. If you're running SQL 2008, then you're looking at a classic case for using the resource governor.

For an example of how to do this, see http://msdn.microsoft.com/en-us/library/cc280384.aspx.

Basically what you're doing is

- Creating a resource pool (very limited, so groups using this pool won't bring the server to its knees)

- Creating a workload group to use that pool

- Writing a classifier function that helps SQL know when to put people into that pool

This way you don't have to cut anybody off.

You'll still probably need to go and talk with the people using Access and find out what they're doing that they think can't do any other way. Access does make some things very easy to do - things that aren't (at least not for everyone) easy to do any other way, like developing cheap, quick interfaces that can be run pretty much on any Windows machine at little expense.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 7

I kind of like rgoerwit method, if you don't get the response frmo these users you want then you might restrict them to the point that it is possible but EXTREMELY painful to do it their way. You know, queries that should take seconds take hours. Locking them out may not be the best way, but overall availability takes precendence, if they are effectively locking out other users then their method isn't acceptable.

CEWII