Not too long ago I was prompted with an issue that was on a server I was not overly familiar with. This task was to figure out why I was getting so many log on failure messages in the error log. The messages were the standard “failed log attempt” message and if it was on a system that I knew I may have been able to just remember where the account was coming from. On a familiar system I would have known some of the application history and been able to back track to the problem. However, this was not the case. All this left me with was one question:
How do you track a failed log on back to the application that tried to log on to the SQL Server?
After a little trial and error I was able to find the source of the issue by using a trace file in the profiler. There is an event in the profiler that will allow you to track failed log in attempts.
Open Profiler (located under the performance and tools menu)
Start a new Trace by connecting to the server
You should be prompted with a properties window, here is where you can tell Profiler if you want to save your results and where you can save them.
Move over to the event selection tab. (I removed or deselected all the events listed, to make the results much more specific)
Check the Show all events and in the main window Scroll down until you see Security Audit
Check the Audit Login failed
Start the trace and you will start to see all the related data to failed logins. When selecting the event you can also select other columns that you might want to see in your results. From my results I was able to determine what application was passing along and the credentials that were failing.