Ошибка 18456 серьезность 14 состояние 8 sql server

title description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic helpviewer_keywords

MSSQLSERVER_18456

A connection attempt is rejected due to a failure with a bad password or username in SQL Server. See an explanation of the error and possible resolutions.

MashaMSFT

mathoma

jopilov, randolphwest

01/16/2023

sql

supportability

reference

18456 (Database Engine error)

MSSQLSERVER_18456

[!INCLUDE SQL Server]

Details

Attribute Value
Product Name SQL Server
Event ID 18456
Event Source MSSQLSERVER
Component SQLEngine
Symbolic Name LOGON_FAILED
Message Text Login failed for user ‘%.*ls’.%.*ls

Explanation

You get this error message when a connection attempt is rejected because of an authentication failure. User logins can fail for many reasons, such as invalid credentials, password expiration, and enabling the wrong authentication mode. In many cases, error codes include descriptions.

User action

The following examples are some of the common login failures. Select the exact error that you’re experiencing to troubleshoot the issue:

  • Login failed for user ‘<username>’ or login failed for user ‘<domain><username>’

  • Login failed for user ‘NT AUTHORITYANONYMOUS’ LOGON

  • Login failed for user ’empty’

  • Login failed for user ‘(null)’

Login failed for user ‘<username>’ or login failed for user ‘<domain><username>’

If the domain name isn’t specified, the problem is a failing SQL Server login attempt. If the domain name is specified, the problem is a failing Windows user account login. For potential causes and suggested resolutions, see:

Potential cause Suggested resolution
You’re trying to use SQL Server Authentication, but the SQL server instance is configured for Windows Authentication mode. Verify that SQL Server is configured to use SQL Server and Windows Authentication mode. You can review and change the authentication mode for your SQL Server instance on the Security page under Properties for the corresponding instance in SQL Server Management Studio (SSMS). For more information, see Change server authentication mode. Alternatively, you can change your application to use Windows Authentication mode to connect to SQL Server.
Note: You can see a message like the following one in the SQL Server Error log for this scenario:
Login failed for user '<UserName>'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.
Login doesn’t exist on the SQL Server instance you’re trying to connect to. Verify that the SQL Server login exists and that you’ve spelled it properly. If the login doesn’t exist, create it. If it’s present but misspelled, correct that in the application connection string. The SQL Server Errorlog will have one of the following messages:
— Login failed for user 'username'. Reason: Could not find a login matching the name provided.
— Login failed for user 'Domainusername'. Reason: Could not find a login matching the name provided.This can be a common issue if you deploy an application that uses a DEV or QA server into production and you fail to update the connection string. To resolve this issue, validate that you are connecting to the appropriate server. If not, correct the connection string. If it is, grant the login access to your SQL Server. Or if it’s a windows login grant access directly or add it to a local or domain group that is allowed to connect to the database server. For more information, see Create a Login.
You’re using SQL Server Authentication, but the password you specified for SQL Server login is incorrect. Check the SQL error log for messages like «Reason: Password did not match that for the login provided» to confirm the cause. To fix the issue, use the correct password in your application or use a different account if you can’t remember the password. Alternatively, work with your SQL Server administrator to reset the password for the account.
If the application is SQL Server Integration Services (SSIS), there may be multiple levels of a Configuration file for the job, which may override the Connection Manager settings for the package.
If the application was written by your company and the connection string is programmatically generated, engage the development team to resolve the issue. As a temporary workaround, hard-code the connection string and test. Use a UDL file or a script to prove a connection is possible with a hard-coded connection string.
Server name is incorrect. Ensure you’re connecting to the correct server.
You’re trying to connect using Windows authentication but are logged into an incorrect domain. Verify that you’re properly logged into the correct domain. The error message usually displays the domain name.
You aren’t running your application (for example, SSMS) as an administrator. If you’re trying to connect using your administrator credentials, start your application by using the Run as Administrator option. When connected, add your Windows user as an individual login.
Login is deleted after a migration to a contained database user. If the Database Engine supports contained databases, confirm that the login wasn’t deleted after migration to a contained database user. For more information, see Contained Database Authentication: Introduction.
Login’s default database is offline or otherwise not available. Check with your SQL Server administrator and resolve issues related to database availability. If the login has permissions to other databases on the server and you don’t need to access the currently configured default database in your application, use one of the following options:
— Request the administrator to change the default database for the login using ALTER LOGIN statement or SSMS.
— Explicitly specify a different database in your application connection string. Or if you’re using SSMS switch to the Connection Properties tab to specify a database that is currently available.Applications like SSMS may show an error message like the following one:
Cannot open user default database. Login failed.
Login failed for user <user name>. (Microsoft SQL Server, Error: 4064)
SQL Server Errorlog will have an error message like the following one:
Login failed for user '<user name>'. Reason: Failed to open the database '<dbname>' specified in the login properties [CLIENT: <ip address>]
For more information, see MSSQLSERVER_4064.
The database explicitly specified in the connection string or in SSMS is incorrectly spelled, offline, or otherwise not available. — Fix the database name in the connection string. Pay attention to case sensitivity if using a case sensitive collation on the server.
— If the database name is correct, check with your SQL Server administrator and resolve issues related to database availability. Check if the database is offline, not recovered, and so on.
— If the login has been mapped to users with permissions to other databases on the server and you don’t need to access the currently configured database in your application, then specify a different database in your connection string. Or if you’re connecting with SSMS, use the Connection Properties tab to specify a database that is currently available.
SQL Server Errorlog will have an error message like the following one:
Login failed for user <UserName>. Reason: Failed to open the explicitly specified database 'dbname'. [CLIENT: <ip address>]
Note: If the login’s default database is available, SQL Server allows the connection to succeed. For more information, see MSSQLSERVER_4064.
The user doesn’t have permissions to the requested database. — Try connecting as another user that has sysadmin rights to see if connectivity can be established.
— Grant the login access to the database by creating the corresponding user (for example, CREATE USER [<UserName>] FOR LOGIN [UserName])

