I was working with one of my client queries and they were having issue while connecting to SSIS on the database server, SQL1, from the application server, APP1. They were getting the below error message:

Connecting to the Integration Services service on the computer "SQL" failed with the following 
error: "Class not registered".?

Looking at the error message, it indicates that something is not registered or perhaps this issue is related to some missing DLLs. This is not something related to security or connection blockage.

I first checked the versions of SSIS and SSMS on the application server, APP1, from where the user is trying to connect. In this case, we noticed there is a version mismatch. The prerequisite for SSIS to connect is that the SSIS and SSMS versions should match. I was thought this was the solution and decided to implement this with the client. I upgraded the SSMS version on the application server, tried to connect, and again, I received an error message. Luckily this time it was a different one.

The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (Microsoft.SqlServer.DTSRuntimeWrap)

In this environment, we have SQL Server 2016 running as a named instance on a static port. The application login is not a member of the local admin group on the database server, as per company standards.

I followed these troubleshooting steps:

• Verify the RPC service is running as per the error message. It was running.
• Verify security is setup correctly for SSIS in group admin and the DCOM permissions even though there is no permission related error
• Test port 135 connection from APP1 to SQL1. Try to connect to the telnet port 135 as SSIS uses this port number

After completing these, the application was still unable to connect to SSIS. At this point, I was pretty sure this error was not related to permissions, so what else?

It could be related to the network or something that is blocking the connection. As it is very common that a firewall is enabled on the database server in almost every company, this me me think that the firewall might be blocking the connection. To test I disabled the firewall during non-production hours.

Yes, the connection starts working. Now I need to ensure I enable the firewall and then dig further into the issue with the netstat command. I figured out the port that SSIS is using, but it is strange that the SSIS service in not using port 135. Instead it is running on a set of higher (dynamic) ports that change every time I restart the service. Now, I needed to make sure all that range of dynamic ports are open and all connections have been allowed from MsDtsSrvr.exe. to make this work.

Solution

I needed to open the range of ports from 49152-65535. These are the dynamic ports for named instances. I also need to allow all connections from MsDtsSrvr.exe. SSIS is not running on 135 for a named instance.

In the Windows Firewall, I created a rule in Inbound Rules on the database server, SQL1, in Windows Firewall. I could also create that rule in one of the GPOs and then run “gpupdate /force” on SQL1. Here is the configuration for the rule:

• Rule name: Test_RPC1
• Rule direction: Inbound Rule
• Action: Allow the connection.
• Protocol – TCP
• Local IP – (10.10.xx.xx)
• Remote IP – App Server IP address – APP1
• Local Port – ()
• Remote Port – 49152-65535

Once this was done, everything worked fine.