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.
Login 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 error to 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 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.
- 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 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 try 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 situation, the server will not recognize the user and user will get the failed login 18456 error. 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
- Go to the Properties of selected server (from the right click menu).
- Now go to the Security page and under Server authentication choose the option SQL Server and Window Authentication mode.
- Now expand Security > Logins and right-click the server name, and select Properties.
- In the Login properties dialogue box, provide the password details (under SQL Authentication).
- Now go to the Status tab, and choose Enabled under Login.
- 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 login 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.
- In the Login – New window, click Search.
- Now type the username of the account which you have selected in the Logins folder and then click Check Names. Click OK.
- Now back in the Login – New window, go to the Server Roles tab and check the sysadmin option.
- Now go to Securables tab, and verify the role addition.
In this blog I have explained about SQL Server error 18456, what are its causes and its states indicating the type of error. The above mentioned methods are used to resolve SQL server login problems.