August 28, 2017 at 1:55 pm
No, remember the job sometimes worked initially and then stated to fail as often as not. The job history only has three days in it as of today so I believe we have now run 4 straight times without error which would be three times before the Saturday night / Sunday morning patch cycle so once after patching.
August 28, 2017 at 2:02 pm
Good to know. I thought it was working since the patching. Which, while true, has not had a long enough runtime to verify that it is "fixed".
Could it be that the account being used to execute the stored procedure/SSIS package got locked at some point (either on the SQL side or on the AD side)? I expect that the account getting locked prior to the job starting would result in the job not starting at all, so I am thinking it might be getting locked in the middle of the job run and auto-unlocking at some point after the job completes?
Where I work, our AD accounts auto-unlock after a set period of time being locked.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
August 29, 2017 at 2:14 pm
I do not know if it is wise or not but we run several different SQL Server installations using the same domain username for the SQL Service account and the SQL Server Agent services. We have not seen any issues on any other server. for now we are in wait and see mode..
September 8, 2017 at 2:23 pm
Update the developer modified the task to run sequentially instead of having the SSIS package launch the various modules in parallel and it has ran for the last couple days without error. The Window System Administrators set up the Windows Performance monitor and everything looks good in it. We are just going to run for a few days and see if the problem comes back or if we can figure out a test to duplicate the process.
May 11, 2022 at 7:32 pm
If you're running into this issue with SQL 2014 Maintenance Plans, even if the RunAs account and service accounts are fine (as confirmed in EXECUTE AS testing), then try the following:
Check the Local Connection authentication in the Maintenance Plan.
- if it's set to an account & it's appropriate to do so, switch to Windows NT Authentication, save out the plan
- if it's already on WinNT, switch to an account, save out the plan, go back in, switch to Windows NT Authentication, save out the plan again.
There was no logging anywhere that would catch this. I was just thinking where else authentication could be involved, since the failures were around that, but everything was set up properly everywhere else....
As Grandma would say: "It's always in the last place you look". :smirk:
May 23, 2022 at 8:34 am
We found out Cylance was blocking the termination of the process. Once we added the Agent & DTS executables to the whitelist, everything ran great.
Grandma's adage still applies!
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply