Login failed for user (Microsoft SQL Server, error 18456)

Rate this post

Getting “Login failed for user (Microsoft SQL Server, error 18456)” error is a nightmare for the DBA’s and developers while working on the SQL Server Database. The error code indicates a failed login attempt which may be occurred due to several reasons. Though, the foremost reason for getting such Microsoft SQL Server error code 18456 could be a wrong password entry.

However, in many cases, an error code comes up with a description that helps users to get a clear picture of the error. But as you can see in the screenshot above, it doesn’t define the exact reason for getting the SQL Server error 18456. Though, in order to get the detailed information about the Microsoft SQL Server error code 18456, a Database administrator can check the event log.

Here in this article, I am trying to explain to you all the possible solutions to fix “Login Failed for user (Microsoft SQL Server, error 18456)” error.

Also Read: SQL Database corruption Errors, Causes and Solutions

Why SQL Server login failed for user error 18456 occurs?

SQL Server login failed for user error can occur due to any of the following reason:

Invalid credentials when logging into SQL Server
Enabling wrong authentication mode
Password expired of the existing user account
Disable username on the Server

States of SQL Server Error 18456

State	Description
1	Authentication failure
2 and 5	Incorrect or invalid userid. (Error: 18456, Severity: 14, State: 5.)
6	Try to login with Windows but specifying as a SQL Server account. (Error: 18456, Severity: 14, State: 6.)
7	Login disabled. (Error: 18456, Severity: 14, State: 7.)
8	Password incorrect. (Error: 18456, Severity: 14, State: 8.)
9	Invalid password
11 and 12	Valid login credentials but server access failed. (Error: 18456, Severity: 14, State: 11.)

Workaround to resolve Microsoft SQL Server error code 18456

Before going through the procedure, make sure that the credentials used for login the SQL Server should be valid. Follow the below steps to enable “SQL Server Authentication” to log in through it.

Launch Microsoft SQL Server Management Studio.
Do right-click on SQL Server under Object Explorer, and go to Properties.
In the Server Properties dialogue box, click on Security tab.
Under Server authentication, opt for the SQL Server and Windows Authentication mode.
Here you need to expand Security -> Logins and right-click on the server name and select Properties.
Now in the Login properties dialogue box, pass the password details (using SQL Authentication).
Finally, move to the Status tab, and select Enabled under login and click OK and restart the SQL Server.

Also Read: How to Recover MS SQL Database from Suspect Mode

Note: If you want Windows Authentication instead of SQL Server Authentication option, it is required to have admin rights to the user to attempt successful login. To do so, follow the below-mentioned steps:

Launch SQL Server, and log in as an SQL administrator.
Move to the Server where the user account exists and click to Plus (+) sign to expand the Logins.
Right-click on the account that you want to log in as Windows Authentication, and click New Login.
Now in the Login – New dialogue box window, click Search and type username which you have selected in the Logins folder, and click on Check Names button.
Here in the Login-New window, move to Server Roles and check sysadmin.
Finally, go to the Securables tab to verify the role addition.

You can perform any of the above-mentioned methods to fix the “Login Failed for user (Microsoft SQL Server, error 18456)”. However, if you still unable to fix it, you can opt for a professional SQL Database Recovery tool.