How to resolve the login failure SQL Server error code – 18456?

Read time 6 minutes

“We have been using SQL Server to manage our databases for a long time and update it with the latest version as soon it gets updates. But one of our team members started getting the error that login failed for the user. We always use SQL Server authentication and provide valid credentials. But the user is not able to connect with the server.”

The above issue can happen to any SQL Server user and make it impossible to access your server and the saved databases. Generally, the user name is accepted but the password is considered false. And the error message displayed does not define the reason. However, administrators can check the error details in the event log to get a clear picture of the problem.

More About Server Error Code 18456

Microsoft SQL Server error code 18456 denotes a failed login attempt which may be due to an incorrect password entry. Generally, it happens even if the username is accepted. And the error message displayed does not define the reason. However, administrators can check the error details in the event log to get a clear picture of the problem.

This error may be interpreted as a mechanism to prevent unauthorized persons from accessing the server by guessing the credentials. But genuine users may find it hard to overcome the problem.

Probable causes and of SQL server login error code 18456

• The username may be misspelled
• The user account may not exist in the SQL Server.
• The authentication mode would be different. Perhaps you are using SQ Server Authentication but Windows Authentication set for the user.
• The username and password may have changed and the user should contact the administrator for the new credentials.
• If the error is not showing any description but shows that the administrator has not authorized you to get the details of the error. Contact the administrator and get help with the issue.
• The password of your SQL account is incorrect and the administrator may assign you the new credentials.

If the above solutions do not work for you, then you should run some basic troubleshooting methods.

How to protect your password during SQL Server authentication?

Unlike Windows Authentication, the SQL Server authentication method does not use user account credentials but creates its credentials in the SQL Server. The user must provide the username and password each time they try to access the SQL Server Management Studio using SQL Server authentication. There are some tips related to managing passwords for it.

• It would help if you changed the password routinely to protect it from unauthorized access. SQL Server has a policy where “the user must change password at next login” each time they open the application.
• A password expiration policy sets the number of days for the password’s validity. After that, you must change it.
• The DBA can choose the Windows password policies that put the parameters for password length and inclusion of complexity level.

Resolution to SQL error code 18456

When an SQL Server is started for the first time, there is a possibility that ‘Windows authentication’ is enabled under the security option. In such a situation, the server will not recognize the user and the user will get the failed login 18456 error. If you want to login through ‘SQL Server Authentication’, then enable it by following the steps:

1. Go to the Properties of the selected server (from the right-click menu).
   
2. Now go to the Security page and under Server authentication choose the option SQL Server and Window Authentication mode.
   
3. Now expand Security > Logins and right-click the server name, and select Properties.
4. In the Login properties dialogue box, provide the password details (under SQL Authentication).
5. Now go to the Status tab, and choose Enabled under Login.
6. Click OK and restart the SQL server.

But if you want to use the ‘Windows authentication’ option, then it will be better if an administrator provides admin rights to the user, so that he must be able to log in without any hassle.

1. At first, login to SQL Server as an administrator, and go to the server where the particular user account is residing. Expand the Logins, right-click on the desired account and click New Login.
   
2. In the Login – New window, click Search.
   
3. Now type the username of the account which you have selected in the Logins folder and then click Check Names. Click OK.
   
4. Now back in the Login – New window, go to the Server Roles tab and check the sysadmin option.
   
5. Now go to the Securables tab and verify the role addition.

All the above-mentioned methods are used to overcome login problems. However, they cannot help when the SQL database is corrupt or MDF files are inaccessible. To recover SQL data and to repair the MDF files in such cases, you need an efficient MDF recovery tool like Kernel for SQL server recovery. It scans, analyzes, and repair MDF or NDF file and brings back all the precious data within no time.

Conclusion

Microsoft SQL Server error code 18456 for failed login attempt by a user is annoying to authenticated users and therefore its probable reasons are discussed along with two login enabling methods via Windows Authentication or SQL Server Authentication are explained to users to overcome the situations. For users encountering other SQL Server errors due to corrupted database files, a popular and reliable SQL Database Recovery tool is suggested as well.