Solved

After SQL 2016 Upgrade SA Account Keeps Getting Locked by Backup Jobs

Posted on 2016-09-23
3
92 Views
Last Modified: 2016-09-27
I have run into a problem where I have two servers with SQL 2014 Enterprise in an Availability Group.  I have recently upgraded them both to 2016.  I recently noticed that the backups were failing after the upgrade happened.  It turns out that every time you try to run the backup job the SA account gets locked and the job then fails.  I tried to run the cleanup job after unlocking the SA account and the Cleanup Job worked fine.  I am not sure what is causing the SA account to lockup but here is the error getting generated.

Executed as user: Domain\SQLCluster-Dev. Microsoft (R) SQL Server Execute Package Utility  Version 13.0.1601.5 for 64-bit  Copyright (C) 2016 Microsoft. All rights reserved.    Started:  1:21:23 PM  Progress: 2016-09-23 13:21:24.23     Source: {5BE264D6-635B-4B9B-846D-13B85D8A5B13}      Executing query "DECLARE @Guid UNIQUEIDENTIFIER      EXECUTE msdb..sp...".: 100% complete  End Progress  Error: 2016-09-23 13:21:29.88     Code: 0xC0024104     Source: {7D7CED07-B744-4EEA-9CDF-601795A9B95B}      Description: The Execute method on the task returned error code 0x80131904 (Login failed for user 'sa'.). The Execute method must succeed, and indicate the result using an "out" parameter.  End Error  Warning: 2016-09-23 13:21:29.88     Code: 0x80019002     Source: OnPostExecute      Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED.  The Execution method succeeded, but the number of errors raised (2) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.  End Warning  Error: 2016-09-23 13:21:29.89     Code: 0xC0024104     Source: Reporting Task for subplan-{089B7DEF-4CC0-49F8-8822-F6E069621C2D}      Description: The Execute method on the task returned error code 0x80131904 (Login failed for user 'sa'.). The Execute method must succeed, and indicate the result using an "out" parameter.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  1:21:23 PM  Finished: 1:21:29 PM  Elapsed:  6.141 seconds.  The package execution failed.  The step failed.


I saw some article that said this may be due to having two instances on the machine so I uninstalled the SQL 2014 server and am still getting the same error.  Please help as I need to resolve this prior to upgrading our production environment.
0
Comment
Question by:GovxIT
  • 2
3 Comments
 
LVL 11

Accepted Solution

by:
Nakul Vachhrajani earned 500 total points
Comment Utility
Option 01: The theory is that the SSIS packages for the maintenance operations are themselves locking out the "sa" login.

If your maintenance SSIS packages have been developed on other machines or with older passwords, the validation phase may be trying to connect to the instance thereby locking the user out.

Set the "Delay Validation" property to TRUE on the control flow containers in the SSIS package and you should be all set.

Option 02: The other theory is that some other process is using older credentials, locking out the user.

Did you check the SQL Server error log? If I am not mistaken (I don't have SSMS in front of me right now), failed logins are logged. You should therefore see failed login attempts for "sa" login.

The log entry should have the process Id of the process initiating the connection and the IP address of the associated machine.

You can then check out the machine and rectify the connection strings on the concerned applications.
0
 

Author Closing Comment

by:GovxIT
Comment Utility
THanks for your help.  This led us in the right direction to get the issue resolved.
0
 
LVL 11

Expert Comment

by:Nakul Vachhrajani
Comment Utility
You're welcome! If you do not mind my asking, can you please share what the issue was - was it the validation operations on various SSIS tasks or was it some other process trying to login with an old/expired password?
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now