[SQL Server Agent] Packages in SSISDB not visible, SQL Server Authentication + Proxy

Lam Hoi 1 Reputation point
2022-04-25T12:51:49.143+00:00

Hello, I have been struggling with setting up a SQL Server Agent Job for SSIS package.

Environment:

  • Server A: SQL Server 2012 (MSSQL Server Management Studio)
  • Server B: Java application
  • Tools: Visual Studio 2019 Community (for implementing SSIS packages)

In server A, by using a domain admin account (with Windows Authentication), I could deploy my SSIS packages to "Integration Services Catalogs -> SSISDB -> XXXX folder":
196192-%E8%9E%A2%E5%B9%95%E6%88%AA%E5%9C%96-2022-04-25-%E4%B8%8B%E5%8D%8884041.png
They could be executed successfully by "Right Click -> Execute".

Now I would like Server B's application to connect to Server A with "SQL Server Authentication" and trigger those packages. I learned that DB accounts with "SQL Server Authentication" cannot deploy and execute packages in SSISDB. A proxy account is needed. So the follows are what I have done:

In server A, by using a domain admin account

  1. Create "Proxy Credential"
    196165-%E8%9E%A2%E5%B9%95%E6%88%AA%E5%9C%96-2022-04-25-%E4%B8%8B%E5%8D%8884058.png
    with ID & PW exactly the same as the domain admin's
  2. Map the "Proxy Credential" to the account Server B is using(sa or tempuser)
    196117-%E8%9E%A2%E5%B9%95%E6%88%AA%E5%9C%96-2022-04-25-%E4%B8%8B%E5%8D%8884215.png
  3. Create a proxy for subsystem "SSIS Package Execution"
    196077-%E8%9E%A2%E5%B9%95%E6%88%AA%E5%9C%96-2022-04-25-%E4%B8%8B%E5%8D%8884823.png
    1. Create a new job under "SQL Server Agent"
      196201-%E8%9E%A2%E5%B9%95%E6%88%AA%E5%9C%96-2022-04-25-%E4%B8%8B%E5%8D%8884904.png

But then I encountered a problem: the deployed project/package(s) are not visible
196175-%E8%9E%A2%E5%B9%95%E6%88%AA%E5%9C%96-2022-04-25-%E4%B8%8B%E5%8D%8884945.png

Is there any permissions/authorities of I am missing?

Besides, I am still a beginner of SSMS. Is there any other ways to achieve my goal?

Thank you so much.

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

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. ZoeHui-MSFT 33,296 Reputation points
    2022-04-26T03:01:19.107+00:00

    Hi @Lam Hoi ,

    As the image shown, when you connect serverA in serverB, it is success and we could see the ssis packages in the SSISDB.

    When you configure the agent job, please make sure the server you choose is serverA.

    196421-image.png

    If I misunderstand your issue, please incorrect me.

    Regards，

    Zoe

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    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.