Solved

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

Posted on 2016-09-23
3
117 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 12

Accepted Solution

by:
Nakul Vachhrajani earned 500 total points
ID: 41813113
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.
1
 

Author Closing Comment

by:GovxIT
ID: 41816192
THanks for your help.  This led us in the right direction to get the issue resolved.
0
 
LVL 12

Expert Comment

by:Nakul Vachhrajani
ID: 41818086
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?
1

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 …
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

861 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

29 Experts available now in Live!

Get 1:1 Help Now