Avatar of Sailordlv
Flag for United States of America asked on

Help with Tracing

Hi Experts-

I've been searching here & MS site with no luck so far, I hope you can help me out.

We use tracing to monitor the use of the SQL Database instances on multiple machines. One of the functional uses is to evaluate "who" logged in from "where" with "what" application, in order to isolate the events we are concerned with from those that are related to routine use of an application. (I should add that similar functionality is used on all versions, this isn't restricted to 2000/2005/2008).

The problem we've identified is that when making a connection to SQL, the client system can change the identified machine name and/or application used, as presented to SQL; therefore there is no assurance that the application identified or the hostname used and logged, is valid.

Does anyone know of a simple way to capture the actual IP address of the client machine (whether an app server or workstation, we use both) on connection, either in addtion to or in place of the 'HostName' column?

Here is our current trace setup:

select @str1 =
'Audit Login,'+
'Audit Logout,'+
'Audit Login Failed,'+
'Audit AddLogin Event,' +
'Audit Login Change Property Event,'+
'Audit Login Change Password Event,' +
'Audit Add Login to Server Role Event,'+
'Audit Add DB User Event,'+
'Audit Add Member to DB Role Event,'+
'Audit Add Role Event,'+
'Audit Object Derived Permission Event,'+

-- List the columns you want to capture
@str2 =
Microsoft SQL Server

Avatar of undefined
Last Comment

8/22/2022 - Mon
Anthony Perkins

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Only local, these are intranet applications, so it would be sufficient for us to see the connecting-machine. Of course many will be from a web-server or application server, but those we expect.
Anthony Perkins

Only local, these are intranet applications
If that is the case, than you can use the UserHostAddress method in the Request object.

Thanks, we're going to pursue this direction.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.