SQL SERVER – FIX Error 18456, Severity: 14, State: 5. Login failed for user

SQL
2 Comments

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:

SQL SERVER - FIX Error 18456, Severity: 14, State: 5. Login failed for user login-failed-18456-01

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: 5. Login failed for user login-failed-18456-04

Solarwinds

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:

SQL SERVER - FIX Error 18456, Severity: 14, State: 5. Login failed for user login-failed-18456-02

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.

SQL SERVER - FIX Error 18456, Severity: 14, State: 5. Login failed for user login-failed-18456-03

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)

Solarwinds
, ,
Previous Post
SQL SERVER – Database Backup and Restore Job Management
Next Post
SQL SERVER – Setup Screen Not Launching While Updating a Patch

Related Posts

2 Comments. Leave new

  • Jack Whittaker
    August 8, 2018 4:22 pm

    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?

    Reply
  • 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.

    Reply

Leave a Reply

Menu