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 2 and 7 and type the answer here:
  • Post
  • Hi, Hermann

       Please check out the following blog:  http://blogs.msdn.com/sql_protocols/archive/2007/06/18/connecting-to-sql-server-2005-on-vista-and-longhorn.aspx

    or install Yukon SP2, find out the article in Books online "How to: Connect to SQL Server from Windows Vista" for the issue understanding and resolution.

    Good Luck!

    Ming.

  • PingBack from http://sql.khanzhin.info/?p=9

  • I'm getting Error: 18456, Severity: 14, State: 16 at startup. I have a windows service that depends on SQLServer (Express) and tries to login using the 'Transactor' account. This fails at startup but I can login and start the service manually and it's fine. I would really appreciate some help with this, it wouldn't be a stretch to say I'm floundering. ERRORLOG follows:

    2007-08-08 14:18:39.25 Server      Authentication mode is MIXED.

    2007-08-08 14:18:39.25 Server      Logging SQL Server messages in file 'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.

    2007-08-08 14:18:39.25 Server      This instance of SQL Server last reported using a process ID of 2260 at 8/08/2007 2:14:43 p.m. (local) 8/08/2007 2:14:43 a.m. (UTC). This is an informational message only; no user action is required.

    2007-08-08 14:18:39.25 Server      Registry startup parameters:

    2007-08-08 14:18:39.25 Server       -d c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf

    2007-08-08 14:18:39.25 Server       -e c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG

    2007-08-08 14:18:39.25 Server       -l c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf

    2007-08-08 14:18:39.28 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

    2007-08-08 14:18:39.28 Server      Detected 2 CPUs. This is an informational message; no user action is required.

    2007-08-08 14:18:39.43 Server      Using dynamic lock allocation.  Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node.  This is an informational message only.  No user action is required.

    2007-08-08 14:18:39.50 Server      Database mirroring has been enabled on this instance of SQL Server.

    2007-08-08 14:18:39.50 spid5s      Starting up database 'master'.

    2007-08-08 14:18:39.64 spid5s      Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.

    2007-08-08 14:18:39.78 spid5s      SQL Trace ID 1 was started by login "sa".

    2007-08-08 14:18:39.79 spid5s      Starting up database 'mssqlsystemresource'.

    2007-08-08 14:18:39.79 spid5s      The resource database build version is 9.00.3042. This is an informational message only. No user action is required.

    2007-08-08 14:18:39.96 spid5s      Server name is 'TXWC02'. This is an informational message only. No user action is required.

    2007-08-08 14:18:39.96 spid5s      Starting up database 'msdb'.

    2007-08-08 14:18:39.96 spid8s      Starting up database 'model'.

    2007-08-08 14:18:40.09 spid8s      Clearing tempdb database.

    2007-08-08 14:18:40.32 Server      A self-generated certificate was successfully loaded for encryption.

    2007-08-08 14:18:40.32 Server      Server is listening on [ 'any' <ipv4> 1433].

    2007-08-08 14:18:40.32 Server      Dedicated administrator connection support was not started because it is not available on this edition of SQL Server. This is an informational message only. No user action is required.

    2007-08-08 14:18:40.32 Server      The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

    2007-08-08 14:18:40.32 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.

    2007-08-08 14:18:40.53 Logon       Error: 18456, Severity: 14, State: 16.

    2007-08-08 14:18:40.53 Logon       Login failed for user 'Transactor'. [CLIENT: 10.20.1.179]

  • Campbell, thanks!  for state 11, running as administrator worked.

  • State=16 means that the incoming user does not have permissions to log into the target database.  So for example say you create a user FOO and set FOO's default database to master, but FOO does not have permissions to log into master.

    This can also happen if for example the default database for user FOO is not online (for example the database is marked suspect).

    So to check on this theory, try logging the user into some other database (like master) and then try using the USE DATABASE command to switch to the target database, you will get a better error message as to the root cause of the failure.

  • Matt I'm assuming your comment is directed at my post. I understand what State=16 means, the issue is that it is only occurring when the machine is booting. My Windows service has a dependency on SQLServer so starts only after SQLServer has started. However I consistently get a login failure at boot time but when I subsequently login the Windows service can be started manually without any problem. I guess what I'm looking for is feedback on whether there is a workaround for this issue. I have seen a number of posts on the web about the issue but I haven't yet seen a good solution. The one solution I have seen is modifying the dependent service to loop with a delay for a period while trying to connect at startup. This is not a solution that will work for my environment as the Windows Service is a third party product (and no I cannot go back to the vendor and ask them to insert a delay at startup).

  • We have a new server running win 2003 and sql server 2005. Our users have an Access database that contains ODBC linked tables to the sql 2005 db.

    The windows users belong to an active directory security group and this group has been granted access to the database that Access is using.

    The server log is consistently showing the 18546 error with state 5 indicating invalid user? Users are quite happy and the underlying tables are being updated. I've been looking at this all day now and can see nothing obvious wrong.

    I'm new to sql 2005 so any help would be greatly appreciated.

  • This can happen for example if your company has auditing software running and checking if your SQL Server has weak passwords.  I see this periodically on my network.  Our IT group has a process that scans for SQL Servers and then attempts to log into these using weak sa passwords to detect insecure SQL Servers.

    Look at the source ip address and run ping -a <ip> to determine the source of the requests.

  • Hi,

    We've currently in the process of changing web hosts, and are having to move our databases to SQL Server 2005. In a trial run we tried to copy a database from our local SQL Server 2000 development machine to our hosted SQL Server 2005 machine. We got a 'login timeout' error when the package was being built. So, having installed SQL Server 2005 Developer edition on my local machine we're running tests trying to use the copy database wizard to copy a database between two SQL Server 2005 machines. We've had ports opened on both firewalls for this traffic, and have ensured that remote connections are allowed. We've located the error logs, and the following error is listed:

    Error: 18456, Severity: 14, State: 8.

    Now, thanks to this article I understand that this is a password mis-match, but what I don't understand is why! The passwords have been entered correctly (including case). I've tried changing the password via the Management Studio, and other suggestions here, but still no joy.  This is delaying our migration of sites and databases. If we just import the data from the existing server to the new one we lose all Primary Key information, and any defaults set for certain fields - information we need to retain as we bring the databases back to the local machine on a regular basis as part of our backup strategy. Can you help us to get this working?

    Thanks

  • I received this error message also after deleting/renaming the default database of the login concerned. After resetting the default database of the login, it's fine.

  • ...or if the default database is not set.

    (SQL 2005 Server Manager, Connect to Server with Admin --> Object Explorer --> Server --> Security --> Logins --> context menu on Login concerned --> Properties --> General

  • Although this blog is the most helpful source I detected so far, I can't find any helpful solution on

    Error: 18456, Severity: 14, State: 16.

    Login failed for user 'abc'. [CLIENT: <named pipe>]

    Problem is that we experience this error only occasionally, so I guess it's a timing problem. With other words: connections with exactly the same ODBC connection fails, some seconds later it works perfectly. Therefore it's not related to lack of rights and the errlog's don't show any hint that the DB is marked suspect.

    Any advices very warm welcome.

    Best regards, Martin.

  • I just want to say Thank you

    Your table lead me straight to the source of my issue and I was able to fix it within 2 minutes.

  • We have deleted one of the sharepoint portal from our sharepoint server along with the database. after deletion of the portel, we are continiously receiving the below event in our SQL 2005 server every 5 minutes.

    2007-10-10 20:54:18.35 Logon       Login failed for user 'SERWIZSOL\Svc-Sharepoint'. [CLIENT: 10.64.130.160]

    2007-10-10 20:54:18.37 Logon       Error: 18456, Severity: 14, State: 16.

  • The below message is the one I got when I tried to connect using Sql server 2005. Could anyone please try to help me  to sort it out

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

    Cannot connect to GURUMURTHY-PC.

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

    Login failed for user 'Gurumurthy-PC\Gurumurthy'. (.Net SqlClient Data Provider)

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

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

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

    Server Name: GURUMURTHY-PC

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

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

    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.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)

      at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)

      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()

Page 8 of 27 (398 items) «678910»