We’ve all had to isolate the source of login failures from time to time, and after seeing a bit of a surge in the number of queries on the forums asking for help with just this problem, I thought I’d start the new year with a quick way of pinning down these failures; I did hunt around on the web assuming this must have been a topic that had been locked down plenty of times on other sites/blogs, but I was rather surprised that, whilst there are plenty of articles on what a login failure is and what all the codes returned in the error message mean etc, I could not find anything that went through all the steps a dba would need to go through to narrow down exactly where the failed login request was coming from.
This blog is my attempt to plug that gap and show you how to isolate the exact process causing the problem.

The technique is pretty much version independent, so is not specific to any particular version, but I will assume you know how to use SQL Server Profiler and capture a trace.

Login Error 18456

A login failure will throw an 18456 error and will be accompanied by the following entry in the SQL Server error log (SQL Server 2000 tends not to display the IP address):

2009-01-15 09:40:24.55 Logon Error: 18456, Severity: 14, State: 8.
2009-01-15 09:40:24.55 Logon Login failed for user 'Domain\User'. [CLIENT: xxx.xxx.xxx.xxx]


The severity of the error indicates the seriousness of the error. A severity level of 14 indicates an error in the range described as user correctable, which is understandable for login failures.

The next item of information the error provides is the state number. Most errors have a state number associated with them which provides further information which is usually unique to the error that has been thrown. For a login error, state: 8, shown in the above example, indicates an invalid password was used.

The state number therefore provides invaluable information about the reason for the login failure and can often be enough to identify the cause of an 18456 error.

The table below illustrates what some of these state values mean:

The next item of information is the login (SQL Server or Windows) generating the failure, followed by the IP address of the host from which the login was attempted, which provides a useful cross-reference to confirm we’re looking at the right host when we’re trying to isolate the login failure.

Isolating the login failure

If the information provided in the error log for the login failure is not enough to isolate the source of the errors, the next step is to run a quick trace against SQL Server to get more information.
The easiest way to identify the process generating the login failures is via a SQL Server Profiler (SSP) trace.

If you’re running SQL Server 2005 or above and you still have the default trace enabled (which is on by default in an out-of-the-box installation) then you don’t need to start a new trace; check out my SQL Server default trace post instead.

If the default trace is disabled, or you have an earlier version of SQL Server, then read on.

Start SSP, and, using either your favourite trace template, or via a new trace template (File > Templates > New Templates…) make sure the following columns are selected:

• ClientProcessID
• Hostname
• LoginName
• NTUserName
• NTDomainName
• ApplicationName

These columns can be found in the Trace Properties dialog on the Events Selection tab. If they are not visible, tick the Show all columns checkbox. Note that SPID is always a selected column by default, and cannot be de-selected.

Under Events select the Audit Login Failed event under Security Audit. As we’re only interested in login failures this is the only event we need, and will help ensure that any performance impact from running the trace can be kept to a minimum. On a production system it’s never really advisable (imho) to run a graphical SSP trace on the server; always use a server side trace.

Figure 1 below shows a completed trace template:

It might look a bit sparse, but we are only interested in a specific error.

Step 1
Save the modified trace template and launch a new trace, specifying the saved template as the template for the new trace and wait for the login failures. Stop the trace after a login failure has been generated.

Step 2
The Hostname column should have recorded the name of the server that the invalid login emanated from and the ClientProcessID should have captured the Process ID, or PID of the offending process (or processes if there are multiple processes involved).

Step 3
Log on to the server triggering the errors, and list the PIDs of the relevant processes. This can be done using Tasklist or Task Manager.

To view the PIDs via Task Manager, start Task Manager (Shift+Ctrl+Esc), go to View > Select Columns… and tick the checkbox labelled PID (Process Identifier) and click OK.

Click on the Processes tab to bring all the processes running on that server into view (make sure Show all processes from all users is ticked) and click on the PID column heading to sort the PIDs in descending or ascending order.

Step 4
Once you’ve isolated the process responsible via the PID it should just be a matter of identifying where that process stores the credentials it uses for logging into SQL Server and verifying them.
Usually, the process will be a service, so it’s just a question of bringing up the Services plugin via Control Panel, or Start > Run > services.msc should also do the trick.

That’s it, so happy hunting!

Useful links
Troubleshooting: Login failed for user ‘x’
Understanding “login failed” (Error 18456) error messages in SQL Server 2005