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

Question

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:

1. Verified Integration Services 13.0 are running on the server I'm trying to connect to.
2. Verified host is running SQL Server 2016.
3. Re-installed SSMS 16.5.3 and SSDT for VS2015
4. 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.
5. I can connect to Integration Services from another VM. Just not on my local machine any longer.
6. 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?

Answer 1

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.

Answer 2

See Connect to a remote SSIS server, there are several limitations.

Answer 3

Hi @Johnny SQL ,

May I know if you Install Integration Services (SSIS) in the remote server？

Best Regards,
Mona

----------

If the answer is helpful, please click "Accept Answer" and upvote it.
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.

Answer 4

Hi @Johnny SQL ,

Please try to follow steps outlined here: connecting-to-integration-services-access-is-denied-in-sql-server-2016-or-2017

Additionally, launch your SSMS 'as Administrator'.

Answer 5

Here is the detailed error message if it may help. It doesn't make sense to me as my computer is using SSMS 16.5.3 and the server is running SQL Server 2016 and SSIS 13.0.

===================================

Cannot connect to XXXXXXXXX.

===================================

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

---

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476

---

Program Location:

at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

Connecting to the Integration Services service on the computer "XXXXXXXXXX" failed with the following error: "The specified service does not exist as an installed service.".

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.

---

For help, click: http://go.microsoft.com/fwlink/?LinkId=506689

---

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)
at Microsoft.SqlServer.Dts.SmoEnum.DTSEnum.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData()
at Microsoft.SqlServer.Management.Sdk.Sfc.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Sdk.Sfc.Enumerator.Process(Object connectionInfo, Request request)

===================================

Connecting to the Integration Services service on the computer "XXXXXXXXXXXXXXX" failed with the following error: "The specified service does not exist as an installed service.".

This error can occur when you try to connect to a SQL Server 2005 Integration Services service from the current version of the SQL Server tools. Instead, add folders to the service configuration file to let the local Integration Services service manage packages on the SQL Server 2005 instance.

---

Program Location:

at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetServerInfo(String bstrMachineName, String& serverVersion)
at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)