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:

screenshot of TCP/IP configuration

Double clicking the TCP/IP gave me this:

TCP/IP properties, showing wrong IP address

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:

screenshot of TCP/IP configuration

Double clicking the TCP/IP gave me this:

TCP/IP properties, showing wrong IP address

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.

error 18456 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.

error 18456 sql server

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.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

You may encounter the SQL Server Error 18456 if the server could not authenticate the connection and this can be caused by the non-availability of the administrator rights to the SQL server or if the TCP/IP protocol is disabled in the SQL server settings.

The issue arises when the user tries to connect to the SQL server (local or remote) but encounters the error 18456 (with different states).

Login Failed Microsoft SQL Server Error 18456

Login Failed Microsoft SQL Server Error 18456 Fix

You can fix the SQL server error 18456 by trying the solutions below but before that, check if restarting the server, client computer, and networking computers solves the issue. Moreover, make sure you are typing the correct username and password (not copy-pasting the address).

Also, check if you are entering the correct database name (no typo in it) and make sure you have updated the configuration file accordingly. Furthermore, check if unlocking the account (by using the query ALTER LOGIN WITH PASSWORD= UNLOCK) solves the issue. If you are seeing the errors in the SQL errors log, then make sure your SQL server is not under attack. Last but not least, make sure the server’s clock and client computer clock is correctly set.

Launch the SQL Server as Administrator and Disable UAC on the Server

You may encounter the error 18456 if the SQL server does not have the elevated permissions to execute its operation and launching it as administrator (or disabling the UAC controls on the server) may solve the problem.

Open the SQL Server as Administrator

  1. Click Windows and type SQL Server Management Studio.
  2. Now right-click on SMSS and select Run as Administrator.
    Launch Microsoft SQL Server Management Studio as Administrator
  3. Then click Yes (if UAC prompt received) and check if the SQL server is clear of the error 18456.
  4. If not, then check if disabling UAC on the server machine solves the issue.

Launch the SQL Server in a Single User Mode

  1. Click Windows, type, and open the SQL Server Configuration Manager.
  2. Now right-click on the SQL Server service (in the SQL Server Services tab) and select Properties.
    Open Properties of the SQL Server
  3. Then head to the Startup Parameters tab and in the Specify a Startup Parameter box, type:
    -m
  4. Now click on Add and apply the changes.
    Add the “-m” Parameter to the Startup Parameters of the SQL Server
  5. Then right-click on the SQL Server service and select Restart.
    Restart the SQL Server Service
  6. Now click Windows, type: SQL Server Management Studio, right-click on SMSS, and select Run as Administrator.
  7. Now check if you can connect to the SQL Server as administrator.
  8. If so, then add the domain account to the SQL server and assign it the SysAdmin role.
  9. Now go back to the SQL Server Configuration Manager window and remove the -m parameter in the Startup Parameters tab.
  10. Then restart the SQL Server service (step 3) and check if the SQL server is working fine.

If the issue persists, check if the startup parameters or path details are properly configured. If the issue is still there, make sure your user account does have the required permissions to the database/ reporting services, and then check if the issue is resolved.

Enable the TCP/IP Protocol in the Server Configuration Manager

The error code 18456 in the SQL server means that the server could not authenticate the connection and this can happen if the TCP/IP protocol required to access the database on a network is disabled in the Server Configuration Manager. In this context, enabling the TCP/IP in the SQL Server Configuration Manager may solve the problem.

  1. Click Windows and expand Microsoft SQL Server with a year name like 2008 (you may need to scroll a bit to find the option).
  2. Now open SQL Server Configuration Manager and click Yes (if UAC prompt received).
  3. Then, expand SQL Server Network Configuration and select Protocols for (the server/database name) in the left pane.
  4. Now, in the right pane, double-click on TCP/IP and select Yes in the dropdown of Enabled.
    Open TCP/IP in Protocols of SQL Server Network Configuration
  5. Then apply your changes and click Windows.
    Enable TCP/IP in SQL
  6. Now type Services, right-click on the result of Services, and select Run as Administrator.
    Open Services as Administrator
  7. Then right-click on the SQL Server (with the server’s name) and select Restart.
    Restart the SQL Service in the Services Window
  8. Now check if the SQL server is clear of the error 18456.

