Understanding "login failed" (Error 18456) error messages in SQL Server 2005


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


Comments (411)

  1. Alan says:

    Im developing the 5 stars programme in .NET

    Im having this problem with sql server 2005 in the first example of c#:

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

    What should I do?

    My email: alankind@hotmail.com

    Thanks!

  2. Ignacio Abel says:

    I have exactly the same problem using SQL Server 2005.

    Cheers,

    Ignacio Abel.

  3. Matt Neerincx [MSFT] says:

    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 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.

  4. EH says:

    Hello,

    when connecting from ODBC with SQL security, all connections are failing.

    Error is 18456, Severity: 14, State: 8.

    Creating new logins, and triple-checking the passwords did not help…

    Thank you

    EH

  5. Tom says:

    I am having the same error.  The database engine will not allow any logons.  The other engines seem to be fine.  This still means that I do not have permissions for any of the databases.  This is using local or domain accounts.  

    I created this problem by detaching one database running on the server.  I went to connect the datafiles and I cannot get into the server at all.

  6. Matt Neerincx [MSFT] says:

    When you say "Same Error" I am assuming you mean:

     Error is 18456, Severity: 14, State: 8.

    This error is pretty clear, means that the password supplied does not match the password given.  Note that passwords in SQL 2005 are case-sensitive, this could be an issue.

    If you are a local administrator to the computer, then you should always be able to log into SQL.

    Also, try using the admin port if the normal ports are not working, for example, to connect to local default instance:

     sqlcmd -E -Sadmin:.

    To connect to local named instance:

     sqlcmd -E -Sadmin:.Instance1

  7. Tom says:

    Thanks so much for your help.

    I was able to use SQLCMD to gain access and rebuild access for my database

    admin account.  I was then able to use this account to reattach the detached

    database.  When I did this the database and all user access to SQL2005 was

    back to normal.  I am not sure why this happened.  We have about ten other

    databases on this SQL server.  The same users had access to these tables as

    well as the detached table so the detached table was not the only table for

    which they had access.  The detached table was just a table of ‘production’

    data.  

    I will try and see if I can recreate the problem but will wait for

    the weekend to try that!

  8. Rolf says:

    Re: state 16 – if there is no other database to log into, how do we determine what the problem is?  Users have installed SQL Server Express 2005 using Windows Authentication, where user id and password should not be an issue, right?

  9. rm says:

    hi there

    i’m getting a ‘State: 1′ (yes, in the server’s event log). what could this mean?

    thanks in advance!

  10. SQL Protocols says:

    Regarding ‘State: 1′, are you running SQL Server 2005 or SQL Server 2000?  Supportability improvements were made to 2005 to make the states more unique but 2000 still reports ‘State: 1′ in every case.

    - Vaughn

  11. rm says:

    we’re running SQL Server 2005 CTP. server authentication is set to "SQL Server and Windows Authentication mode". when connecting using windows accounts everything is fine, but any attempt to connect as e.g. ‘sa’ fails with this ‘State: 1′ error message… greets, rm

  12. SQL Protocols says:

    Another question, are you looking at the error message state that the client receives or the error message state in the server error log?  By default the server error log is at "C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG".  Also, you mentioned you’re running at SQL2K5 CTP rather than RTM.  Which CTP are you using?  If you’re using an old CTP it may be the case that unique state improvement hadn’t yet been made.

  13. shi says:

    I’m getting Error: 18456, Severity: 14, State: 27

    Could you help me on state 27, please.

    I’m running SQL 2005 version 9.00.1399.06

    Thx

  14. Hi Shi,

    Error state 27 signifies the server could not determine the initial database for the session.  Did you specify a database name in your connection string?  Are you sure that the name was spelled correctly?

    Il-Sung.

  15. Sinish Gopi says:

    This Error is completly related to SQL Server authontication. This will ome where there will be a mismatch in the Existing Password and the Given passowrd.

    I faced this issue when I changed the SQL Server start up account.

    sinishkg@hotmail.com

  16. Nam says:

    I have error state 10 what does that mean?

  17. EH says:

    Hi,

    just a hint for those with state 8 (wrong password):

    With SQL 2005, the passwords are CASE-SENSITIVE, see http://support.microsoft.com/kb/907284

  18. Tan says:

    Hi,

    08/01/2006 02:28:39,Logon,Unknown,Error: 18456 Severity: 14 State: 23.

    08/01/2006 02:28:47,Logon,Unknown,Error: 18456 Severity: 14 State: 16.

    We had the problem with error state 16 and 23 on our SQL 2005 (64 bits). The error came and gone by itself, any ideas?

    Tan

  19. Steve says:

    We get this error trying to connect using tcp/ip. sa can connect using tcp/ip, but all other sql logins fail. This began after creating a TCP ENDPOINT listening on port 1090. The endpoint has been dropped, server restarted. Cannot get sql logins to connect using tcp/ip.

    Login failed for user ‘PrefAdmin’. [CLIENT: …]

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

  20. Steve says:

    re: can’t connect using tcp/ip – when the logins are in the sysadmin role, they can connection via tcp/ip – otherwise they cannot.

  21. SQL Protocols says:

    Hi, Steve

       The error state 12 indicates that your sql account has no access to the server, have you grant login to it? Can you also try connecting over named pipe, what happens?

       Overall, this is not connectivity issue, the following forum can help you w/ the specifiy sql security problem.

       http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1

    Good Luck!

    ming.

  22. jeff_yao says:

    I get the following error messages in the sql server error log

    Source Logon

    Message

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

    and

    Source Logon

    Message

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. [CLIENT: 185.23.11.33]

    The scenario is: We set up log-shipping (LS) between a clustered sql server system (source server) and a stand-alone sql server box (target server). (SQL Server 2K5 EE + SP1), and LS goes very well, but on the target server, we found the above-mentioned error messages.

    Thanks for your help in advance…

  23. Vineet Dewan says:

    I tried to connect to the SQL Server 2005 Express edition Microsoft Server Management Studio.

    I gave the followign:

    Authentication: SQL Sever Authentication

    Login: sa

    Password:

    It gave me error 18456

    On checking the log, following entry was found

    2006-09-12 14:18:19.20 Logon       Error: 18456, Severity: 14, State: 7.

    2006-09-12 14:18:19.20 Logon       Login failed for user ‘sa’. [CLIENT: <local machine>]

    Please let me know how to allow sa to log in using SQL authentication.

    Regards

    Vineet Dewan

  24. Lena Venter says:

    I resolved my issue with the sa login ‘state 8′ by unticking the enforce password policy in the properties of the sa user, this is for SQL2005 only.

  25. Carmen says:

    Did someone figure out the State: 1 issue?

    I am starting my SQL server on sigle mode and trying to login under the account that installed the server but still I get this error:

    2006-09-19 13:52:29.29 Logon       Error: 18461, Severity: 14, State: 1.

    2006-09-19 13:52:29.29 Logon       Login failed for user ‘ASSUREONBAssureonManager’. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]

    In

    C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG

  26. SQL Protocols says:

    Hi, Carmen

       This is more SQL security area, you can post your question in following forum, many experts can help you shortly:

    http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=92&SiteID=1

    Good Luck!

    Ming

  27. SD says:

    I have an issue with the report server.  We used to have a web farm and not scaled down to a single  sql server with the reporting components.

    The problem is when I enable windows authetication through IIS it is trying to access the page via "domainservername$" from client IP x.x.x.x.  The server name is previous sql reporting services install.  It is no longer installed on there.  

    I never had reporting services configured until now.  How can I have the report server use the domain user credentials rather than "domainservername$"?

  28. marcin says:

    Error: 18456, Severity: 14, State: 5

    would anyone know how to correct this error?

  29. Hans-Georg says:

    Hi @all

    We have a SBS2K3 with SQL2k5.

    On every Startup we get the following Error:

    Ereignistyp: Fehlerüberw.

    Ereignisquelle: MSSQL$SHAREPOINT

    Ereigniskategorie: (4)

    Ereigniskennung: 18456

    Datum:  17.10.2006

    Zeit:  00:20:25

    Benutzer:  NT-AUTORITÄTNETZWERKDIENST

    Computer: PDC

    Beschreibung:

    Fehler bei der Anmeldung für den Benutzer ‘NT-AUTORITÄTNETZWERKDIENST’. [CLIENT: 192.168.2.250]

    Weitere Informationen über die Hilfe- und Supportdienste erhalten Sie unter http://go.microsoft.com/fwlink/events.asp.

    Daten:

    0000: 18 48 00 00 0e 00 00 00   .H……

    0008: 0f 00 00 00 50 00 44 00   ….P.D.

    0010: 43 00 5c 00 53 00 48 00   C..S.H.

    0018: 41 00 52 00 45 00 50 00   A.R.E.P.

    0020: 4f 00 49 00 4e 00 54 00   O.I.N.T.

    0028: 00 00 07 00 00 00 6d 00   ……m.

    0030: 61 00 73 00 74 00 65 00   a.s.t.e.

    0038: 72 00 00 00               r…    

    The Client: 192.168.2.250 is the SBS itself with the internal LAN NIC (the SBS have 2 NIC´s for WAN and LAN)

    The NT-AuthorityNetworkservice is in Logon of the DB and have dbcreate and dbsecurity rights.

    What can we do?

    regards.

  30. I have the following error, no severity displayed, I can’t ping IP address and it does not belong to us.

    Any hel will be appreciated.

    Event Type: Failure Audit

    Event Source: MSSQLSERVER

    Event Category: (4)

    Event ID: 18456

    Date: 19/10/2006

    Time: 09:27:26

    User: N/A

    Computer: INET

    Description:

    Login failed for user ‘sa’. [CLIENT: 81.27.111.162]

  31. RDuke says:

    I have a VB5 app that’s providing the login/password through an ADO connection string, and been unable to establish a successful remote server connection to SqlExpress. Mine keeps going back and forth between state 16 and state 8, and I assure you the password being provided is correct. What gives with this authentication?

  32. Moshe Rosenberg says:

    We just migrated to a new sql server with SQL Server 2005 installed (we moved from 2000).

    We are experiencing two issues that we cannot resolve:

    1. The site and database clients that use this SQL Server run very slow now. We have noticed that the lsass.exe process consistantly uses 25% CPU, +/- 10%. When we stop SQL server the lsass.exe process goes down to 0. What can be causing this? The website uses a SQL user account, not a windows user account.

    2. We have an error in the event log that might be related to the issue above. It appears every few minutes:

    Event Type: Failure Audit

    Event Source: MSSQLSERVER

    Event Category: (4)

    Event ID: 18456

    Date:  10/25/2006

    Time:  11:54:42 AM

    User:  NT AUTHORITYSYSTEM

    Computer: MSDB

    Description:

    Login failed for user ‘NT AUTHORITYSYSTEM’. [CLIENT: <local machine>]

    The error log from SQL gives more info:

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

    What is causing this? How can this be traced?

    I am stuck here. All help greatly appreciated.

    Moshe

  33. Nan Tu (MSFT) says:

    Moshe,

     One of the improvements in SQL 2005 is that all logins are always encrypted using SSL. If you have frequent connection logins, you will see lsass being quit *active*. Is it your case?

    For the error in the ERRORLOG, the STATE tell that the process doesn’t have permission of the login database. It apears to use NT authentication, running as localsystem. You can find who is trying to login from your local machine and go from there…

  34. Mahesh says:

    I keep on getting this error intermittently and the user specified in the error log has got the correct permission.

    2006-10-31 08:58:36.01 Logon       Error: 18456, Severity: 14, State: 16.

    2006-10-31 08:58:36.01 Logon       Login failed for user ‘AbsLayer’. [CLIENT: <local machine>]

    2006-10-31 08:58:37.28 spid53      Starting up database ‘AbsLayerRepository’.

    I have added retry logic in my code which keeps on retrying the connection for the configurable amount of time, when I get this error I can see in my application log that its retrying the connection and most of the time it passes after 2 – 3 attempts but some time it goes up to 9-10 attempts so I feel it to be unreliable in the live system. Can some one please help me why this error occurs or is there any patch to resolve this issue.

  35. Umair says:

    I have found an Error:

    Server Name: UMAIR130786SQLEXPRESS

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

    It works well using Windows Authentication

    but with SQL Server Authentication it results in the above mentioned error.

  36. Juan Peguero says:

    I was getting the same error, and I try everything listed on the Forum, and could not get rid of the error. It looks like there is some funkiness on the ODBC Connection, and it will always use the current user logged on the computer, not the specified SQLSever account, to send over the network for the connection profile.

    To eliminate the error, I had to grant the Domain Users access to the SQL Database the program was trying to connect to. In my case, all users had access to the database, but security settings can be put in place to limit the users’ access.

    In other words, I could not fixed the problem, so I had to make a workaround.

  37. Bertie says:

    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.

  38. KarenM says:

    Il-Sung, thanks so much for writing this up — definitely the most helpful source of debugging info for login failures to SQL!

  39. Shawn says:

    What is State 16?  I am getting Error: 18456, Severity: 14, State: 16

  40. SQL Protocols says:

    Hi Shawn,

    State 16 indicates that target database could not be determined.  This may mean that the specified database is non-existent, you do not have permissions on the database, or the database may not be online.  Also, you can do as Matt mentioned in his comments on March 14 — try connecting to a different database and then use the USE DATABASE to the problematic database and see what the error is.

    Il-Sung.

  41. Cindy says:

    I have an SQL Server 2005 cluster. Windows Authentication works fine on the primary node but after failing over onto the secondary node I am getting the 18456 error State 11.  

    I noticed someone else posted concerning this error state but I do not see a response to this issue.

  42. adamfool says:

    I am getting the state 16 error, but it is not recurring.  However there is a separate partition on the server that holds the logfiles which I noticed have not changed since the last time I received the state 16 error.  Also the partition is almost to capacity.  Are these two problems related or do the logfiles not overwrite themselves?  I will go ahead and apologize for dumb questions.  I have no experience in SQL and my job requires me to administer an enterprise GIS database!

  43. prashanth,my mail id is prashanth.krishnan@tcs.com says:

    Hi ,

    When I try to start the merge agents in SQL server 2000 I get the error message as below:

    ________________________________________

    The process could not connect to Subscriber ‘ATELBSNT65′.

    (Source: ATELBSNT65 (Agent); Error number: 20084)

    —————————————————————————————————————

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.

    (Source: ATELBSNT65 (Data source); Error number: 18456)

    _______________________________________

    There are 3 servers ATELBSNT65,66,67.

    When I try to access merge agents through ATELBSNT67 this error occurs.

    ATELBSNT67 is the publisher and ATELBSNT65,66 are the two subscribers.

    So please help.

  44. Mash says:

    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??

  45. Belsteak says:

    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 AUTHORITYSYSTEM’. [CLIENT: <local machine>]

    Do you have any idea ?

    Thnks

  46. Belsteak says:

    Hello again,

    I searched a bit more. NT AUTHORITYSYSTEM 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…

  47. Carol Walter says:

    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.

  48. Mash says:

    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

  49. khaki says:

    login failed user sa for sql srv 2000

  50. craig hogan says:

    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.

  51. Tim says:

    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!

  52. Adam Barnard says:

    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

  53. Tom says:

    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

  54. Matt Neerincx (MSFT) says:

    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.

  55. Luc Kremers says:

    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

  56. Nick Pattman says:

    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

  57. MCG_Val says:

    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

  58. Il-Sung Lee says:

    What is the corresponding error in the server’s error log?

    Thanks,

    Il-Sung.

  59. Robert says:

    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?

    Thanks

    Robert

  60. Jim says:

    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

  61. Ben says:

     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.

  62. Matt Neerincx (MSFT) says:

    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.

  63. Jesus Carranza says:

    Hi Il-Sung,

    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?

    Regards

    Jesus

  64. Il-Sung says:

    Hi Jesus,

    Unfortunately, the correct error state is not reported by the SQL Server 2000 server.  There is no configuration that can change this.

    Il-Sung.

  65. Sergei says:

    Hi,

    My email is sergei@ssw.com.au.

    I have got SQL Server 2005 SP2 on WinXp SP2.

    I tried sqlcmd -S machine_name -U machine_nameuser_name -P user_password.

    It failed with error: "Login failed for user machine_nameuser_name"

    I can open SSMS using run as machine_nameuser_name,connect using windows authentication (in this case machine_nameuser_name) and it works fine.

    My question is why this command-line does not work

    sqlcmd -S machine_name -U machine_nameuser_name -P user_password?

    Sergei.

  66. Matt Neerincx (MSFT) says:

    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_nameuser_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:machine1user1 cmd.exe

    Enter the password for machine1user1:

    Attempting to start cmd.exe as user "machine1user1" …

    This will open a new cmd window and from there you can run sqlcmd -S server -E

    Matt

  67. SQL Protocols says:

    Hi, Sergei

      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.

    Good Luck!

    Ming.

  68. faberyx says:

    Hi everybody, ige this error when i try to connect to sql server from studio express

    Error Number: 18456

    Severity: 14

    State: 1

    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?

    Fabrizio

  69. SQL Protocols says:

    Hi Fabrizio,

    Have you looked at the server’s error log and determined the error state reported by the server as outlined above?

    Il-Sung.

  70. Jacques says:

    Hi All

    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)

    Any advice?

  71. EH says:

    Hi, useful information, please add this to Books Online

    Thx

  72. Derek says:

    This article:

    http://groups.google.com/group/microsoft.public.inetserver.iis.security/browse_thread/thread/68c216b10e7fa70/69aacf4a582ec20c%2369aacf4a582ec20c

    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:

    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

  73. sig says:

    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.

    Any suggestions?  

    Thx.

  74. Matt Neerincx (MSFT) says:

    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.

  75. Today it is all about security relating to SQL Server. The first session is being taught by a Software

  76. Doug says:

    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!

  77. Bill says:

    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 AUTHORITYANONYMOUS 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?

  78. Satish K. Sharma says:

    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>]

  79. Carl says:

    Hi,

    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.

  80. Andy says:

    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 ‘SQLServerAndy’. [CLIENT: <local machine>]

    Please help to solve this problem. Thanks so much.

  81. MING LU says:

    Hi,Satish

      Can you be more specific how you create the login and how you make connection by using which authentication, basically, the full connection string.

    Thanks!

    Ming.

  82. Satish K. Sharma says:

    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.

    Login failed for user ‘sa’. Reason: Server is in single user mode. Only one administrator can connect at this time. [CLIENT: <local machine>]

    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…

  83. rambo says:

    See this link if your error is related to:

    Re: The AcquireConnection method call failed with error code 0xC0202009.

    http://forums.microsoft.com/msdn/showpost.aspx?postid=160340&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=2

    (Very Good)

    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 Server90DTSBinnDTExec.exe" /FILE "D:ProjectsFremont Dialer SSISDMFDailyDataFeedDMF_Daily_Data_FeedDMFDailyDataFeed.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 akmsweb@gmail.com

     Was this post helpful ?

  84. Nitin says:

    Ok,

    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.

    Any ideas?

  85. IndusCreed says:

    Figured out :)

    The Sql Server 2005 needed to be run as Administrator. Dang it – Vista !!

  86. nmadba says:

    Error 18456

    Severity 14

    State 16

    SQL 2005 SP2

    Mixed Mode

    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.  

    I’m stumped.

  87. Butt Crack says:

    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.

  88. i like getting my ass licked by dirty whores.

  89. Gary says:

    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 gtinkel@profsft.com.

    Thanks

  90. Alex says:

    Hi,

    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?

  91. Campbell says:

    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.

  92. Olivier says:

    Hi,

    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.

    Best regards,

    Olivier

  93. Kevin says:

    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…

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

  94. soumya says:

    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

  95. I discovered a few stepstones when connecting via JDBC to a locally installed SQLSever Express 2005 database. Here are my observations:Activate &quot;Accepting TCP/IP&quot; connections in the SQL Server Configuration Manager. Otherwise the SQLServer Expr

  96. SQL Protocols says:

    Ralle,

    Yes, you are correct.  The Microsoft SQL Server 2005 JDBC driver requires SQL Server (any SKU) to have TCP/IP support enabled.

  97. Gregg says:

    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?

  98. jaap@myprovider.com says:

    exec sp_password @new = ‘sqlpassword’, @loginame = ‘sa’

    alter login sa

    with password = ‘sqlpassword’ unlock,

    check_policy = off,

    check_expiration = off

  99. Gregg says:

    Jaap@myprovider.com-

    THANKS! Don’t know why that worked, but it did, and I thank you.

  100. In Windows Vista: All Programs -> Microsoft SQL Server 2005 -> (Right Click) SQL Server Management Studio -> Run as administrator

  101. MING LU says:

    Hi, Hermann

       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.

    Thanks!

    Ming.

  102. SQL Protocols says:

    Hi, Hermann

       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.

    Good Luck!

    Ming.

  103. SQL Protocols says:

    Hi, Hermann

       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.

    Good Luck!

    Ming.

  104. Dave says:

    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 FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG’.

    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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmaster.mdf

    2007-08-08 14:18:39.25 Server       -e c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLLOGERRORLOG

    2007-08-08 14:18:39.25 Server       -l c:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATAmastlog.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]

  105. Erik says:

    Campbell, thanks!  for state 11, running as administrator worked.

  106. Matt Neerincx (MSFT) says:

    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.

  107. Dave says:

    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).

  108. Keith Hobbs says:

    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.

  109. Matt Neerincx [MSFT] says:

    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.

  110. Karen Bryan says:

    Hi,

    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?

    Thanks

  111. Locke_ says:

    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.

  112. Locke_ says:

    …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

  113. Martin says:

    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.

  114. Anon says:

    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.

  115. vramakrishna_t says:

    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 ‘SERWIZSOLSvc-Sharepoint’. [CLIENT: 10.64.130.160]

    2007-10-10 20:54:18.37 Logon       Error: 18456, Severity: 14, State: 16.

  116. Gurumurthy says:

    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-PCGurumurthy’. (.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

    Severity: 14

    State: 1

    Line Number: 65536

    ——————————

    Program Location:

      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 System.Data.SqlClient.SqlConnection.Open()

      at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)

      at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

  117. Terry Brown says:

    I am getting an Error: 18456, Severity: 14, State: 11. when my users are triing to make an ODBC connection to SQL 2005 STD from Acess 2007. I have sqitched the SQL server from Windows Authentication mode to SQL server and Windows Authentication mode, this did not help. Any ideas? Thank you.

  118. Nimish says:

    I have added the sqlserver 2005 instance using sp_addlinkedserver. And when I try to use the linked server inside my query it says login failed for user ‘sa’. When I checked at the error log on remote server(where the SQL Server 2005 is installed) the State was 8.

    But I am able to connect to the SQL Server instance from Mgmt Studio Express and also using openrowset distributed queries.

    Can I know the actual problem?

  119. Ken says:

    I am getting Error 18456 State 8 sporadically for many users. Sometimes they can log on OK, and sometimes not, using the same password. The logins and passwords were migrated from SQL2000 using sp_help_revlogins.

    If I change the password, they can log on using the new password, but later that same new password is rejected. If they try again later, it may work again!

  120. Geo says:

    I’m having the same problem as Ken.  My databases were restored to sql 2005 server and are in 2000 compatibility mode.  Usually the logins work but occasionally for no apparent reason I get Error 18456 State 8.  I know the password is correct as it is coming from the config file and not changing.

    I tried deleting the users and recreating them by hand but still have the same sporadic problem.

  121. Geo says:

    SQL Server build is 9.0.3159 by the way

  122. Dominique says:

    Hello,

    I have the same error. What is stange is that the ODBC connection was working last week when I used it and today when I tried again it failed… Any corruption?

    Thanks,

  123. Dominique says:

    Hello

    I have the error 18456 with any users when one specific user is login in to the PC only???

    Test1 on PC 1:

    User A log to the PC

    ODBC with UserA is fine

    ODBC with UserB is fine

    User B log to the PC

    ODBC with UserB failed error 28000-18456

    ODBC with UserB failed error 28000-18456

    Test1 on PC 2

    User A log to the PC

    ODBC with UserA is fine

    ODBC with UserB is fine

    User B log to the PC

    ODBC with UserB is fine

    ODBC with UserB is fine

    What is going on???

    Thanks

    Dominique

  124. Nameless says:

    We have this strange Error:

    [298] SQLServer Error: 18456, Login failed for user ‘DomainDOMAINAdministrator’. [SQLSTATE 28000] We have windows authentication turned on this happens on a MOSS Server with SQL Server 2005

  125. hello says:

    hi all,

    i have encountered this login failed error with State 1 using SQL Server 2000. What does state 1 indicate?

    While using the server explorer i had to make a connection where i encountered this error message.

    Please mail me the replies asap to dency_opus911@yahoo.com

  126. Dissonance says:

    Hello, I have read technet forum about this error but I still have no answer about state 12. I have tried to give all permissions that I know for ‘testlogin’ except sysadmin and it has no effect! Please help!

  127. Rob says:

    I have an Error -18456 but no state level given. It just states Login failed to user.

    Microsoft SQL server Error-18456.

    I am trying to get access to sql server authentication mode. While I am able to get access to windows authentication mode.

    I made shure to choose Mixed authentication mode while installing my sql server 2005 software.

    Can any one help me thanx.

  128. Rob says:

    I have an Error -18456 but no state level given. It just states Login failed to user.

    Microsoft SQL server Error-18456.

    I am trying to get access to sql server authentication mode. While I am able to get access to windows authentication mode.

    I made shure to choose Mixed authentication mode while installing my sql server 2005 software.

    Can any one help me thanx.

  129. Simon Larsen says:

    I think you will find that you have a sql security group or user which has a default database set which it no longer has access to.

    If you look in the logs are you getting a successful login followed immediately by a failed login? that’s what I had and it was a local group that the user was a member of which had that error. Gave public access to the db and done.

  130. bfinley says:

    All I just received the same error and found the problem was related to Reporting Services.  You may want to open SQL Server Configuration Manager, shutdown reporting services and retry the sqlcmd.

    I am not certain if this has been mentioned.  I am running Enterprise on Win2003 server.  

    Good luck.

  131. Francisco Rodriguez says:

    Hi everybody, we had a "State 16" problem with one of our databases in an ASP.NET app running in a SQL Server 2005 box. In the SQL Server error log we found this:

    2006-10-31 08:58:36.01 Logon       Error: 18456, Severity: 14, State: 16.

    2006-10-31 08:58:36.01 Logon       Login failed for user ‘<appuser>’. [CLIENT: <server>]

    Following comments from this blog entry we checked <appuser>’s login in server, and <appuser>’s user in database, and they were ok. We also checked schemas in database and we found that one of them had a nonexistent owner, that is, the owner of the schema was not a valid login in the server. This happened because we moved the database from another server, where the owner was a valid one.

    So we changed the owner of the schema to dbo, and that was it! State 16 error was gone and our app worked perfectly.

    I hope this tip will help anyone.

    Cheers,

    FRodriguez

  132. beezel says:

    Holey Moley! Bertie’s solution worked for me!

    arrow over to the beginning of your password text box.

    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.

  133. vee says:

    I get the error code 18456 connection failed then 2000 for one of my employees but it only occurs when loggin in to one specific computer. She can log in under all other computers.

  134. Kraig says:

    Just FYI for those getting the State: 16 error.  After beating my head against the wall checking permissions, DB owners, etc. I found one old SQL Agent job that was trying to run for a database that no longer existed. Everytime the job tried to run it gave this error, all I had to do was delete the job.  So it’s not always something complicated.

  135. pietjegates says:

    I receive this error in the following scenario:

    A user account had acces to 2 databases on the same SQL server via windows group membership.

    I deleted the first database (and the corresponding log in) between the time the user had logged on to the domain and the starting of the "SQL server management studio". The connection to the SQL Server was made trough the memebership of the group of wich the login was deleted.

    Solution: User logged off and on (windows) and the correct group was used to connect to the SQL server.

  136. Dan says:

    Re all the questions about state 16, I don’t know if they are related but I was seeing intermittent state 16 errors, but just before they started a transaction log full error was written to the log.  Truncating and shrinking appears to have solved this.

  137. AbTseg says:

      Please I need your help

                       Thank you!

  138. Jaime says:

    Hi,

    we are getting this error with Serverity: 14 and State: 8. We don’t understand the reason why this error only has appear today but with a several number of users. Could someone help us?

    Thank you!

  139. Mach says:

    Hi,

    I keep getting this error too. But I can’t get into the error log file to see what state it is. It keeps telling me that access is denied. I’ve even created an everyone permission with full control and that didn’t fix my issue. Then I created a whole new username/login and that keeps getting the same results. Any suggestions?

    I’m running Windows Server 2003 & SQL Server 2005.

  140. Matt Neerincx (MSFT) says:

    Ok, if you cannot start SQL Server AND you cannot access the ERRORLOG file using notepad, then I suspect something else on your system has the ERRORLOG file locked and this is bad and needs to be fixed first.

    It could be something in the file system like a virus scanner doing this.  First thing I would do is set the SQL Server service (in Services) to manual, then reboot the machine.  After reboot, but before attempting to start SQL, see if you can open the ERRORLOG file using notepad.  Let me know how this goes.

  141. Mach says:

    Alright, we’ve fixed the problem with connecting with SQL Server. User can now successfully Open and connect using Management Studio. I checked the directory for where the error log should be but when I hover my mouse over the folder, it tells me that the folder is empty (and I can’t access it). Just to double check, does this sound like a correct path for the errorlog:

    C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLERRORLOG [Should be here]

    Because it’s not there.

    Thanks for your help by the way.

  142. nins says:

    I created a login and assigned it to the sysadmin role on my local server, after which I restared the server. Also, I changed the max number of concurrent users allowed to 1. Now when I try connecting to the server, I get the error message, ‘Connection was successfully established with server but an error occured durin the login process’. When I check the error log the following is noticed "Error: 17809, Severity: 20, State: 3. Could not connect because the maximum number of ‘1’ user connections has already been reached. The system administrator can use sp_configure to increase the maximum value. The connection has been closed." I tried changing the max number of user connections allowed by using sqlcmd utility. In the command prompt it says value changed from 1 to 10, but when i try to log in to the server i get the same old error.

  143. Peter says:

    Where can I find a complete list of Error State for Error 18456? If there is no such list, I hope the table in this blog will be updated to include common error states such as State 16.

  144. Peter says:

    I can duplicate Error State 5 and 8.  How to duplicate Error State 2 and 9?

  145. Luis Rivera says:

    01/15/2008 12:11:53 ERCreateSiteWizard[16f4:17d0]:  —-Storage,0: [Microsoft][ODBC SQL Server Driver][SQL Server]Error de inicio de sesión del usuario ‘sa’. [18456]

    Error: 18456, Severity: 14, State: 8

    Errror this does not allow me to connect to SQL Server 2005.

    This connection is made by COM ports. Someone has an idea?

    my e-mail: luis.rivera@nasoft.com

  146. Matt Neerincx (MSFT) says:

    I’ll try to get the full list posted here.

    Also, you guys know about the security ring buffer in SQL 2005 SP2, read this:

    http://blogs.msdn.com/lcris/archive/2007/02/19/sql-server-2005-some-new-security-features-in-sp2.aspx

  147. Matt Neerincx (MSFT) says:

    State #8 means the password did not match.  So just an invalid password.

  148. I believe we have an answer for the Severity: 14 and State: 16, while using SharePoint Server 2007.

    While attempting to enable SSO for the first time, if you fail to configure the Microsoft Single Sign-on Service with the proper ‘Service Account’ you will get an error. It seems that SharePoint creates a job looking for the ‘SSO’ database, even though it failed to create it – reason for State:16.

    Steps we took to correct the issue:

    1. Opened Windows Services

    2. Configured Microsoft Single Sign-on Service to use the proper account

    3. Opened Central Administration >> Operations >> Manage settings for single sign-on

    4. Configured properties to use the same account used for Microsoft ‘Single Sign-on Service’

    The database (SSO) was created successfully and all the Error: 18456 went away. Hopefully this works for all of you that were experiencing the same issue.

  149. kiks says:

    Server Name: jmsvsqlp40.corp.jmfamily.com,51321

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

    HELP!!!!

  150. kiks says:

    Server Name: <server name>

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

    HELP!!!!

  151. Zorya says:

    Hi,

    After we upgrade some dev instances to SQL 2005 SP2 build 3215 i got the error when trying to connect with SQL authentication (Windows Authent is working for me, but not for others). I cannot assure somebody didnt type a wrong password.

    But after a while, everything is working fine again….

    Is there a process that locks the connection on an instance after a failed logon attempt ????

  152. gy says:

    If you are running it on Vista, select "Run as Administrator" by pop-up menu (right click).

  153. Ollin says:

    After looking for a solution for my problem with state 11 for a while I have several times stumbled over a solution for you who uses Vista and XP as your platform for SQL.

    Might be helpful for other states as well.

    -Turn off "simple file-sharing" using Control panel->folder options-> view

  154. Frodoger says:

    We have also a login problem but its sporadic.

    We use SQL Server 2005 SP2 on Windows 2003. The Client use ODBC through SQL Server native Client.

    What can we do to search a solution? Its important for use because every time we get this error the Client application crash.

  155. Frodoger says:

    plz delete and forgot my reply. i have not complete read my error picture. sorry.

  156. Asif Bawany says:

    sql server configuration manager – Enable TCP/IP and named pipes; by default these settings are disabled because of security settings; I tried and it worked for me :)

  157. Mark Brinton says:

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

    I found the other posts very helpful.  Using Windows authentication on a local admin account which had sql 2005 sysadmin privileges, our install scripts would intermittently fail.  The scripts would detach, copy and attach mdfs from one server to a different server.  Sometimes it worked.  Sometimes it failed.  

    What needed to be done is to refresh/resynchronize Server metadata with database metadata.  Critically important, it seems, is to re-establish a link between the database owner of the newly attached db to an existing server login.  

    The upgrade scripts were obviously erroneous in this regard and this bug, which existed for YEARS and was latent from versions SQL 7.0 through SQL 2000, decided to announce its presence in the data migration from SQL 2000 to SQL 2005.

    Thanks to everyone who have taken the time to contribute.  It helped alot.

  158. ccardwell says:

    Here’s a different twist. I am running SQL Server 2005 and using Netapp storage devices for the databases. As a part of the configuration I have a seperate connection to the netapp devices using a VLAN on the switch. This connection is where all communications between the netapp and the windows OS happens for controlling disk IO. I just discovered that my System Administrator account is attempting to authenticate over the Netapp data connection rather than over the primary LAN connection. The only other database that I have running seems to be working just fine. I was attempting to install a new application which would fail when it tried to create the database due to a login failure. The ‘data’ vlan is not routed at all so it was unable to authenticate against the DC and the install/create db failed. Is there a way to tell SQL Server to only use the primary LAN connection to authenticate with the DC?

    This is a rather truncated question as I realize that I have left out a lot of detailes – this is in the interests of brevity. If anyone has any ideas about this and would like more information I will provide more details as necessary. Basically I have two NICS functioning on two diferent Networks and want to be sure that SQL server only looks to a specific NIC/LAN for authentication.

    Thanks in advance for any assistence someone/anyone can provide.

    Charlie

  159. Patrick S says:

    Hello,

    I have a question.  We have two SQL 2005 boxes in our dev environment.  Our Active Directory is housed on a SBS 2003 server, which has recently been rebuilt.  After we brought the rebuilt SBS server back online, we began to see weird permissions issues.  Currently, our SQL boxes use Windows Authentication and Kebros.

    The problem we’re experiencing (seemingly after the SBS 2003 server rebuild) was that many of our linked server objects between the two SQL servers began to fail.

    The errors we’re seeing are:

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’.  Error: 18456, Severity: 14, State: 11.

    The Linked Server properties are set to use the "login’s currentl security context", meaning you should be connecting across servers as yourself (windows authentication).  All of this worked last week, but after the SBS rebuild its all failing.  Any ideas?

  160. SQL Protocols says:

    Hi, Patrick

    I would recommend you check out Nan’s blog entry on this error for linked servers:

    http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

    Since you recently rebuilt your Active Directory server and don’t indicate that you changed anything in the SQL Server instances, I would first:

    1. Make sure that the Active Directory accounts that are used to log in to SQL Server do not have the following property set in Active Directory: Account is sensitive and cannot be delegated

    2. Make sure that the Active Directory accounts that each SQL Server service runs under are trusted for delegation in Active Directory.

  161. SQL Protocols says:

    Hi, Patrick

    I would recommend you check out Nan’s blog entry on this error for linked servers:

    http://blogs.msdn.com/sql_protocols/archive/2006/08/10/694657.aspx

    Since you recently rebuilt your Active Directory server and don’t indicate that you changed anything in the SQL Server instances, I would first:

    1. Make sure that the Active Directory accounts that are used to log in to SQL Server do not have the following property set in Active Directory: Account is sensitive and cannot be delegated

    2. Make sure that the Active Directory accounts that each SQL Server service runs under are trusted for delegation in Active Directory.

    Good luck!

    Dan

  162. Patrick S says:

    Dan,

    Thanks.  I did just realize something, the links work when I remote desktop into the server and check the link to the other server…  They fail when I use SQL studio on my desktop and test the link objects.  That’s a double-hop versus a single-hop, mentioned in the article you reference…. which makes it a Kebros problem I’m thinking.  I’m going to keep digging.  Let me know if you have any more ideas, and thanks for responding!

    Patrick-

  163. Sandy says:

    I have 18456 alternating between state 8 and 16.

    If I look at the user in my C# connection string in SQL Studio management, the password always changes to a default 15 chars password however many times I change it. The user’s status is locked out and the checkbox is greyed so I can’t change it.

  164. SSG says:

    For State 11 errors the Domain account (DomainWinNTlogin for example) was accessing SQL Server fine and then at some point we experienced the State 11 errors (maybe related to Mirror Failover or some other issues on the server).  I had to drop the Account from the Local Admin group and this stopped the errors occurring and then I added the account back in to the Local Admin group and errors did not return.

  165. brad says:

    thanks for a well written instructions

  166. Steve Barnes says:

    State 16 may also mean the database name is not correctly "cased".  Yes, believe it or not, the SQL server on my dev box would accept either case "dbname" or "DBNAME" in the connection string.  The SQL server on my Win2003 Server, on the other hand, demanded the exact case spelling as when the database was created.  Both database servers are at SP2.

    Again, Il Sung’s suggestion to test connectivity via the USE <DATABASE> command was the key to a more relevant error message.

    Related to

    Error: 18456, Severity: 14, State: 16

    Login failed for user ‘<username>’. [CLIENT: <IP Address>

  167. JP Roberts III says:

    This wasn’t the exact answer, but it led to my solution.  Thanks!

  168. STan says:

    Сцуко!

    Нихуя не помогли ваши хуевые советы!

  169. Torsten says:

    Hi there,

    I’ve written a service that connects to a SQL-Server 2005 (Express) database. The service works fine but two times a day I receive an error:

    Error: 18456, Severity: 14, State: 16

    Login failed for user ‘wtime’. [CLIENT: <local machine>]

    The error mostly occurs at 9:30 pm and 2:00 am but not always. May a backup-program cause the problem. I read that maybe the database is offline at the moment, but how could this be if this were the reason? After getting this error, the service itself tries to connect to the db as long as neccessary, and so I was able to see in the log files that the db is not available for just about 1 min, after this period of time the service is able to reconnect to the database again. I also found an article describing how to disable a service-job that cleans up no more used sessions, but no jobs are running on the sql-server.

    Let me know if you have any ideas to solve the problem.

  170. SQL Protocols says:

    Is the SQL Express offline at that time? If so, it’s a by-design behavior for SQL Express. Please check this forum post see if it applies to your case. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=309672&SiteID=1

    Thanks,

    Xinwei

  171. Torsten says:

    Hi Xinwei,

    no, I don’t think the SQL-Express is offline or idle at that time, nothing in the log implies to that:

    2008-04-30 01:49:51.89 spid51      Starting up database ‘wtimserv’.

    2008-04-30 01:51:07.48 spid51      Starting up database ‘wtimserv’.

    2008-04-30 01:53:38.28 spid51      Starting up database ‘wtimserv’.

    2008-04-30 01:54:53.98 spid51      Starting up database ‘wtimserv’.

    2008-04-30 01:59:56.93 spid51      Starting up database ‘wtimserv’.

    2008-04-30 02:11:13.81             Error: 18456, Severity: 14, State: 16

    2008-04-30 02:11:13.81             Login failed for user ‘wtime’. [CLIENT: <local machine>]

    2008-04-30 02:11:16.81 spid51      Starting up database ‘wtimserv’.

    2008-04-30 02:14:58.34 spid51      Starting up database ‘wtimserv’.

    2008-04-30 02:18:45.73 spid51      Starting up database ‘wtimserv’.

    2008-04-30 02:20:01.32 spid51      Starting up database ‘wtimserv’.

    2008-04-30 02:23:47.46 spid51      Starting up database ‘wtimserv’.

    hmmm… or is it idle and the "Starting up database" – Message is a keep alive signal?

  172. Imran Taher says:

    I got this message again and again, to say my sql server is full of this error… Login failed for user ‘NT AUTHORITYSYSTEM’. [CLIENT: <local machine>]

    As suggested I checked Sql Server logs which says

    Error: 18456 Severity :14  State :16

    Recently I took some of the databases offline which we were not using. But when I checked that login ‘NT AUTHORITYSYSTEM’ has access to two databases which were taken offline I brought those two offline databases online, I hope the error might not occur again, and as of now I did not see any error message. Still fingures crossed.

    Thanks for the information.

  173. Concerned Programmer says:

    Where can I find the server’s error log?

  174. SQL Protocols says:

    By default, the error log is located at Program FilesMicrosoft SQL ServerMSSQL.nMSSQLLOGERRORLOG and ERRORLOG.n files.

    HTH

  175. SteveH says:

    My Severity 1 fix:

    I had to use SQL Server 2005 (Developer Edition) Surface Area Configuration and then "Add New Administrator" granting myself privileges on the next screen to log in (using Windows Authentication). Then I was good to go. Easy once you figure it out. I guess they were paranoid and didn’t want to automatically assume that the person installing it would actually want to log in as administrator (?).

  176. Patric says:

    Hey Guys,

    what is State 4 all about?

    Short information:

    We recently get the Error:

    18456 State 4

    - SQL Server 2005 Developer 64 Bit

    - Windows Server 2008 Enterprise 64 Bit

    Well intersting Thing here is, that just the Active Directory Administrator can Login to the Server. We tryed several things:

    User with SQL Admin rights. -> No Effect

    Just the Active Directory Admin and Local Administrator can Login to the Databases.

    Also interesting, the Initializer and Controls could be controlled and the Login works great. But, there is no possibility for a Local or Remote Connection to the Datebases.

    Activating Romte Connection Functionality -> No Effect

    Ok we tryed a lot of stuff.

    Then we installed the SQL Server again in Mixed Authentication Mode. Ports in the Router are opened. And the first effect anyone can now login! But no Remote connection possible.

    So we found out, that the Software Firewall in Windows Server 2k8 blocked the SQL Server. So this is easy. So now everything is working correct an we can work with it.

    But 2 questions still remains:

    First, what is that State 4 Thing all about. And please no internal Error Comment!

    Why can just the Active Directory Administrator login?

    And why have be as creative as that to get a workaround?

    patric.boscolo@studentprogram.de

    Microsoft Student Partners Germany

  177. My id "sa" is going disable several times when i go to connect the database in sql server 2005

  178. Sandro says:

    I had this problem with state #8 (Password mismatch). I removed the "Enforce password policy" and now it’s working.

    My password was not wrong. If I pasted it in the login form on the server it would work, and if I pasted on my PC it would not. Until I uncheck this option.

    Sorry but this is a bug! Lost hours for this…

  179. lashai says:

    hello,

    mymyspace is blocked please help me unblock it

  180. Shyam says:

    I’m tring to connect one server application with string "Provider=SQLNCLI;Server=195.22.3.218;Database=userdb;Uid=sa;Pwd=xyz;"

    Then The folowing error message is showing….

    Unable to initilize MSSQL

              <mssql_error:IDispatch error #3149:Login failed for user ‘sa’.>

    Thanks in advance my emailid is::

    smanohar@rmsindia.com

  181. SQL Protocols says:

    Shyam,

     You will need to go to the server that is running SQL Server, find the server’s ERRORLOG file (located at something like: %ProgramFiles%Microsoft SQL ServerMSSQL.1INSTANCENAMELogERRORLOG), and get the State from the ERRORLOG file; then come back to this blog and compare the state with the states mentioned in the blog.  As this blog post points out, there are many potential causes for the "Login failed for user ‘sa’" error message, but the state will pin down the exact root cause.

    Hope this helps!

    Dan Benediktson

    SQL Protocols

  182. Esa says:

    Our application was running as a network service on a remote server (2003) and login to SQL was attempted with DomainServer$.

    Everything had worked fine for a long time, but on one day we ran into

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

    Finally the solution was to remove that login from SQL Server and recreate it – and everything started to work immediately.

    What could be the reason for this? Has something happened in AD that had broken the login. All the time Security Event Log created success audit events for the connection but SQL Server gave the error.

  183. Stefano says:

    hi..

    i’m new using sql server.. and i have a problem to login:(

    i don’t know what i have to do.

    i hope you can help me.

    regads…

    ps.. my english is bad… sorry:))

  184. Matt Neerincx (MSFT) says:

    Hi Esa,

    This coild happen if the SID of the login changed.  When the login is added to SQL the SID of the login account it used to store the identity of the account.  If the user is dropped and re-created in AD, then a new globally unique SID is generated.  Hence the old SID stored in SQL’s security tables is no longer valid, you need to drop and re-create the login.  I’ll double check with the security group to see if there is way to "refresh" the SID without having to drop and re-create the login.

  185. Jyoti says:

    I  am getting "login failed" (Error 18456) error messages in SQL Server 2005 with no severity # and state #. I am unable to login into my college SQL server 2005 from home. Please help.

    Jyoti  

  186. Gazza says:

    Got this state 11 error.  removed the user from local admin which fixed the issue.

  187. MadMax says:

    *** Look Here ****

    If you are in VISTA right click on "run as administrator" and all will be well.

    Geez.

  188. bluefang says:

    hi

    (Login failed for user ‘sa’. [CLIENT: 172.17.5.5]

    Error: 18456, Severity: 14, State: 16.)

     now ,I know that the meaning of state 16 is no permissions .but,i think that it’s not possible about

    sa. i hope you can tell me the reason.

  189. SQL Protocols says:

    State 16 is a little more general than no permissions: it means that login failed while trying to log in to the database specified in the connection string.  This can happen for a variety of reasons, the most common of which is that the user specified a database that they don’t have permission to.

     Another possibility, though, is that you’ve specified a database that doesn’t exist: for instance, maybe you misspelled the database’s name.  Another possibility would be that that database is in single-user mode.

    Hope this helps,

    Dan

  190. bluefang says:

    Thanks!

    however,this problem will appear  every 5 minute.

  191. MadMax said:

    *** Look Here ****

    If you are in VISTA right click on "run as administrator" and all will be well.

    Geez.

    Thanks MadMax, You saved my time…

  192. Sandeep says:

    A common reason for Error is 18456, Severity: 14, State: 8. is in a development shop where the client is located in a domain and the server is not. In this case if your client is not configured to use TCP/IP you should also see corresponding errors on your Windows Server Security Audit Log – event id 529 – Unknown user name or bad password.

    The solution (in SQL2005) is to configure an alias that uses TCP as the preferred protocol. To do this go to SQL2005 …-> Configuration Tools -> SQL Server Configuration Manager -> SQL Native Client Configuration -> Alias. Lookup SQL2005 for help with setting parameters.

    This should work for SQL2000 as well but the navigation path to set it up would be different.

  193. Tanveer says:

    hi,

    My issue is exactly same which bluefang has posted earlier…

    (Login failed for user ‘sa’. [CLIENT: 172.17.5.5]

    Error: 18456, Severity: 14, State: 16.)

    Does anyone has a solution for this…

    Thanks

  194. Shai says:

    We’re seeing an issue in our environment where we have a .NET 1.1 web application on a Windows 2003 server machine and SQL Server 2005 on a Windows 2000 server machine.  In the application, we use SQL logins to connect to the db.  What we are seeing is, when a bad password is used at login, an error message is generated (login failed, state 8) in the event log which is what’s expected, but then we continually see the same error message repeating for the next few hours as if the connection is still alive and retrying automatically.  Any ideas why?  Thanks

  195. Red Davidson says:

    I’m getting error 18456, severity 14, state 58.  I’m trying to use SQL Server Authentication.  Any info on what this might indicate.

  196. HariKrishna says:

    I too receiving the same error now a days on SQL Server 2005. where we can set this sa password after installations of server.

    Any help will be Appriciated.

  197. clydde says:

    I have the same mistake that you:

    SQL query failed .. reason [Microsoft] [ODBC SQL Driver] [SQL Server] Login failed for user ‘myuser’ SQL statments…

    The system W.Vista Home edition.

    At the moment I believe that I khan fix the problem, login me again whenever I do to consultation, in fact if I make myself it does not give mistake, but it is to solution that I do not like.

  198. k says:

    In a batch file, I am invoking sql script files in a loop. When each script file is run I am establishing a connection to SQLExpress instance and running script. Randomly it fails on one of the script file saying login failed. Every time it fails at different script file when i restart. Any idea, what could be the wrong?

  199. tareq says:

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

  200. SQL Protocols says:

    k and tareq,

     Can you provide the full Login Failed error message from the SQL Server ERRORLOG?  That will contain a state, which as this blog post indicates is the important piece of information that tells why the login is failing.  Tareq, in your case, I think that state is from the client, since State 1 is the generic we send to the client to avoid information disclosure.  The server ERRORLOG file will have a different state.

  201. SQL Server 2005 e l’errore 18456 durate il logon

  202. kasandco says:

    I am building some reports using SQL Server 2005 Reporting Services, using a shared data source. The particular SQL authentication I need to use connects OK in the shared data source definition, but always fails with State: 8 when I plug the shared data source name into a report’s dataset definition.

    I can put the credentials "locally", as it were, into a report and that’s fine. Ultimately, though, I will want to schedule the reports with Report Manager so I need to get the shared data source working.

    I have tried this many many times: trust me the password IS correct! Any ideas, anybody?

  203. Adrian Balzano says:

    This error appears on our server. But I can’t find usefull documentation… Can you help me ? The server stop to respond after this.

    11/03/2008 09:35:36,Logon,Unknown,Login failed for user ‘XXXXX’. Only administrators may connect at this time. [CLIENT: xx.ww.yy.zz]

    11/03/2008 09:35:36,Logon,Unknown,Error: 18451 Severity: 14 State: 1.

  204. Jeremy Bond says:

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

    Not Sure this has been mentioned…..

    Also noticed the error can be caused by SQL Jobs that reference databases that have been removed or taken offline. We’ve disabled the job in question and the error has gone away. You will need to remove the job or resolve the conflicts within the job.

  205. sid says:

    Why this msg has been written to the error log for specific servers only???

  206. leo_learner says:

    Hi Guys!

    i am using Sql Server 2000 and getting this error in logs.

    18456 :

    Login failed for user ‘sa’.

    I am very much stuck as these failures cause sql server to stuck down frequently. I dont have information like IP address of attacker like in 2005, so i have no idea what to do.

    Please if any one have solution for it, please sort this problem out.

  207. odumah ojorma says:

    Hope this helps.

    http://benojor.spaces.live.com/blog/cns!7D1B073C4B441563!170.entry

  208. gary b says:

    I found that I had several jobs with incorrect parameters listed. Deleting these jobs eliminated the state 16 errors that were occurring every minute.

    While scrolling to end of this thread to add this information, I noticed Jeremy Bond said:

    "Error: 18456, Severity: 14, State: 16.

    Also noticed the error can be caused by SQL Jobs…"

    Should have read ALL posts in this thread before I spent an hour tracking down bogus login.

    Hope this eliminates some frustration for you.

  209. Manoj says:

    I am configuring Link Server for delegation between Server A and Server B. I am able to connect to both the servers from my workstation with auth_scheme ‘KERBEROS’. I have created a link server on Server A for Server B with Security setting of "Be made using the login’s current security context". When I run the query against the link the server I get the following error.

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

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. [CLIENT: ??.??.??.??]

    My login exists on both the SQL Servers as SysAdmin. All help is appreciated.

  210. Maria says:

    In my case:

    I had Sharepoint running on a server with SQL2000. I migrated the Dbs off of that server to a machine running SQL2005. Once migrated, the new db server started spewing 18456, State 11. Specifically it said: Login failed for user DOMAINSharepointserver$. As this was not an AD user, but a Computer, I didnt really get it.

    But, to resolve the problem, I went to the SQL 2005 server and created a new login under Security as DOMAINSharepointserver$ (made sure to match spelling/case perfectly.) I gave that login DBCreator rights, and in User Mapping I checked the boxes for the sharepoint dbs, and then verified it had Permission to Connect to Database Engine enabled, and hit OK.

    Error 18456 stopped. Didnt have to restart any services on either box.

  211. manojmsingh says:

    My issue is that I can’t even create a link server successfully it throws the error

    Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’. [CLIENT: ??.??.??.??]

    In my first post I may have hinted that the link server was created successfully but I can’t do that. so for some reason it is not doing the security account delegation even though I am able to login to the server A and server B with ‘KERBEROS’.

    The setting on the computer object in AD has also been set to "Trust for delegation".

    Any other setting that I may be missing.

  212. Matt Neerincx (MSFT says:

    Ensure when you create the linked server that you look very very carefully at the Security section and select the option that is right for you.

    The SIMPLEST solution is to select "Be made using this security context" then supply a NT user name and password to use.  This avoids all Kerberos double hop issues as the linked server runs under an account you control and the account logs in at the server (versus passed from client to server).

    Likewise you can assign mappings for users here as well, this is another solution to avoid Kerberos double hop issues.

    If you truely want the incoming client’s account to be used with the linked server connection, then select "Be made using the login’s current security context".  This option REQUIRES Kerberos double hop to be enabled.

    To enable Kerberos double hop you need to ensure:

    1. Account that SQL Server service is running under has rights to delegate accounts in AD.

    2. Each user account coming from client has "allows delegation" set in AD.

    3. Each client can fully resolve the FQDN of the SQL Server (DNS is setup correctly, they are not connecting via IP address for example).

    4. A SPN (Server Principle Name) is registered for the SQL Server service account.

    Setting up Kerberos is not for the faint of heart unfortunately.

  213. manojmsingh says:

    thanks for the information and I don’t think I am faint of heart.

    #1 this I was told is only for the computer and not the service account.

    #2 "Account is sensitive and cannot be delegated" is not selected for the account I am using to run query across link server

    #3 my workstation can fully resolve the FQDN of the sql server. Verified by running ping -a <servername>

    $4 Done and verified by running setspn -L

  214. manojmsingh says:

    Also we do need security account delegation over the link server, this is for BizTalk setup. Secondly for #1 Account is sensitive and cannot be delegated is not selected for the service account.

  215. tfudge says:

    2008-12-09 12:46:05.59 Logon       Error: 18456, Severity: 14, State: 16.

    2008-12-09 12:46:05.59 Logon       Login failed for user ‘NEWMILLSCADARobert Hardin’. [CLIENT: 192.168.1.100]

    2008-12-09 12:47:15.59 Logon       Error: 18456, Severity: 14, State: 16.

    2008-12-09 12:47:15.59 Logon       Login failed for user ‘NEWMILLSCADARobert Hardin’. [CLIENT: 192.168.1.100]

    2008-12-09 12:48:25.60 Logon       Error: 18456, Severity: 14, State: 16.

    I get these eroors every ten seconds. I am not a databse expert, but a control systems engineer and have no idea how to fix this. Any help would be appreciated.

    Thank You,

    Tim

  216. R.Hoek says:

    Hello,

    I’m also getting State 16 errors on a SQL user, which has permissions to access the target database. And we’re also using IIS (from an other server) to connect to the SQL server.

    As far as i’ve read correctly, this error can also occour when the database of (temporarely) offline / not available.

    But we have not taken the DB offline, so SQL server has some trouble accessing the DB itself.

    Is there any way to determine why the DB is offline/not accesable (logging?) other then using the USE DATABASE statement?

  217. Chris says:

    we had the Error 18456. We followed the steps that described in the document and resovled the problem:

    http://www.fulltimedba.com/2008/12/11/Error18456Severity14State11.aspx

  218. I’ve been learning a few things about troubelshooting login problems over the last few months. I first

  219. SQL Server says:

    I’ve been learning a few things about troubelshooting login problems over the last few months. I first

  220. greg aiken says:

    sql2005 error log lists…

    error 18456

    severity 14

    state 16

    i read above in this thread two possible reasons, but i am offering a 3rd.  when i ran ssmsee and saw the database in the left pane.  the database icon was not the normal icon.  instead of seeing the normal icon, i saw an icon of a persons head and shoulders in front of the database. and next to the database were the words ‘single user’.  somehow the database took on the property of ‘single user’.  once this happened, none of the sql authenticated logins (which previously worked), could be used to log into the database.  fortunately detaching, and reattaching the db, set things right again.

    but my question is…  what does it mean when it says the db is in ‘single user’ mode, and more importantly WHAT COULD HAVE CAUSED THIS?  the mode change basically left an office unable to get any work done.

    any help here would be appreciated…

  221. SQL Protocols says:

    Greg,

    You probably have other application or service(e.g. SQL Agent) running to turn the database into "single user" mode. You can use SQL Server Profiler to find out who did it. Please refer to BOL

    http://msdn.microsoft.com/en-us/library/ms345598.aspx

    Thanks.

    Xinwei

  222. Delson says:

    the sql server loging mode is set to windows authentication set it to sql authentication , to do this right click on the server in sql server managment> properties and then change the loging mode.

  223. Faran says:

    If you got state 1 log_in failed message, you need to add some SQL privileges to the account you’ve logged in with.

    Go to  SQLServerManagementStudio

    Configuration Tools

    SQL Server Surface Area Configuration

    Add new Administrator

    Add the two privileges on the left to your account (which you are logged in with)

  224. greg aiken says:

    dear Xinwei,

    the problem posted on 12-17 continues to happen with regularity.  your information says to use ‘sql profiler’ to figure out who, or what process, is mysteriously changing the database status to ‘single user’ mode.

    question 1.  is ‘sql profiler’ available for sql 2005 express?  if so, where, or how does one find, or install it?

    question 2.  if ‘sql profiler’ is only available with sql 2005 standard (or higher).  how would one setup sql profiler for sql 2005 standard?

    question 3.  in speed reading books online about ‘sql profiler’ it seems that one must know what events or sql commands to trace.  can you suggest how one would set the correct sql to look for that might set the database mode to single user mode?  ive seen there is a sql command that explicitly sets the database mode to single user, but ive also read that some built-in functions such as ‘dbcc’ might also set the database mode to single user.  who knows, perhaps there are many such functions…  how would you suggest i try to configure ‘sql profiler’ to detect what is changing the database mode to ‘single user’?

    thanks alot

  225. felipe says:

    hi, i am getting a sql error with state 5.

    how do i solve this?

    thanks!

  226. Daniel Macey says:

    I have also encountered a couple of new States in SQL Server 2008:

    * Login failed for user ‘<username>’. Reason: Failed to open the explicitly specified database. [CLIENT: <IP>]

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

    This error occurs when the database specified as the initial catalog is unavailable (for security or otherwise). This was reported as a State 16 in SQL Server 2005.

    * Login failed for user ‘<username>’. Reason: Failed to open the database specified in the login properties. [CLIENT: <IP>]

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

    This error occurs when the users default database is unavailable. This was reported as State 16 in SQL Server 2005.

    Hope this helps someone else.

  227. Niklas Klingspetz says:

    After installing SP3 (KB955706) for SQL Server 2005 i got an:

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

    in sqlservers logfiles. Eventlog just says

    Login failed for user ‘<username>’. [CLIENT: <local machine>]

    I used Windows Authentication in the 2005 Management Studio, the connection was working just fine before SP3.

    SOLUTION: Use "run as administrator" when starting Microsoft SQL Management Studio 2005.

    ENVIRONMENT: Vista Business – all recommended updates for SQL Server 2005 and Vista as of 2009-02-04 installed.

  228. sandra says:

    what about state:0 ?

    I have this error and I dont know what happens:

    A connection was successfully established with the server, but then an error occurred during the login process. (provider: TCP Provider, error: 0 – Se ha forzado la interrupción de una conexión existente por el host remoto.) (.Net SqlClient Data Provider)

    ——————————

    For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=10054&LinkId=20476

    ——————————

    Server Name: DESSQL2008

    Error Number: 10054

    Severity: 20

    State: 0

    ——————————

    Program Location:

      at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

      at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)

      at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)

      at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)

      at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()

      at System.Data.SqlClient.TdsParserStateObject.ReadByte()

      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 System.Data.SqlClient.SqlConnection.Open()

      at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)

      at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

    thanks a lot

  229. Thomas Ho says:

    I would like to ask if my DTS run normally before and nothing change in DTS and scheduelr. However in one day that is error: SQL server Error string:  Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’

    Please advise how to fix it

  230. One of my favorite errors that I get paged for is the login failed (error 18456). I have a few users

  231. Anubhab says:

    Can some one let me know, what is the usual way to log in. Because whene ever I am trying to log in the same error (18456) is poping-up. I am unable to install the databases I have. I am in a need, help me.

  232. maurice says:

    create bullitinadministrator use in sqlserver

  233. Robert says:

    i am trying to take a database into a SQL Server 2008 Express Edition installation from a SQL Server 2005 Express Edition installation.  the application that uses this database was designed for deployment with MSDE 1.0 but i have tested it to work without issue with MSDE 2000 and SQL Server 2005 Express Edition.  unfortunately with 2008 i run into the following error:

    2009-03-02 19:10:20.04 Logon       Error: 18456, Severity: 14, State: 8.

    2009-03-02 19:10:20.04 Logon       Login failed for user ‘usrx’. Reason: Password did not match that for the login provided. [CLIENT: x.x.x.x] (note that it displays the right IP –> i just x’d it out)

    i know the password by heart having had to pickup supporting this product after its acquisition from a company that made no callbacks regardless of situation for months at a time.  i have manually, through the configuration utility, dropped the login/schema from the db and deleted it from the list of logins and then rebuilt through such without success.  have also done the following through a query without success:

    use MSDE_X exec sp_dropuser ‘usrx’

    go

    use MSDE_X exec sp_droplogin ‘usrx’

    go

    use MSDE_X exec sp_addlogin ‘usrx’, ‘usrxpass’, ‘MSDE_X’, ‘english’

    go

    use MSDE_X exec sp_grantdbaccess ‘usrx’

    go

    use MSDE_X exec sp_addrolemember ‘db_owner’, ‘usrx’

    go

    can anyone tell me what i am missing?  i am testing this in a VMware install of XP w/SP3 and looking at the system with SQL Server 2005 Express Edition i can’t see anything different between that setup and the 2008 setup…

    thanks in advance!

    - Robert

  234. Greg says:

    I am running into the same Event Error 18456. I have a server 2003 in a clustered environment. I am simply trying to pull in an Excel spreadsheet using the following query:

    Select * from openrowset(‘MSDASQL’,’Driver={Microsoft Excel Driver (*.xls)};

    DriverId=790; Dbq=Template.xls;DefaultDir=S:Configuration;’,

    ‘Select * from [Locations$]’)

    Openrowset is enabled.

    User: NT AuthorityAnonymous Logon

    Event Viewer Error: login failed for user ‘nt authority anonymous logon’

    All rights have been assigned to the directory that the spreadsheet is in.

    Any suggestions?

  235. Lars says:

    Hi I have a customer who recive the

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

    this comes for both the domain users and for the SQL SA user and a SA user we creates on his server  they all have Serveradmin premissions

    Any good ideas on what to look for?

  236. Levin says:

    Hello,i got a state 38 error,please help!

    Below is the error log:

    2009-03-18 22:52:22.91

    login         error: 18456,Severity: 14,state: 38。

    2009-03-18 22:52:22.91

    login          Login failed for user ‘sa’.

  237. Kristoffer says:

    Also getting state 16 as of friday the 20th starting at 10:30am CET. Weirdly enough, afaik we didn’t do anything to any account or system at that time. Have no idea what to do, it happens about twice per day and a quick restart of the sharepoint server and we’re up again. The SQL is on a seperate server that’s always online.

  238. Peter B.L. Rasmussen says:

    I have just had a case of "State 1 login failure", that was caused by the fact that when I installed the SQL server, I was using a domain controller, but this has since been demoted so I don’t use a domain controller anymore. Of course the logins that were registered as valid for maintaining the SQL server, and all other access was closed. I did an uninstall/reinstall to fix this. I should have seen this before demoting my domain controller, bud didn’t, so here’s a lesson to learn.

    This just to supply the information that this also can be a reason for the "State 1" failure.

    Peter

  239. Michael Baas says:

    Thanks, useful blog which helped me to move one step forward in my struggle setting up SS2005. Unfortunately I am getting "Error: 18456, Severity: 14, State: 5" – the 5 seems to indicate "Invalid userid". I am getting this prob only when logging in through ASP.NET-application; with Mgmt Studio I can access that same db fine.

    SQL Server is set to use mixed authentication, the ConnectString I am using is "<add name="MyConnectionString2" connectionString="Data Source=MyServer;Database=NDFI;Initial Catalog=MyTable;Persist Security Info=False;User ID=MyAdmin;Password=MyPswd" providerName="System.Data.SqlClient" />

    I am running out of ideas here, would appreciate some help :)

  240. Larry says:

    I am getting a Error: 18456, Severity: 14, State: 8 when attempting to login via Citrix but when I log in locally using the same password I am able to log in. Anyone ever experience this?

  241. Mark Ryan says:

    I also encountered the Error 18456 Sev 14 State 16 on our MS Sql Server. At first i try to reset the password for my user but i have no success. But when I try again to look at the users properties having the error i found out that there is no default database assign for that user. I just assign a database and presto I can log on to my database.

    My Slotuion:

    1. Log on using sa account.

    2. Expand secuirity and logins.

    3. Right click on the user account with login problem problem and choose properties.

    4. On properties genral tab, you will see an drop down option for default database used for the account, choose a database for the account to used and click ok or apply then ok.

    5. Log off on sa account and try to log in again.

    That solution works for me.

  242. Parvathi says:

    can anybody please tell me what to do if i forget the admin password orhow to retrieve password

  243. SQL Protocols says:

    Hi Parvathi,

     If this is SQL Server 2005 or SQL Server 2008, you can refer to this entry on Raul Garcia’s blog, which details what to do if you have forgotten the SA password and have removed the built-inAdministrators group from the sysadmin server role: http://blogs.msdn.com/raulga/archive/2007/07/12/disaster-recovery-what-to-do-when-the-sa-account-password-is-lost-in-sql-server-2005.aspx

  244. Ali says:

    My server is Win2003 with MSSQL2005 and hosted a site with good trafic. 5000 uniqe visitor per day.

    my Event Viewer application log section is full of this error warning:

    Login failed for user ‘sa’. [CLIENT: 127.0.0.1]

    Event ID: 18456

    My web application is working correctly. and I can use managment studio remotely or localy. with SA or with windows auth.

    But what is this error?

    Best Reg

    Ali

  245. Chris says:

    I had the same problem.

    I have management studio installed on my desk and was building a disk cleanup maintenance plan for my sql server and would get an error as soon as I clicked ‘save’. I couldn’t understand why I was getting the error. Then I whent down to the data center where our server is located and I logged in to the physical server. I then created the maintenance plan there. This time I got No error! Now that I look back, it makes sense.

  246. setgraph says:

    If you have installed Windows SharePoint 3.0, "Error: 18456, Severity: 14, State: 38", caused SQLSERVERAGENT does not start.

    Set start mode for SQL Server Agent to automatic, fix this problem.

  247. Ralphie says:

    What does category (4) in parentheses mean?

  248. angeli says:

    hey i m  also have the same error in the event log i m  getting Server Name: "

    "Error Number: 18456"

    "Severity: 14"

    "State: 1"

    "Line Number: 65536"

    The following message might also be returned:

    "Msg 18456, Level 14, State 1, Server , Line 1"

    "Login failed for user ”."

    but on client side i get the error "datasource not defined and default driver not specified" using sql 2005  workgrop on server 2003 sbs .

    no problem with coding cause whn i try to connect to trial sql 2005 enterprise version on xp it connects without hassles  …..

    email :- angelijadhwani@hotmail.com

  249. Anil says:

    Hi

    I was getting same error mentioned by "angeli", when I connect to db server through SQL Management Studio. I used the technique mentioned by "Bertie", using back arrow key while selecting to copy password from a text file and it worked perfectly OK. Great tip Bertie, thanks.

  250. Vihang Shah says:

    Hi all jst try this. I hope it ll solve all ur problems.

    – SQL Query to Alter the [sa] login and reEnable it

    USE [tempdb]

    ALTER LOGIN [sa] WITH PASSWORD=N’123′, DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    GO

    ALTER LOGIN [sa] ENABLE

    @VHNG

  251. Ankit says:

    Heya!!

    Just Right click on microsoft SQL server Management Studio and run it as administrator.

    It worked for me!

  252. Ram says:

    I have an ssis package writes data into sql server but gives me error Error: 18456, Severity: 14, State: 8. Logon Failed for user.

    The userid is valid and works fine for other packages where I read data from sql server. The same userid and pwd are used across the appln where it works completely fine.

    Kindly help.

  253. Ramesh Rajamohan says:

    Thanks SSG. Your suggestion has helped me to resolve my problem:

    SSG said:

    For State 11 errors the Domain account (DomainWinNTlogin for example) was accessing SQL Server fine and then at some point we experienced the State 11 errors (maybe related to Mirror Failover or some other issues on the server).  I had to drop the Account from the Local Admin group and this stopped the errors occurring and then I added the account back in to the Local Admin group and errors did not return.

  254. Someyoung Guy says:

    So – my setup is…

    SCCM / IIS

    SQL 2005 latest SP

    SCCM with a remote SQL instance.  I kept getting the error "Login failed for user ‘NT AUTHORITYANONYMOUS LOGON’"

    I would also try and install SCCM and ALWAYS get the error that "Setup failed to install SMS Provider".

    All the sites kept telling me that it’s an SPN problem but I didn’t think it was and I confirmed more than once that I did have the SPN stuff in my domain.

    I installed:  SQL Server Management Studio Express

    I browsed my SQL Server install:  Security > Logins > Right click "New Login" > Search > type in "anonymous" and click "check names" > I had "Windows Authentication" so I left that checked.

    Under "Server Rolls", I added the Anonymous Logon to the Sysadmin roll which I suspect is NOT a good thing.

    That let me install my Management Point instance on my SCCM server instead of having to install it on my SQL server which I tried and it still didn’t work out in the end.

    Perhaps this is a bug?  Is this some sort of sick joke or just the way SCCM works with a remote SQL server?

    If I undo the Anonymous Login from the sysadmin roll I can’t connect to the DB with the SCCM Manager Console.  Why when you kickoff the install of SCCM it uses an anonymous logon on the remote SQL server then bombs unless you add the anyonmous logon to the sysadmin roll on the SQL server?

    This just doesn’t seem right…or is it?

    HELP!!!

  255. Skrisa says:

    Hi,

    the problem is in settings DB server.

    This settings is> after right login on DB server on servername (Sql Server 10. …) and select Properties

    Next step> Click on left panel, item Security and choice requestit value on group panel with SQL Authentication

    .. das ist ales  :))

  256. billiken66 says:

    This is a VERY informative thread!

    I’m getting the Error: 18456, Severity: 14, State: 8., but the situation doesn’t make sense.  The application that is failing to connect uses an ODBC connection.  The ODBC connection succeeds in connecting to the database.  The application returns a failure to connect, however, and we see the State: 8 error in the SQL log for that user.

    One possible complication: We’ve moved this DB from SQL 2000 to SQL 2005 and have put the compatibility level at 90.  The vendor has told us that the version of SQL Server is irrelevant to their code.

    Any ideas?

    Thanks,

    Bob

  257. nisha says:

    i m not able to connect my sql server 2005 database

    when i put username passoword then it show login failed.pls solve my problem

  258. Jonathan Scott says:

    Hi,

    Im getting the error

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

    Now, i’ve read through all this and tried all the various options suggested.

    Essentially the I am using Windows Authentication. My user is in a functional group, which has access to the required sql server. Now, everyone else in my functional group can connect to the server without any issues, though I can not.

    I’ve asked the admin guys to remove me from the functional group, and re add me to it as suggested in one of the above comments.

    I’ve tried accessing it from a different machine, same login though, and I get the same error.

    Can anyone point me in the right direction?

    Thanks

  259. Maybe this will help someone.

    Firstly ignore the message and go to the Error Logs (in the Log directory of your Microsoft SQL Server folder in Program Files).

    Open the log file up and look at the recent entries. The explanation is there.

    If you get something along the lines of (xxxxxxx is the username):

    2009-09-04 10:36:52.73 Logon       Error: 18456, Severity: 14, State: 58.

    2009-09-04 10:36:52.73 Logon       Login failed for user ‘xxxxxxx’. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]

    it means your SQL server only accepts Windows Authentication. Change it in the properties of the SQL Server Root.

    To do this using Microsoft SQL Server 2008 using Microsoft SQL Server Management Studio Express, log in as an administrator and right click on the server (at the root of the navigation tree) and click Properties. Go to the Security Section and change the login to SQL Server and Windows Authentication mode. Click OK and restart the service (you may need to use services.msc to restart it). Beware SQL Server is not as secure as Windows Authentication.

  260. anirudha says:

    i have a error 18456 how to solve it so send me answer to

    anirudhakumar.gupta@gmail.com

  261. Raji says:

    Am getting this error " Error is 18456, Severity: 14, State: 8." and this problem is only when i try to connect my system from a Windows2008 machine. When i try to connect to the database from a Windows2003 or a VISTA machine it seems to be fine with the SAME USERNAME and PASSWORD. I also tried a simple ODBC connection from my Windows2008 machine and it works fine.

    Can you suggest me what the problem is????

  262. zahan says:

    hi,

    I am getting State 16 errors. The windows account is the service account and has syadmin privileges. default db is master but master is fine

  263. Balaji G says:

    I get the above mentioned error message when i try to logon to my application which connects to sql server database for checking login information.

  264. Balaji G says:

    Error message i am receiving is Error NO:-2147217843, Native Error:18456 State:42000 Description: Login failed for user ‘sa'[SQL-]

  265. Ken Almond says:

    I was getting Error 18456, Severity 14, state: 16 while trying to use bcp.exe   I don’t really understand WHY i couldn’t use [] (brackets) around the DB name in particular but that was the fix.  Here was the bad bcp.exe command:

     bcp.exe [dbname].dbo.[111TabName] in c:/file.bcp -c -E -S [locahostname] -U xx -P pp

    Here’s the fix:

     bcp.exe dbname.dbo.[111TabName] in c:/file.bcp -c -E -S localhostname -U xx -P pp

    When I removed the [] brackets around the -S hostname it started getting Error 18456, Severity 14, state: 16.  Then when I removed [] brackets around the DB Name it WORKED!.

    The reason I used brackets is because I need them for a table name starting with digits….  so I just added them to db name (for some reason) because I thought they were ubiqutous for TSQL syntax…  Bad assumption in this case

  266. Fred says:

    I was getting the dreaded Error: 18456, Severity: 14, State: 16 as well after moving a Database from one Sql server to another. The security on the database was setup with AD groups.

    The strange thing was: for users in certain AD groups everything worked fine. Others could not login anymore (even though they had all necessary permissions), and we got the State: 16 error.

    Turned out to be caused by the fact that the new server was in a different AD domain from the users and groups. – the AD groups that didn’t work anymore were "Domain Local" groups … which can’t be seen from another domain, even though there is a trust (which makes perfect sense, but you have to think about it ;-). – The solution is to change the AD groups to "Global" groups (if possible, only domain local groups can hold user accounts from both domains) and/or create groups in the other domain and setup security on the DB for those as well.

    Just wanted to add this possible cause as it’s a different angle yet from the others already mentioned here ;-)

  267. Adolfo says:

    Hi,

    I’m trying to connect via Management Studio to different servers, and in all cases, I get the error 18456, severity 14, state 8 error in any of the servers.

    I’m usign the "sa" account with its password, but I can’t login to any of the servers.

    Now, when I run the SSMS with another domain or local user (right-click, EXECUTE AS) I have no problem whatsoever. Plus, when I try to connect via Windows authentication or with an SQL account that has a blank password, I don’t have any problem.

    Why could this be happening?

  268. Baduel says:

    Maes eso es muy facil, solo deben de de dar clic derecho en el usuario para ver las propiedades, ver el estado del usuario y cambiar a enable

  269. Heino Lengfelder says:

    When starting up my Server I always received the message with error state 4, because of a service trying to connect to a database which was not started at that time.

  270. SCOTT says:

    start>run>"cliconfg">OK

    Enable NamedPipes and TCP.  Set Shared Memory to ‘enabled’.

    Worked for me.

  271. Tom says:

    experienced this on a 6 node cluster – each of the nodes was trying to log into the others – it was just tons of chatter between the nodes, all returning the following msg:

    Message

    Login failed for user ‘<DomainNameClusterNode$’. [CLIENT: xx.xx.xx.xx]

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

    I created a login for each node on all the others – no perms other than public to master.  Errors had been going on at least every minute for some time.  

    Immediately after the creation of the logins, the errors stopped.

    cool.

  272. Napster says:

    You have follow these steps :

    go to Management studio/enterprise manager=>rightclick on it go to Properties =>

    go to security => Server authentication=> select SQL Server  and windows authentication mode.

    That it.

    hope this will work for U.

    cheers

  273. Gerridae says:

    I keep getting the following event log error on my SQL2005 server:

    Event Type: Failure Audit

    Event Source: MSSQLSERVER

    Event Category: (4)

    Event ID: 18456

    Date: 11/17/2009

    Time: 7:50:00 AM

    User: N/A

    Computer: SQ02

    Description:

    Login failed for user ‘domainintra_svc’.

    the SQL logs have these two errors repeated over and over, which correspond to the eventlog error:

    Date 11/17/2009 8:00:00 AM

    Log SQL Server (Current – 11/17/2009 8:00:00 AM)

    Source Logon

    Message

    Login failed for user ‘domainintra_svc’. [CLIENT: 10.1.30.33]

    Date 11/17/2009 8:00:00 AM

    Log SQL Server (Current – 11/17/2009 8:00:00 AM)

    Source Logon

    Message

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

    these errors happen ever 10 minutes and correspond to the errors on our sharepoint server:

    Event Type: Error

    Event Source: Windows SharePoint Services 3

    Event Category: Timer

    Event ID: 6398

    Date: 11/17/2009

    Time: 8:30:00 AM

    User: N/A

    Computer: WSS01

    Description:

    The Execute method of job definition Microsoft.SharePoint.Search.Administration.SPSearchJobDefinition (ID 070e9fbc-b7f1-4132-99e8-f8775f31027c) threw an exception. More information is included below.

    Login failed for user ‘domainintra_svc’.

    Event Type: Error

    Event Source: Windows SharePoint Services 3

    Event Category: Database

    Event ID: 3351

    Date: 11/17/2009

    Time: 8:30:00 AM

    User: N/A

    Computer: WSS01

    Description:

    SQL database login failed. Additional error information from SQL Server is included below.

    Login failed for user ‘domainintra_svc’.

    I know it has something to do with SQL/Windows Authentication, but not really sure where.  hopefully someone can help.

  274. Junior Mayhe says:

    This error can be also solved with:

    - go to sql server

    - right click on server, choose properties

    - click on security

    - on server authentication, enable SQL Server authentication

    also enable and configure password sa user for node security on serve tree.

  275. Rick says:

    I get Severity: 14, State: 8 with a sprinkling of State: 5’s against SQL2005 in a single domain. There are days where I don’t get them at all but also days of plenty. I can see ONE error resulting from a mistyped password, but why do they keep going?

    Looking over some recent comments, I already have NamedPipes, TCP, and Shared Memory enabled for SQL, as well as SQL Server authentication.

    If "also enable and configure password sa user for node security on serve tree" means that the sa account should be enabled, ours isn’t, but the errors never mention the sa account just a couple other accounts that are enabled and used.

  276. Rick says:

    Finally figured out that it was the DSN file we use for linking Access to SQL tables. You want to use the Windows authorization flavor, so "Trusted_Connection=Yes" should be in it along with a UID pointing to a valid username, which will happen automatically when you go into Access and recreate the DSN choosing Windows authorization this time. Yes, you’ll have to relink all your tables with this new file, but it’s a small price to pay. Otherwise a plague of State 5’s and 8’s may be upon you when people do queries.

  277. prince1709 says:

    This error can be also solved with:

    - go to sql server

    - right click on server, choose properties

    - click on security

    - on server authentication, enable SQL Server authentication

    also enable and configure password sa user for node security on serve tree.

    Goto SQL Server as in?

  278. Cris Conner says:

    I was getting error state 11 for a windows login. I looked at the sql server permissions and noticed that the Effective Permissions were missing ‘CONNECT SQL’ It was because the individual I was trying to add was part of a Active Directory Group that I wasn’t aware of, and that AD Group had “DENY CONNECT” The Deny took precedence over the individual login’s right to connect. I just dropped the login that had the Deny Connect. Problem solved. I also made the Domain Admins take him out of that group since the guy changed jobs.

  279. matt says:

    I just downloaded sql server express 2005,  I cannot connect to a server.  I have used the same login on two other computers with the same sql server express 2005, and it worked.  

    I even tried turning off the firewall and antivirus program.  Nothing works.

    Any help would be greatly appreciated.

  280. Muhammad Farooq Khan says:

    Actually I am tryint to install the billing software and connecting it with SQL Server whenever I try I get this below message. Unless I have done all the necessary steps including the Security tips but unable to connect. Please provide me support.

    (Microsoft SQL 2005 Server Express Edition)

    Unable to connect to server.

    Please check the SQL Server Authentication policy should be "SQL Server and Windows".

    To check please open SQL Enterprise Manager and look under Properties–>Security–>Authentication

  281. Youssef Rihani says:

    from security tab of the sa properties, clear "enforce password policy" and type the pasword of the sa twice again. now you should be able to connect.

  282. Yana says:

    Error: 18456, Severity: 14, State: 1

    would anyone know how to correct this error?

    What should I do?

  283. Andrew says:

    Thank you for the helpful post, Il-Sung Lee.

    I was able to determine, by looking at the SQL Server log, that the State was 8. Password mismatch.

    However, I have about 5 services running that access the local SQL Server instance, and there doesn’t not seem to be any information in the log about which application/service attempted to login with an incorrect password.

    Is there a way, for example by using Process Monitor or somesuch tool, to know which Process ID tried to logon?

  284. anjali says:

    I am trying to connect my client machine with sql server 2000 but the error thrown each time is

    Connection :failed

    SQL State : 28000

    Sql Server Error : 18452

    [microsoft][sql server driver][sql server][login failed for user null.

    Reason : not associated with the trusted sql server connection.

    This connection is functioning properly in 3 client machine but throwing error for two client machine.

    I have tried to change the above process described (Security-Login—–]

    and the second problem is its showing the name of server two times in the same list .. so is it conflicting with the server detection.

    Your valuable feedback is highly appreciated.

    thank you

  285. rklimaszewski says:

    On a cluster, could connect to an instance on one node with the Surface Area Configuration tool, but not the other node–I was getting state 11 error.  All security was identical, and I was logged in with Admin and SYSADMIN privs (a different account than the SQL service account).

    The error from SAC was:

    Computer VSERVER does not exist on the network, or the computer cannot be configured remotely. Verify that the remote computer has the required Windows Management Instrumentation components and then try again.

    Since this is a cluster, remote connections is always enabled.

    Even though I was logged in with an Administrative account, I did a "Run As" on the SAC menu item and UNCHECKED the "Run this program with restricted access" to work around this issue.

    I did not see any differences in WMI security between the cluster nodes.  This instance is running SP3 build 4266.

  286. Bruce says:

    To check please open SQL Enterprise Manager and look under Properties–>Compatibility–>Privilege Level check the Run this program as an administrator

  287. Eric says:

    Suddenly started receiving a log full of login failed messages with Error: 18456, Severity: 14, State: 16.

    This happens for any login where the default db is not explicitly stated. Explicitly stating the default db in the connection works fine, but should not be required in a correctly working server (so long as the default db is correctly set).

    The users are using domain login AND access is granted by an AD group. The group has the default database attribute set, the default database is online, and the users have access to no other databases by membership in any other group.

    I’ve also tried to rule out schemas without owners and SQL Agent jobs referencing db’s that don’t exist. I remain baffled.

    Any ideas what I’m missing or how to troubleshoot this?

    Cheers,

    Eric

  288. sam says:

    Hi dear,

    I am getting error 18456 after trying to log in,using windows authentication.What I have is sql server 2005, 90.It is installed on a window vista and I have been using it for ages.

    Any ideal what I can do to get connected?

    Thanks in advance!

    sam

  289. samama1975@yahoo.com says:

    Hi!

    As said before,the error I get is 18456.There was no state ,line or severity with the message.I hope this info will help.

    Thanks,

    sam

  290. Sam F says:

    installed a copy of SQL Server 2005 on my PC. Now when I start it I select my own PC as the server name and the Authentication is set to "Windows Authentication". My username is already written and greyed out and the password box is greyed out. However when I click connect it fails with error 18456.  I get error state 11:Valid login but server access failure.

  291. Mohammed Bukhari says:

    You just need to start the sql with admin previlage or run as administrator, hope should work.  

    Regards,

  292. Manoj says:

    There could be much more resions, as the original says.

    1) Check the Server Property for the security configuration, If it is windows, bydefault SQL SA account should be disables.

    2) Try to enable SA account

    3) Match the password.

    4)Make sure other login user the Secure (Connect to SQL Server)

    5) and much more since the more error you get you have more solution to resolve that… See the BOL and other SQL forums..

    regards,

    Manoj

  293. Jyothi says:

    Hi,

    I'm getting Error:18456, Severity:14, State:1.

    Could any one help me on this issue.

    Thank you in advance,

    Jyothi

  294. Jyothi says:

    Hi,

    I'm getting Error:18456, Severity:14, State:1.

    Could any one help me on this issue.

    Thank you in advance,

    Jyothi

  295. suresh says:

    Hi.. i am using SQL Server 2008. server authentication is set to "SQL Server and Windows Authentication mode". when connecting using windows accounts everything is fine, but any attempt to connect as e.g. 'sa' fails with this 'State: 1' error message… greets, rm

  296. eli says:

    hi

    my error state is 1

    what can ido?

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

  297. Bala says:

    Who ever is facing any problem in sql while login in sql authentication mode, just try this trick,

    Step 1: Login in to windows authentication

    Step 2: Run following script:

    – ***********************************

    – SQL Query to Alter the [sa] login and reEnable it

    USE [tempdb]

    ALTER LOGIN [sa] WITH PASSWORD=N'123', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

    GO

    ALTER LOGIN [sa] ENABLE

    – ***********************************

    now close your SSMS and restart it and try to login again by userID 'sa' and pwd '123'…

    Enjoy…

    @BALA

  298. sivaprasad s says:

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

    Login failed for user ''. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: <local machine>]

    State 58 means An attempt to login using SQL authentication , where Server is configured for Windows authentication only.

  299. jp says:

    Same problem here. Installed sqlserver locally to do some development testing. Wondering best way to setup permissions to get on the db.

  300. Johnny says:

    What if I have a state 11 error on a LAN?

  301. kiran says:

    Hello if u face Microsoft SQL server, Error:18456

    Then open management studio as administrator.

    I hope that this works as it did for me.

  302. Dhaya says:

    The post is really helping me in undertstanding the error happening in our Production SQL Server 2005.

    Below are the Error Message Details :

    Source        : Logon

    Message      : Login failed for user 'NT AUTHORITYSYSTEM'. [CLIENT: <local machine>]

    Date            : 6/14/2010 8:03:19 AM

    Log              : SQL Server (Current – 6/16/2010 2:02:00 AM)

    Source         : Logon

    Message      : Error: 18456, Severity: 14, State: 16.

    I am getting this error every minute logged in the SQL Server Error Log.

    One question : You have mentioned we may need assistance from CSS. I am not familiar with CSS before. May I know something about CSS?

  303. nickl says:

    I have the same error with state 16 and the user NT AuthoritySystem on the local SQL server.

    The database where user tries to log on is marked as suspect by some restore.

    - What is the error, what is the consequenz? suspect db causes login error or login error causes suspect db??

    - What can I do to get things straight again?

  304. nickl says:

    I have the same error with state 16 and the user NT AuthoritySystem on the local SQL server.

    The database where user tries to log on is marked as suspect by some restore.

    - What is the error, what is the consequenz? suspect db causes login error or login error causes suspect db??

    - What can I do to get things straight again?

  305. ast says:

    I am getting

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

    could someone help me how to

    deal with this problem?

    I am running SQL Server 2008

    I can log on using windows authentication but

    having this problem when using sql server authentication

    sa account

    thanks for the response!!!

  306. Laura Sparks says:

    I am trying to sign up for http://www.Caring4Cancer.com–i keep getting login failed

  307. mark gao says:

    I met this error:Error:

    18456, Severity: 14, State: 16.

    Login failed for user 'NT AUTHORITYSYSTEM'.

    At last i found that the reason for this error When a replication-job run, after carefully checked,i found that i dropped publication and subscriber database , and i forgot to delete theses replication jobs.

    So when theses jobs run, SQL can't log NT AUTHORITYSYSTEM in to my dropped  publication and subscriber database

  308. asif says:

    Im having this problem with sql server 2005 in the first example of c#:

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

    asifsab@yahoo.com

  309. Dan says:

    A little trick I found after I was getting Login errors using management studio on the localhosst (win2008R2)

    You need to "run as adminstrator"

  310. xdev says:

    What about the same error but for:

    - windows authentication

    - user which has sysadmin role

    - connection is local

    - state is 1 ?

  311. gladz says:

    the error state…  INvalid attempt to read when no data is found… how could I be able to resolve it???????

  312. gladz says:

    I really need your response… thank you… email me at gladztinosa@yahoo.com

  313. Mani says:

    Hi,

    Just change the server authentication mode to "SQL Server and Windows Authentication mode" in Server Properties -> Security Tab.

    Sure It will be working fine.

    Thnx

  314. Brett Nieland says:

    Tank you.  You have help yet another.

  315. Brett Nieland says:

    Thank you.  You have help yet another.

  316. titi says:

    I have sharepoint 2007 .I am site collection administrator but I loose full control permission (now my permission is like disigner permission) I suspect my db sql 2005 express size I shtink wss_content  the size is now 2 .39 GB  but didn't work .when I show the  error log I found

    error:18456, security:14, state:16

    login faild for user 'NT AUTHORITYNETWORK SEVICE'.[client :IP@]

    can someone help me

  317. Prabhleen Singh says:

    I am getting an error 18456,severity 14 and state 1. What I 've to do know

  318. Abhinav says:

    Hi Friends,

    Please help me with this error

    I have been getting this error:

    SQL Server Assertion: File: , line=440 Failed Assertion = '!PSess ()->FHasWorkspaceRef ()'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.

    I figured out that this error is due to a bug in sql server 2005 and it would be solved by installing SQL Server 2005 service pack 2, and I did install it but still I get these errors again and again..

    Pleasssse Help. Thankss..

  319. mohamed faizal says:

    I have got the following error:

    login failed for user 'sa'

    category: 4

    event id: 18456

    Can anyone came across this issue…….

  320. meera says:

    HI,

    I am using sql server 2005.i denied the serveradministrator to connect to database engine.Now the problem is that i am not able to connect to the database engine in windows authentication mode.

    plz tell me how to grant permission to admin.

  321. Keith says:

    Hi,

         I have a user that uses dynamics and can connect and will start working but then something will hang or report will not run. and i find 18456 severity 14 state 8 messages in in log on server. which points to a password mismatch , but if the password was incorrect user will not be able to login on dynamics/sql..

    any ideas?  I am think about reloading users SQL client to see if these clears up the errors.

  322. Frank says:

    I am getting the below error on sql 2008.  can some one help me with this. The domain account is a sysadmin on the server. This msg pops up every 1 min in the errorlog

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

    Logon        Login failed for user 'domainSPS2007'. Reason: Failed to open the explicitly specified database.

  323. Mark Donoghue says:

    For those struggling with State 1.  State 1 means you dont have permissions to even get an error message.  In my case it was because the server was set to Windows Authentication mode only (not mxied mode or Windows & Sql mode).  Once I changed the server mode and restarted it worked fine.

  324. harneet says:

    hi all,

    me getting error 18456 state:1 and severity:14……..what should i do???

    plz can anybody mail the solution at harneetsingh3@gmail.com

  325. Victor says:

    Any comments on why we're seeing the following in the ERRORLOG:

    04/13/2011 12:13:48,Logon,Unknown,Login failed for user 'DOMAINNODE1$'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: 172.21.0.228]

    04/13/2011 12:13:48,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 11.

    So it's on a newly built SQL Server 2008R2 Cluster with SPN configured.

  326. cmois says:

    Hi,

    i have Login failed for user "x" Error 18456 as error messages in SQL Server 2008.

    Plz what to do?

  327. flauder says:

    Hi,

    i have Login failed for user "x" Error 18456 as error messages in SQL Server 2008.

    Plz what to do?

  328. Jose Angel Yanez says:

    I had state "1" after renaming a database!!

  329. liamfawcett@hotmail.com says:

    I had the same problem with state 38 ,

    I had an sql server pc that was running within a domain. (i wanted my app to connect to this instance)

    I resolved it by creating a login for each user via the sql server  Logins , also a user for the database with the domainnameuser  

    (I was using windows auth  for my server and local instance false as I didnt want a local copy created for my users, the problem was that although my user could access the sql server via windows auth- but tconnecting to the database, I would still need a user and login for each person accessing my database.

    (also obviously make sure you have added the firewall ports for sql browser and sql server!)

  330. Dilip says:

    I am getting this error in log

    Error : 18456, Severity : 14, State : 11

    Login failed for user 'NT AUTHORITYNETWORK SERVICE'

    this error is generating in the front end .net application

    We ar using the use Integrated Security=SSPI with an IIS/ASP

    application.The DB is assigned to  NT AUTHORITYSYSTEM  login

    Please help

    with regards

    Dilip D

  331. Joe Larson says:

    Using Sql Studio Express to connect to a Sql Server 2005, I'm getting Error: 1846, State: 8 — which should be password mismatch.  Yet, when I use the same user name and password from a jdbc based client like Squirrel, it works fine.  I have another machine where I have the login details saved where things work fine — I am loath to try retyping the password there because if it doesn't work, then I can no longer support this system.  Anyway, why might I be getting a password mismatch error when the password is correct?

  332. Damien Munsch says:

    J'ai un problème avec le LAN  du programme docteur connectivité . J' aimerais résoudre ce problème. Pouriez -vous m'indiquer toutes les étapes qui me permettront de résoudre le problème? Je vous remercie!!!

  333. Anil Verma says:

    erver Authentication. The 'sa' login details are correct but still getting the following error message:

    Quote:

    Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)

    In order to resolve the issue, please refer to the following steps:

    1. Login to the MSSQL Server Management Studio with Windows Authentication.

    2. In SQL Server Management Studio Object Explorer, right-click the server, and then click Properties.

    3. Under the Server Properties, select a page of "Security".

    4. Select the Server authentication as "SQL Server and Windows Authentication mode" and click Ok.

    5. Restart the SQL Services and then try to login with 'sa' details.

  334. mail@mypsychicadvice.com says:

    Login failed for user 'sa'. Reason: Password did not match that for the login provided I installed happysql.com software to block hackers attack for brute force password. It works great. I tried to find solution but this one is only helped me to save my sql.

  335. Joseph says:

    The most common I encounter in my case is sql authentication not enabled. This helped http://www.sql-server-business-intelligence.com/…/microsoft-sql-server-error-18456-login-failed-for-user

  336. zeeshan says:

    Sounds like your database server isn't configured for both NT and SQL authentication. or you can use sa user that have access over target database.

  337. Nanigak says:

    The following link has details of this error sqlserverlearner.com/…/microsoft-sql-server-error-18456

  338. ruby says:

    i have error  18456 in windows 7.please give me solution

  339. ruby says:

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536         Login failed for user  

    plz  give me solution as soon as possible

  340. anji says:

    ALTER LOGIN sa ENABLE ;

       GO

       ALTER LOGIN sa WITH PASSWORD = '<enterStrongPasswordHere>' ;

       GO

  341. Fred Latimer says:

    Hi Sam,

    I go through the following link: http://www.sql-server-business-intelligence.com/…/microsoft-sql-server-error-18456-login-failed-for-user

    as you provided in the conversation, it its going to help.

    Thanks

  342. Mauricio Ramirez says:

    Hi!

    I have the vry same problem with status 16 and it appears that my DB are in recovery every 5 seconds. Why is that?

    While the DB are in recovery mode they won't accept logins.

    What to do?

    MRamirez

  343. santbir singh says:

    hi

    this is an issue if you are running window 7

    please right click on the icon of sql server management studio and then click on run as administrator

    after that login menu appear

    just choose ->

    Server Type : Database Engine

    Server Name:   ( . )      // don't put any brackets only type single dot here (if you didn't given any user name for more information or for sql server installation just see this video tutorial)

    Authentication : Windows Authentication

    it could be possible you gave an instance name if you remember than put it as

    computername/instance

    type your computer name and after slash type the instance if you remember

    watch this tutorial complete guide to run sql for begginers

    http://www.youtube.com/watch

    this is part 1

    http://www.youtube.com/watch

    and this is part 2

    it will really work

  344. sqlsam says:

    check your sql server authentication mode.

  345. Dr.Doug says:

    On one machine we have This problem:

    SQL Server on server log reports failed login codes:

    Error: 1846

    Severity: 14

    State: 11

    But on other we do not.

    The machine…

    SQL Server 2008 R2 64x V10.50.2500.0

    Running on VMWare

  346. happysql says:

    Please visit [URL="http://www.happysql.com/&quot;]www.happysql.com[/URL] . Login failed for user 'sa'. Reason: Password did not match that for the login provided.  My servers were 32 and 64 bits I purchased happysql software from happysql.com because this Software to create IP security Policy automatically and Block IP attack over SQL server for 24 x 7. Safety from SQL Hackers and Your answer to Hacking Attempts. The program is designed to loop through the event log and locate IP’s that attempt to hack your SQL database. It gives guaranteed water tight security for blocked IP. Once the IP is put on the list, you should not be seeing any additional attacks from that IP since the IPSec policy handles connections at the lowest level.

  347. Eric_G says:

    Hello,

    On SQL Server 2005 SP3

    One time I had the same error 18456, but with no Level and State.

    After a look at Windows Events it seems to be a rights trouble.

    I create my domainaccount to the SQL Server logins with sysadmin role.

    Then start SQL Agent like a charm !

  348. Emil Glownia says:

    Here's another link that covers the same subject with slightly more explanation (screenshots) http://www.katieandemil.com/microsoft-sql-server-error-18456-login-failed-for-user

  349. amador camach says:

    hi, I have a problem sqlstate 28000, sql error server 18456. what i should do??? i really need to run this program. with this program do the orders of the day to day maintenance of the company. thanks, regards.

  350. Earl Grey says:

    Love it!! The errors are kept non descript for security reasons to keep people from understanding why it's failing, but here are the reasons for the failures. Thank goodness hackers can't use google :) Here's an even better option, to stop the possibility of the wrong people having access, stop all authentication. Sheez MS, could you get any dumber??

  351. Arnault says:

    I've got a state: 14 with Sql Server 2008 R2 accessing from Excel 2003. Can you help? Thanks a  lot

  352. rumen says:

    right click mouse->run as administrator…

  353. Gary says:

    Excellent post.  I was fumbling until I saw this.   It would be good to repost and update the list of States or at least point to where to find the full list.   I found that 58 means the user is logging in with SQL User credentials but the database server is configured to only use Windows Login credentials.

  354. AL says:

    Reset the password; try it and change the passw back. worked for me.

  355. Afagh says:

    try RUN AS ADMINISTRATOR  in order to connect using windows authentication

  356. GAURAV CHAUHAN says:

    Dear All,

    I had faced same issue in my SQL Server 2008R2 SSMS and i have fixed the same have gone through SQL logs and I have analyzed that "Issue is related to authentication".

    So I have run SSMS.exe as administrator and it’s allowed me to login.

    Cheer guys!!!!

    Regards

    Gaurav Chauhan

  357. dee says:

    sql server 2005 has error 18456 severity 14 state 16 login failed for user 'NT AUTHORITYSYSTEM' – this user has sysadm and everything else, why is there an error several times a minute?  Ive checked security / roles / … All is in place.  

  358. MIchael says:

    I have the problem in sql server 2008

    error:18456

  359. Ryan says:

    For users with State 11 or State 12:

    This will allow your Windows Login (with admin priv) to access the SQL server!

    msdn.microsoft.com/…/dd207004.aspx

  360. Krishantha says:

    how to  pix Error code sq l server 18456

  361. Yah says:

    LOL I just got this error message of 1.  Out of the blue.  No idea why this is happening . But Thank You microsoft for providing a such dog&hit error messages.  Gawd I hate M$

  362. vikash tyagi says:

    right click on the sql server manager studio and select run as administrator becose it treat your system name as subuser of administrator

  363. login error with user Sa says:

    ===================================

    Cannot connect to NAGARAJU-PC.

    ===================================

    A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server) (.Net SqlClient Data Provider)

    ——————————

    ——————————

    Error Number: 2

    Severity: 20

    State: 0

    ——————————

    Program Location:

      at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

      at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)

      at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)

      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 System.Data.SqlClient.SqlConnection.Open()

      at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)

      at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

  364. Robert says:

    I have installed sql server 2005 yesterday,after that i restored all my  databases ,today i am traying to opening sql but   i am unable to login with WINDOWS AUTHENTICATION and  SQLSERVER AUTHENTICATION

    could you please suggest me…..

    Thanks

    Robert

  365. flobi says:

    What about state 1?  I'm getting error 18456 state 1 (severity 14)

  366. Prasad says:

    Hi,

    I have an user with sysadmin permissions still it is giving state 16 error? Can someone please help? Thanks

  367. francy says:

    SQLSTATE: 42000

    code: 4060

    message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Cannot open database "DatMasterColegios__2013" requested by the login. The login failed.

    Error:

    este es el que a mi me aparece que puedo hacer

  368. Nathan says:

    08/06/2013 16:15:29,Logon,Unknown,Login failed for user '<username>'. Reason: Password did not match that for the login provided. [CLIENT: x.x.x.x]

    08/06/2013 16:15:29,Logon,Unknown,Error: 18456<c/> Severity: 14<c/> State: 8

    I have tried resetting the password on the SQL Server 2012 and I am able to get back in just fine. However, if I switch PC's or logout of Windows 7 and back in again, and use the same credentials that just worked. The same error message comes up. We have roaming profiles enabled and we are trying to resolve why this keeps happening. Any input would be most helpful. Thanks!

  369. dright says:

    I'm having problem to login as "sa" I already alter  password and changed the Auth mode, but still can't login. I having this error msg:

    Server Name: *****

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

  370. Awanish Kumar says:

    Plz solve this Issues

    C:UsersAdministrator>sqlcmd -S F2502148 -U Administrator -P inf0db@

    Msg 18456, Level 14, State 1, Server F2502148, Line 1

    Login failed for user 'Administrator'.

  371. Awanish Kumar says:

    Hello Every one

    Please help me :

    Ms Sql Server 2008 R2

    i try to login with CMD,but its not  work

    my exercise is below

    C:UsersAdministrator>sqlcmd -S F2502148 -U Administrator -P inf0db@

    Msg 18456, Level 14, State 1, Server F2502148, Line 1

    Login failed for user 'Administrator'.

  372. Aruna says:

    I've had the same problem. Restart all the SQL Server services. To restart the services go to Start > Administrative Tools > Services and find the sql services and restart them.

  373. Mark Freeman says:

    One to a few times during the work day, seemingly at random, I am getting a log entry of "Login failed for user '<our domain>mfreeman'. [CLIENT: 192.168.0.48]". This is paired with a log entry of "Error: 18456, Severity: 14, State: 16." at the same time.

    I captured this with Profiler and found that it is for the master database (which is online and not having any troubles — I can run queries against it in SSMS just fine) and is coming from an ApplicationName of ".Net SqlClient" (which could be anything).

    This is my own login (which has the sysadmin role in the instance), and it is coming from my workstation. I get it

    Is there any way to find out more specifically what might be attempting the access or why it might be failing?

  374. irakli says:

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

    help

  375. Ranjeet says:

    i am having this problem with sql server 2005 in the first example of c#:

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

    What should I do?

    my email   rs007202@gmail.com

  376. Kunal says:

    Login failed for user 'Kunal-PCKunal'. (.Net SqlClient Data Provider)

    what can i do?

    my email:prajapati.kunal3@gmail.com

    thanks

  377. raj says:

    error: 18456, state 1??????????????

  378. vipul sachan says:

    SQL server error: 18456 overview – This error occurs when your authentication request is been successfully accepted by SQL server named, but due to some reason SQL server is not able to grant the access to connect.

    For more detail check here -

    vsstack.blogspot.in/…/microsoft-sql-server-error-18456-login.html

  379. Bujji says:

    Hello,

    TITLE: Connect to Server

    ——————————

    Cannot connect to PC.

    ——————————

    ADDITIONAL INFORMATION:

    Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)

    For help, click: go.microsoft.com/fwlink

  380. Jo Elaine Alsup says:

    I need to know what I should do about my log_in failure. I turn on computer, login page appears. I hae to people: Elaine and Jo E. I cannot remember my password to the Jo E. account because I have been ill and just forgot it. But it is the Administrators account. It is very disabling to my ability to use computer. Thank you for your attention.  Elaine Alsup

  381. majid khan says:

    i use sql2012

    i have this error 18456

    Error Number: 18456

    Severity: 14

    State: 1

    Line Number: 65536

    help me?

  382. sumit says:

    I have exactly the same problem using SQL Server 2005.

    email me -braviear@gmail.com

  383. david.burghgraeve@colruyt.be says:

    Error: 18456, Severity: 14, State: 1

    Solution for me: Log off from the session and do a fresh logon to the (remote) system. looks like somekind of corruption of the SQL Server Management studio.

  384. raja says:

    logon is an invalid event type error occurred in sql server 2005

  385. Trung Do says:

    I think these step below will help you:

    1. Open Microsoft SQL Server Management Studio.

    2. Login with Window Authentication user

    3. Right click Database server –> chose Properties

    4. Click Security –> Server authentication –> change to "SQL Server and Windows Authentication mode"

    5. Click OK –> restart services.

    Done.

    Hope helpful.

  386. Alan M says:

    Check if your SQL server is configured to windows and SQL authentication mode.

  387. vijay says:

    "Error Number: 18456"

    "Severity: 14"

    "State: 1"

    "Line Number: 65536"

    I am having the same problem. I tried to login both Authentications, got the same problem pls help me vijaydba2@gmail.com

  388. priya says:

    send solution to 18456 and invalid password send solution thankyou

  389. LEE says:

    Thanks for this.  I have never had a server set to just windows authentication so I had no idea about this property.  But here is an idea, Microsoft.  If the server authentication is set to Windows Authentication mode then don't present SQL Server login option in the drop down when connecting to the server.   I'm using SQL Server 2012 and this behavior is the same as it was in v 2005.

  390. Jeff says:

    I have Error 18456, Severity 14, State 29.  I can't find any information on this state anywhere.  Any suggestions?

  391. Ramesh K says:

    Useful information, thank you for sharing

  392. Jason Clark says:

    You can post your queries regarding to SQL Server Error 18456 in this forum site http://sysc.org/

  393. Nigel Nikon Metrology says:

    Who are CSS ?

    We have state 38, what does that mean ?

  394. ayush says:

    Message

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

    what is error