User ip tracking

Hello everyone, I have a fairly unique need :) I am trying to
determine the use/clients for databases in my corporation that I am
maintaining, but that noone seems to know what they are for. Many of
these databases never seem to have anybody connected to them in the
current activity.

What I'd like to do is find a way to audit the logins, so everytime
someone connects to a database it simply logs the clients IP address,
what login they used, and maybe what time. I've been searching google
for this and have found tons of information on auditing the logins,
but not the clients, such as by ip. Any help in this regard would be
GREATLY appriciated!

Joshua

Re: User ip tracking

jlaustill@gmail.com (jlaustill@gmail.com) writes:
> Hello everyone, I have a fairly unique need :) I am trying to
> determine the use/clients for databases in my corporation that I am
> maintaining, but that noone seems to know what they are for. Many of
> these databases never seem to have anybody connected to them in the
> current activity.
>
> What I'd like to do is find a way to audit the logins, so everytime
> someone connects to a database it simply logs the clients IP address,
> what login they used, and maybe what time. I've been searching google
> for this and have found tons of information on auditing the logins,
> but not the clients, such as by ip. Any help in this regard would be
> GREATLY appriciated!

First: next time you ask a question like this, please state which version
of SQL Server you are using. Given the nature of the question, I will assume
SQL 2000.

Seems like it's time to run a trace. The trace would filter for the database
id, and I think it's best to have one trace per database. You would specify
a fairly low max size for the traces, without any rollover option. This is
because if you happen to trace a database that has lot of activitity, you
want to load the server with the trace. And the nice thing is that after a
while you can check which traces that are still running.

Which events would you include in the trace? The first that comes to mind
is SQL:BatchCompleted, but a datbase may be referenced from another database
in a query. I would take one of Object:Opened or Locks:Acquired. Both
of these are likely to generate tons of events as soon as a database
gets used.

I don't know how much experience you have server-side traces, or Profiler
for that matter. But you need to run these traces server-side, to reduce
the load. You can still use Profiler to set up a template trace, and
then generate a script from the trace.

I suggest that you try this out on a test server, before you go live.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: User ip tracking

On Jun 20, 3:50 pm, Erland Sommarskog wrote:
> jlaust...@gmail.com (jlaust...@gmail.com) writes:
> > Hello everyone, I have a fairly unique need :) I am trying to
> > determine the use/clients for databases in my corporation that I am
> > maintaining, but that noone seems to know what they are for. Many of
> > these databases never seem to have anybody connected to them in the
> > current activity.
>
> > What I'd like to do is find a way to audit the logins, so everytime
> > someone connects to a database it simply logs the clients IP address,
> > what login they used, and maybe what time. I've been searching google
> > for this and have found tons of information on auditing the logins,
> > but not the clients, such as by ip. Any help in this regard would be
> > GREATLY appriciated!
>
> First: next time you ask a question like this, please state which version
> of SQL Server you are using. Given the nature of the question, I will assume
> SQL 2000.
>
> Seems like it's time to run a trace. The trace would filter for the database
> id, and I think it's best to have one trace per database. You would specify
> a fairly low max size for the traces, without any rollover option. This is
> because if you happen to trace a database that has lot of activitity, you
> want to load the server with the trace. And the nice thing is that after a
> while you can check which traces that are still running.
>
> Which events would you include in the trace? The first that comes to mind
> is SQL:BatchCompleted, but a datbase may be referenced from another database
> in a query. I would take one of Object:Opened or Locks:Acquired. Both
> of these are likely to generate tons of events as soon as a database
> gets used.
>
> I don't know how much experience you have server-side traces, or Profiler
> for that matter. But you need to run these traces server-side, to reduce
> the load. You can still use Profiler to set up a template trace, and
> then generate a script from the trace.
>
> I suggest that you try this out on a test server, before you go live.
>
> --
> Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Erland,

Thanks for your response, I hadn't even thought about using
profiler :) I've used it extensively as a Database Developer to run
traces for optimizations, but never took the time to realize that it
could be used as a security audit tool. Setting this up took only
minutes, and within minutes I had my answers and knew who was using
what databases. I now have contacts for each of them.

I assume WAY to much when I write posts, but you were correct in
assuming 2000. I'm the king of asking questions without giving enough
information when I'm frustrated, thanks again man!