June 20, 2005 at 7:40 pm
I would like a way to monitor connections - is there a way to determine who is who when many users login using the same account via website / app pool?
Also, I was using SQL spy today and just happened to catch a *questionable* connection by the host name... is there a way to log hosts/ip over time?
TIA,
-D
June 23, 2005 at 8:00 am
This was removed by the editor as SPAM
June 23, 2005 at 9:24 am
Lumigent Entegra.
It's related to Log Explorer, using some of the same technology. It also tracks object changes in the system so you can see if unknown users are changing tables or procs or something without permission.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
June 23, 2005 at 9:52 pm
In general, all that SQL Server has to differentiate once user from another is the login. If N different individuals connect to SQL Server and they all use the same login name ("sa", "MyDomain\WebServiceAccount"), then that's all SQL will know about its users.
For ad hoc "who's using the database now" stuff, use sp_who2, or work the "Current Activity" node in Enterprise Manager. This shows who's connected at the time you ran the procedure or clicked the node.
For monitoring over time, use SQL Profiler, using Audit Login and maybe a few related counters. Here, you can select data columns that will show the login/account (as mentioned above), the host machine, and the application that's establishing the connection. These last two can be useful in that they may say what machine the connection is coming in from, or what application is being run. However, these values are settings that are configured in the communications layers (OLE DB, ODBC, et. al.), and they can be readily overriden, set to garbage values, or (most often) overlooked by developers. (We have a lot of connections established by application "Windows 2000 (R) operating system"...)
SQL profiler can be used for ad hoc tracing over fairly lengthy periods of time, but you have to leave it up and running on a logged-in client machine. However, it is essentially a front-end to the SQL Trace routines, which can be configured to run without an active client connection. Setting up these traces, getting at the data produced, and actually using/understanding it gets pretty involved, and you're best off reading Books Online (and devoting a chunk of time to getting familiar with it) to get the details on this. A good start is to use SQL Profiler to define a trace, and then script it out for further work.
Third party tools may do this as well, and probably do it better (even if only from the user interface perspective). I couldn't say, I've never had the luxury of working at a place where management would buy into purchasing wifty third-party auditing tools. If you can swing that, go for it!
Philip
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply