Tracking down program from Event Viewer

Question

The event log has hundreds of login failed errors for SQL Server. How do you find out the application generating them?

There are lots of potential candidates on the server and since it's logging in as SA just like half the applications do it's not enough that I know the username.

This is three log in attempts per second all day up to a point where all the sites halt for about 30 seconds. Some asp.net errors appear and then it's back to 3 failed SQL logins per second.

But I can't see a way to trace the actual program name.

Answer 1

A failed login error will usually look something like this:

Login failed for user 'bob'. 
Reason: Could not find a login matching the name provided. [CLIENT: 10.59.68.215]

If you're trying to isolate the issue to a particular server, the client's IP address should be clearly displayed in the error message, so that gives you at least something to work from.

If you're trying to pin it down further, fire up SQL Server Profiler. This was previously not available for some editions of SQL Server, but the SQL Server 2012 Express SP1 Management Studio finally includes it, meaning it's available to everyone!

Once you've logged in to your server with Profiler, use "Events Selection" tab and choose to show "All events" and "All columns". You'll want to check the box just to the left of Security Audit --> Audit Login Failed, then use the Run button.

Then, simply leave profiler running until you see a hit or three. This will show you additional information including the Application Name, and the Client Process ID.

EventClass:      Audit Login Failed
ApplicationName: Microsoft SQL Server Management Studio
ClientProcessID: 4680

In a busy production environment, the more appropriate way to capture this information is to script out a server-side trace that captures the events to a .trc file on the server, or to use Extended Events.

Answer 2

First I agree with @gWaldo some definite fish-slapping is needed. Second if you run a trace with the failed login event you can get the Application Name and the Host Name. The application name is probably .Net or something like it but might eliminate some possibilities. Or you might get lucky and get the application name directly. The host name is the name of the machine the connection is coming in from. That should also narrow down your possible applications, hopefully to 1. With any luck between the two you can get it down to a reasonable number of suspects.