Not able to see SSIS packages when SQL Instance and Integration Services are on two separate nodes.

Question

Not able to see SSIS packages when SQL Instance and Integration Services are on two separate nodes.

dg

SSC-Addicted

Points: 414
More actions
October 6, 2018 at 11:20 am

#379783

I have a three nodes in Failover Clustered Instance 2016 SQL Server. Also I have SSIS installed on all three nodes. The SSIS is not clustered.
svnode01.abc.local
svnode02.abc.local
svnode03.abc.local
SQL Server Integration Services can be accessed by the same names.
I have an instance:
SQLinstance01.abc.local\instance01
I can connect to the above instance no matter where the instance is located on either of the three nodes.
My MsDtsSrvr.ini.xml file looks like this:
<ServerName>SQLinstance01.abc.local\instance01</ServerName>
I used the clustered instance name, and the above is on all three MsDtsSrvr.ini.xml located on all three nodes.
If the instance is located on the same server where I am connected to Integrated Services as in
svnode01.abc.local
and
SQLinstance01.abc.local\instance01
I am able to connect and see packages, everything is fine. The problem comes when I connect to Integration Services on node svnode01.abc.local and the instance is located on another node svnode02.abc.local. In this situation after connecting, I can not see any packages in MSDB, I get the following error: "Login failed for user NT AUTHORITY\ANONYMOUSLOGON'. (MsDtsSrvr)

How can I make this work without getting the above error?
Thank you,
dg
PS. Sorry for the extreme detailed question.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

crow1969 Hall of Fame Points: 3001 More actions · Answer 1

I am going to guess that you have the SSIS service running as Local System, or some other such account. You can try to switch that for a service account registered on the domain.

Since the Database Engine is on a different server from the SSIS service, the SSIS service is (apparently) trying to log in from the machine you have connected to. Confirm this by looking at the errorlog of the instance of the Database Engine. It should have login failures recorded from the node that the SSIS service is running on.

dg SSC-Addicted Points: 414 More actions · Answer 2

I did check the error log and is exactly as you described but, the SSIS is running under the same domain account as the instance. Noticed that the Browser Service is running under the NT AUTHORITY\LOCALSERVICE. Can this be the issue and I should change it to the same domain account?

dg SSC-Addicted Points: 414 More actions · Answer 3

dan green-260895 - Tuesday, October 9, 2018 12:22 PM
I did check the error log and is exactly as you described but, the SSIS is running under the same domain account as the instance. Noticed that the Browser Service is running under the NT AUTHORITY\LOCALSERVICE. Can this be the issue and I should change it to the same domain account?

Changed to domain account for the Browser Service. Same error. I start to think that maybe there is no solution and you can access/view packages only from the same node that hosts the instance.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

Integration Services is NOT cluster aware. Do you have it setup as a resource of the cluster? There is an article describing the pros and cons of this setup here:
https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-ssis-in-a-cluster?view=sql-server-2016

dg SSC-Addicted Points: 414 More actions · Answer 5

Chris Harshman - Wednesday, October 10, 2018 8:55 AM
Integration Services is NOT cluster aware. Do you have it setup as a resource of the cluster? There is an article describing the pros and cons of this setup here:
https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-ssis-in-a-cluster?view=sql-server-2016

I knew about not being cluster aware and this paragraph explain everything:

"However, that computer cannot delegate the user's credentials to the virtual server on which SQL Server is running. This is known as a double-hop scenario."

Thank you.

dg