locked
"Login failed for user" error occurs when run Maintenance plan with SQL login account in SQL Server 2016 RRS feed

  • Question

  • Hello,

    Recently, I have upgrade from SQL Server 2008 to SQL Server 2016,

    After the upgrade, I encountered a bug which the symptom is exactly match the below link:

    https://support.microsoft.com/en-us/help/4486936/fix-login-failed-for-user-error-occurs-when-you-run-maintenance-plan-w

    From the link, we know that this bug is fixed in Cumulative Update 5 for SQL Server 2016 SP2

    However, I still encounter the same problem with Cumulative Update 13 for SQL Server 2016 SP2

    The bug I faced is occurred when I use [sa] account, if I replace it with windows account in backup job, then there is no more messages.



    Anyone is facing the same problem and have idea to solve it?

    Thanks.
    Wednesday, June 10, 2020 9:51 AM

All replies

  • We should avoid using old method of Maintenance Plan. Create an SQL Job for your Backup tasks and then it is easy to monitor, maintain and track.

    https://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

    Wednesday, June 10, 2020 10:33 AM
  • Hi,

    you can use the scripts from Ola Hallengren

    https://ola.hallengren.com/

    He did a good job for maintenance scripts.

    Take a look and you'll be happy.

    Kind regards,

    Andreas

    Wednesday, June 10, 2020 11:47 AM
  • Hi Matthew,

    As normal, it should be fixed after upgrading to the CU5 SQL Server 2016 or later.

    Could you please have a try to restart the SQL Server service or even server and check again?

    Please try to disable the option to "Enforce password policy" for the sa account and check whether this could be hepful to you.

    In addition, I could not reproduce this issue from my side. If you are sure about this issue,I  recommend you to submit your issue to the Microsoft feedback at this link https://feedback.azure.com/forums/908035-sql-server . This site will serve as a connecting point between you and Microsoft, and ultimately the large community for you and Microsoft to interact with. Your feedback enables Microsoft to offer the best software and deliver superior services, meanwhile you can learn more about and contribute to the exciting projects on Microsoft feedback.

    Best regards,

    Melissa

    -------------------------------------------

    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


    Thursday, June 11, 2020 2:31 AM