Solved

Help with Tracing

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 ?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

752 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