Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2016-09-23
3
Medium Priority
?
366 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

636 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