SQL Server login error 18456- Know how to resolve it

In this article, you’ll come to know how to troubleshoot the SQL Server Error Code 18456. I’ll take into the picture the reasons behind the occurrence of this particular error code and the possible manual and alternate solutions to fix the same.

MS SQL Server error 18456 appears when there is a failure on login attempt made by the user. It appears even if the username has been accepted without actually defining the reason of the error. There can be various reasons for this error. However, error details can be checked by the administrators in the event log.

SQL login error 18456- An Overview

Generally, the error messages are displayed with a description of what went wrong as in Outlook. But, in case of SQL Server login failed error code 18456, no proper reason for failed login is displayed making it complicated for the users to fix the error. The application is developed in a way to protect from unauthorized users. However, Administrators can check and view the event log for getting the details of the error 18456. The SQL server login error 18456 is displayed as:

Common causes for SQL Server login error code 18456

There are some probable causes behind this error:

1. One among the username and password or both are incorrect.
2. Password for the user login has been expired. While disabling the username on the server, it may happen.
3. SQL server is not able to recognize the “Windows Authentication” when the SQL server is logged in for the first time on the SQL server.
4. Different password on different servers by a single user.

Resolutions to SQL Server Error 18456

#1 Manual method

When you start the SQL Server for the first time, there are chances that under the security option “Windows Authentication” is enabled. In this scenario, the server may not recognize the user and the above error appears. So, in order to log in through SQL server authentication, follow the below steps.

• Open the properties of the server you’ve selected by right click.

• A server properties window prompt will be opened. Go to the Security option and click on the radio button of SQL Server and Windows Authentication mode under Server authentication.

• Now, go to Security ⇒ Logins and select the properties by right-clicking the server name.
• Under the dialog box of Login properties, enter the password details.
• Move to the status tab. Under the login option, choose the enabled and click OK to start the SQL server.

If you wish to use the option of “Windows Authentication”, it will be good if the users are provided admin rights by the administrator for a hasslefree login.

• First, log in to the SQL server as an administrator and move to the server having a user account. Expand the tree structure by clicking on Logins. Right click on the user account and click on New Login.

• A Login-New window will be opened, click on the Search.

• Type in the user name of the selected account in the Logins folder. Hit a click on Check names and proceed with OK.

• Now, move back to Login-New prompt, go to the Server Roles and click on the checkbox of sysadmin.

• Verify the role addition by going to the Securables tab.

#2 Alternate method

One can use manual methods to fix this particular error code in SQL server. However, if the problem is not fixed even by applying all the above methods in a sequence, there are chances that your database files in SQL server have been corrupted. Hence, you need professional software to repair MDF/NDF and other database files in the SQL server so that the error code 18456 get fixed. You are recommended to employ an SQL Database recovery software. It repairs all the corrupted NDF/MDF and other database files in SQL server in an effective manner.

You may also read: Perform Microsoft SQL Password Recovery Safely

Conclusion

Microsoft SQL Server Login Error Code 18456 is one of the common glitches that SQL users have to face. It can be resolved using the methods that I have explained above. However, there are chances that for some users it can’t be fixed manually. In that scenario, a professional SQL database recovery software can be used. It is an efficient software for this issue.

Related Post