?
Solved

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

Posted on 2016-09-23
3
Medium Priority
?
299 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:Jeff Perry
[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 14

Accepted Solution

by:
Nakul Vachhrajani earned 2000 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:Jeff Perry
ID: 41816192
THanks for your help.  This led us in the right direction to get the issue resolved.
0
 
LVL 14

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

Get MySQL database support online, now!

At Percona’s web store you can order your MySQL database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card.

Question has a verified solution.

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

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

777 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