I recently had to reimage my machine. I've installed SSMS 16.5.3. I cannot connect to Integration Services on another machine that is also running SQL Server 2016 and Integration Services 13.0. So far I have: Verified Integration Services 13.0 are running on the server I'm trying to connect to. Verified host is running SQL Server 2016. Re-installed SSMS 16.5.3 and SSDT for VS2015 I realize going forward it's preferred to use the SSISDB in Integration Services Catalogs, however these are legacy apps and we have no plans to move them. I need to connect this way. I can connect to Integration Services from another VM. Just not on my local machine any longer. UPDATE - I disabled the local firewall and still get the same error. I used to connect to the server through Integration Services before the re-image. If I remote into another machine and connect, it works fine. What else can I try to connect to Integration Services? Could it be something with the version of Windows from the re-image? Am I forgetting to install something else?

Here is the resolution I received from my Microsoft ticket. Cause: The latest updates on Windows 10 is causing the RPC to NOT ignore the LocalService value. So it is looking locally for the Integrations services rather than contacting the remote machine. Resolution: Search the System Registry Computer\HKEY_CLASSES_ROOT\ hive [make sure you check under the 32bit system hive ( e.g. Computer\HKEY_CLASSES_ROOT\WOW6432Node)] as well, for this respective search key MsDtsServer130. Changes made to one registry location may update the values under the other in most cases. Usually MsDtsServer130 would be available under 2 locations e.g. (Computer\HKEY_CLASSES_ROOT\AppI & Computer\HKEY_CLASSES_ROOT\WOW6432Node\AppID ). Note: MsDtsServer120 for SSIS 2014, MsDtsServer130 for SSIS 2016, MsDtsServer140 for SSIS 2017, MsDtsServer150 for SSIS 2019. Spot the LocalService entry in the registry for this identified hive and rename the registry entry from LocalService to LocalService_A. No reboot is required. Relaunch SSMS and try connecting. This registry change will prevent SSMS to look for local installation of SSIS and thus initiate a RPC call to remote SSIS.

Here are my local installed programs. Is anything missing that I should have to connect to the remote SSIS 13.0?

SSMS 16.5.3 unable to connect to Integration Services 13.0 on another server

Johnny SQL 96

I recently had to reimage my machine. I've installed SSMS 16.5.3. I cannot connect to Integration Services on another machine that is also running SQL Server 2016 and Integration Services 13.0.

So far I have:

Verified Integration Services 13.0 are running on the server I'm trying to connect to.
Verified host is running SQL Server 2016.
Re-installed SSMS 16.5.3 and SSDT for VS2015
I realize going forward it's preferred to use the SSISDB in Integration Services Catalogs, however these are legacy apps and we have no plans to move them. I need to connect this way.
I can connect to Integration Services from another VM. Just not on my local machine any longer.
UPDATE - I disabled the local firewall and still get the same error.

I used to connect to the server through Integration Services before the re-image. If I remote into another machine and connect, it works fine. What else can I try to connect to Integration Services? Could it be something with the version of Windows from the re-image? Am I forgetting to install something else?

Yitzhak Khabinsky 25,206 Reputation points

2020-10-08T14:11:22.083+00:00

Hi @Johnny SQL ,

Please update your post with the exact error message.
Johnny SQL 96 Reputation points

2020-10-12T13:40:11.173+00:00

@Yitzhak Khabinsky I have updated my post with the error screenshot.
Yitzhak Khabinsky 25,206 Reputation points

2020-10-12T15:57:14.02+00:00
Please share two more things:

SSMS, Help => About (screen shots)

SQL Server version on the server with SSIS: SELECT @@VERSION;

Microsoft is really pushing for SSISDB Catalog: 36926791-ssis-13-0-4474-does-not-work-in-ssms-after-v17-2
Johnny SQL 96 Reputation points

2020-10-12T16:04:47.323+00:00

@Yitzhak Khabinsky I have updated the images. We are aware of the SSISDB push, however at this time this is where our older packages are stored. Newer ones are stored in SSISDB.
Yitzhak Khabinsky 25,206 Reputation points

2020-10-12T16:11:45.5+00:00

