Topics from the Microsoft SQL Server Protocols team - Netlibs, TDS, SQL Browser, etc.
In continuing with the theme of understanding error messages I'll discuss the "login failed" messages that are surfaced by the client and written to the server's error log (if the auditlevel is set to log failures on login which is the default) in the event of an error during the login process.
If the server encounters an error that prevents a login from succeeding, the client will display the following error mesage.
Msg 18456, Level 14, State 1, Server <server name>, Line 1Login failed for user '<user name>'
Note that the message is kept fairly nondescript to prevent information disclosure to unauthenticated clients. In particular, the 'State' will always be shown to be '1' regardless of the nature of the problem. To determine the true reason for the failure, the administrator can look in the server's error log where a corresponding entry will be written. An example of an entry is:
2006-02-27 00:02:00.34 Logon Error: 18456, Severity: 14, State: 8.
2006-02-27 00:02:00.34 Logon Login failed for user '<user name>'. [CLIENT: <ip address>]
2 and 5
Attempt to use a Windows login name with SQL Authentication
Login disabled and password mismatch
11 and 12
Valid login but server access failure
SQL Server service paused
Change password required
Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights
I am getting an Error: 18456, Severity: 14, State: 11. when my users are triing to make an ODBC connection to SQL 2005 STD from Acess 2007. I have sqitched the SQL server from Windows Authentication mode to SQL server and Windows Authentication mode, this did not help. Any ideas? Thank you.
PingBack from http://ghillie-suits.info/?p=23716
I have added the sqlserver 2005 instance using sp_addlinkedserver. And when I try to use the linked server inside my query it says login failed for user 'sa'. When I checked at the error log on remote server(where the SQL Server 2005 is installed) the State was 8.
But I am able to connect to the SQL Server instance from Mgmt Studio Express and also using openrowset distributed queries.
Can I know the actual problem?
I am getting Error 18456 State 8 sporadically for many users. Sometimes they can log on OK, and sometimes not, using the same password. The logins and passwords were migrated from SQL2000 using sp_help_revlogins.
If I change the password, they can log on using the new password, but later that same new password is rejected. If they try again later, it may work again!
I'm having the same problem as Ken. My databases were restored to sql 2005 server and are in 2000 compatibility mode. Usually the logins work but occasionally for no apparent reason I get Error 18456 State 8. I know the password is correct as it is coming from the config file and not changing.
I tried deleting the users and recreating them by hand but still have the same sporadic problem.
SQL Server build is 9.0.3159 by the way
I have the same error. What is stange is that the ODBC connection was working last week when I used it and today when I tried again it failed... Any corruption?
I have the error 18456 with any users when one specific user is login in to the PC only???
Test1 on PC 1:
User A log to the PC
ODBC with UserA is fine
ODBC with UserB is fine
User B log to the PC
ODBC with UserB failed error 28000-18456
Test1 on PC 2
What is going on???
We have this strange Error:
 SQLServer Error: 18456, Login failed for user 'Domain\DOMAINAdministrator'. [SQLSTATE 28000] We have windows authentication turned on this happens on a MOSS Server with SQL Server 2005
i have encountered this login failed error with State 1 using SQL Server 2000. What does state 1 indicate?
While using the server explorer i had to make a connection where i encountered this error message.
Please mail me the replies asap to firstname.lastname@example.org
Hello, I have read technet forum about this error but I still have no answer about state 12. I have tried to give all permissions that I know for 'testlogin' except sysadmin and it has no effect! Please help!
I have an Error -18456 but no state level given. It just states Login failed to user.
Microsoft SQL server Error-18456.
I am trying to get access to sql server authentication mode. While I am able to get access to windows authentication mode.
I made shure to choose Mixed authentication mode while installing my sql server 2005 software.
Can any one help me thanx.
I think you will find that you have a sql security group or user which has a default database set which it no longer has access to.
If you look in the logs are you getting a successful login followed immediately by a failed login? that's what I had and it was a local group that the user was a member of which had that error. Gave public access to the db and done.
All I just received the same error and found the problem was related to Reporting Services. You may want to open SQL Server Configuration Manager, shutdown reporting services and retry the sqlcmd.
I am not certain if this has been mentioned. I am running Enterprise on Win2003 server.