How to Fix MS SQL Server Error: 18456?

SQL Server is the most commonly used Relational database management by many organizations to maintain their database. The primary function of the SQL server is to store and retrieve data from the database as per the application request. SQL server provides data management on the top of the core database management system. Server authenticates the valid user by login credentials. The authentication phase may sometimes prompt error messages due to several reasons. SQL Server Error 18456 is one such error where the Server fails to authenticate the user.

Error details
Error ID: 18456
Symbolic Name: LOGON_FAILED
Message: "Msg 18456, Level 14, State 1, Server, Line 1” or “Login failed for user’’
This error occurs mostly when the login to the server fails. The error message includes account distributor_admin and issues with account replication. The repl_distributor created by the replication allows distributor and publisher to communicate. The distributor_admin must possess a valid password to login to remote server.

Common Reasons Behind the SQL Server ERROR 18456

There can be several reasons behind the error. The common causes behind the login failure can be due to any of the below mentioned cases.

• SQL Authentication is not enabled in the server.
• Invalid user ID or Password.
• Misspelled user ID or Password.
• Expired password due to any fatal errors.

The error state displayed along with the error message indicates the cause of the error. So that user can easily carry out appropriate actions to fix the error. Some common error states and description are given below.

• State 2 or 5 indicate Invalid user ID
• State 7 shows Login disabled and password mismatch error
• State 8 is for Password mismatch
• Invalid password is denoted by state 9.
• 11 and 12 states indicate Valid login but the server access fails due to other reasons
• State 18 indicates that password Change is required

How to Overcome the Above Situations?

The first and foremost solution is to check whether the SQL Authentication mode in enabled on the Server. Before that, make sure that the password and the username are not misspelled or incorrect. So that error due to manual mistakes can be eliminated.

How to Enable the SQL Authentication Mode?

Use SQL Server Management Studio to configure or change the properties of the SQL server. The following steps help you to enable SQL authentication on SQL Server 2008.

1. Connect to SQL server using Windows Authentication mode.
2. Right click on SQL Server and go to its properties as shown in the figure.
   
3. Select the security settings from the server properties window

4. Enable the option SQL server and Windows Authentication Mode and click OK.

5. Restart the server after setting the authentication mode to SQL Server Authentication mode. The figure shows the restarting of SQL Server 2008.

This helps you to fix the user Login failure with error ID: 18456 if SQL server authentication mode was not enabled in the server.

Observation

SQL Server error 18456 occurs if the server fails to authenticate the user and thereby user login fails. This can happen if the SQL server authentication mode is not enabled in the server. This can happen due to manual errors such as misspelled password or username. The above mentioned procedure helps you to enable SQL Authentication mode on SQL server 2008 thereby helps you in fixing error: 18456, the user login failure. But If you forget the password of your crucial SQL database file, then to reset SQL Admin password you need an advanced SQL password recovery utility which allows you reset lost MS SQL database password and regain access on your database.


Appreciate your suggestions. Please comment your feedback and reach me out via email