Block by application or log by application name

[monkeylizard]

SQL 2000, possibly moving to 2005 Mode 80

I have a set of purchased apps that use SQL security rather than Active Directory. Each user needs their own SQL account with full read/write/exec rights as the apps use combinations of stored procs and direct updating. Within the apps, users have security profiles controlling what they can and can't do with the underlying data. But anyone with the right skills could simply connect to the DB using their provided SQL logins and something like MS Access and do pretty much anything they want.

I know that SQL can report what applictaion is being used by a user to connect to a DB, as seen in the "Application" column of the SQL Activity Monitor.

Would it be possible to block users from connecting if the Application <> "name of purchased apps"? If not, can I log any transactions made where the Application <> "name of purchased apps"?

I don't have access to the code of the apps, so I think the Application Roles are out of play here.

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[monkeylizard]

Triggers are an option for the logging on key tables if that's the route I need to take. Too many tables to trigger all of them.

Can a trigger be set to only fire if the command comes from a connection made by an app not in an authorized list? I wouldn't want to log every transaction, say on a payment table because most of them are being made via an app with security controls built-in. It's just that (hopefully) rare time when a user connects with an unauthorized app to make changes.

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[Crowley16]

it would be possible to use triggers for this, and in 2000 that's probably your only real option.

If you're moving to 2005, then there's something called application roles which will help you a LOT in this situation. See here for details

--------------------
Procrastinate Now!

 

[monkeylizard]

Thanks Crowley16. But I don't have access to the apps' source code, so I wouldn't be able to edit them to execute the sp_setapprole stored proc.

I've checked with our internal auditors. They would be satisfied if I can log all activity (besides Select, if I can exclude that) performed by anyone not connecting via the approved applications. I know that the app name can be spoofed by a slick user, but the odds of that are pretty low, especially since the users don't know what names are being reported by the authorized apps.

Can this be done? Triggers will be fine as audit has also stated they are only concerned with a user security table and a couple of monetary transaction tables.

Obviously the trigger would be set to fire on INSERT UPDATE or DELETE transactions, but how can I filter to those coming from apps not contained in a list of approved apps? I can hardcode the apps list in the triggers.

Additionally, I have a couple of system-level accounts used for various job executions that I wouldn't want to log, regardless of the app name.

Here's the overall logic:

If User not in ('SysID1','SysID2','SysID3','sa')
BEGIN
  If Application not in ('App1','App2','App3')
  BEGIN
   INSERT INTO LOG_TABLE (USER,APP,SQL,DATE)
     VALUES (User,Connecting App, Sql statement being executed, getdate())
  END
END

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[RiverGuy]

I've used Logon triggers for this in SQL 2008 before. So if you move to 2005, that would be an option. But if you're really worried about savvy users making an ad-hoc connection -- if they somehow find out the Application Name setting in the application's connection string, they could reuse it in their ad-hoc connection to circumvent the logic.

 

[Jamfool]

Another option would be to lock the box down via a firewall change e.g only connections from ThisAppBox can get to ThisDatabaseBox. Again if your users can get on the AppBox you have problems ;p

 

[Crowley16]

eh, you don't need to modify your ap at all, you just need to set up the SQL Server so that a particular application has a certain set of permissions...

and you already have that since if you can set triggers, then you can set app roles...

But if you're sticking with the trigger route, a trigger will have pretty much the same functionality as a standard stored procedure.
you can use Program_Name() to detect the application name, and suser_sname() to find out the current user. Note, there's also user_name() but this is not the same!

I'd suggest you use an after insert/update trigger on the relevant tables, and make sure you use a set approach.
If there's a lot of criterias to keep track of, then maybe create a table to store them, and just reference the table in the trigger, will make admin much easier in the future.

--------------------
Procrastinate Now!

 

[monkeylizard]

Crowley16, I must be mistaken on app roles. I thought the connecting app had to tell SQL which app role to activate for the connection. Is that incorrect?

Thanks for the Program_Name() and suser_name() values. That's helpful. Is there also a way to collect the actual SQL statement being executed? I'm thinking of the SQL Server Profiler. When I'm logging a user's activity with that tool, I can click any record in the grid and I see the details in the lower viewer. The details show the actual SQL statement.

I don't necessarily need to log what an individual field value was before and after an update or insert statement, so much as I need to know what the actual statement was.

Monkeylizard
Sometimes just a few hours of trial and error debugging can save minutes of reading manuals.

 

[odianewell]

I cann't log on, cause i don't know password. Someone else had this micon pc before me .windows 2000 professional, built on NT Technology. So how can Iput my name and change password.