I am really running out of options what to suggest. It is clearly a bug.
The only remaining option is to install SQL Server 2016, SP2 CU15 on the SSIS server.
If it won't work, I would suggest to open a support case with Microsoft.
Johnny SQL 96 Reputation points

2020-10-12T19:03:10.373+00:00

@Yitzhak Khabinsky I really do appreciate the help and time you took to offer suggestions! Thank you. If I come across the fix, I'll respond back.
Yitzhak Khabinsky 25,206 Reputation points

2020-10-12T19:35:44.417+00:00

👍
Johnny SQL 96 Reputation points

2020-10-19T19:15:14.153+00:00

@Yitzhak Khabinsky I was able to work with Microsoft and they helped fix the issue. Here is what they had me do.

Search the System Registry Computer\HKEY_CLASSES_ROOT\ hive [make sure you check under the 32bit system hive ( e.g. Computer\HKEY_CLASSES_ROOT\WOW6432Node)] as well, for this respective search key MsDtsServer130. Changes made to one registry location may update the values under the other in most cases. Usually MsDtsServer130 would be available under 2 locations e.g. (Computer\HKEY_CLASSES_ROOT\AppI & Computer\HKEY_CLASSES_ROOT\WOW6432Node\AppID ).
MsDtsServer120 for SSIS 2014, MsDtsServer130 for SSIS 2016, MsDtsServer140 for SSIS 2017, MsDtsServer150 for SSIS 2019.

Spot the LocalService entry in the registry and rename the registry entry from LocalService to LocalService_A.
No reboot is required. Relaunch SSMS and try connecting. This registry change will prevent SSMS to look for local installation of SSIS and thus initiate a RPC call to remote SSIS.
Yitzhak Khabinsky 25,206 Reputation points

2020-10-19T19:35:16.653+00:00

Hi @Johnny SQL ,

Glad to hear that you were able to resolve the issue. 😊
I would suggest to add a new answer at the bottom of the thread and copy there everything what you wrote above as a comment. And please mark it as an 'Accepted Answer' This way it will be helpful to other folks if they would bump into the same issue.

P.S. Please connect with me on LinkedIn.

Accepted answer

Johnny SQL 96 Reputation points

2020-10-20T10:11:06.857+00:00

Here is the resolution I received from my Microsoft ticket.

Cause:

The latest updates on Windows 10 is causing the RPC to NOT ignore the LocalService value. So it is looking locally for the Integrations services rather than contacting the remote machine.

Resolution:

Search the System Registry Computer\HKEY_CLASSES_ROOT\ hive [make sure you check under the 32bit system hive ( e.g. Computer\HKEY_CLASSES_ROOT\WOW6432Node)] as well, for this respective search key MsDtsServer130. Changes made to one registry location may update the values under the other in most cases. Usually MsDtsServer130 would be available under 2 locations e.g. (Computer\HKEY_CLASSES_ROOT\AppI & Computer\HKEY_CLASSES_ROOT\WOW6432Node\AppID ).
Note: MsDtsServer120 for SSIS 2014, MsDtsServer130 for SSIS 2016, MsDtsServer140 for SSIS 2017, MsDtsServer150 for SSIS 2019.

Spot the LocalService entry in the registry for this identified hive and rename the registry entry from LocalService to LocalService_A.
No reboot is required. Relaunch SSMS and try connecting. This registry change will prevent SSMS to look for local installation of SSIS and thus initiate a RPC call to remote SSIS.
Please sign in to rate this answer.
Russell, Dav (NYC-INI) 1 Reputation point

2021-05-12T13:56:59.467+00:00

I do not find MsDtsServer130 when I search my regedit. I am trying to connect from my laptop to SSIS on a VM.

Johnson Thomas 1 Reputation point

2022-08-05T15:57:04.64+00:00

This worked for me. Thanks a lot.
Sign in to comment

5 additional answers

Johnny SQL 96 Reputation points

2020-10-09T18:25:56.49+00:00

Here are my local installed programs. Is anything missing that I should have to connect to the remote SSIS 13.0?
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Share via

SSMS 16.5.3 unable to connect to Integration Services 13.0 on another server

5 additional answers