none
Maintenance plan - Login failed for user 'sa' on master database RRS feed

  • Question

  • I have 5 new SQL Server 2016 implementations at 5 different clients that exhibit the behavior below:

    - Launch SSMS and connect to the SQL instance as 'sa' (or alternate sysadmin SQL login)

    - Create a maintenance plan that can be as simple as performing a full backup of all databases

    - Execute the maintenance plan

    - Backup completes successfully

    - SQL Server logs record  the following error:

    Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]
    Error: 18456, Severity: 14, State: 8.

    Followed by the logging of successful database backups.

    - SQL Profiler trace indicates it is failing on the 'master' database.

    - Creating an alternate SQL login with sysadmin role and creating maintenance plans using that new SQL login has the same results

    This became an issue with one client who has 'enforce password policy' enabled on the 'sa' (or any other sysadmin) login with a domain password policy that locks out for 15 minutes after 3 failed logins.

    What happens at that client is the scheduled maintenance plan starts, fails several times on the 'master' database then the backups fail because the 'sa' (or alternative sysadmin login) is now locked out.  So far the workaround is to disable 'enforce password policy' on the 'sa' (or alternate sysadmin) login.

    Anyone have any ideas on how to resolve this?

    Wednesday, January 25, 2017 7:20 PM

All replies

  • Hi, it may be worth taking a look at the 'Manage Connections' option in the maintenance plan for potential issues or to try add/reset the connection option.
    Wednesday, January 25, 2017 8:01 PM
  • Would assigning the SQL Agent Service startup account 'NT Service\SQLSERVERAGENT' (or whatever yours is) as 'owner' of the SQL Agent Job/maintenance plan, be an option in your clients environment?

    The default 'virtual' account is not subject to domain password expiration policy and has necessary privilege to run Jobs / maintenance plans, without necessarily being member of Sysadmin fixed server role.

    [Please mark as answer if this post helps you]

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Thursday, January 26, 2017 2:21 AM
    Thursday, January 26, 2017 2:19 AM
  • HI,

    only for clarify

    1.is The sql authentication enable?

    2. can you change the sa pasword

    3. The sql server agent service account must be sysadmin.

    4. Create a new maintenance plan and check  the local connection

    please send us feedback with logs

    Regards,


    If the answer was helpful, please mark it as useful. Si la respuesta le ayudó, por favor márquela como útil.

    Tuesday, January 31, 2017 3:33 PM
  • Hi

    Are  you store SA password in anywhere in SSIS job ?

    Reset Password for SA account.

    Use profiler  /Extended  event to which session failed for SA account.


    Please Mark it as Answered if it answered your question OR mark it as Helpful if it help you to solve your problem.

    Tuesday, January 31, 2017 5:21 PM
  • Hi,

    I have the same situation
    Is the problem solved?

    Regards,
    RT

    Wednesday, March 1, 2017 10:01 PM
  • I had the same problem and opened a ticket with Microsoft. The support engineer was able to reproduce the problem on SQL 2016 RTM and my version 13.0.4411.0 (SP 1 CU 1). He was pretty certain it was a SQL 2016 bug.

    How to reproduce: Use any SQL account and assign it system admin privileges on the SQL Server instance. Create a maintenance plan. (Mine does a DBCC CheckDB, a Reorganize Index, and an Update Statistics.) Run the maintenance plan. It will succeed, but after it succeeds, it will log a number of failed logins. We did not take the time to figure out where (against what database) the failed logins were occurring. I have not heard back from Microsoft yet on (1) whether it actually has been classed as a bug, and (2) when it will be fixed.

    Workarounds: (1) Use a Windows account to create maintenance plans. (2) Others???

    Thursday, March 9, 2017 9:48 PM
  • Friday, March 10, 2017 8:33 AM
  • Nicole, did you ever hear anything from Microsoft? I am experiencing the same issue and would like to get it resolved.
    Saturday, March 18, 2017 4:58 PM
  • Verify that you are using windows and SQL Authentication, right click on your SQL Server in management studio go to properties and select security.
    • Proposed as answer by world_walker Thursday, September 28, 2017 7:19 AM
    Saturday, March 18, 2017 6:01 PM
  • Microsoft Support indicated to me that they do not know the cause of this issue and although they plan to correct it, they don't have an ETA. Other than throwing failed logins and potentially locking an account for which password policy is enforced, they know of no other problems caused by this issue. (They recommend using Windows accounts for creating maintenance plans, because Windows accounts are more secure.) So continuing to run maintenance plans under a SQL account is OK provided the password policy does not need to be enforced.

    Sorry to not have better news!

    Friday, March 24, 2017 9:54 PM
  • Am getting the same issue. It's quite annoying to see the 'Login failed' error each time the job is run. 
    Friday, July 14, 2017 6:26 AM
  • 6-Aug-2017

    Just configured a new SQL Server 2016 running on Windows Server 2012 -- same issue:

    Created maintenance plans via the "Wizard".

    When ANY plan was executed, the job would fail with the following message:

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

    Message

    Login failed for user 'sa'. Reason: Password did not match that for the login provided. [CLIENT: <local machine>]

    The SQL Server was configured to use mixed Windows and SQL authentication.

    Password was reset (several times).

    SQL server and windows server restarted (several times).

    Nothing worked.

    Came across this post and (out of desperation) unchecked the "Enforce password policy" option for "sa" as follows:

    In SQL Server Management Studio: Click on Security -> Logins -> Right-click on user sa -> Properties... UNCHECK the Enforce password policy checkbox Click OK

    Exit and restart SSMS.

    Select a maintenance plan and Execute it. -- SUCCESS !!

    Login Properties - sa

    Hope that helps someone -- I spent nearly 7 hours on this !

    I hope MS will issue a fix sometime soon !

    Sunday, August 6, 2017 11:03 PM
  • Unchecking the "enforce password policy" for sa did not work for me.

    It still generated the "Login failed for user 'sa'" when I ran one of the jobs in a maintenance plan that backs up the transaction log of one of our databases.

    I guess I could try the windows-auth work-around, or just ignore the warnings for now. Kind of un-nerving, though. At first, I thought "uh-oh, shenanigans are being attempted".

    Has anyone seen if this might be fixed by CU4? I haven't had time to apply any of the CUs for sp1 yet, and am hoping that will fix it.

    -Thanks

    Thursday, August 10, 2017 6:55 PM
  • Possibly try, 

    in the maintenance plan go to Manage Connections -> Local server connection -> select "Use Windows NT Integrated security"


    Thursday, August 10, 2017 9:40 PM
  • I think there are two similar issues going on in this post. One being that the maintenance plan is in fact being executed. The other being that the plan is not being executed. Apparently the error is the same. In my case the maintenance plan was not being executed and unchecking the enforce password policy on SA resolved my issue.
    Friday, September 8, 2017 10:54 PM
  • Iam not sure if you move out from SSMS 2016(has some bugs) to SSMS 2017-see if this works until unless if some apps/configuration/any other way  uses the sa accounts with incorrect password update still be an issues.


    Regards, S_NO "_"

    Wednesday, September 13, 2017 4:44 PM
  • I too faced the similar issue. Then I Tried using windows account to run the maintenance plan. You can change it in Manage connections button. Now I am not getting that error in the log. Hope it works for you too.
    Thursday, December 28, 2017 5:02 AM
  • WORKs for ME !!!!!!!

    Thank you very much !!!!

    Edu

    Wednesday, April 4, 2018 9:31 AM
  • Did Microsoft ever release a KB for this issue?
    Tuesday, November 6, 2018 5:09 PM