question

Ashwan1234 avatar image
0 Votes"
Ashwan1234 asked ·

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

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
10 |1000 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

2 Answers

Monalv-msft avatar image
0 Votes"
Monalv-msft answered ·

Hi @Ashwan1234 ,

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.



·
10 |1000 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Ashwan1234 avatar image
0 Votes"
Ashwan1234 answered ·

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

·
10 |1000 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Visible to all users

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.