Failed Logins

Posted: April 27, 2011 in SQLServerPedia Syndication

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. 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s