Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Understanding "login failed" (Error 18456) error messages in SQL Server 2005

Rate This

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 1
Login 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>]

n
The key to the message is the 'State' which the server will accurately set to reflect the source of the problem.  In the example above, State 8 indicates that the authentication failed because the user provided an incorrect password.  The common error states and their descriptions are provided in the following table:
 

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

 
Other error states indicate an internal error and may require assistance from CSS.
 
Il-Sung Lee
Program Manager, SQL Server Protocols

Disclaimer: This posting is provided "AS IS" with no warranties, and confers no rights

Leave a Comment
  • Please add 5 and 8 and type the answer here:
  • Post
  • For users with State 11 or State 12:

    This will allow your Windows Login (with admin priv) to access the SQL server!

    msdn.microsoft.com/.../dd207004.aspx

  • how to  pix Error code sq l server 18456

  • LOL I just got this error message of 1.  Out of the blue.  No idea why this is happening . But Thank You microsoft for providing a such dog&hit error messages.  Gawd I hate M$

  • right click on the sql server manager studio and select run as administrator becose it treat your system name as subuser of administrator

  • ===================================

    Cannot connect to NAGARAJU-PC.

    ===================================

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

    ------------------------------

    ------------------------------

    Error Number: 2

    Severity: 20

    State: 0

    ------------------------------

    Program Location:

      at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

      at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)

      at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)

      at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)

      at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)

      at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)

      at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)

      at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)

      at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)

      at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)

      at System.Data.SqlClient.SqlConnection.Open()

      at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)

      at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

  • I have installed sql server 2005 yesterday,after that i restored all my  databases ,today i am traying to opening sql but   i am unable to login with WINDOWS AUTHENTICATION and  SQLSERVER AUTHENTICATION

    could you please suggest me.....

    Thanks

    Robert

  • What about state 1?  I'm getting error 18456 state 1 (severity 14)

  • Hi,

    I have an user with sysadmin permissions still it is giving state 16 error? Can someone please help? Thanks

  • SQLSTATE: 42000

    code: 4060

    message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database "DatMasterColegios__2013" requested by the login. The login failed.

    Error:

    este es el que a mi me aparece que puedo hacer

  • 08/06/2013 16:15:29,Logon,Unknown,Login failed for user '<username>'. Reason: Password did not match that for the login provided. [CLIENT: x.x.x.x]

    08/06/2013 16:15:29,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8

    I have tried resetting the password on the SQL Server 2012 and I am able to get back in just fine. However, if I switch PC's or logout of Windows 7 and back in again, and use the same credentials that just worked. The same error message comes up. We have roaming profiles enabled and we are trying to resolve why this keeps happening. Any input would be most helpful. Thanks!

  • I'm having problem to login as "sa" I already alter  password and changed the Auth mode, but still can't login. I having this error msg:

    Server Name: *****

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

  • Plz solve this Issues

    C:\Users\Administrator>sqlcmd -S F2502148 -U Administrator -P inf0db@

    Msg 18456, Level 14, State 1, Server F2502148, Line 1

    Login failed for user 'Administrator'.

  • Hello Every one

    Please help me :

    Ms Sql Server 2008 R2

    i try to login with CMD,but its not  work

    my exercise is below

    C:\Users\Administrator>sqlcmd -S F2502148 -U Administrator -P inf0db@

    Msg 18456, Level 14, State 1, Server F2502148, Line 1

    Login failed for user 'Administrator'.

  • I've had the same problem. Restart all the SQL Server services. To restart the services go to Start > Administrative Tools > Services and find the sql services and restart them.

  • One to a few times during the work day, seemingly at random, I am getting a log entry of "Login failed for user '<our domain>\mfreeman'. [CLIENT: 192.168.0.48]". This is paired with a log entry of "Error: 18456, Severity: 14, State: 16." at the same time.

    I captured this with Profiler and found that it is for the master database (which is online and not having any troubles -- I can run queries against it in SSMS just fine) and is coming from an ApplicationName of ".Net SqlClient" (which could be anything).

    This is my own login (which has the sysadmin role in the instance), and it is coming from my workstation. I get it

    Is there any way to find out more specifically what might be attempting the access or why it might be failing?

Page 26 of 27 (396 items) «2324252627