Agent jobs for SSIS pkgs are not running with package define login

Ashwan 521 Reputation points
2021-02-25T01:37:01.207+00:00

SSIS version 2016 SP2 runs in Server A
database version: 2016 runs in Server B

I deployed packages and configured connection manager Service user call "SRV_CRM_report"(AD user) login in under Server A SSIS Integration Service catalogs) . "SRV_CRM_report" AD user has permission on Server B database and checked

Now package has scheduled to run though the agent on Server A( service user call "Server-A_AG").

When I run the SSIS job from the agent(Server A), then its failed . Then I granted permission to Server-A_AG service user database on Server B.
That shows package connection manager user "SRV_CRM_report" and didnt use to connect to Server B.

Now the SSIS job is running from the agent (SERVER A) . I checked the process and I can see job is running using Server-A_AG service user NOT from "SRV_CRM_report"

Is that mean do I have to use proxy user to define as "SRV_CRM_report" ?

Wired the SSIS pkg the way it runs

Can you please advice how do I get it run using SRV_CRM_report"

thank you

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,450 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,891 Reputation points
    2021-02-25T08:46:08.347+00:00

    Hi @Ashwan ,

    We should create a SQL Server Agent proxy in SQL Server by using SQL Server Management Studio or Transact-SQL.

    A SQL Server Agent proxy account defines a security context in which a job step can run. Each proxy corresponds to a security credential. To set permissions for a particular job step, create a proxy that has the required permissions for a SQL Server Agent subsystem, and then assign that proxy to the job step.

    Best regards,
    Mona

    ----------

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  2. Ashwan 521 Reputation points
    2021-03-07T21:56:05.423+00:00

    hi Monalv
    Thank you for the update. I understand proxy account has security. However I wounder think why the option given to add connection manager details will overwrite access method when run through the SQL agent. Do you know what aspects package will run using connection manager please ? further more we need to hard cord the password as well. If decide to change the password then those are stop working unless change it with in SQL server proxy user level. wired way. do you think have any solution from MS

    thanks

    0 comments