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
What is the corresponding error in the server's error log?
Error: 18456, Severity: 14, State: 16.
I'm getting this error trying to connect a service to the database and the user I've verified has access to the database that it's trying to connect to. Is there something I need to do differently for a service? The service is related to MOSS 2007. It's the Microsoft Single Signon Service. Any suggestions?
I am getting this same error as Error number 18452 Severity 14 State 1 with SQL2005, Windows Authentication ONLY.
My user is permitted on the server, I can connect with Management Studio.
No space in ServerName, Windows firewall disabled.
The calling program is Delphi5
I'm seeing error state 23 repeatedly in my logs:
“Error: 18456, Severity: 14, State: 23.”
Despite some scouring of the Web and MSDN I could find no information about this one. Please post the answer if possible.
State 23 is pretty rare. What this means is the server was in the process of shutting down and at the same time some user was trying to log in. SQL blocks new users from logging in when the server is shutting down. So if you have fairly consistent logins I would expect to see this one from time to time when the server is shut down, it's harmless.
I'm receiving the Error 18456 in my Microsoft SQL Server 2000 - 8.00.818 Standard Edition but when looking in the ERRORLOG file I cannot see any state not severity number, Do I need to configure anything in order to obtain those?
Unfortunately, the correct error state is not reported by the SQL Server 2000 server. There is no configuration that can change this.
My email is email@example.com.
I have got SQL Server 2005 SP2 on WinXp SP2.
I tried sqlcmd -S machine_name -U machine_name\user_name -P user_password.
It failed with error: "Login failed for user machine_name\user_name"
I can open SSMS using run as machine_name\user_name,connect using windows authentication (in this case machine_name\user_name) and it works fine.
My question is why this command-line does not work
sqlcmd -S machine_name -U machine_name\user_name -P user_password?
The username and password you pass in as -U and -P are SQL Server usernames and passwords, not Windows usernames and passwords.
So you cannot say:
sqlcmd -S machine_name -U machine_name\user_name -P user_password
If you want to log in as a specific Windows user, then you need to shell a command prompt as that user and use:
sqlcmd -S machine_name -E
You can shell a cmd prompt by using the RunAs command, like so:
C:\>runas /user:machine1\user1 cmd.exe
Enter the password for machine1\user1:
Attempting to start cmd.exe as user "machine1\user1" ...
This will open a new cmd window and from there you can run sqlcmd -S server -E
The command line parameter " -U -P" is expected to use SQL Authentication which requires you log on as a SQL user. The SQL User can be a map of your windows account or non-windows account. In your case, you were using a machine account to access the DB. So, you can not use "-U -P", instead, do run as this machine account and execute "sqlcmd -S machine_name -E".
Or you can create a SQL login by
create login [login_name] with Password='...'
Then use sqlcmd -S machine_name -U [login_name] -P <pwd> to log on.
Hi everybody, ige this error when i try to connect to sql server from studio express
Error Number: 18456
Line Number: 65536
i can connect from visual studio and other softwares and from studio express on other machines. Does anyone know what the problem could be?
Have you looked at the server's error log and determined the error state reported by the server as outlined above?
I am having a similar problem where the state is 16. What is strange is that it occurs in the middle of a job and at intermittent intervals. The job would one day execute perfectly fine and the next day fail with error 18456 state 16. If you re-execute the job from the particular step that failed as the user that runns the job the job would succeed. The user that executes the job engine is a server admin and also starts all related SQL services. (SQL, SSAS, SSIS, etc)
Hi, useful information, please add this to Books Online
They found a fix to the barrage of event log entries. Basically there is a setting in SQL Enterprise Manager's Tools > Options menu to regularly "Poll" the server to find out its state. Regardless of what auth method was used to register the server, it uses the client machine's user credentials to try to determine the state of the SQL Server's services. Unchecking the box will stop the error messages.
Still don't know WHY this happens, though - anyone?
Post in reply to:
I have SQL 2000 SP 4 running with both SQL & Windows Auth. selected.
The moment I open Enterprise Manager from a computer that is not on the domain I get the following errors in the NT eventlog although I am using SQL Auth in Ent. Manager:
Logon attempt by: MICROSOFT_AUTHENTICATION_PACKAGE_V1_0
Logon account: [Remote NT User ID]
Source Workstation: [Remote Workstation Name]
Error Code: 0xC0000064
Reason: Unknown user name or bad password
User Name: [Remote NT User ID]
Domain: [Remote Workstation Name]
Logon Type: 3
Logon Process: NtLmSsp
Authentication Package: NTLM
Workstation Name: [Remote Workstation Name]
Caller User Name: -
Caller Domain: -
Caller Logon ID: -
Caller Process ID: -
Transited Services: -
Source Network Address: 10.2.5.16
Source Port: 0
I was under the impression that the whole point of SQL Auth. is not to try and Aut. the local logged on user?
Any pointers would be appreciated.