Solved

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

Posted on 2016-09-23
3
192 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 13

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 13

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

Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Suggested Solutions

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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

730 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