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

    We are running SQl2005 Enterprise CTP2, and we keep getting

    Login failed for user 'administrator'. [CLIENT: 202.163.221.227]

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

    have already checked and the administrtor is part of the sys admin role

    Can any one help please??

  • Hello,

    We have a brand new server 2005 64 bit SP1. We can't see the logs of the server from the entreprise manager (error).

    When i get to the logs by other means, i always see this error :

    Logon       Error: 18456, Severity: 14, State: 11.

    Logon       Login failed for user 'NT AUTHORITY\SYSTEM'. [CLIENT: <local machine>]

    Do you have any idea ?

    Thnks

  • Hello again,

    I searched a bit more. NT AUTHORITY\SYSTEM login was missing (deleted???) I created a new one, gave the sysadmin role and i can read the logs again...

    The why of the disappear of the login is another story, we are DBA and system working on the server...

  • I'm setting up my first SQL2K5.  I've set up about seven SQK2K, but all of them use Windows Authentication.  This one has to use SQL authentication.  This is a research database with a single user.  I want to give him the db-owner role.  When I try to login with the login I made for him I get an MS SQL Error 4064. Cannot open default database. The database log says Error 18456 Severity 14 State 16.  The default database is referenced in the user login window and that database in online.  Is there something I need to do besides create a login to the database server for this user, create a login to his default database, and add him to the db_owner role?

    Thx.

  • HI,

    A couple of days back i have reported a problem about Error: 18456, Severity: 14, State: 5. and in SQl logs i used to get Login failed for user 'administrator'

    The solution was to disable SQl Fibers

    The system is running fine now.

    Thx

  • login failed user sa for sql srv 2000

  • PingBack from http://www.do-ict.nl/2007/01/28/sharepoint-and-sql-server-error-18456/

  • Had same problem state 14, I am running SQL 2005 dev edition with SP1, after some playing around I my user account didn't have access to the MSSQL folder, I found by running the SQL Server Management studio as 'administrator' from the right click menu all works fine.Very odd though as I installed SQL from my user which is an administrator, looks like vista handles user differently.

  • Hi, I'm getting a state 1 error with SQL Server 2005 Express - I've followed links from this forum but so far have had no luck at all in finding an answer (or even a cause!)

    Help!

  • Hi,

    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

    Logon Failure:

    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.

    Adam

  • Hi,all.

    I got an error state 1.

    I created a user called "sa"(because there was no system admin) and I tried to connect to the db engine with this user,but I can't.

    "Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. (Microsoft SQL Server, Error: 18452)" is displayed.

    How can I connect?

    please inform me to realhermes618@gmail.com

  • You probably don't have mixed mode security enabled on your SQL Server.  By default only integrated security is allowed.

    Open SQL Server Management Studio and right click on the instance node in the Object Explorer and select Properties.  Click on Security page and ensure that "SQL Server and Windows Authentication mode" option is selected.  Press OK and restart SQL.

  • Hi,

    I have SQL server 2005 enterprise edition and IIS  server 6.0 on one machine, and trying to connect through ASP which uses the IUSR_machinename account.

    I get error message 'login failed' and the serverlog shows state 11. That is great, but I haven't found any additional information as to solve this.  'server access failure' sounds pretty obscure. Any ideas how to resolve this ?

    THanks so much in advance,

    Luc

  • Hi All,

    Error: 18456, Severity: 14, State: 16 happened on my server when the owner of the files on the file system is not a user in SQL Server. I had the following scenario:

    I imported a database, and specified the name of the imported database in a connection string. This failed to connect with the login failed message, but worked when I connected via master and specified "Use Database" within my query as suggested by Il-Sung.

    When trying to generate database diagrams I was then told that the database did not have a valid owner, but when I right clicked on the databse properties an owner (sa) was listed. However, when I looked at the files owner, there was none specified.

    To correct this problem in SQL Sever 2005, do the following:

    1. Open SQL Server Management Studio

    2. Expand Databases

    3. Right Click on the Database with problems and choose 'Properties'

    4. Left Click the 'Files' node

    5. Ensure that an appropriate owner is listed, if none is then set it

    That resolved the issue with the database that I had this error with.

    Hope this helps,

    Nick

  • Hi All,

    We are using the following connection string in VB.NET

    "Server=.\PRG;Integrated Security=false;Database=test9 ";User Id=sa;Password=mypassword;"

    to connect to a database that we have created previously using a similar connection string:

    "Server=.\PRG;database=master;Integrated Security=false;Uid=sa;Pwd=mypassword;"

    This works sometimes, but most of the times it returns the following error:

    "Cannot open database "test9" requested by the login. The login failed.

    Login failed for user 'sa'"

    we are going crazy!!, can you help us?

    Thanks

Page 4 of 27 (396 items) «23456»