Sql-server – Tracking down program from Event Viewer

sql server

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.

Best Answer

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.

Selecting login failed events

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.

Related Question