Some errors are historic and have the most common root cause. Sometimes we really don’t know why they happen and I have seen clients go nuts to identify the real reason for the problem. In a recent email interaction with one customer – who was migrating from Oracle to SQL Server. He kept telling me that they were getting login errors from their Java application. And even when they used the SQL Server Management Studio it was erroring out with Error 18456 code. They sent me a snapshot like this for login failed:
This was not self-explanatory and as usual, I searched this blog to get a few posts around this error. I sent him this and said, if these don’t solve your problem – can you please send me more details.
- SQL SERVER – FIX Error 18456, Severity: 14, State: 6. Login failed for user
- SQL SERVER – FIX Error 18456, Severity: 14, State: 58. Login failed for user
In about an hour, I received a mail again stating the above were not solving his problem. He sent me a bigger screenshot as shown below:
Though this was a good starting point, this was not good enough information for me based on what SSMS was sending as output.
I reviewed the above blogs just to realize I had forgotten to give a bigger detail. A lot of these login failures are also logged in ErrorLog. I realized and asked the Developer to check in their error log. And incidentally, they figured out the actual root cause. Since he didn’t get back to me for 3 hours – it was my turn to ask what went wrong because I was curious to understand the actual reason. I got a screen shot as shown below and it explained quite a bit.
If you are not sure where to get the ErrorLog, check the post: SQL SERVER – Where is ERRORLOG? Various Ways to Find ERRORLOG Location.
On further investigation, it was learnt that their application was changing the password for their users in their application code, but since it was load balanced, it was getting into some mess. But I was glad how explicit and detailed information Error Logs give that helped this user.
Have you seen and used such information in your environments for such failures? What were your troubleshooting tips? Let me know via comments.
Reference: Pinal Dave (https://blog.sqlauthority.com)
6 Comments. Leave new
Hi Pinal
I have a 2016 server – call it Gandalf
There are three instances – Default, Frodo, and Sam
The syspolicy purge history runs every night on every instance. It always succeeds
1) When the job runs on the Frodo instance, no issues
2) When the job runs on the default instance, the error log on Frodo says Default agent account failed to log in –
Error: 18456, Severity: 14, State: 5.
Login failed for user ‘agent_default’. Reason: Could not find a login matching the name provided. [CLIENT: ] –
3) When the job runs on the Sam instance, the error logs on Default and Frodo say
Login failed for user ‘agent_default’ and
Login failed for user ‘agent_Frodo’
Obviously something isn’t quite right – and it only happens with the syspolicy_purge_history job
Any ideas?
I have seen similar error
Error: 18456, Severity: 14, State: 5.
Issue has been fixed after I drop and recreated the Login & User with same Name; Yes What you heard is right.
@krishna karnati I have faced similar issue in times creating replication and using windows authentication ..is there suggestion how can i solve it
I experienced the same error/failure after (AlwaysOn AG) failover to the secondary replica. The root cause is the new primary replica doesn’t have users e-logger and e_client under Security > Logins. Why are these 2 users not replicated? Notes: all DBs are synchronized before the failover.
This article only explains to look in the error log when a 18456 error occurs. Which is in itself good advice. However it’s of no use to give this article a title that specifically references the 18456, severity 14, error 0x5 error, which can be very hard to diagnose and fix. Giving a generic article such a specific title only obscures the search results for finding real solutions to the problem.
What worked in my case was simple: Reboot the server.