Solved

Help with Tracing

Posted on 2012-03-12
4
229 Views
Last Modified: 2012-08-14
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'
0
Comment
Question by:Sailordlv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 500 total points
ID: 37712668
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
 

Author Comment

by:Sailordlv
ID: 37714479
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37720794
Only local, these are intranet applications
If that is the case, than you can use the UserHostAddress method in the Request object.
0
 

Author Comment

by:Sailordlv
ID: 37722102
Thanks, we're going to pursue this direction.
0

Featured Post

Veeam gives away 10 full conference passes

Veeam is a VMworld 2017 US & Europe Platinum Sponsor. Enter the raffle to get the full conference pass. Pass includes the admission to all general and breakout sessions, VMware Hands-On Labs, Solutions Exchange, exclusive giveaways and the great VMworld Customer Appreciation Part

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question