I came across an odd problem with SQL Server Express. We have a test server with various versions of SQL Server installed for QA to use. Of the QA techs was unable to connect to the instance of SQL Server Express when he used a SQL Server login. The code was not returning an error error code, so I decide to take a look at it.
I tried connecting to Express from MS SQL Server Management Studio under that account. SSMS returned an 18456 error, basically it couldn’t connect. That was just slightly more useful than what I had before, but it provide two things. It ruled out the app as cause of the problem, and it gave something that I could google with.
The best match I got was Understanding “login failed” (Error 18456) error messages in SQL Server 2005, on the SQL Protocols blog. Basically, the error 18456 error is purposely vague to prevent nasty people from trying to hack into your server. To figure out why the login failed, you need the “error state”. Error state is not returned to the client, but it’s written to the log file. That file will be in the LOGS folder of that instance of SQL Server under the name ERRORLOG. From the log file, you’ll get more information about why the login failed:
2007-04-10 09:54:45.14 Logon Error: 18456, Severity: 14, State: 8.
2007-04-10 09:54:45.14 Logon Login failed for user 'youridhere'. [CLIENT: 10.0.0.1]
From the first line, we get the eror state. Yay, it’s 8. So what does that mean? The states are documented in the blog posting reference above, but I couldn’t find it in my copy of the BOL. It is documented in the online version (and probably in the latest BOL) at <a title=”“Login failed for user ‘
| State | Description |
|---|---|
|
</p>
2 |
</p>
User ID is not valid. |
|
</p>
5 |
</p>
User ID is not valid. |
|
</p>
6 |
</p>
An attempt was made to use a Windows login name with SQL Server Authentication. |
|
</p>
7 |
</p>
Login is disabled, and the password is incorrect. |
|
</p>
8 |
</p>
The password is incorrect. |
|
</p>
9 |
</p>
Password is not valid. |
|
</p>
11 |
</p>
Login is valid, but server access failed. |
|
</p>
12 |
</p>
Login is valid login, but server access failed. |
|
</p>
18 |
</p>
Password must be changed. |
Any other error state is considered to be an internal error.
So it boils down to the password being incorrect, which in this case it was.