How do I trace SQL Server Failure Audit events?

Question

I recently took over management of a Windows 2003 server. The application log is being filled up with messages like these:

Event Type: Failure Audit
Event Source: MSSQLSERVER
Event Category: (4)
Event ID: 18456
Date:  3/5/2010
Time:  4:00:30 PM
User:  N/A
Computer: FAIROAKS1
Description:
Login failed for user 'administrator'. [CLIENT: <local machine>]
Data:
0000: 18 48 00 00 0e 00 00 00   .H......
0008: 0a 00 00 00 46 00 41 00   ....F.A.
0010: 49 00 52 00 4f 00 41 00   I.R.O.A.
0018: 4b 00 53 00 31 00 00 00   K.S.1...
0020: 07 00 00 00 6d 00 61 00   ....m.a.
0028: 73 00 74 00 65 00 72 00   s.t.e.r.
0030: 00 00                     ..      

For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.

I'd like to figure out what program is causing these. Is there a way to trace and find out which process is causing these errors?

Answer 1

This is caused by some account not being able to log to the SQL Server instance installed on that server.

The message above means there is a SQL login (not Windows Auth) called "administrator" that is not set up in sys.server_principals or the password used is incorrect.

Now, if you run SQL profiler then you should be able to trap the connection attempt including HostName and Application name. Audit Login Failed Event Class is a good starting point.

Potentially, this is a serious issue: it could be an attack, of a failed monitor attempt. But it's nice you can shut-down your SQL Server instance and no-one notices ... :-)