我想我们都处理过错误 18456，无论是应用程序无法访问 SQL Server、凭据随时间变化，还是用户无法正确输入密码。对该错误号进行故障排除的技巧是，返回给尝试连接的客户端或应用程序的错误消息有意含糊不清（大多数错误的错误消息都类似，并且状态始终为 1）。在少数情况下，会包含一些附加信息，但在大多数情况下，这些条件中的一些对最终用户来说是相同的。为了找出真正出了什么问题，您需要具有对 SQL Server 的替代访问权限并检查日志中错误消息中的真实状态。我今天刚刚帮助我们的支持团队解决了一个客户的 18456 问题——一旦我们追踪错误日志并看到它是状态 16，
在 SQL Server 2012 中，有一个名为“包含的数据库”的新功能——我在这里和这里都写过关于它的博客. 此功能带来了新的安全层，如果您使用此功能，它可能会蔓延到您的视线：包含的用户身份验证失败。这里有各种各样的事情可能会出错。如果您与包含的用户连接但忘记指定数据库名称，SQL Server 将尝试授权您作为 SQL 登录名，并且您将失败并显示状态 5（如果没有使用该名称的 SQL 登录名）或状态 8（如果还有一个同名的SQL登录，但密码不匹配）。如果您指定了正确的用户名和包含的数据库，但输入了错误的密码，则会出现新的状态 65。身份验证过程的工作方式是，如果 SQL Server 在您指定的包含的数据库中找不到您的用户，它会在服务器级别再次尝试，然后放弃（它不会 如果您在那里匹配，请检查所有其他包含的数据库 - 我希望您同意这是一件好事）。如果您没有在连接字符串中指定数据库，那么它不会成功，除非 - 巧合 - 您拥有一个与服务器级登录用户具有相同用户名和密码的包含用户，该用户也可以访问您的包含数据库。这令人困惑，我强烈建议不要这样做。
当我看到人们在这个问题上苦苦挣扎时，我几乎总是看到这篇旧的（现在格式很糟糕）MSDN 博客文章的答案（参见MSDN 的另一个版本），其中有一个非常简短的部分列表和许多未回答的问题. 这里出现了一个更新的列表，其中包含一些有用的信息，但它仍然不完整。
所以这里是我认为登录失败的所有各种状态的更完整列表。为了完整性，我在状态 1 下包含了一个 18470 的实例。
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 '<x\y>'. 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 Domain\Username. 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 domain\username 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 '<x\y>'. 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.
我确定我错过了一些，但我希望这是对您可能遇到的大多数 18456 错误的有用总结。如果您发现任何不准确之处，或者您知道我遗漏的任何状态（或原因），请告诉我。
如果您使用的是包含数据库，则在解决登录失败时会有一些额外的复杂性，特别是如果您尝试创建与服务器级登录名相同的包含用户。这是一个你可能不想进入的probably……
原文链接：https://codingdict.com/
