Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Help with Tracing

Posted on 2012-03-12
4
Medium Priority
?
231 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 1500 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

722 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