Microsoft SQL Server (MSSQL) login failures are a common problem that may get readily resolved with some basic troubleshooting procedures. Let’s take a look at the details of the issue to see if we can figure out what’s going on.
A Brief Overview To The SQL Server Error
This information will assist us the user we need to troubleshoot if the error appears as “login failed for user ‘username>’.” We’ll use the error number in the notification as a starting point for our search for future steps. It’s Microsoft SQL Server Error: 18456 in this circumstance.
We may see “Microsoft SQL Server Error 18456” with the severity and state number in other instances. A state number may not indicate much on its own, but it can provide further information about what’s wrong and where to check next.
The most common mistakes states are 18456 and 18457. The descriptions and possible remedies provide a fast overview and a possible troubleshooting aid.
| State | Description and Potential Solution |
| 1 | There is no extra information available. That usually means you don’t have the authority to get error details as a SQL Administrator. (Try logging in as an Administrator to the server and then connecting to SQL.) |
| 2/5 | The username is incorrect. (It’s possible that the user does not exist or that the user’s name gets misspelled.) |
| 6 | With SQL Authentication, a Windows username got used. (With Windows usernames, use Windows Authentication.) |
| 7 | The username’s login has been disabled, and the password has to get entered incorrectly. (As a SQL Administrator, activate the user’s login and reset the password for the user.) |
| 8/9 | The password for the user is wrong. (Confirm that the password is accurate, or log in as a SQL Administrator and reset the password.) |
| 11/12 | Although the login is genuine, access to the server got denied. (Run your program as an administrator, then assign a specific login and permissions to the Windows user on the SQL server.) |
| 18 | The password for the user must get changed. (Login as a SQL Administrator and change the user’s password.) |
Additional Error Information
The nature of the authentication issue gets intentionally hidden in the error message sent to the client to increase security. However, an error in the SQL Server error log has an error state that corresponds to an authentication failure. To figure why your login failed, compare the error status to the following list.
| State | Descritpion |
| 1 | It is not possible to obtain information about the error. That indicates that you do not have permission to receive error information. For more information, contact the SQL Server administrator. |
| 2 | The user ID you entered isn’t correct. |
| 5 | The user ID you entered isn’t correct. |
| 6 | When using SQL Server Authentication, an attempt got made to use a Windows login name. |
| 7 | The password is wrong, and the login is disabled. |
| 8 | The password has to get entered incorrectly. |
| 9 | The password is incorrect. |
| 11 | Although the login is genuine, access to the server got denied. When a Windows user has access to SQL Server as a member of the local group, but Windows does not provide administrator credentials, this error can occur. To connect, utilize the Run as administrator option to start the connecting software and add the Windows user to SQL Server as a login. |
| 12 | Although the login is genuine, access to the server got denied. |
| 18 | It is necessary to change the password. |
| 38, 46 | The database requested by the user could not get found. |
| 58 | When SQL Server gets configured Windows Authentication, a client tries to log in using SQL Authentication. Another reason for this is when the SIDs do not match. |
| 102-111 | Failure of the AAD. |
| 122-124 | Error due to an invalid user name or password. |
| 126 | The database that the user requested does not exist. |
| 132-133 | Failure of the AAD. |
Other error statuses indicate an unexpected internal processing error.
Solutions to Microsoft SQL Server Error 18456
Here’s the rundown of the best solutions to fix Microsoft SQL Server Error 18456.
- Log In with Remote Desktop
You must be able to log in to the server or at the very least be able to connect to MSSQL using Windows Authentication using Microsoft SQL Server Management Studio to do the troubleshooting and fixes. Connecting directly to the server using a Remote Desktop Connection is the most popular and easy option. These Knowledge Base articles will assist you in setting up a Remote Desktop Connection if you require more information.
- Run Microsoft SQL Server Management
You’ll want to start Microsoft SQL Server Management Studio once you’ve logged onto the server (SSMS). For configuring, managing, and administering MSSQL, SSMS is the ideal tool. You’ll get prompted to log in to the server when you launch SSMS. Windows Authentication gets enabled by default on most MSSQL servers, which means you must log in as the Windows Administrator or the SQL Administrator account supplied when MSSQL was installed and configured.
MSSQL supports SQL Server Authentication in addition to Windows Authentication. SQL Server Authentication may or may not be enabled by default, depending on the version of MSSQL and how it was installed and configured.
- Checking the Server Authentication Mode
Once we’ve logged into SSMS with Windows Authentication, we’ll need to double-check the security settings to see if MSSQL gets configured to accept both Windows and SQL Authentication. Choose Properties from the right-click menu of the Server Name at the top of the Object Explorer window in SSMS. After that, go to the Security page.
It is the most likely reason for Error 18456. Login failed for user ‘username>’ if Windows Authentication is the only mode configured. You can log in to MS-SQL with either a SQL user and password or a Windows user and password if the Server authentication option gets set to support SQL Server and Windows Authentication. You must restart the SQL Server service after making this change.
- Restart the SQL Service
To apply the new authentication mode settings, right-click the Server Name at the top of the Object Explorer window in SSMS and select Restart.
- Checking SQL User Permissions
We must answer the following questions as we examine SQL user permissions:
- Is it possible for the user to log in?
- Is the user’s password set up correctly?
- Does the user have the necessary rights to access the database in question?
Expand Security, Logins in the SSMS Object Explorer. Find the user who was unable to log in. A red x next to a user’s name shows that he does not log in.
Right-click the user and select Properties, then click the Status page to allow the person to log in. Click OK to enable the user’s login.
We can check that the user no longer has a red x after refreshing the list of user logins. The user should now be able to log in. In this case, the SQL user ‘sa’ does not log in due to a lack of permission.
To continue troubleshooting the user, right-click the user and select Properties, then go to the General page. You can create a new password here and confirm it with the confirmation password. To save the new password, click OK. We build a new password to know what and when we log in.
- Mapping the User to the Database
Checking user mapping to ensure the user has access to the required database and setting or verifying their database role is the final step in troubleshooting a user. Select Properties from the user’s right-click menu, then go to the User Mapping page. Choose a database from the drop-down menu. Select the desired/required memberships from the database role memberships. Click the OK button.
Conclusion
This article outlined how to identify Error 18456 characteristics in this troubleshooting post to assist us in hunting down the issue’s root cause. Connect with us if you face any query as our experts will love to help you out.