If that did not do the trick, then make sure you are connecting to the right port of the SQL server (especially if you are using the server in a multi-server environment).

Change the Authentication Mode of the SQL Server

The SQL server might show the error 18456 if the authentication method of the SQL server is not properly configured (e.g: you are trying to login using SQL server authentication whereas the server is configured to use the Windows authentication). In this case, changing the authentication method of the SQL server may solve the problem. Before moving on make sure the status login for the present user (for example SA) is enabled.

  1. In the Object Explorer of Microsoft SQL Server Management Studio, right-click on your server and select Properties.
  2. Now, in the left pane, select Security, and in the right pane, select SQL Server and Windows Authentication (or vice versa).
    Enable SQL Server and Windows Authentication
  3. Then apply your changes and in the Object Explorer, right-click on the server.
  4. Now choose Restart and once restarted, check if you can connect to the database without error 18456.

If you cannot log into SQL, then you may install MS Power Tools and run the following in an elevated command:

psexec.exe -i -s ssms.exe

Afterward, you may use the installation account of SQL to make the changes and also make sure the SA account is not disabled:

Enable the SA Account and Reset the Account Password

If you cannot connect to the SQL Server, then enabling the SA account of the SQL server and resetting its password may solve the problem.

  1. Launch Microsoft SQL Server Management Studio (you may have to use the domain admin account) and expand Security.
  2. Then double-click on Logins and open SA.
    Open the SA Account in the Microsoft SQL Server Management Studio
  3. Now enter a new password and confirm the password (make sure you use a strong password).
  4. Then head to the Server Roles tab and make sure the following roles are selected:
    Public
    
    Sysadmin

    Enable the Public and Sysadmin Server Roles for the SA Account
  5. Now head to the Status tab and in the right pane, select Enabled (under Login).
    Enable the SA Account in SQL
  6. Then apply your changes and click the Windows button.
  7. Now type Services and right-click on it.
  8. Then select Run as Administrator and steer to the SQL Server service.
  9. Now right-click on it and select Restart.
  10. Once the service is restarted, check if the error 18456 of the SQL server is cleared.

Create a New Login and Restart the Reporting Services

If you cannot use any account to connect to the database, then creating a new login and restarting the reporting services may solve the problem.

  1. Launch the Microsoft SQL Server Management Studio and expand its Security tab.
  2. Then expand Logins and right-click on it.
  3. Now select New Login and enter the credentials (in the login name select the computer account) if using the SQL Server Authentication.
    Create a New Login in the SQL Server
  4. Then make sure to uncheckUser Must Change Password at Next Login” and select the database.
  5. Now head to the Server Roles tab and select the Public role.
  6. Then, in the User Mapping tab, make sure to choose the database and select db_owner.
    Select db_owner for the Database in SQL
  7. Now apply your changes and click Windows.
  8. Then type Services and right-click on the result of Services. Then select Run as Administrator.
  9. Now right-click on the SQL Server Reporting Service and select Restart.
    Restart the SQL Server Reporting Service
  10. Then reconnect to the database and check if the SQL server is clear of the error 18456.

If so, make sure that you have created a user in BUILTIN\administrators, and then you can use that user to manage the SQL Server. If you have restored the database from a backup, it will be better to remove and re-add the users to clear any old user entries. If you want to run the SQL server as a different user, then type Microsoft SQL Server in the Windows Search, Shift+Right-click on the SQL Server, and select Run as a Different User.  Last but not least, check if using Azure Data Studio with the SQL server sorts out the issue.

Photo of Kevin Arrows

Kevin Arrows

Kevin Arrows is a highly experienced and knowledgeable technology specialist with over a decade of industry experience. He holds a Microsoft Certified Technology Specialist (MCTS) certification and has a deep passion for staying up-to-date on the latest tech developments. Kevin has written extensively on a wide range of tech-related topics, showcasing his expertise and knowledge in areas such as software development, cybersecurity, and cloud computing. His contributions to the tech field have been widely recognized and respected by his peers, and he is highly regarded for his ability to explain complex technical concepts in a clear and concise manner.

Понравилась статья? Поделить с друзьями:
  • Sspicli dll ошибка win 10
  • Ssd ошибки стирания
  • Sso1000 ошибка аккаунта samsung
  • Ssangyong kyron ошибка 0108
  • Ssd ошибка доступа