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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-01-17 : 11:12:41
|
| Darby writes "We have several MS SQL 7.0 databases running on NT 4.0 that are accessed by off the shelf applications. Since I can not modify how the application logs into the SQL database, I can not implement Application Roles to prevent users from accessing the database through other applications like Query Analyzer or Excel. Is there another way I can prevent people from accessing the database from Query Analyzer, but still let them access through the off the shelf application?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-17 : 11:42:23
|
| Usually applications have specific settings for machine or workstation name. If these settings are hardcoded into the connection to SQL Server, you can test for them using the HOST_NAME() function (there are similar functions that escape me now, but you can find them in Books Online linked to HOST_NAME).Unfortunately these won't directly allow you to prohibit access to objects without some more work. You'd have to add triggers to tables that rollback any transactions that occur with a different HOST_NAME than the app provides. Since Excel and Query Analyzer would use a different HOST_NAME, this would effectively disallow access.For example, assume the legitimate app sets a HOST_NAME of "ClientAPP". A trigger can be written to do this:CREATE TRIGGER ON myTable FOR UPDATE ASIF HOST_NAME()<>'ClientAPP'BEGINROLLBACK TRANSACTIONRETURNENDYou can do something similar in a stored procedure to stop execution if the HOST_NAME isn't the right one, but it would be redundant if you apply it to triggers. |
 |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-17 : 12:21:48
|
| Rob's idea is a good one. I had this problem one time with a client, and I wrote a job that fired every 5 minutes that selected from sysprocesses where the hostname was excel, access, vb and I would kill the spids that were using those apps.Then It would xp_sendmail those users (And the DBA team) letting them know they were using an unathourized application on the production server.Combined with Rob's triggers, I think that would be a pretty solid way of preventring the behavior.-Chad |
|
|
|
|
|
|
Limit application access to SQL database w/out Application Roles