Ssms ошибка 18456

Just happened to me, and turned out to be different than all other cases listed here.

I happen to have two virtual servers hosted in the same cluster, each with it own IP address. The host configured one of the servers to be the SQL Server, and the other to be the Web server. However, SQL Server is installed and running on both. The host forgot to mention which of the servers is the SQL and which is the Web, so I just assumed the first is Web, second is SQL.

When I connected to the (what I thought is) SQL Server and tried to connect via SSMS, choosing Windows Authentication, I got the error mentioned in this question. After pulling lots of hairs, I went over all the setting, including SQL Server Network Configuration, Protocols for MSSQLSERVER:

Double clicking the TCP/IP gave me this:

The IP address was of the other virtual server! This finally made me realize I simply confused between the servers, and all worked well on the second server.

Just happened to me, and turned out to be different than all other cases listed here.

I happen to have two virtual servers hosted in the same cluster, each with it own IP address. The host configured one of the servers to be the SQL Server, and the other to be the Web server. However, SQL Server is installed and running on both. The host forgot to mention which of the servers is the SQL and which is the Web, so I just assumed the first is Web, second is SQL.

When I connected to the (what I thought is) SQL Server and tried to connect via SSMS, choosing Windows Authentication, I got the error mentioned in this question. After pulling lots of hairs, I went over all the setting, including SQL Server Network Configuration, Protocols for MSSQLSERVER:

Double clicking the TCP/IP gave me this:

The IP address was of the other virtual server! This finally made me realize I simply confused between the servers, and all worked well on the second server.

By default login failed error message is nothing but a client user connection has been refused by the server due to mismatch of login credentials. First task you might check is to see whether that user has relevant privileges on that SQL Server instance and relevant database too, thats good. Obviously if the necessary prvileges are not been set then you need to fix that issue by granting relevant privileges for that user login.

Althought if that user has relevant grants on database & server if the Server encounters any credential issues for that login then it will prevent in granting the authentication back to SQL Server, the client will get the following error message:

Msg 18456, Level 14, State 1, Server <ServerName>, Line 1
Login failed for user '<Name>'

Ok now what, by looking at the error message you feel like this is non-descriptive to understand the Level & state. By default the Operating System error will show ‘State’ as 1 regardless of nature of the issues in authenticating the login. So to investigate further you need to look at relevant SQL Server instance error log too for more information on Severity & state of this error. You might look into a corresponding entry in log as:

2007-05-17 00:12:00.34 Logon     Error: 18456, Severity: 14, State: 8.
or

2007-05-17 00:12:00.34 Logon     Login failed for user '<user name>'.

As defined above the Severity & State columns on the error are key to find the accurate reflection for the source of the problem. On the above error number 8 for state indicates authentication failure due to password mismatch. Books online refers: By default, user-defined messages of severity lower than 19 are not sent to the Microsoft Windows application log when they occur. User-defined messages of severity lower than 19 therefore do not trigger SQL Server Agent alerts.

Sung Lee, Program Manager in SQL Server Protocols (Dev.team) has outlined further information on Error state description:The common error states and their descriptions are provided in the following table:

ERROR STATE       ERROR DESCRIPTION
------------------------------------------------------------------------------
2 and 5           Invalid userid
6                 Attempt to use a Windows login name with SQL Authentication
7                 Login disabled and password mismatch
8                 Password mismatch
9                 Invalid password
11 and 12         Valid login but server access failure
13                SQL Server service paused
18                Change password required


Well I'm not finished yet, what would you do in case of error:

2007-05-17 00:12:00.34 Logon     Login failed for user '<user name>'.

You can see there is no severity or state level defined from that SQL Server instance’s error log. So the next troubleshooting option is to look at the Event Viewer’s security log [edit because screen shot is missing but you get the

idea, look in the event log for interesting events].

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.