Also, check the extensive list of error codes at Troubleshooting Error 18456.

For more troubleshooting help, see Troubleshooting SQL Client / Server Connectivity Issues.

Login failed for user NT AUTHORITYANONYMOUS LOGON

There are at least four scenarios for this issue. In the following table, examine each applicable potential cause, and use the appropriate resolution:
See the note below the table for an explanation of the term double hop.

Potential causes Suggested resolutions
You’re trying to pass NT LAN Manager (NTLM) credentials from one service to another service on the same computer (for example: from IIS to SQL server), but a failure occurs in the process. Add the DisableLoopbackCheck or BackConnectionHostNames registry entries.
There are double-hop (constraint delegation) scenarios across multiple computers. The error could occur if the Kerberos connection fails because of Service Principal Names (SPN) issues. Run SQLCheck on each SQL Server and the web server. Use the troubleshooting guides: 0600 Credential Delegation Issue and 0650 SQL Server Linked Server Delegation Issues.
If no double-hop (constraint delegation) is involved, then likely duplicate SPNs exist, and the client is running as a LocalSystem or another machine account that gets NTLM credentials instead of Kerberos credentials. Use SQLCheck or Setspn.exe to diagnose and fix any SPN-related issues. Also review Overview of the Kerberos Configuration Manager for SQL Server.
Windows Local Security policy may have been configured to prevent the use of the machine account for remote authentication requests. Navigate to Local Security Policy > Local Policies > Security Options > Network security: Allow Local System to use computer identity for NTLM, select the Enabled option if the setting is disabled, and then select OK.
Note: As detailed on the Explain tab, this policy is enabled in Windows 7 and later versions by default.
Intermittent occurrence of this issue when using constrained delegation can indicate presence of an expired ticket that can’t be renewed by middle tier. This is an expected behavior with either linked server scenario or any application that is holding a logon session for more than 10 hours. Change delegation settings on your middle-tier server from Trust this computer for delegation to specified services only – Use Kerberos Only to Trust this computer for delegation to specified services only — Use any protocol. For more information review Intermittent ANONYMOUS LOGON of SQL Server linked server double hop.

[!NOTE]
A double-hop typically involves delegation of user credentials across multiple remote computers. For example, assume you have a SQL Server instance named SQL1 where you created a linked server for a remote SQL Server named SQL2. In linked server security configuration, you selected the option Be made using the login’s current security context. When using this configuration, if you execute a linked server query on SQL1 from a remote client computer named Client1, the windows credentials will first have to hop from Client1 to SQL1 and then from SQL1 to SQL2 (hence, it’s called a double-hop). For more information, see Understanding Kerberos Double Hop and Kerberos Constrained Delegation Overview

Login failed for user (empty)

This error occurs when a user tries unsuccessfully to log in. This error might occur if your computer isn’t connected to the network. For example, you may receive an error message that resembles the following one:

Source: NETLOGON
Date: 8/12/2012 8:22:16 PM
Event ID: 5719
Task Category: None
Level: Error
Keywords: Classic
User: N/A
Computer: <computer name>
Description: This computer was not able to set up a secure session with a domain controller in domain due to the following: The remote procedure call was cancelled.
This may lead to authentication problems. Make sure that this computer is connected to the network. If the problem persists, please contact your domain administrator.

An empty string means that SQL Server tried to hand off the credentials to the Local Security Authority Subsystem Service (LSASS) but couldn’t because of some problem. Either LSASS wasn’t available, or the domain controller couldn’t be contacted.

