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,'+
'ExistingConnection,',

-- List the columns you want to capture
@str2 =
'TextData,'+
'DatabaseID,'+
'DatabaseName,'+
'NTUserName,'+
'HostName,'+
'ApplicationName,'+
'LoginName,'+
'SPID,'+
'StartTime,'+
'EndTime,'+
'Permissions,'+
'ServerName'
SailordlvAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
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
Correct.  It is a trivial change in the connection string.

Does anyone know of a simple way to capture the actual IP address of the client machine
Do you mean the local IP address or external IP addresses as well? You can do the first with .NET, however the second will not give you the exact IP address, but rather the proxy.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SailordlvAuthor Commented:
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.
0
Anthony PerkinsCommented:
Only local, these are intranet applications
If that is the case, than you can use the UserHostAddress method in the Request object.
0
SailordlvAuthor Commented:
Thanks, we're going to pursue this direction.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.