Several issues can lead to this error such as virus attack, firewalls, DCOM tunneling being disabled, and remote server registry faults. But our interest here is to focus on DCOM functionality and Firewall issues.
First step is to make sure that two computers even communicate each other and this can be done by with ping command.
Also we should check to make sure that Remote Procedure call Locater is not stopped, if it is not started, we should start the Romote Procedure Call Locator in 'Control Panel > Services' on both the server and client machine.
When we try to connect to a remote SSIS service underneath we leverage the DCOM functionality on the Windows operating system.
Most applications tend to have fixed TCP and UDP ports but DCOM is different. DCOM dynamically assigns a TCP port and a UDP port to each executable process serving DCOM objects on a computer. Client side applications discover the port associated with a particular object by connecting to and using the services provided by DCOM's Service Control Manager (SCM). The SCM always operates at a fixed network port 135 for both TCP and UDP on every computer.
This DCOM's feature of dynamic port allocation provides programmers the flexibility of not hard coding applications for specific ports. The advantage is that different applications will not try to use the same port and end up with a clash. This same feature of DCOM doesn’t make it a good buddy of the firewall because it can choose any port between 1024 and 65535 .
The important thing to note is that when the SSIS service makes a call back to the SSMS client, it creates a completely new connection to the client and sends method calls over that separate channel. This is similar to the generic client server call with role reversal. This time the server (SSIS Service) is the client and the client (SQL Server Management Studio) is the server.
So if we shut down the firewall we will be able to overcome this error. Shutting down firewall is not at all an acceptable solution to any customer facing systems, and more over every system should be fully secured and it is not safe to turn off the firewall settings completely. Instead of this should add rule to execute MsDtsSrvr.exe executable program which is located in the installation folder of SQL Server Instance C\Program Files\Microsoft SQL Server\100\DTS\Binn.
In some cases we might have a hardware fire wall in place and we can’t leave such a large number of ports open. System administrator will like opening ports from 1024 and 65535 on the firewall.
Steps on how to configure a port range for DCOM
In order to allow DCOM to operate efficiently we need to open these ports in the firewall:
- Port 135 TCP and UDP
- The ports range: 1024-5000 UDP
Though network administrator and security administrators won’t be happy to do these changes and some might even refuse to open their firewall to this range. In that case, we can define a more specific ports range using these steps:
- Click on Start -> Run.
- Type DCOMCNFG for the DCOM console
- Go under Components Services ->Computer ->My Computer
- Right click on My Computer and
- Go to the Default protocols tab.
- Choose the properties of the
Connection Oriented TCP/IP.
- Add the range we want DCOM to be
It is recommended that a minimum of minimum of 100 ports are set, between ports 1024 to 10000. In case if we receive COM+ related events or errors because of this setting we can revert it later or increase the ports range accordingly.
Steps to Configure Windows Firewall for SSIS (SQL Server Integration Services)
To enable remote connection for MS SQL Server Integration Services (SSIS), we can configure the Windows Firewall application by following the below steps.
- Open the Windows Firewall from the
Control Panel menu.
- Click on the Exceptions tab.
- Click the "Add Program" button.
- And browse to the folder where the
SQL Server instance installation
folders. C:\Program Files\Microsoft SQL Server\100\DTS\Binn\MsDtsSrvr.exe is the default installation folder.
It is shown in the figure: Figure 1: Step 1: Adding Firewall Rule
Now Click Open then on the next screen click OK button to add the SSIS executable to the application exceptions list.
It is shown in the figure: Figure 2: Step 2: Adding Firewall Rule
Then the port # 135 should be added to the exceptions list as a TCP port. In the description name text box, give a name and then specify the port number 135 and define it as a TCP port.
It is shown in the figure: Figure 3: Step 3: Adding Firewall Rule
After these Microsoft Firewall configuration updates, we should be able to connect to the MS SQL Server Integration Services (SSIS) successfully.
I hope these steps and workaround will cover the answer for this quiz question.