Check the event logs on the client and the server for any network-related or Active Directory-related messages that were logged around the time of the failure. If you find any, work with your domain administrator to fix the issues.

Login failed for user ‘(null)’

An indication of «null» could mean that LSASS can’t decrypt the security token by using the SQL Server service account credentials. The main reason for this condition is that the SPN is associated with the wrong account.

To fix the issue, follow these steps:

  1. Use the SQLCheck or Setspn.exe to diagnose and fix SPN-related issues.

  2. Use SQLCheck to check whether the SQL Service account is trusted for delegation. If the output indicates that the account isn’t trusted for delegation, work with your Active Directory administrator to enable delegation for the account.

  3. Diagnose and fix Domain Name System (DNS) name resolution issues. For example:

    • Ping IP address by using PowerShell scripts:

      • ping -a <your_target_machine> (use -4 for IPv4 and -6 IPv6 specifically)
      • ping -a <your_remote_IPAddress>

    • Use NSLookup to enter your local and remote computer name and IP address multiple times.

  4. Look for any discrepancies and mismatches in the returned results. The accuracy of the DNS configuration on the network is important for a successful SQL Server connection. An incorrect DNS entry could cause numerous connectivity issues later.

  5. Make sure that firewalls or other network devices don’t block a client from connecting to the domain controller. SPNs are stored in Active Directory. If the clients can’t communicate with the directory, the connection can’t succeed.

Additional error information

To increase security, the error message that is returned to the client deliberately hides the nature of the authentication error. However, in the [!INCLUDEssNoVersion] error log, a corresponding error contains an error state that maps to an authentication failure condition. Compare the error state to the following list to determine the reason for the login failure.

State Description
1 Error information isn’t available. This state usually means you don’t have permission to receive the error details. Contact your [!INCLUDEssNoVersion] administrator for more information.
2 User ID isn’t valid.
5 User ID isn’t valid.
6 An attempt was made to use a Windows login name with SQL Server Authentication.
7 Login is disabled, and the password is incorrect.
8 The password is incorrect.
9 Password isn’t valid.
11 Login is valid, but server access failed. One possible cause of this error is when the Windows user has access to [!INCLUDEssNoVersion] as a member of the local administrators’ group, but Windows isn’t providing administrator credentials. To connect, start the connecting program using the Run as administrator option, and then add the Windows user to [!INCLUDEssNoVersion] as a specific login.
12 Login is valid login, but server access failed.
18 Password must be changed.
38, 46 Couldn’t find database requested by user.
58 When SQL Server is set to use Windows Authentication only, and a client attempts to log in using SQL authentication. Another cause is when SIDs don’t match.
102 — 111 Azure AD failure.
122 — 124 Failure due to empty user name or password.
126 Database requested by user doesn’t exist.
132 — 133 Azure AD failure.

Other error states exist and signify an unexpected internal processing error.

More rare possible cause

The error reason An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. can be returned in the following situations.

  • When the server is configured for mixed mode authentication, and an ODBC connection uses the TCP protocol, and the connection doesn’t explicitly specify that the connection should use a trusted connection.

  • When SQL server is configured for mixed mode authentication, and an ODBC connection uses named pipes, and the credentials the client used to open the named pipe are used to automatically impersonate the user, and the connection string doesn’t explicitly specify the use of a trusted authentication.

To resolve this issue, include TRUSTED_CONNECTION = TRUE in the connection string.

Examples

In this example, the authentication error state is 8. This indicates that the password is incorrect.

Date Source Message
2007-12-05 20:12:56.34 Logon Error: 18456, Severity: 14, State: 8.
2007-12-05 20:12:56.34 Logon Login failed for user ‘<user_name>’. [CLIENT: <ip address>]

[!NOTE]
When [!INCLUDEssNoVersion] is installed using Windows Authentication mode and is later changed to [!INCLUDEssNoVersion] and Windows Authentication mode, the sa login is initially disabled. This causes the state 7 error: «Login failed for user ‘sa’.» To enable the sa login, see Change Server Authentication Mode.

See also

  • 0420 Reasons for Consistent Auth Issues
Источник

Good afternoon,

I’d been asked by one of our users to enable mixed-mode logins on his laptop’s SQL Server (sandbox server). Having done that, I attempted to log in via the ‘sa’ account to verify, but ran into an issue. The basic config settings are below, followed by the startup log info, and finally the actual problem is at the bottom of the post.

=========================================
==============CONFIGURATION==============
=========================================

**These are the *enabled* settings**

Surface Area Configuration
—Local and remote connections
—-Using TCP/IP only

SQL Server configuration Manager
—Protocols for MSSQLSERVER
—-Shared Memory
—-TCP/IP
—Client Protocols
—-Shared Memory
—-TCP/IP

