The generic message “Login Failed for User (Microsoft SQL Server, Error: 18456)” will often mean you entered invalid credentials when logging into SQL Server. If you are using the correct credentials, this blog will discuss possible solutions.

Log in errors with Microsoft SQL Server (MSSQL) are a fairly common issue and can be easily solved with some basic troubleshooting steps. Before we dig in, let’s take a look at the details of the SQL server error 18456 and try and determine the cause.

Solutions to Microsoft SQL Server Error 18456

Sometimes, the error presents as “login failed for user ‘<username>’,” this information will help us as we identify the user we need to troubleshoot. From the message, we’ll know the error number as a reference to search for the next steps. In this case, it is Microsoft SQL Server, Error: 18456.

Other times the error message that is returned to the client deliberately hides the nature of the authentication error or may show a severity or state number. In the SQL Server error log, the corresponding error contains an error state that maps to an authentication failure condition. Compare the error state to the following list to determine the reason for the login failure.

These states of the error, 18456, are the most common. The descriptions and potential solutions offer a quick explanation and potential troubleshooting guide.

Probable causes of SQL server login error code 18456

SQL Server login failures can happen due to various reasons.

• The username or password entered is incorrect.
• The wrong authentication mode is enabled.
• A single username may have different passwords on different servers. So the user must be sure that he is inputting the right combination.
• Password of the user account is expired.
• User account is deleted from the server.

How to resolve SQL server error code 18456

In most cases, users encounter Microsoft SQL Server Error 18456 with state 6 when they attempt to authenticate using SQL Server Authentication. In the SQL connection windows switch to the Windows Authentication as the authentication type and tries to connect to the SQL database under an account with local administrator rights.

If Windows Authentication works properly 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 SQL server error 18456. If you want to login through ‘SQL Server Authentication’, then you need to open the Properties of your SQL Instance and go to the Security tab. Change the server authentication mode to SQL Server and Windows Authentication mode (this is the mixed authentication mode).

Follow these steps below

1. Go to the Properties of 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.

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.

1. In the Login – New window, click Search.
   
2. Now type the username of the account which you have selected in the Logins folder and then click Check Names. Click OK.
   
3. Now back in the Login – New window, go to the Server Roles tab and check the sysadmin option.
   
4. Now go to Securables tab, and verify the role addition.

In this blog I have explained SQL Server error 18456, what its causes and its states indicating the type of error. The above-mentioned methods are used to resolve SQL server login problems.