SQL server error 18456 is a fairly frequent problem that is easily fixed by following some straightforward troubleshooting procedures.

All questions, no matter how small, are answered by Bobcares as part of our Microsoft SQL Server Support Services.

Let’s look more closely at how our Support team helped to fix SQL server error 18456.

Error 18456 in SQL server: How to resolve?

Occasionally, the error message will read, “Login failed for user ‘username>’.” Knowing this information will help us pinpoint the user whose account needs to be fixed. We can use the message’s error number as a guide when looking for the appropriate next steps. In this instance, the error code is 18456 from Microsoft SQL Server.

Other times, the severity and state number may not be present; we may only see “Microsoft SQL Server Error 18456.” A state number may not mean much on its own, but it can provide additional information about what is wrong and where to look next. The most typical error states are those with the code 18456.

Step 1: Connect to a remote desktop

We must be able to log into the server or at the very least establish a Windows Authentication connection to MSSQL using Microsoft SQL Server Management Studio in order to perform troubleshooting and find solutions. Connecting directly to the server using a Remote Desktop Connection is the most popular and straightforward method.

1. Firstly click the Start button.
2. Then, press Run.
3. Then enter mstsc and hit the Enter key.
4. Put the server’s IP address next to Computer.
5. Finally, click the Connect.

The Windows login screen will appear if everything goes according to plan.

Step 2: Launch Microsoft SQL Server Management

Run Microsoft SQL Server Management Studio after logging in to the server (SSMS). The best tool for configuring, managing, and administering MSSQL is SSMS.

We will be prompted to log into the server when we launch SSMS. Since Windows Authentication is typically enabled on MSSQL servers by default, we must log in using either the Windows Administrator account or the account designated as the SQL Administrator during MSSQL’s installation and configuration.

In addition to Windows Authentication, MSSQL supports SQL Server Authentication. We may or may not have SQL Server Authentication enabled by default depending on the MSSQL version and how it was installed and configured.

Step 3: Monitoring the Server Authentication Mode

We must check the security settings after entering SSMS with Windows Authentication to make sure MSSQL is configured to support both Windows and SQL Authentication.

1. Firstly, right-click the Server Name in SSMS’s Object Explorer window at the top.
2. Then select Properties.
3. Then click the Security page.
4. Error 18456, Login failed for user ‘’ is most likely caused if Windows Authentication is the only mode set up.
5. Finally, we can log into MS-SQL with either a Windows user name and password or a SQL user name and password by setting the server authentication mode to allow both SQL Server and Windows Authentication.

We’ll need to restart the SQL Server service after making this change.

Step 4: Start the SQL Service again

To apply the new authentication mode settings in SSMS, right-click the Server Name at the top of the Object Explorer window and select Restart.

Because Windows Authentication mode was the only mode set up in the example above and because the user ‘sa’ is a SQL user and SQL Server Authentication was not allowed, Error 18456 occurred.

Step 5: Monitoring SQL User Permissions

We must respond to the following inquiries as we examine the SQL user permissions:

• Is it okay for the user to log in?
• Has the user created a strong password?
• Has the user been granted the necessary rights to access the desired database?

1. Firstly, expand Security and then Logins in SSMS Object Explorer.
2. Then find the user who was unable to log in. This user login has been disabled, as indicated by an x on the user.
3. Then right-click the user and select Properties to enable login for that user.
4. Click the Status page after that. Click OK to enable login for the user.
5. Finally, we can verify that the user no longer has a red x after refreshing the list of user logins. The user should be able to log in as a result.
6. Right-click the user to continue troubleshooting the user.
7. Then choose Properties and click the General page.
8. Here, we can type a brand-new password before typing in the verification password.
9. Then to save the new password, click OK.
10. In order to be certain of the password when we try to log in, we set a new password for the user.

Step 6: Connecting the User to the Database

We check user mapping as the final step in user troubleshooting to ensure that the user has access to the desired database and to set or confirm their role in the database.

1. Firstly right-click the user.
2. Then choose Properties.
3. Then click the User Mapping page.
4. From the list of databases, we can choose the database.
5. Choose the required/desired memberships from the database role memberships.
6. Finally, Click OK.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, we discovered how to identify Error 18456’s specifics in order to find the issue’s root cause.
Still, seeking assistance? Our Microsoft SQL Server Support team is available at all times to help.