SQL Server Performance Forum – Threads Archive

DDL Triggers and unauthorized access

I’m trying to figure out a way to use the new DDL trigger functionality along with one of the dynamic performance views (sys.dm_exec_sessions?) in SQL 2005 to monitor connections to the database and track users who are using third party products to view data outside of the application. Our developers have a bad habit of connecting to production databases using Toad or Rapid SQL to "view" the data using the application ID and we’d like to audit and eventually prevent this behavior. Any ideas?

Like posted on the other thread – don’t allow the developers access to the production server. Get your basic security straightened out.

i think its ok to let lead developers (who have a sense of responsibility) have non-sa/dbo access to the prod db, as long as they know how to check an execution plan before running a query but anything that requires sa/dbo needs to be discussedd between the dev lead & dba lead

also, most developers do not need to see up to the minute data, usually yesterdays backup is good,
so have a separate server with some big SATA drives to restore the previous nights backup

<<don’t allow the developers access to the production server.>> We are working on this and we are supposed to have a "gentleman’s agree" in place that they will not access production. Unfortunately, this agreement is not being followed. As I mentioned in the orginal post, they are not using personal id’s to access the database, rather the id that has been created for the application. In a perfect world they would not know the passwords for the application id’s, but I don’t work in a perfect world and do not have the "pull" (I’m new to the organization) to get them changed. What I’m looking for is a way to enforce the "gentleman’s agreement" we have. I’m looking for a way to identify who is logging in (workstation id) or just prevent them from connecting with one of the third party tools. We currently do this in Oracle using a "on login" trigger and interrogating the .exe that is connecting to the database. I was looking for something similiar in SQL Server.

A gentleman’s agreement works only if both sides honour it. Looks like you have every argument you need to change the passwords. If you are working with Windows accounts only, then you can deny login to the network accounts of your developers, but that doesn’t preclude them from logging on with a SQL Server login if they have the password. Nice feature from Oracle, though I expect things can get confusing after a while, and difficult to troubleshoot. Better not to let it get this far out of hand to begin with.

<<If you are working with Windows accounts only>> Novell shop so authentication is handled via SQL logins. <<doesn’t preclude them from logging on with a SQL Server login if they have the password.>> Exactly the problem we are having. <> Actually the Oracle solution is very simple. The on login trigger allows us to check what exe they are using for their connection against a small table and if it is something we disallow, the session is killed and the user gets a message that indicates they tried to access the database using a tool we do not allow.

While it may seem drastic… What about having a scheduled job which runs every 5 minutes and kills any non-approved applications’ connections Cheers
Twan

Yea a scheduled job could do a select from sys.dm_exec_connections. Then look at the "client_net_address" column. If the address is not the same as the production application server then kill the connection. Or to be less dramatic join to sys.dm_exec_sessions via session_id to find the hostname. Then record it or net send spam them with a warning <img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> Note that both host and application name can be specified in the connectionstring so it might not be the 100% real host.

Thanks for the ideas!

]]>