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

Question

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

Lam Hoi 1

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

Create "Proxy Credential"

with ID & PW exactly the same as the domain admin's
Map the "Proxy Credential" to the account Server B is using(sa or tempuser)
Create a proxy for subsystem "SSIS Package Execution"
1. Create a new job under "SQL Server Agent"

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.

Answer 1

ZoeHui-MSFT 22,211

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.

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.

ZoeHui-MSFT 22,211 Reputation points

2022-04-29T06:42:53.61+00:00

Do you have further question on this , could we offer more support?
If this helps on your issue, you could mark it as answer so other user with similar problem could see this easier. :)
Lam Hoi 1 Reputation point

2022-05-01T01:39:36.753+00:00

Sorry for my late reply......I was too busy in these 4-5 days to try different solutions, but still it does not work out. And I found that I missed your message.

I would try my best to make my words clear.

Let's say I have two computers: PC300 & PC301.

PC300 is the one which owns the SQL server. It has SSIS project & package(s) deployed to its SSISDB. Of course, I have no problem in executing the packages after logging on it.

But this is not what I want because the java application is installed on PC301. I need my PC301's program to be able to execute those packages stored in PC300. Connecting to PC300 directly from PC301 by using "SQL Server Authentication" is not going to work based on my research. I hope my program in PC301 would be able to use a sa account("SQL Server Authentication") to pretend to be a "Windows Authentication" one, then to trigger Stored proc/SQL Server Agent job to run the packages.

Here, I decided to use SQL Server Agent Job + Proxy(with id & pw same as the domain admin in PC300). Then, I do the proxy setup in PC300. And map it to an account PC301 is able to use. (Correct me if this is not appropriate) Here I encountered the problem while I am setting up the SQL Server Agent job in PC300, I could not find the package(s) in "catalog". I do not know why.

Thank you.

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

1 answer

Activity