Solved

Help with Tracing

Posted on 2012-03-12
4
227 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
  • 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

821 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