Login failed for user ”. (Microsoft SQL Server, Error: 18456) in Sql Server 2008
- Posted by Sqltimes
- On October 14, 2012
- 0 Comments
This is an interesting error that I ran into recently. After some research and gathering useful information I stumbled up on this link from one of the Microsoft folks. This helped me appreciate the error message a bit more.
Login failures are common. We see them everyday (hopefully not on production machines). But the error message is very generic. From initial glance, all you can gather is that the login failed (Eureka !! I already know that). But this articles gives insight into what this seemingly simple (or obscure) message is by design. The key here is the State.
Lets look at a sample error message from Management Studio (SSMS):
|
1
2
3
4
5
6
7
8
9
10
11
|
TITLE: Connect to Server------------------------------Cannot connect to xxx.xxx.xxx.xxx. (IP Address of Server)------------------------------ADDITIONAL INFORMATION:Login failed for user ''. (Microsoft SQL Server, Error: 18456)For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476------------------------------BUTTONS:OK------------------------------ |
As you can see the first part says the obvious, albeit important. Gives name of the server you are trying to connect to. “ADDITIONAL INFORMATION:” section gives the meat of the error message that it’s a “Login Failure” with error number “18456”. Note the time when this error occurred (not displayed in the error message, but have an approximate idea when the error occurred).
Now let’s find the real reason why the login attempt failed.
Go to the server and look at “Sql Server Error Logs”. Open the most recent error log and scroll down to the approximate time when the error occurred. You see something like this.
Drill down to the Sql Server Error Logs
2012-10-09 11:14:25.21 Logon Error: 18456, Severity: 14, State: 8.
2012-10-09 11:14:25.21 Logon Login failed for user '<user name>'. Reason: Password did not match that for the login provided.[CLIENT: xxx.xxx.xxx.xxx]
This server error log provides the missing link to correctly understand what went wrong during login. Mostly it is either “password mismatch” or “Login disabled and password mismatch” or “Invalid userid”. This is identified by State in the error message.
Use the following table to map each state to its description.
| 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 |
The State value in the error message from Management Studio (SSMS) always says 1, it is by design. To keep it more obscure and secure. The puzzle is solved only by looking at the remaining description from the Error Log.
Thank you Il-Sung Lee (Program Manager, SQL Server Protocols) for your blog.
Update on December 04, 2012: For more Error States, please refer to this blog here. It saved me several times, recently, in figuring out what was going on.
Hope it helps,
0 Comments