locked
why my sa user always login failed? RRS feed

  • Question

  • we have installed windows server 2012 R2 OS, and installed sql server 2016 in this VM. why our sa user always login failed after few days latter. And if I uncheck Enforce password policies, the sa user will login failed next day. This is why? Does it related to my windows server 2012 R2 OS?


    • Edited by Oscar_Wu Monday, May 22, 2017 1:00 AM
    Monday, May 22, 2017 1:00 AM

All replies

  • Hello,

    Please Review SQL Server ErrorLog, there you will find details about why the Login attempt failed.

    See View the SQL Server Error Log (SQL Server Management Studio)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, May 22, 2017 5:53 AM
  • Hi qing__,

    As Olaf suggested, you’d need to review SQL Server error log to figure out what exactly you are seeing(the error state of login failed event). After you find the exact error state, please post it back here so we can proceed further.

    I’ve also found a related blog for your reference.

    If you have any other questions, please let me know.

    Regards,
    Lin

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 22, 2017 10:03 AM
  • Hi, 

    please refer to the following screenshot:

    here I re-describe my question clearly:

    we have encountered the  following two situation:

    1) un-check any options, and  next day, we login failed;

    2). enable Enforce password policies, the login failed after few days. 

    Please help me check it.

    • Edited by GoyourGod Tuesday, May 23, 2017 9:35 AM
    Tuesday, May 23, 2017 9:32 AM
  • What we need from the errorlog file is the errors that are generated when a failed login occurs. That includes the reason why the login is refused. This is the first step in troubleshooting the failed login. Based on the reason, we can then contemplate what next step is.

    Possibly something re-applies security policy for your sa login, but that is only wild speculation - again, the error from the failed login attempt will tell us more.


    Tibor Karaszi, SQL Server MVP (Web Blog)

    Tuesday, May 23, 2017 9:46 AM
  • check if the issue you are facing has been mentioned over here: https://support.microsoft.com/en-in/help/555332/-login-failed-for-user-error-message-when-you-log-on-to-sql-server 
    Tuesday, May 23, 2017 1:33 PM
  • Do you have policy-based management setting setup?? Something seems to be adjusting the settings if it's not you...

    https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/monitor-and-enforce-best-practices-by-using-policy-based-management

    https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/tutorial-administering-servers-by-using-policy-based-management

    the second link walks you through setting these things up, thus leading you to where you might look.

    • Edited by JJordheim Tuesday, May 23, 2017 1:49 PM
    • Proposed as answer by GoyourGod Tuesday, June 6, 2017 12:29 AM
    Tuesday, May 23, 2017 1:47 PM