Sql server agent job to execute a ssis package without a proxy account

Nishan Ratna 150 Reputation points
2023-05-11T00:17:07.4766667+00:00

I created a SSIS package that extracts data from a table in server 1 and load data into a text file in a folder in server 2(not in the same domain). My windows account has access to this shared folder in the other domain. Then I created a SQL server agent job. In the job step properties tab, type was selected as SQL Server integration service package. Package source was selected as file system. If I execute the package under SQL server agent service account, I get an error saying I need a proxy account. So I created a proxy account using my windows credentials. Now it runs fine. I tested this on development server. However, my manager does not want to implement this (proxy account method) on production server, as it uses my windows credentials. Is there another way to do this? If so, can you please help me. Thanks

SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,485 questions
0 comments No comments
{count} votes

Accepted answer
  1. ZoeHui-MSFT 34,576 Reputation points
    2023-05-11T03:21:14.5366667+00:00

    Hi @Nishan Ratna,

    If you want to run the package via agent job, you must use one account which have access to the shared folder.

    So you may give another account which has the permission as the proxy account or run the job with that account.

    You may also check here which talked about Security best practices for SSIS package execution via SQL Agent job.

    Regards,

    Zoe Hui


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

    1 person found this answer helpful.

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 42,381 Reputation points
    2023-05-11T05:39:43.33+00:00

    Is there another way to do this?

    Change your manager to a different mind, or grant the required permission to the SQL Server-Agent service account.

    1 person found this answer helpful.
    0 comments No comments