SSMS
—Server Properties
—-Security
——SQL Server and Windows Authentication mode
—-Connections
——Allow remote connections to this server
—Login Properties — sa
—-Both enforcement options *unchecked*
—-Default database — master
—-Status
——Permission to connect — Grant
——Login — Enabled

=========================================
===============STARTUP LOG===============
=========================================

2009-09-08 14:29:36.82 Server      Microsoft SQL Server 2005 — 9.00.1399.06 (Intel X86)
    Oct 14 2005 00:33:37
    Copyright (c) 1988-2005 Microsoft Corporation
    Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

2009-09-08 14:29:36.82 Server      (c) 2005 Microsoft Corporation.
2009-09-08 14:29:36.82 Server      All rights reserved.
2009-09-08 14:29:36.82 Server      Server process ID is 2188.
2009-09-08 14:29:36.82 Server      Logging SQL Server messages in file ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG’.
2009-09-08 14:29:36.82 Server      This instance of SQL Server last reported using a process ID of 5300 at 9/8/2009 2:29:31 PM (local) 9/8/2009 6:29:31 PM (UTC). This is an informational message only; no user action is required.
2009-09-08 14:29:36.82 Server      Registry startup parameters:
2009-09-08 14:29:36.84 Server           -d C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf
2009-09-08 14:29:36.84 Server           -e C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG
2009-09-08 14:29:36.84 Server           -l C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.ldf
2009-09-08 14:29:36.91 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2009-09-08 14:29:36.91 Server      Detected 2 CPUs. This is an informational message; no user action is required.
2009-09-08 14:29:38.77 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.
2009-09-08 14:29:39.24 Server      Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2009-09-08 14:29:41.29 Server      Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2009-09-08 14:29:41.37 Server      Database Mirroring Transport is disabled in the endpoint configuration.
2009-09-08 14:29:41.55 spid5s      Starting up database ‘master’.
2009-09-08 14:29:42.13 spid5s      Recovery is writing a checkpoint in database ‘master’ (1). This is an informational message only. No user action is required.
2009-09-08 14:29:42.60 spid5s      SQL Trace ID 1 was started by login «sa».
2009-09-08 14:29:42.74 spid5s      Starting up database ‘mssqlsystemresource’.
2009-09-08 14:29:43.58 spid9s      Starting up database ‘model’.
2009-09-08 14:29:43.60 spid5s      Server name is ‘TANDEL-47’. This is an informational message only. No user action is required.
2009-09-08 14:29:43.76 Server      Error: 17190, Severity: 16, State: 1.
2009-09-08 14:29:43.76 Server      FallBack certificate initialization failed with error code: 1.
2009-09-08 14:29:43.80 Server      Warning:Encryption is not available, could not find a valid certificate to load.
2009-09-08 14:29:43.83 Server      Server is listening on [ ‘any’ <ipv4> 1433].
2009-09-08 14:29:43.83 Server      Server local connection provider is ready to accept connection on [ \.pipeSQLLocalMSSQLSERVER ].
2009-09-08 14:29:43.83 Server      Server local connection provider is ready to accept connection on [ \.pipesqlquery ].
2009-09-08 14:29:43.83 Server      Server is listening on [ 127.0.0.1 <ipv4> 1434].
2009-09-08 14:29:43.83 Server      Dedicated admin connection support was established for listening locally on port 1434.
2009-09-08 14:29:43.83 spid9s      Clearing tempdb database.
2009-09-08 14:29:43.91 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
2009-09-08 14:29:44.79 spid13s     Starting up database ‘msdb’.
2009-09-08 14:29:44.79 spid15s     Starting up database ‘eVisioning4.3’.
2009-09-08 14:29:44.79 spid14s     Starting up database ‘ReportServerTempDB’.
2009-09-08 14:29:44.79 spid12s     Starting up database ‘ReportServer’.
2009-09-08 14:29:47.49 spid9s      Starting up database ‘tempdb’.
2009-09-08 14:29:49.23 spid12s     The Service Broker protocol transport is disabled or not configured.
2009-09-08 14:29:49.24 spid12s     The Database Mirroring protocol transport is disabled or not configured.
2009-09-08 14:29:50.40 spid12s     Service Broker manager has started.
2009-09-08 14:29:51.35 spid15s     CHECKDB for database ‘eVisioning4.3’ finished without errors on 2009-08-09 00:00:08.340 (local time). This is an informational message only; no user action is required.
2009-09-08 14:29:51.43 spid5s      Recovery of any in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC) has completed. This is an informational message only. No user action is required.
2009-09-08 14:29:51.43 spid5s      Recovery is complete. This is an informational message only. No user action is required.

