Era of Data specialise in SQL Server
architecture and perfomance tuning

and was set up by a SQL Server specialist who has previously worked for Microsoft and Sybase.
This site is dedicated to providing information, tips and tools for Microsoft SQL Server.
Era of Data
Blog
23/01

Identifying the source of SQL Server login failures (18456 errors)

Tags:    18456, Default trace, Login failures, NT AUTHORITYANONYMOUS LOGON, SQL Profiler

Share:              

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.

18456 errors

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:

State    Error description
1        Account is locked out
2        User id is not valid
3-4      Undocumented
5        User id is not valid
6        Undocumented
7        The login being used is disabled
8        Incorrect password
9        Invalid password
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
18       Password expired
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 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 events are selected:
  • Audit Login Failed (under Security Audit)
  • User Error Messages (under Errors & Warnings)
  • 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:
  • TextData
  • SPID
  • ClientProcessID
  • Hostname
  • LoginName
  • NTUserName
  • NTDomainName
  • ApplicationName
  • DatabaseName
  • Error
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. (Edit: 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 figure in the screenshot 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.

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 (although I must admit I’ve fallen off my high horse on that front more than once).

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. 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, TextData, ClientProcessID, Hostname, NTUserName, NTDomainName and so on. 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). Please note: if your connection is via JDBC no client PID will get picked up.

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.
 

Figure 2 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.

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!

[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.]

Troubleshooting: Login failed for user ‘x’

Understanding “login failed” (Error 18456) error messages in SQL Server 2005
Comments

 
Leave a comment
Solve the equation 9 + 5
Leave a comment
14th August 2015 14:25:00
Excelent article to investigate the case.
Simha
24th June 2014 05:41:00
Very useful blog post it help immensely, a must read. Good way to do SQL error analysis. Well done!!
Robbo
17th April 2014 10:59:00
Nice work my friend! Found the problem within seconds!
Barry McAnally
6th March 2014 15:49:00
Thanks this worked for me :)
Jeroen
14th February 2014 16:45:00
Perfect. Found the problem process immediately with this. Great to have resources like this!
Corey
12th July 2012 14:30:00
THANK YOU. This is concise, clear, and extremely helpful. I’m a SharePoint person, not a SQL person, but this enabled me to find and solve my issues. Moreover, it adds another tool to my very limited SQL bag of tricks.

Well Done, Sir.
Keith V
4th May 2012 19:56:00
SPN’s for SQL Server can be a nightmare to configure, especially on a cluster. I guess you’ve already stepped through this doc: http://msdn.microsoft.com/en-us/library/ms189585(v=sql.90).aspx but it’s worth double-checking and listing out the SPNs (via SetSpn -L SQLServiceAccount).

I would also check via management studio:
– the account is not disabled
– the database user account associated with that login is not orphaned or somehow disabled in the default database associated with that login (displayed on the login’s properties)

Worth also ensuring that that these accounts haven’t become locked in AD.

Let me know if that helps at all.
Ajmer Dhariwal
4th May 2012 18:30:00
Hello,

When attempting to implement kerberos authentication between BizTalk 2006 servers and SQL 2005 servers, SQL will not restart after SPN’s are created. SQL is clustered over 2 nodes. SQL runs under one service account and BizTalk is running under a different account. After restarting SQL, we get failed logins, Severity 14, State 11.

We changed the password on the account that is running SQL, changed the password entry for SQL in the Services console and in the Cluster Admin console, but continued getting same errors. Eventually, after resetting password several times, it finally started successfully. The only errors seen between SQL Agent, Server and Event logs are the 18456, 14,11.

The error state indicates “Login valid but server access failed”. What exactly does this mean? Access to what has failed? How do I troubleshoot this?
DetRich
27th December 2011 21:49:00
Searched for hours until coming across this entry — great article! Thank you for sharing!
Anonymous
10th December 2011 18:56:00
This is the sql server agent account and it sounds like the account is locked out or disabled in some way.
From a dos prompt run “net user /domain” where is the full service account name (including domain name) and check the Account Active status and there is no password expiry for the account.
If that doesn’t help then send me the full fragment from the error log showing the login failure.
Ajmer Dhariwal
9th December 2011 16:55:00
awsome article, it allowed me to find the process and the service but i’m still getting the lock outs. they are happening on the master database of our sql1/sql1 instance. the process is sqlagent90.exe and the service is SQL Server(sql1) i have verified that the account running the service is correct, i have verified that the service account is in the security folder of the master database.

