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 get this in the logs:
Error: 18456, Severity: 14, State: 11.
Did a scan through your blog page (very helpful, btw), but didn't see any recommendations for State = 11.
States 11 and 12 simply mean the Windows user coming in does not have login access to the server. SQL Server validated the Windows user's token, figured out who it is, but the Windows user has not been granted access to the server.
Today it is all about security relating to SQL Server. The first session is being taught by a Software
For State 11 - the login ID did not have a profile...I logged in to create the profile and all is right with the world!
Hi. I renamed my SQL Server 2005 machine (within the same domain), and any jobs (e.g. backups) I try to run from Mgt. Studio are now getting:
05/08/2007 03:28:04,Logon,Unknown,Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. [CLIENT: 10.70.40.55]
05/08/2007 03:28:04,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.
I have done the sp_dropserver/sp_addserver (w/ local) dance. This is a standalone server with no links. Service accounts are all using SYSTEM.
Any ideas what I can do to repair this?
I m also facing the same problem while connecting the server in single user mode..
Error: 18461, Severity: 14, State: 1.
Login failed for user 'sa'. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]
I tried to log in Database engine but it doesn't allow me to. It keeps giving me this message:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - No connection could be made because the target machine actively refused it.) (.Net SqlClient Data Provider)
I have no idea how to fix this problem. Could you please help me out? Thanks.
Hi. I have a problem with my connection. I checked the error log, it shows:
2007-05-19 22:19:27.47 Logon Error: 18456, Severity: 14, State: 11.
2007-05-19 22:19:27.47 Logon Login failed for user 'SQLServer\Andy'. [CLIENT: <local machine>]
Please help to solve this problem. Thanks so much.
Can you be more specific how you create the login and how you make connection by using which authentication, basically, the full connection string.
The stiuation is:
Database server : SQl Server 2005
databse mode: Single user mode
user (used to connect to database server):
sa (for sql authentication), local admin level user for windows authentication
Error: Error: 18461, Severity: 14, State: 1.
utilities tried to connect to database server: MS SQL SERVER managment studio and sqlcmd (the command line utility)
please suggest on the same. if i m missing any thing or for any other detail feel free to contact...
See this link if your error is related to:
Re: The AcquireConnection method call failed with error code 0xC0202009.
Read the whole blog. The 'post 20' blog from akeiii solved my problem with running 32-bit apps on 64-bit SQL and for connection issues to MS Access 2003 databases (must run in 32-bit mode). Besure to look at changing Step package type to "Operating System (Cmdexe)" and entering a command string line like: "C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /FILE "D:\Projects\Fremont Dialer SSIS\DMFDailyDataFeed\DMF_Daily_Data_Feed\DMFDailyDataFeed.dtsx" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING E.
Also look at using the dtexecui.exe to help create the command string that appears after the DTExec.exe program above.
The DTExec.exe in the "program files (x86)" directory.
If you need more clarification or help email me on email@example.com
Was this post helpful ?
I just installed Sql Server Deveplopment Edition along with SP2 on Vista. The login method chosen at the time of installation was Windows Authentication.
Installation was successful but I can not connect to Database engine and keep getting "Login failed for user" with State being 11 (sql error log). The sql server related services are running under LocalSystem account.
Figured out :)
The Sql Server 2005 needed to be run as Administrator. Dang it - Vista !!
SQL 2005 SP2
Failed login is a domain account but the error message says the client is <local machine>
Also, the failed login successfully logs in many times during the day.
There are no error messages in the SQL Agent log, just the SQL Server logs. Profiler didn't pick up any unsuccessful logins(despite the login failure showing up every minute on the SQL Server logs). I've confirmed that the same user account successfully logins to all of the databases that it's mapped to. No user complaints, just tons of failed login attempts in the SQL logs.
Wow, I can't believe I resolved this issue by luck!
I encountered this error on my local machine. So what I did was ...
1. Logon to SQL Server using windows authentication.
2. Right click in the query window that appears and select "Open Server in Object Explorer"
3. Go to object explorer and open the "Security" folder and then the "Logins" folder.
4. Double-click the "sa" user and change the password. Also, like another user mentioned above, untick the "Enforce Password Policy" in addition to resetting the password.
Now if you will all excuse me, I must go and play with myself.