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
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.
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.
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?
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
Line Number: 65536
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 Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)