Microsoft SQL Server is one of the most widely used database system that allows users to handle multiple databases. It provides an authentication feature to let the authorized user access the database. So, a user needs to provide login credentials to access specific database. However, it has been observed that when an attempt is made to login to server, it displays SQL Server error 18456, which restricts them to access the database account. In this section, we are going to discuss various types of error 18456 in SQL server and simple steps to resolve them.
What is SQL Server Login Error 18456
SQL Server allows the login to database by using certain credentials: Server name, login, password. However, in certain cases when a user attempts to login to the database, it generates error code 18456 indicating wrong login credentials. As a result, the users become unable to access database and corresponding components.
Microsoft SQL Server Login Error Code 18456 States
The error 18456 may arise with different error states, which may be due to different reasons. Thus, depending upon the situation, major error states generated by SQL Server include:
1) State 2 & State 5 : Invalid UserId
SQL Server generates error 18456 with state 2 and 5, if you are trying to login to server with invalid userid. This may arise due to following reasons:
- The server has not yet granted access to the user Id. Due to restricted access, SQL Server does not allow to login to the specified server.
- There may be spelling or typing mistake in the server name entered by the user.
Error: 18456, Severity: 14, State: 5.
Login failed for user ‘chepsi’. Reason: Could not find a login matching the name provided. [CLIENT: ]
1) Firstly, make sure that the server name entered is absolutely correct and accurate.
2) If the server name is accurate but the login problem still persists, then go for authenticate mode settings. Follow the below mentioned steps to enable the authentication for specific server:
- Using SQL Server Management Studio, navigate Object Explorer >> Connect.
- Right click on the server name.
- Choose Properties option from the list.
- Under Server Properties, Go to Security option.
- Select SQL Server and Windows Authentication mode for allowing server access.
This procedure enables SQL Server authentication mode so that user id can be easily associated from the server at the time of login.
2) State 7: login disabled and password mismatch
SQL Server generates this error state in case the login account for the specified user has been disabled and user is trying to login with wrong password.
Error: 18456, Severity: 14, State: 7.
Login failed for user ‘chepsi’. Reason: An error occurred while evaluating the password. [CLIENT: ]
Firstly, the disabled account needs to be enabled from admin end. This can be done by the following steps:
- Using SQL Server Management studio, login to system account using valid credentials.
- Browse Object Explorer >> Security >> Logins.
- Now, right click on the specific user name and select Properties option.
- Under status tab, mark Enabled for login option.
- Click on Ok to save the changes made.
3) State 8: Password Mismatch
One of the most commonly generated error state is due to incorrect password entered by the user. It means that the password entered by the user at the time of login does not match with the actual password. As a result, SQL Server displays password mismatch error and restricts login.
Error: 18456, Severity: 14, State: 8.
Login failed for user ‘chepsi’. Reason: Password did not match that for the login provided. [CLIENT:]
The user can reset the password for a particular account with the help of admin. The following steps need to be performed to reset or change the password:
- Login to System account using the valid credentials.
- Navigate Object Explorer >> Security >> logins.
- Select the user name whose password needs to be changed.
- Right click on user name and click on Properties option.
- Under General settings window, type new password.
Once the login password is changed, the user can login with new password and access the database easily.
4) State 11 & 12: Valid login but server access failure
This error indicates that the login credentials provided by the user are correct but Windows Authentication has failed. The most possible reasons leading to this error state include:
1. The login for the particular account is denied from permission class.
2. The CONNECT permission might be granted for the Group users; instead of individual login.
Error: 18456, Severity: 14, State: 11.
Login failed for user ‘chepsi’. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT:]
In case of SQL related denial error, SQL Server provides error state 12:
Error: 18456, Severity: 14, State: 12.
Login failed for user ‘chepsi’. Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT:]
For error state 11, perform the following measure:
- Delete the current login account and recreate with new credentials
- Run SQL Server as admin and then, try to login again.
- Another way may be to disable the UAC mode.
However, the error state 12 can be resolved by:
Using admin access, make sure that the access is allowed for the specified user. If not, enable the login access.
5) State 13: SQL Server service paused
In case, the user tries to login to SQL server at the moment when SQL service has stopped, the server generates an error state 13. It indicates that the server cannot establish any connection at the moment.
Error: 18456, Severity: 14, State: 13.
Login failed for user ‘chepsi’. Reason: SQL Server service has been paused. No new connection can be accepted at this time. [CLIENT:]
Restarting the SQL Server services may eradicate this error and allow smooth login for the specified account. So, try to restart the server by following steps:
- Click on Object Browser from SQL Server Management Studio.
- Now, connect to the instance of database service using Connect option.
- Click on Restart to initiate the server services again.
Automated way to Resolve SQL Error Code 18456
The users can also go for third party SQL password recovery tool to overcome various states error related to error code 18456. It allows to recover the password and access the account without encountering any kind of login error.
SQL Server authentication issues may create haphazard for the users and hamper their regular tasks. One such error is Microsoft SQL Server error 18456, which is basically a login error that restricts the users from accessing the account. Various error states associated with the error and their resolutions have been discussed. Moreover, an automated SQL password recovery tool has also been discussed to let the users overcome error fastly and efficiently.