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. The login (whether using SQL or Windows Authentication) does not exist. Pre-SQL Server 2005, State 1 always appeared in the log for all login failures, making for fun troubleshooting. 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. I included an instance of 18470 under state 1 for completeness.Įxample (note: the verbose message always has suffix) 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). 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: There is also a new state 65 which occurs if you have specified the correct username and contained database, but entered an incorrect password. 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 are a variety of things that can go wrong 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. In the next version of SQL Server, there is a new feature called "contained databases" - I've blogged about it here and here. 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 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. 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 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). 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.
0 kommentar(er)