our sql enviorment is in a MS cluster. this failure aduit is only happening on this node of the cluster. i’m lost as to what to do now.
Philip
25th November 2011 09:49:00
Very important and nicely written post
Ashish Jain
3rd November 2011 18:43:00
The trace can be stopped or paused and saved so you can go back over it in your own time.
You can capture a similar trace of the processes and their PIDs by running an e.g. perfmon trace, or you can just open a DOS prompt (command window) on the host sending the invalid logins and typing “tasklist” and hitting return.
This will list all the processes running on that host along with their PIDs which you can then reconcile with the trace output. You can also redirect the tasklist output to file via “tasklist > c:temptasks.txt”. If you use the following syntax with “tasklist >> c:temptasks.txt” you can append the output to a text file, which means you can repeatedly run the tasklist command on a scheduled basis, for example, and this will allow you to capture all the processes and their PIDs over the time interval you schedule it for. With the trace and tasklist data saved you can go over it at your lesiure to reconcile the logins with their PIDs.
Ajmer Dhariwal
2nd November 2011 17:06:00
Thank you for the information. This has been somewhat helpful, at least more so than other articles I have found. I am having one issue though. The trace is capturing the information but it goes by so fast I can’t catch the PID on task manager when it’s caught in profiler. Is there anything I can do to see this? I don’t know what process is running to cause failure and that is what I am trying to catch. Please advise.

Much thanks!!!
Diane
12th August 2011 21:34:00
I was getting those mysterious Error: 18456, Severity: 14, State: 38. messages every three minutes on one of my test servers, and this helped me pinpoint the db name and the client process that was trying to access it. I had suspected it was a database we recently migrated to a production server, and the application support person was arguing it couldn’t be, he had changed all the pointers. This solved the argument quickly and finally.

Thanks very much for posting this info.
Deb
19th July 2011 17:02:00
Thank you for pointing me in the right direction
Ozie
8th July 2011 23:44:00
Thank you! This is very helpful. You have some excellent SQL Karma coming your way :)
Myra Rosa
13th June 2011 21:01:00
Good point JJ – something I’d entirely forgotten in my hurry to keep the profiler template as light as possible.
Ajmer Dhariwal
27th May 2011 21:48:00
Hi Chris, Hi Ajmer,

for me works following to get the databasename:
You can trace requested databasename with profiler, you must select “User Error Message” under “Errors and Warnings”! So add this Event to your trace template beside “Audit Login Failed”

If your trace is active and the login error occurs you will see a message like:
Then you will se textdata like “Cannot open database “XYZ” requested by the login. The login failed
JJ
22nd May 2011 18:17:00
Hi,

This is the best description I’ve found so far on how to identify failures. It’s helped me to find out quite a bit after I got done with a SQL server migration where we trimmed databases which supposedly were not in use.

One question: Is there a way to tell what non-existent database a particular login is requesting?

I have an unknown web-based app that is triggerred by an unknown individual which I can’t predict when it’ll happen next, so continually using SSP would seem to be taxing if run for a number of days waiting for that to happen. If I could just find out what database the user or process is trying to request, that might clue me in to what application is requesting the non-existent database to either obsolete the app or bring back online the database.

Currently, I’m using alerts which tell me when the event happened, what the login and client IP is and the error, “Failed to open the explicitly specified database”. The

Thanks in advance for any tips or advice!

-Chris
Chris
20th May 2011 10:06:00
Great clearly written article , it helped me find the cause of a login error i had for months.

Thanks
Rob
22nd March 2011 22:08:00
Hi Chris,

This is a bit of a flaw in the current setup as the database engine knows everything (the client process id, hostname, db name the login is trying to use etc etc) but the error messages do not include all the pertinent information (for what it’s worth, it’s being addressed in the next version: http://connect.microsoft.com/SQLServer/feedback/details/590004/add-database-name-to-error-18456-message).
The good news is that the login is still there, otherwise you’d get a different error, so I’d suggest that the you check the properties for that login in SSMS and view the default database that login is associated with – this should identify the db in question.
If the default database is master, or some other valid database then the process in question must be specifying an alternate name in the connection string it is using to connect, so you’ll have to identify where this proc is storing that info – normally the trace identifies the hostname and process pid which should help.
You can use SSP to save the trace template as a file and run it as a server side trace which will have minimal impact on the server (File > Export > Script Trace Definition…) and control it via sp_trace_setstatus.

Good luck!
Ajmer Dhariwal
3rd March 2011 12:59:00
Best description of troubleshooting login errors that I’ve seen on the web – nicely done!
Margaret
23rd December 2010 12:22:00
Thank you, it helped me find the cause to my login error i had for month. Very well written.
Larry
6th June 2010 00:10:00
Very informative and helpful – save me from utter frustration. Thanks for the work.
Rick Morgan
25th June 2009 16:07:00
THANK YOU!!! I have been searching days to resolve this error and your instructions worked wonders for me!!!!
Michael
15th June 2009 02:43:00
Hi.

Thank you VERY much for making the effort of leaving a well-written and easy to follow article.

It helped me isolated a login failure on our MS-SQL 2000 server that was occurring every second.

Kind Regards,

Jason
Jason TEPOORTEN

Contact

About

With over 15 years in the IT industry, including several years working in the financial sector in London where he developed a proven track record on tuning critical low latency trading systems as a SQL Server DBA and stints at the world’s biggest software companies including Microsoft, and Sybase in roles ranging from technical support to consultancy, Era of Data was set up in 2007 by Ajmer Dhariwal in order to specialise in all things related to Microsoft SQL Server, from remote DBA work to ad-hoc consultancy with a particular focus on performance tuning and optimisation.
TwitterLinkedIn
Enter the result of:
8 + 5

design & code LemonArt.com