=========================================
==========DESCRIPTION OF PROBLEM=========
=========================================

*Note* all connection attempts are being made inside SSMS.

When connecting through Windows Authentication, I can log in just fine using my domain account. When I attempt to log in through the SQL Server «sa» account, I get the following message:
A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 — No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

This corresponds with the following entry in the log:
2009-09-08 14:33:52.69 Logon       Error: 18456, Severity: 14, State: 8.
2009-09-08 14:33:52.69 Logon       Login failed for user ‘sa’. [CLIENT: <local machine>]

If I specify in the connection dialog to explicitly use TCP/IP, instead of the above message, I simply get the same «login failed» entry as the logfile (minus the State 8 info).

Now, I know that State 8 typically means a password mismatch, however I went in early on in the troubleshooting process and changed the password to the letter ‘a’ for the purpose of eliminating any possible mistypes. I am 100% certain the password I’m entering is correct.

I can log in via the ‘osql’ shell command without any problems.

The problem only seems to crop up when using the SSMS app (installed on the same machine as the SQL Server).

Additionally, I tried creating a new user and logging in through it. Same problem as ‘sa’.

I’ve tried just about every reasonable combination of settings I can think of. I don’t use SQL Server myself, so I’m running out of ideas on where to go from here.

Thoughts?

—Zack Townsend

Источник

Не думали, что это троян? Еще вариант — локально стоит софт, который неправильно сконфигурирован (при установке тот увидел наличие MsSQL и предложил там хранить логи, но пароль не угадал и теперь все время бомбит).

Если сами не можете «вычислить» виновного просмотром списка запущенных процессов, то воспользуйтесь программой, которая показывает открываемые сокеты — смотрите какой процесс «каждые 2-5 секунд по 3-5 раз» открывает соединение на порт 1433 или 1434.

Источник

Updated in July 2020 with a few new states.

I think we’ve all dealt with error 18456, whether it be an application unable to access SQL Server, credentials changing over time, or a user who can’t type a password correctly.

The trick to troubleshooting this error number is that the error message returned to the client or application trying to connect is intentionally vague – the error message is similar for most errors, and the state is always 1. In a few cases, some additional information is included, but for the most part several of these conditions appear the same to the end user. The reason for this is to be careful not to disclose too much information to a would-be attacker.

But this makes troubleshooting hard.

In order to figure out what is really going wrong, you need to have alternative access to the SQL Server and inspect the log for the true state in the error message. I helped our support team just today solve a client’s 18456 issues – once we tracked down the error log and saw that it was state 16, it was easy to determine that their login had been set up with a default database that had been detached long ago.

When I see folks struggling with this problem, I almost always see them pointed to this old MSDN blog post (or this other version from MSDN), which has a very brief partial list and a lot of unanswered questions. A newer list appears here, with some useful info, but it is still incomplete.

So here is what I consider a more complete listing of all the various states for login failures. I included an instance of 18470 under state 1 for completeness.

State Example / Description
(note: the verbose message usually has [CLIENT: <IP>] suffix)
1 Error: 18470, Severity: 14, State: 1.
Login failed for user ‘<x>’.
Reason: The account is disabled.
State 1 now occurs when a login is disabled – but actually, the error in the log is 18470, not 18456 – because the login is disabled, it doesn’t get that far. See state 7.Prior to SQL Server 2005, State 1 always appeared in the log for all login failures, making for fun troubleshooting. 🙂
2 Error: 18456, Severity: 14, State: 2.
Login failed for user ‘<x>’.
Reason: Could not find a login matching the name provided.
The login (whether using SQL or Windows Authentication) does not exist. For Windows Auth, it likely means that the login hasn’t explicitly been given access to SQL Server – which may mean it is not a member of an appropriate domain group. It could also mean that you’ve created a server-level login, mapped a database user with a different name to that login, and are trying to connect using the user name, not the login name. This is the same as State 5, but State 2 indicates that the login attempt came from a remote machine.
5 Error: 18456, Severity: 14, State: 5.
Login failed for user ‘<x>’.
Reason: Could not find a login matching the name provided.
Like state 2, the login does not exist in SQL Server, but the login attempt came from the local machine. For both state 2 and 5, prior to SQL Server 2008, the reason was not included in the error log – just the login failed message. And starting in Denali, for both state 2 and 5, this error can happen if you specify the correct username and password for a contained database user, but the wrong (or no) database. Note that if you are trying to connect to a contained database using the connection dialog in SSMS, and you try to <Browse server…> for the database instead of typing the name explicitly, you will first receive a prompt «Browsing the available databases on the server requires connecting to the server. This may take a few moments. Would you like to continue?» If the SQL auth credentials do not also match a login at the server level, you will then receive an error message, because your contained user does not have access to master.sys.databases. The error message in the UI is, «Failed to connect to server <server>. (Microsoft.SqlServer.ConnectionInfo)Login failed for user ‘<x>’. (Microsoft SQL Server, Error: 18456).» The takeaway here: always specify the database name explicitly in the options tab of the connection dialog; do not use the browse feature.
6 Error: 18456, Severity: 14, State: 6.
Login failed for user ‘<xy>’.
Reason: Attempting to use an NT account name with SQL Server Authentication.
This means you tried to specify SQL authentication but entered a Windows-style login in the form of DomainUsername. Make sure you choose Windows Authentication (and you shouldn’t have to enter your domain / username when using Win Auth unless you are using runas /netonly to launch Management Studio). In SQL Server 2012 at least, you will only get state 6 if the domainusername format matches an actual domain and username that SQL Server recognizes. If the domain is invalid or if the username isn’t an actual Windows account in that domain, it will revert to state 5 (for local attempts) or state 2 (for remote attempts), since the login doesn’t exist.
7 Error: 18456, Severity: 14, State: 7.
Login failed for user ‘<x>’.
Reason: An error occurred while evaluating the password.
The login is disabled *and* the password is incorrect. This shows that password validation occurs first, since if the password is correct and the login is disabled, you get error 18470 (see state 1 above). It’s possible that your application is sending cached credentials and the password has been changed or reset in the meantime – you may try logging out and logging back in to refresh these credentials.
8 Error: 18456, Severity: 14, State: 8.
Login failed for user ‘<x>’.
Reason: Password did not match that for the login provided.

