18456 에러 찾기
http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx
runas 를 통해서 로그인을 가장하고, 11 번 에러를 고의로 발생시킨 경우이다.
그러므로 11번 에러가 나면 다음 그림을 참고해서 데이터베이스 엔진에 연결할 권한을 주도록 한다.
이 외, 윈도우 계정의 암호를 바꾸면 Service 시작 계정의 암호도 바꾸어 주어야 하며, 특별한 경우에는 sql server 에 등록된 윈도우 계정을 삭제 했다가 다시 넣어줘야 되는 경우도 있다.
SQL Server v.Next (Denali) : Troubleshooting Error 18456
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. 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 setup with a default database that had been detached long ago.
In the next version of SQL Server, there is a new feature called "contained databases" - I've blogged about it here and here. With this feature comes a new layer of security that may creep onto your radar if you use this functionality: contained user login failures. There are a variety of things that can go wrong here. If you connect with a contained user but forget to specify a database name, SQL Server will attempt to authorize you as a SQL login, and you will fail with state 5 (if there is no SQL login with that name) or state 8 (if there is also a SQL login with the same name and the password doesn't match). There is also a new state 65 which occurs if you have specified the correct username and contained database, but entered an incorrect password.
When I see folks struggling with this problem, I almost always see the answer point to this blog post, which has a very brief partial list and a lot of unanswered questions:
http://blogs.msdn.com/b/sql_protocols/archive/2006/02/21/536201.aspx
So here is what I consider a more complete listing of all the various states for login failures, including changes for Denali (I tried to highlight them, but Community Server doesn't want to obey simple HTML). I included an instance of 18470 under state 1 for completeness.
State Description Example (note: the verbose message always has [CLIENT: <IP>] suffix)
1 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
Pre-SQL Server 2005, State 1 always appeared in the log for all login failures, making for fun troubleshooting. :-)
Error: 18470, Severity: 14, State: 1.
Login failed for user '<x>'.
Reason: The account is disabled.2 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. State 2 indicates that the login attempt came from a remote machine.
Error: 18456, Severity: 14, State: 2.
Login failed for user '<x>'.
Reason: Could not find a login matching the name provided.
5 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. Error: 18456, Severity: 14, State: 5.
Login failed for user '<x>'.
Reason: Could not find a login matching the name provided.6 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).
Error: 18456, Severity: 14, State: 6.
Login failed for user '<x\y>'.
Reason: Attempting to use an NT account name with SQL Server Authentication.7 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).
Error: 18456, Severity: 14, State: 7.
Login failed for user '<x>'.
Reason: An error occurred while evaluating the password.8 Probably the simplest of all: the password is incorrect (cASe sEnsiTiVitY catches a lot of folks here). Note that it will say "login" even if you attempted to connect as a contained user but forgot to specify a database (or specified the wrong database).
Error: 18456, Severity: 14, State: 8.
Login failed for user '<x>'.
Reason: Password did not match that for the login provided.9 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. Error: 18456, Severity: 14, State: 9.
???10 This is a rather complicated variation on state 9; as KB #925744 states, this means that password checking could not be performed because the domain account being used for the SQL Server service is disabled or locked on the domain controller. No reason is given in the verbose message in the error log. Error: 18456, Severity: 14, State: 10.
Login failed for user '<x\y>'.11 States 11 & 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).
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.12 See state 11 above.
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.13 This state occurs when the SQL Server service has been paused (which you can do easily from the context menu in Object Explorer). 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.16 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. 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 (see state 27).
Error: 18456, Severity: 14, State: 16.
Login failed for user '<x>'.18 Supposedly this indicates that the user needs to change their password. In SQL Server 2005, 2008 R2 and Denali, 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 go away.
Error: 18456, Severity: 14, State: 18.
???23 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. Error: 18456, Severity: 14, State: 23.
Login failed for user '<x>'.
Reason: Access to server validation failed while revalidating the login on the connection.27 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 (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.
Error: 18456, Severity: 14, State: 27.
Login failed for user '<x>'.28 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 prioer to SQL Server 2008.
Error: 18456, Severity: 14, State: 28.
Login failed for user '<x>'.38 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. 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.
Error: 18456, Severity: 14, State: 38.
Login failed for user '<x>'.
Reason: Failed to open the database specified in the login properties.40 Usually this means the login's default database is offline 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 (deprecated)). This is reported as state 16 prior to SQL Server 2008.
Error: 18456, Severity: 14, State: 40.
Login failed for user '<x>'.
Reason: Failed to open the explicitly specified database.46 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) 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.50 As the message implies, this can occur if the default collation for the login does not match the collation of their default database (or the database explicitly specified in the connection string). Error: 18456, Severity: 14, State: 50.
Login failed for user '<x>'.
Reason: Current collation did not match the database's collation during connection reset.51 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. 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.56 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. Error: 18456, Severity: 14, State: 56.
Login failed for user '<x>'.
Reason: Failed attempted retry of a process tokenvalidation.58 State 58 occurs when SQL Server is set to use Windows Authentication only, and a client attempts to log in using SQL Authentication. 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.65
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 (one reason why this is not recommended).
Error: 18456, Severity: 14, State: 65.
Login failed for user '<x>'.
Reason: Password did not match that for the user provided. [Database: '<x>']
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 in Denali, 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 helping with sanity checking.