Log logins

[Albe V°]

On a huge Sql-Server 7 installation, we have various client applications
distributed along the Lan, accessing one main database.

Each application accesses the db using one out of around ten logins. Most of
them, have only DBDataReader right on the db, as they are consultation
consolles only.

In order to monitor db usage, the customer requires some kind of log of user
access.

My need, mainly, is to INSERT a record into a log table, recording Date,
Time, Login, Host of each access.

But, and this is the problem, the job has to be done by the server itself,
not by each client, because of various reasons:
1) we don't like to increase rights of logins
2) we don't plan to change anything in our custom client application
3) few of those client applications have been developed by foreign
suppliers, so we cannot change them.

My question is: does it exist any kind of authentication LOG, which I can
work on?
Or, is it possible to activate a kind of TRIGGER, reacting on login
authentication?

Thanks in advance

Alberto

[Oscar Santiesteban Jr.]

If you right-click on the server name in enterprise manager (EM) and click
on properties, click on the SECURITY TAB.
You can then audit sucessfull logins, login failures, or all. There is only
2 things you have to remember.
1. You will have to read either the SQL server error log or the Application
log of Windows.
2. If people use generic logins or shared logins, you might not be able to
detect who it was that logged in.

Hope this helps
Oscar...

"Albe V°" <va*****************@hotmail.com> wrote in message
news:JA**********************@twister2.libero.it.. .

On a huge Sql-Server 7 installation, we have various client applications
distributed along the Lan, accessing one main database.

Each application accesses the db using one out of around ten logins. Most of them, have only DBDataReader right on the db, as they are consultation
consolles only.

In order to monitor db usage, the customer requires some kind of log of user access.

My need, mainly, is to INSERT a record into a log table, recording Date,
Time, Login, Host of each access.

But, and this is the problem, the job has to be done by the server itself,
not by each client, because of various reasons:
1) we don't like to increase rights of logins
2) we don't plan to change anything in our custom client application
3) few of those client applications have been developed by foreign
suppliers, so we cannot change them.

My question is: does it exist any kind of authentication LOG, which I can
work on?
Or, is it possible to activate a kind of TRIGGER, reacting on login
authentication?

Thanks in advance

Alberto

[Erland Sommarskog]

Albe V° (va*****************@hotmail.com) writes:

On a huge Sql-Server 7 installation, we have various client applications
distributed along the Lan, accessing one main database.

Each application accesses the db using one out of around ten logins.
Most of them, have only DBDataReader right on the db, as they are
consultation consolles only.

In order to monitor db usage, the customer requires some kind of log of
user access.

My need, mainly, is to INSERT a record into a log table, recording Date,
Time, Login, Host of each access.

The best tool for this is the SQL Profiler. Set up a trace that captures
login events. I don't have the SQL7 docs available, so I prefer to not
give any details, but refer you to Books Online. On SQL2000 you can set
C2 auditing, which enures that you don't loose events if the log runs
out of disk space. (That throttles the server instead.) But I don't think
C2 is available on SQL7. Then again, with only ten users, you are not
likely to fill the log if you only filter logins.

Beware, though, that applications that may be written so that they
connect and reconnect frequently, for instance once per query.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp