Skip to content

MSSQLTREK

My Journey with SQL Server….!

  • Sreekanth’s Profile – About Me

Tag: SSIS Server Maintenance Job

SSIS Server Maintenance Job – Execute as Login failed for the requested login ‘##MS_SSISServerCleanupJobLogin##’

Couple of weeks ago I was analyzing a server for space and noticed SSISDB database was abnormally huge (this Instance was running just a handful of packages). I noticed couple of internal schema tables in SSISDB were huge (with some hundreds of millions of rows), well that’s not right. There should be SSIS Server maintenance job which SQL server creates to purge older entries based on the retention settings right? My immediate action was to check the retention period set and what’s the status of the job.  As I suspected, the job was failing (looks like this has been failing since ages) with below error.

The job failed.  The Job was invoked by Schedule 9 (SSISDB Scheduler).  The last step to run was step 1 (SSIS Server Operation Records Maintenance).
Execute as Login failed for the requested login ‘##MS_SSISServerCleanupJobLogin##’

Next step is to investigate whats up with the user account which the job is complaining about and I noticed ‘##MS_SSISServerCleanupJobUser##’ is orphaned in SSIS database as shown below.

Compare with how it looks on an Instance where this Job is running successfully.

Well, with this the fix is pretty evident, Map the poor fella to the corresponding login (Syntax shown below).

 

Once this has been done, I kicked off the job and it ran successfully (It took almost 2 hours to finish – had to purge few millions of records) and the subsequent scheduled runs were quick (Just few seconds as expected)

Cheers!

Author sreekanth bandarlaPosted on August 15, 2018Categories Generic Errors, SSISTags ##MS_SSISServerCleanupJobLogin##, Execute as Login failed for the requested login, SSIS, SSIS Server Maintenance Job, SSISDB Scheduler1 Comment on SSIS Server Maintenance Job – Execute as Login failed for the requested login ‘##MS_SSISServerCleanupJobLogin##’

Search

Recent Posts

  • neo4j – Basic configuration.
  • How to Install Neo4J in RHEL/CENTOS?
  • How to change IP Addresses in an Always On Availability group.
  • SQL Server Read Scale Availability Groups (AKA Cluster less AGs)
  • Configure/Manage Error logs – SQL Server on Linux.

Top Posts & Pages

  • Upgrading Windows Failover Cluster 2012R2 to 2016
  • The server principal already exists - Msg 15025, Level 16, State 2, Line 1
  • Always On availability group(s) - Rolling upgrades
  • How to change IP Addresses in an Always On Availability group.
  • Obtaining Pipe Delimited Results from SQL Server using SSMS
  • How to Transfer(Copy) Maintenance Plans from One Server to other???....
  • SSMS - Run as a different user shortcut
  • CNOs/VCOs(Computer Objects) and few ways to protect them...!
  • Installing SQL Server from Command line(CMD)
  • Dedicated Admin Connection(DAC) from SSMS

Categories

Top 30 SQL blog 2018

I am A

2014cert
mcitp-dba
MIcrosoft_SQL_Badges_DBASuperhero
MIcrosoft_SQL_Badges_ServerFan2012

Archives

Creative Commons License
This work is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License.

Blog Visits

  • 1,313,307

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 171 other followers

Follow me on Twitter

My Tweets

RSS MSSQLTREK RSS

  • neo4j – Basic configuration.
  • How to Install Neo4J in RHEL/CENTOS?
  • How to change IP Addresses in an Always On Availability group.

Search

  • Sreekanth’s Profile – About Me
MSSQLTREK Blog at WordPress.com.
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy
  • Follow Following
    • MSSQLTREK
    • Join 171 other followers
    • Already have a WordPress.com account? Log in now.
    • MSSQLTREK
    • Customize
    • Follow Following
    • Sign up
    • Log in
    • Report this content
    • View site in Reader
    • Manage subscriptions
    • Collapse this bar
 

Loading Comments...