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)