none
Intermittent State 8 Severity 14 login error for SQL server authenticated users.

    Question

  • Greetings,

    I manage a lab of windows machines with the SQL 2008 R2 client installed. The SQL 2008 R2 server runs in a VMWare virtual machine on our ESXi cluster. Additionally, we have manually created user logins. Most of the time, everything works great and users can log in just fine, but every now and then one of the lab machines will stop allowing users to log in. The error I get is a password mismatch error on the server. Oddly enough, I can move to an adjacent machine and log in with the same account just fine. I have "solved" the immediate issue a couple times by starting a remote desktop session on the offending machine with the server and then trying the login again. It's almost like this "wakes up" the SQL server--which is absurd. Also, I can close the SQL client and reopen it on the offending machine and the problem still persists. I can't really pin down logical event that causes it to start working again.

    SQL server may think there is a password mismatch, but on the client end the password is correct. We've had this issue with several machines intermittently. When it stops working, it throws the same error regardless of who logs in.

    This is very frustrating behavior that is difficult to debug (the error message is virtually useless). Has anyone seen this odd problem before?

    Thanks.









    • Edited by zcbethel Monday, February 27, 2012 9:21 PM
    Monday, February 27, 2012 4:44 PM

All replies

  • http://blogs.msdn.com/b/sqlsakthi/archive/2011/02/06/how-to-troubleshoot-connectivity-failure-error-with-sql-server.aspx There is no miracle, make sure that you typed correct password


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

    Tuesday, February 28, 2012 6:16 AM
  • Are you using SQL Server Authentication or Windows Authentication?

    If using Windows Authentication are the accounts domain accounts, or are you relying on identical local account names with identical passwords?


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, February 28, 2012 4:42 PM
  • Thanks for the link, but unfortunately it did not help me fix the problem. I am sure that the password is correct. The behavior is such that seemingly randomly, a windows client machine will stop connecting any accounts to the server (i.e. they will all fail with state 8). This is using SQL Server Authentication, not Windows Authentication. The SQL accounts have their own passwords. On the offending client machine, if I connect with remote desktop to the SQL server, it magically starts connecting again. Could this be some sort of DNS related thing? Does SQL server try to do reverse lookups or anything like that? How puzzling.

    Tuesday, February 28, 2012 6:51 PM
  • The next thing to check in the SQL Server error log on the SQL Server computer, and the Windows event log on the SQL Server computer. For security reasons, the error passed to the client is sometimes intentionaly vague. But a more complete error is written to the local logs.

    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Tuesday, February 28, 2012 10:46 PM
  • Hi zcbethel,

    As Rick mentioned, please check your SQL Server error log first, if the SQL Server only shows that “Error: 18456, Severity: 14, State: 8 Reason: Password did not match that for the login provided”, I will suspect that there is something wrong with the transportation of the internet.

    Please check if there is anything automatically executed in your SQL Server, such as the SQL Server Agent jobs, firewall configuration and so on.


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, February 29, 2012 7:47 AM
    Moderator
  • Indeed, the only error that is posted is Error: 18456, Severity: 14, State: 8. One for each bad login attempt. I agree that there must be some network related issue in between the process. It seems odd to me though because the server receives the request; somehow the data must be garbled or something like that. The firewall is off, so that is not the problem. The only server agent jobs that run are the backups, which happen at night.

    I did notice that the SQL Server Browser service and SQL Server Reporting Services service are not running. When I try and start them I get "The request failed or the service did not respond in a timely fashion...". Any idea if that could be part of the problem? I'm not exactly sure what those services do.

    When I open the event viewer, I get this error: 

    "The ReportServer service was unable to log on as .\Administrator with the currently configured password due to the following error:
    Logon failure: unknown user name or bad password."

    Very interesting. I'll look into this more.

    Edit: I opened the Reporting Services Configuration Manager and setup the system to use the network service account. Previously it was setup to use the Administrator account. I was able to start the service then. Does this have anything to do whatsoever with the authentication process?


    • Edited by zcbethel Wednesday, February 29, 2012 3:44 PM
    Wednesday, February 29, 2012 3:12 PM
  • Activating the previous service did not solve the issue, but I realized one pretty important thing. The servers on our network have IPV6 enabled, as do the clients. I wasn't aware of this, and actually IPV6 has not even been setup (i.e. there are no DNS entries, etc.). Could it be possible that the windows machine is trying to connect via IPV6 and something is failing? As precaution, I am going to turn that feature off--mostly because we don't need it.

    Edit: I knew it was a bit of a long shot, but that didn't fix it either. It's very odd. I logged in as my domain user (zbethel) and tried to login through SQL Server Management Studio and got the state 8 error. I then logged out and logged back in as Administrator and tried again, and it worked. I've had the same thing happen the other way around. I've also had users come to me and inform me of the problem, but then I walk over and they try again and it works. It seems like it will stop working for a time and then suddenly start again. That behavior suggests a network related issue to me. What things should I be checking?

    This is all the information I get from the server log:

    "Login failed for user 'aketchum'. Reason: Password did not match that for the login provided. [CLIENT: 10.121.14.10]"

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

    The fact that the ip address is V4 proves that V6 wasn't the issue. Unfortunately, that's all the information I get. Is there another internal log somewhere where I could get more detailed information on the authentication process?

    The server installation itself is pretty vanilla. However, we did import tables and user logins from a 2003 instance (using the import tool that SQL Server provides).


    • Edited by zcbethel Wednesday, February 29, 2012 5:11 PM
    Wednesday, February 29, 2012 4:40 PM
  • I realized that I am able to query using sqlcmd from a machine even though I can't login via SSMS, using the same credentials for both interfaces.
    Wednesday, February 29, 2012 8:14 PM
  • I found an interesting workaround. If the error occurs on a machine, I can open the SQL Server Profiler and log in with that. Somehow, this "wakes up" the SQL client and allows SSMS to log in. It sounds like it might be a client side issue?

    • Edited by zcbethel Thursday, March 01, 2012 12:25 AM
    Thursday, March 01, 2012 12:23 AM
  • Zcbethel,

    When you are connecting to server, you will see an option “remember password” under the password textbox. I would suggest you to try to choose that option and see will the problem happen again.



    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, March 01, 2012 2:10 AM
    Moderator
  • I am a colleague of Zcbethel and have been working together with him to resolve this issue.  The "remember password" feature did not work for authentication in this case.  Here is a summary of our issue followed by additional information:

    • The primary issue is that users often (but not always) receive the following error when trying to connect to a database using SQL server authentication:  "Connect to Server -- Cannot connect to <servername> -- Additional information:  Login failed for user <username> (Microsoft SQL Server, Error:  18456)".  Users are typing their usernames/passwords correctly.  The server contains a log entry "Error: 18456, Severity: 14, State: 8 Reason: Password did not match that for the login provided".
    • Intermittent Login failures happen for all users intermittently on most or all of our workstations.
    • When a Login failure happens on a particular workstation, it happens for all users on that workstation for some period of time (varies per workstation, but might be for a few minutes, or might be for a half hour or more).
    • A user is sometimes able to login on another workstation without problems.
    • When a user is having problems logging in, other users on other workstations might not be having any login issues.
    • One workaround that seems to consistently work is to have the user click on "Cancel", go to "Tools-->SQL Server Profiler", and then immediately close the Server Profiler Window (without trying to connect wihin the Server Profiler Window).  The user immediately reattempts to connect via "File-->Connect Object Explorer...".  So far, our experience is that the user is always able to connect at this point.

    Additional information:  We attempted to solve this problem this afternoon by uninstalling/reinstalling the SQL Server 2008 R2 "Management Tools (both "Basic" and "Complete") on multiple workstations.  On one workstation, we uninstalled/reinstalled all of the SQL Server 2008 R2 features that were on the workstation.  The problem is still with us on all of the workstations.

    This issue has been a serious detriment to our users completing their tasks.  Assistance in resolving this issue is greatly appreciated.
    Thursday, March 01, 2012 10:45 PM
  • Zcbethel,

    I want to confirm that do both domain account and SQL Server account get that password error?

    I still suspect it may be a protocol issue. If the value of the TCP Dynamic Ports item is not set, please set it yourself. For more information about how to configure a server to listen on a specific TCP port, visit the following Microsoft Developer Network (MSDN) Web site: http://msdn2.microsoft.com/en-us/library/ms177440.aspx


    Best Regards,
    Iric
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, March 06, 2012 7:28 AM
    Moderator
  • Hi Zcbethel,

    This is happening with one of our system using SQL Server 2008 R2. We are also searching for a solution but yet to find one. For us it is failing for one job but it is suceeding for others. It happens in a specific time. but during that time I am able to login using SQLCMD from that machine.

    Seen 2 difference:

    a. The failed job are not using .net driver instead using something Cedar Shared Components 5.3 SP1 (thats what shows in Application Name of the profiler)

    b. Failed logins are showing as Non-pooled (again got that from profiler)

    I am tracking AUDIT LOGIN Failed and AUDIT LOGIN events

    I am suspecting that connection Pooling has something to do with it. But not sure how to go now, I am stuck.

    Any hwlp would be highly appreciated.

    Regards,

    Wednesday, May 16, 2012 2:40 PM
  • Kindly refer this, may help you.

    http://go4answers.webhost4life.com/Example/logon-fail-error-18456-severity-14-182124.aspx

    Sunday, May 20, 2012 8:11 AM