none
How to troubleshoot "Login failed for user 'xxxxx'. Reason: Password did not match that for the login provided." for which client IP is cluster heartbeat IP address?

    Pertanyaan

  • The SQL Server Database 2014 of my project has an recurring authentication error: "Login failed for user 'xxxxx'. Reason: Password did not match that for the login provided. [CLIENT: 10.10.10.139]".  The error occur from time to time, frequency ranges from several times to several hours.

    The SQL Server Database setup comprises of two server nodes in production environment and one server node in DR environment.  The server nodes in production environment are configured to formed a Windows Failover cluster.  Always On has been configured in three server nodes, two databases have been created and synchronized using Always On.  We have also setup a maintenance plan, with two tasks, one task is to update statistics daily, another task is to check integrity weekly.

    The user xxxxx is a local user created in the above three server nodes separately.  Our Web application connect to the SQL server Always On virtual IP, and authenticate with user xxxxx.  Our application can connect to the two databases susccessfully, hence, we conclude it is not our application that cause the login failed error in SQL Server.  We tried to enable the audit but no 

    Our question is: what is the possible configuration would cause the "login failed" errors of user 'xxxxx'?  We noted the CLIENT IP 10.10.10.139 is the heartbeat IP address configured in the Windows cluster.  Besides, there is no audit failure on the security log.  Are there suggestions how to troubleshoot the authentication error?

    Thanks,
    Harry.


    Harry

    15 Juni 2018 4:18

Semua Balasan

  • Our question is: what is the possible configuration would cause the "login failed" errors of user 'xxxxx'?  

    Hello Harry,

    The error message already tells you clearly the cause: A wrong password is used, enter the correct one.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    15 Juni 2018 4:23
  • Hi,

    do you know the password from the local sql user?

    If yes, try to set the password a second time.

    If no, try to script the sql user with sp_help_revlogin.

    how to use revlogin

    You can check the user / password settings with an udl file from the application server:

    check connection with udl file

    Good luck,

    best regards,

    Andreas

    15 Juni 2018 6:05
  • Check any linked server where username and password is stored ?

    https://social.technet.microsoft.com/wiki/contents/articles/37872.sql-server-installation-on-centos-linux.aspx

    15 Juni 2018 7:23
  • Why the error occurs is quite simple: the incorrect password was supplied. Something is running on 10.10.10.139 trying to connect to SQL Server with that user and the wrong password. How to find it?
    You could set up a trace and capture the Audit:Login Failed Event and make sure that you include the column ClientProcessID. Then you can try to find that process on 10.10.10.139. Obviously, if the ClientProcessID is different every time, that will be futile.

    Another option is to run Process Monitor (from www.sysinternals.com) on 10.10.19.139). Exactly what you would filter for, I cannot say off-hand; you would have to experiment.

    15 Juni 2018 8:12
  • Hello Olaf,

    Thank you for the reply.

    The problem is that, I checked my custom program and verified the password is correct.  So, it is not my custom program that cause the logon error.  Then, I cannot find other places which I have specified to authenticate as user 'xxxx'.  The client IP address (which is heartbeat IP) does not provide a good cue where the authentication is initiated.

    Given I cannot find where the 'xxxx' user is specified, I cannot fix the problem now.

    If you have other suggestions, please let me know.  Thanks.


    Harry

    15 Juni 2018 9:08