locked
Creating a Maintenance Plan / SSIS Package Security / SSIS Service Account RRS feed

  • Question

  • We have a few Maintenance plans which were created by a User with sysadmin privileges.

    That User/Login id had been deleted.  Even though the Agent Jubs (Subplan_1's) that are created with these jobs are running under NT Service/SQLSERVERAGENT, jobs are failing because the Original User (owner of the Maint Plans) is not there anymore.  Getting typical DTSER_FAILURE from the package.

    Found out that the owner's domain id credentials are sort of baked into the SSIS packages during creation.

    So, we will recreate the Maintenance Plans again from scratch under a new SSIS Service Account.  Can somebody tell us what kind of domain and local machine privileges should a new Domain account should have for regular backup operation that has access to the network and backup drives.

    Cornelia


    Tuesday, June 28, 2016 8:28 PM

Answers

  • Hi Cornelia Gibbs,

    As far as I know, maintenance plan basically creates a SSIS package and create a corresponding SQL Agent job to run it. So there are two accounts involved in a maintenance plan: an account to run the package, which in your case is SQL Server Agent account and an account to connect to package source. It appears the job couldn't connect to package source after the account was deleted.

    For backup jobs, SQL Server Agent account is only used to run the package, Backup is actually performed under SQL Server Service account, so your SQL Server Service account should have one of these three default roles: sysadmin server role, db_owner database role or db_backupoperator database role. Also, SQL Server Service account should have full control permission over the backup device(or backup folder).

    For more information, please refer to BOL.

    If you have any other questions, please let me know.

    Regards,
    Lin

    Wednesday, June 29, 2016 10:42 AM