Probably the simplest of all: the password is incorrect (cASe sEnsiTiVitY catches a lot of folks here). Note that it will say «the login provided» even if you attempted to connect as a contained database user but forgot to specify a database, specified the wrong database, or typed the password incorrectly – unless it finds a match, SQL Server doesn’t have any idea you were attempting to use a contained database user.

An interesting case here is Docker containers – docker run will allow you to spin up a container and specify an SA_PASSWORD with certain special characters, like $. However, you will never be able to connect to the container with that password. If you use non-alphanumerics, stick to slightly more benign characters like # and *.
9 Error: 18456, Severity: 14, State: 9.
Login failed for user ‘<xy>’.
Like state 2, I have not seen this in the wild. It allegedly means that the password violated a password policy check, but I tried creating a login conforming to a weak password policy, strengthened the policy, and I could still log in fine. And obviously you can’t create a login with, or later set, a password that doesn’t meet the policy. Let me know if you’ve seen it.
10 Error: 18456, Severity: 14, State: 10.
Login failed for user ‘<x>’.
This is a rather complicated variation on state 9; as KB #925744 states, this means that password checking could not be performed because the login is disabled or locked on the domain controller (note that if SQL Server does not start, it could be because the account that is locked or disabled is the SQL Server service account). No reason or additional information is provided in the «verbose» message in the error log.
11
12		 Error: 18456, Severity: 14, State: 11.
Login failed for user ‘<x>’.
Reason: Login-based server access validation failed with an infrastructure error. Check for previous errors.

 Error: 18456, Severity: 14, State: 12.
Login failed for user ‘<x>’.
Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors.

States 11 and 12 mean that SQL Server was able to authenticate you, but weren’t able to validate with the underlying Windows permissions. It could be that the Windows login has no profile or that permissions could not be checked due to UAC. Try running SSMS as administrator and/or disabling UAC. Another reason could be that the domain controller could not be reached. You may need to resort to re-creating the login (see this post from Simon Sabin). Finally, PSS has recently released more information about states 11 and 12; see this post for potential scenarios and solutions, and also see states 146-149 below for changes in SQL Server 2016.
13 Error: 18456, Severity: 14, State: 13.
Login failed for user ‘<x>’.
Reason: SQL Server service is paused. No new connections can be accepted at this time.
This state occurs when the SQL Server service has been paused (which you can do easily and even accidentally from the context menu in Object Explorer).
16 Error: 18456, Severity: 14, State: 16.
Login failed for user ‘<x>’.

 You may also see:

 A connection was successfully established with the server, but then an error occurred during the pre-login handshake.

