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:

Sep 19, 2017  Pinal Dave is a SQL Server Performance Tuning Expert and an independent consultant. He has authored 11 SQL Server database books, 23 Pluralsight courses and has written over 4700 articles on the database technology on his blog at a Along with 16+ years of hands on experience he holds a Masters of Science degree and a number of database certifications.

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.

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)

In this article, we will explain how to fix the SQL Server Error 18456 and describe the error and show different ways to solve this error.

Join the DZone community and get the full member experience.

Join For Free

Introduction

In this article, we will explain how to fix the SQL Server Error 18456. We will first describe the error and then we will show different ways to solve this SQL Server error using SQL Server commands, backups or external software.

The SQL Server Error 18456

A typical error message when you try to connect is the following:

TITLE: Connect to Server

Cannot connect to SERVERNAME

ADDITIONAL INFORMATION:

Login failed for user 'username.' (Microsoft SQL Server, Error: 18456)

For help, click: http://go.microsoft.com/fwlink?%20SQL%20Server&&&

The error 18456, is a typical login error. There are multiple reasons you might see this message. Here, you have some possible reasons: