We’ve all had to isolate the source of login failures (SQL Server 18456 errors) from time to time, and thanks to older versions of SQL Server not presenting enough data in the error log about these login failures it’s not as straightforward as it could be.
However, with a little help from SQL Server’s tracing tools it’s not too difficult to get to the bottom of these login failures. The technique is pretty much version independent, but I will assume you know how to use SQL Server Profiler and capture a trace.
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 does not 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 'DomainUser'. [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:
|1||Account is locked out|
|2||User id is not valid|
|5||Login not found; sometimes a connecting application is incorrectly set to specify connection via SQL Authentication for a Windows account|
|7||The login being used is disabled|
|10||Related to a SQL login being bound to Windows domain password policy enforcement. See KB925744.|
|11-12||Login valid but server access failed|
|16||Login valid, but not permissioned to use the target database|
|27||Initial database could not be found|
|38||Login valid but database unavailable (or login not permissioned)|
[Edit: Microsoft’s Bob Dorr has an excellent post giving details of the failure IDs in 18056 errors which map to the cryptic state code which contain the root cause of the failure, which should fill in the gaps if you’re looking for login failures against older versions of SQL Server.
The state codes with Login in the name relate to 18456 errors and those with Redo in the name relate to 18056 (connection pooling related) errors].
If it’s a SQL Server 2008 (or onwards) server you’re in luck as the state information is now (finally!) dumped into the SQL Server error log.
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 review the default trace or 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 and/or just run the query below:
select te.name as [event], e.applicationname, e.textdata, e.spid, e.starttime, e.databasename as db, e.loginname as [login], e.hostname as host, e.clientprocessid as pid, (select [path] from sys.traces where is_default = 1 and is_shutdown=0) as tracefile from fn_trace_gettable((select [path] from sys.traces where is_default = 1 and is_shutdown=0), default) e inner join sys.trace_events te on e.eventclass=te.trace_event_id where e.eventclass = 20 order by e.starttime
This will display any login failures from the current set of trace files along with the name of the trace file(s) those errors were captured from, so save off those trace files before they got rolled over and the information gets lost.
Scroll down to the Step 2 section below for information on tracking down the offending process causing the login failure.
If the default trace is disabled, or the information is no longer held in the current batch of default trace files, 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 events are selected:
- Audit Login Failed (under Security Audit)
- Errorlog (under Errors & Warnings)
Enough of the events already. Clear the Show All Events tick box to reduce the clutter, then make sure the following columns are selected:
- StartTime *
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.
*Adding StartTime to the list may be useful here in order to help isolate login failures that only occur at certain times.
When done clear the Show all columns tick box and you’ll end up with something like the template show in Figure 2 below . As we’re only interested in login failures these are the only events we need, and will help ensure that any performance impact from running the trace can be kept to a minimum.
Next we need to filter for the login error. Click on the Column Filters… button and under Edit Filter select Error and on the right hand side under Equals enter 18456 as the error number (Figure 2). This will filter out unwanted benign errors like 5701 and 5703 that tend to pervade a lot of systems:
Finally, click on the Organize Columns… button to re-order the columns. The order is a personal preference but I tend to have it as follows: EventClass, StartTime, Hostname, ClientProcessID, Error, NTUserName, LoginName, NTDomainName, DatabaseName, ApplicationName and so on. Figure 3 below shows a completed trace template:
It might look a bit sparse, but we are only interested in a specific error.
Now, on a production system it’s never really advisable (imho) to run a graphical SSP trace on the server (although I do admit to having fallen off my high-horse on that front in the past). If the login failure being tracked happens once every few days or so, always use a server side trace which you can just set and forget, but as this is a very lightweight trace its impact on the server in most environments (provided the server is not being hit with dozens of connection failures a second) will be negligible.
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.
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). Please note: if your connection is via JDBC no client PID will get picked up.
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.
Figure 4 below shows this Task Manager option:
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.
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!
Note: If the login failure is emanating from a Java based client, the Application Name and ClientProcessID will not be captured, and they will show up with default labels, which will be jTDS for ApplicationName and 123 as the ClientProcessID.
[EDIT: In the brave new world that exists from SQL Server 2012 SQL Profiler has been deprecated and replaced by Extended Events.
Auditing related events, including login failures, have been transferred to the SQL Server Audit component which is covered in the Capturing login failures via SQL Server Audit post.]