State 16, which only occurs prior to SQL Server 2008, means that the default database was inaccessible. This could be because the database has been removed, renamed, or is offline (it may be set to AutoClose). This state does not indicate a reason in the error log. In 2008 and beyond, this is reported as state 40 (see below), with a reason. In SQL Server 2005, this state may also be reported if the user’s default database is online but the database they explicitly requested is not available for the reasons stated above (also see state 27). If you get the pre-login handshake message, it may be because you’ve disabled SSL on the server.
18 Error: 18456, Severity: 14, State: 18.
Login failed for user ‘<x>’.
Supposedly this indicates that the user needs to change their password. In SQL Server 2005, 2008 R2 and SQL Server 2012, I found this was raised as error 18488, not 18456; this is because for SQL logins the change password dialog just delays logging in, and is not actually a login failure. I suspect that, like state 16, this state will no longer appear in future versions of SQL Server.
23 Error: 18456, Severity: 14, State: 23.
Login failed for user ‘<x>’.
Reason: Access to server validation failed while revalidating the login on the connection.
There could be a few reasons for state 23. The most common one is that connections are being attempted while the service is being shut down. However if this error occurs and it is not surrounded in the log by messages about SQL Server shutting down, and there is no companion reason along with the message, I would look at KB #937745, which implies that this could be the result of an overloaded server that can’t service any additional logins because of connection pooling issues. Finally, if there *is* a companion reason, it may be the message indicated to the right, indicating that SQL Server was running as a valid domain account and, upon restarting, it can’t validate the account because the domain controller is offline or the account is locked or no longer valid. Try changing the service account to LocalSystem until you can sort out the domain issues.
27 Error: 18456, Severity: 14, State: 27.
Login failed for user ‘<x>’.
State 27, like state 16, only occurs prior to SQL Server 2008. It means that the database specified in the connection string has been removed, renamed, or is offline (possibly due to AutoClose) – though in every case I tried, it was reported as state 16. This state does not indicate a reason in the error log. In 2008 and onward this is reported as state 38 (see below), with a reason.
28 Error: 18456, Severity: 14, State: 28.
Login failed for user ‘<x>’.
I have not experienced this issue but I suspect it involves overloaded connection pooling and connection resets. I think you will only see state 28 prior to SQL Server 2008.
38 Error: 18456, Severity: 14, State: 38.
Login failed for user ‘<x>’.
Reason: Failed to open the database specified in the login properties.

 or

 Reason: Cannot open database «<database>» requested by the login. The login failed.

The database specified in the connection string, or selected in the Options > Connection Properties tab of the SSMS connection dialog, is no longer valid or online (it might be set to AutoClose or the user may simply not have permission). I came across this once when I typed <default> here instead of picking that option from the list. This is reported as state 27 or state 16 prior to SQL Server 2008.

 Note that this could also be a symptom of an orphaned login. After establishing mirroring, Availability Groups, log shipping, etc. you may have created a new login or associated a user with a login on the primary database. The database-level user information gets replayed on the secondary servers, but the login information does not. Everything will work fine – until you have a failover. In this situation, you will need to synchronize the login and user information (for one example, see this script from the late Robert Davis).

