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 like getting my ass licked by dirty whores.
I am getting this erro when trying to connect to a sql exoress 2005 db throught vb.net on one of my pc's but not the other.
Error: 18456, Severity: 14, State: 16.
Assuming it is a permission problem, I created another DB on the PC that I cannot connect with & still cannot connect through the program. ANy suggestions would be appreciated. (I can be reached at email@example.com.
In one of our test environments we can connect locally through ODBC, but cannot connect from a vmware image or any remote box. This is a mixed mode SQL Server 2005 and we have a sqlserver user which we are using to connect (it is dbowner of all database
We have set Show_errors in the main db service to 1 and restarted, but we do not see the state (another machine on the network does on their own copy of SQL2005SP1). We also have the setting "Audit login errors only", but nothing gets added to the ERRORLOG.
How do we set this debug?
I had an "Error: 18456, Severity: 14, State: 11." in the log, meaning SQL was authenticating ok, but Windows was not. I am running Windows Vista. It turned out I needed to right-click on my app and run as Administrator. It now works fine.
We use Sql Server 2000 SP4 on Windows 2000 SP4. The audit level is set to login failure for this server but we don't get any state informpation in the log file. here is an extract of the log file :
2007-06-19 15:45:02.45 logon Échec de la connexion de l'utilisateur 'sa'.
2007-06-19 15:45:22.17 logon Échec de la connexion de l'utilisateur 'sa'.
2007-06-19 15:45:35.07 logon Échec de la connexion de l'utilisateur 'sa'.
2007-06-19 15:46:21.01 logon Échec de la connexion de l'utilisateur 'sa'.
Do you have an idea why I don't see any "state information" in the log file. I need this information to understand why I get this connection error.
By the way, the connection is OK 99% of the time and Sql Server is used by an ASP web application.
I am getting this erro when trying to run jobs in SQL Agent. I have confirmed the login account used to start Agent is valid. The account also has sysadmin privs....Stumped...
Like every one else, I was frustrated by the strange 18456 error with State=8. I store my password in a textfile, and when I need it, I do a copy and paste into the password field. I use a password something like "Ity@1%6$9#g".
I tried a number of "strangies", but this one worked on more than one occasion: using the direction arrow key pointing left, I moved the cursor back to the beginning of the password. It worked!
Try this at home, folks, it does not hurt a bit, and perhaps it might give the Microsoft boys a hint about the nature of the problem if you report the results.
thanks bertie,this worked for me
I discovered a few stepstones when connecting via JDBC to a locally installed SQLSever Express 2005 database. Here are my observations:Activate "Accepting TCP/IP" connections in the SQL Server Configuration Manager. Otherwise the SQLServer Expr
Yes, you are correct. The Microsoft SQL Server 2005 JDBC driver requires SQL Server (any SKU) to have TCP/IP support enabled.
I am also getting Error: 18456, Severity: 14, State: 8 when my .NET 1.1 app attempts to log into the 2005 Server from a remote machine. The app works against a 2000 Server. The password is correct, as the same login and password can be used to log into the 2005 server from the same remote machine with SSMS. The State: 8 is either bogus or too generic to be useful. Any help?
exec sp_password @new = 'sqlpassword', @loginame = 'sa'
alter login sa
with password = 'sqlpassword' unlock,
check_policy = off,
check_expiration = off
THANKS! Don't know why that worked, but it did, and I thank you.
In Windows Vista: All Programs -> Microsoft SQL Server 2005 -> (Right Click) SQL Server Management Studio -> Run as administrator
What operation you did after you click SSMS? It seems you need to first type the target server name and credential to login then connect.
Can you provide the error message say " 18456 Level ?? State ??" to help us identify the problem.
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.