method to get more info for failed login

Hi,
We are currently tracking SQL Server login failures. But the SQL Server
error log only give infomation about which login has been attempted failed.
Is there a way to tell more info about this failed login, like from which
mechine the failed login came?
Thanks.
Hong Wang wrote:
> Hi,
> We are currently tracking SQL Server login failures. But the SQL
> Server error log only give infomation about which login has been
> attempted failed. Is there a way to tell more info about this failed
> login, like from which mechine the failed login came?
> Thanks.
Yes, you can set up a server-side trace to watch for the Audit Login
Failed event in the Security Audit category. The easiest way to do this
is define the server-side trace from Profiler and then have profiler
save the script for you. use the script in a stored procedure and set
the procedure to auto-start (sp_procoption) when the server starts. All
information is written to a local file onthe server, which is locked by
the trace. You can write a purge procedure that stops the trace and
merges the data from the flat file into table or just copies the file
and then restarts the trace. You have a lot of options here, but the
server-side trace is the way to go.
Use the following columns when defining the trace:
DatabaseID
StartTime
Error
ServerName
Success
TextData
ApplicationName
LoginName
ClientProcessID
SPID
Success will always be 0 for this event. HostName is not passed.
David Gugick
Imceda Software
www.imceda.com