40 Error: 18456, Severity: 14, State: 40.
Login failed for user ‘<x>’.
Reason: Failed to open the explicitly specified database.
Usually this means the login’s default database is offline (perhaps due to AutoClose) or no longer exists. Resolve by fixing the missing database, or changing the login’s default database using ALTER LOGIN (for older versions, use sp_defaultdb, which is now deprecated). This is reported as state 16 prior to SQL Server 2008.
46 Error: 18456, Severity: 14, State: 46.
Login failed for user ‘<x>’.
Reason: Failed to open the database configured in the login object while revalidating the login on the connection.
State 46 may occur when the login (or login mapping to the service account) does not have a valid database selected as their default database. (I am guessing here but I think this may occur when the login in question is attempting to perform log shipping. Again, just a guess based on the few conversations I discovered online.) It can also occur if the classifier function (Resource Governor) or a logon trigger refers to a database that is offline, no longer exists, or is set to AutoClose.
50 Error: 18456, Severity: 14, State: 50.
Login failed for user ‘<x>’.
Reason: Current collation did not match the database’s collation during connection reset.
As the message implies, this can occur if the default collation for the login is incompatible with the collation of their default database (or the database explicitly specified in the connection string). It can also happen if they are using a client tool like Management Studio which may, when they have been disconnected, try to connect to master upon reconnection instead of their default database.
51 Error: 18456, Severity: 14, State: 51.
Login failed for user ‘<x>’.
Reason: Failed to send an environment change notification to a log shipping partner node while revalidating the login.
Like states 11 & 12, this could have to do with UAC, or that the domain controller could not be reached, or that the domain account could not authenticate against the log shipping partner, or that the log shipping partner was down. Try changing the service account for SQL Server to a known domain or local account, rather than the built-in local service accounts, and validating that the partner instance is accessible, as well as the database that is being requested in the connection string and the default database of the login. Note that this could be trigged by the failover partner connection string attribute, and that the database may no longer exist or may be offline, single user, etc.
56 Error: 18456, Severity: 14, State: 56.
Login failed for user ‘<x>’.
Reason: Failed attempted retry of a process token validation.
State 56 is not very common – again, like states 11 & 12, this could have to do with UAC, or that the domain controller could not be reached. Try changing the service account for SQL Server to a known domain or local account, rather than the built-in local service accounts.
58 Error: 18456, Severity: 14, State: 58.
Login failed for user ‘<x>’.
Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only.
State 58 occurs when SQL Server is set to use Windows Authentication only, and a client attempts to log in using SQL Authentication. It can also occur when SIDs do not match (in which case the error text might be slightly different).
62 Error: 18456, Severity: 14, State: 62.
Login failed for user ‘<x>’.
State 62 occurs when a Windows Authentication account tries to access a contained database, and the contained database exists, but the SIDs do not match.
65 Error: 18456, Severity: 14, State: 65.
Login failed for user ‘<x>’.
Reason: Password did not match that for the user provided. [Database: ‘<x>’]
Contained user exists, the database is correct, but the password is invalid. This can also happen if you use a SQL login to connect to a contained database that has a contained user with the same name but a different password (one of several reasons this is not recommended).
102
103
…
110
111		 Error: 18456, Severity: 14, State: 102.
Error: 18456, Severity: 14, State: 103.
Error: 18456, Severity: 14, State: 104.
Error: 18456, Severity: 14, State: 105.
Error: 18456, Severity: 14, State: 106.
Error: 18456, Severity: 14, State: 107.
Error: 18456, Severity: 14, State: 108.
Error: 18456, Severity: 14, State: 109.
Error: 18456, Severity: 14, State: 110.
Error: 18456, Severity: 14, State: 111.
Documented by Microsoft as Azure Active Directory login failures.
122
123
124		 Error: 18456, Severity: 14, State: 122.
Error: 18456, Severity: 14, State: 123.
Error: 18456, Severity: 14, State: 124.
According to Microsoft, these indicate a blank or missing username and/or password.
126 Error: 18456, Severity: 14, State: 126.
The docs say «Database requested by user does not exist.» But it’s not clear why you would get 126 instead of, say, 38 or 40.
132
133		 Error: 18456, Severity: 14, State: 132.
Error: 18456, Severity: 14, State: 133.
Documented by paschott and by Microsoft as Azure Active Directory login failures.
146
147
148
149		 Error: 18456, Severity: 14, State: 146.
Login failed for user ‘<Windows auth login>’.
Reason: Token-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission.

 Error: 18456, Severity: 14, State: 147.
Login failed for user ‘<SQL auth login>’.
Reason: Login-based server access validation failed with an infrastructure error. Login lacks Connect SQL permission.

 Error: 18456, Severity: 14, State: 148.
Login failed for user ‘<Windows auth login>’.
Reason: Token-based server access validation failed with an infrastructure error. Login lacks connect endpoint permission.

 Error: 18456, Severity: 14, State: 149.
Login failed for user ‘<SQL auth login>’.
Reason: Login-based server access validation failed with an infrastructure error. Login lacks connect endpoint permission.

These states replace states 11 and 12 above, but only in SQL Server 2016 or better. The goal was to make the actual underlying issue easier for the sysadmin to diagnose between SQL auth and Windows auth logins, and between connect and endpoint permissions (all without giving any further info to the user trying to log in). For more details, see the latter part of this post.

I am sure I missed some, but I hope that is a helpful summary of most of the 18456 errors you are likely to come across. Please let me know if you spot any inaccuracies or if you know of any states (or reasons) that I missed.

If you are using contained databases, there will be a little extra complication in solving login failures, especially if you try to create contained users with the same name as server-level logins. This is a ball of wax you just probably don’t want to get into…

Thanks to Jonathan Kehayias (blog | twitter), Bob Ward (CSS blog | twitter), and Rick Byham for input and sanity checking.

Источник
  • Remove From My Forums

  • Question

  • This is on SQL Server 2005 for the sa account. I am just looking the in the sql server error log.I know this is a password mismatch. Where is it mismatched? Does there have to be a ‘sa’ network user account now with 2005?

    Any help would be very appreciated.

    Joanne Mahoney

Answers

  • This sound like a either a brute force attack, or an application which is not aware of a password chang (if the password was changed the last time). Try to run a network packet sniffer to see who is connecting from with Adresses with the wrong password.

    Jens K. Suessmeyer

    —
    http://www.sqlserver2005.de
    —

  • raymillr,

    This link should contain the information that you need:

    http://blogs.msdn.com/sql_protocols/archive/2006/02/21/536201.aspx


Источник

  • Ошибка 18456 sql server при подключении
  • Ошибка 18456 sql server 2019
  • Ошибка 18456 sql server 2005
  • Ошибка 18456 sql 2012
  • Ошибка 184 установка radeon software не может быть продолжена так как ваша текущая система windows