Recently one of our DBAs installed a named instance of SQL Server 2008 R2 x64 Enterprise Edition in one of our test environments with Integration Services. Due to the company's security policy, we had to change the TCP/IP default port number in the SQL Server Network Configuration from dynamic to static, with a specific port number.

The developmental team members were not part of the local Server Administration group so we followed several steps, described in detail by this Microsoft KB, to configure a method of connecting to Integration Services Server remotely. However the Developmental team members complained that they still couldn't connect to Integration Services from their local machines.

Investigation Technical Details

During the investigation process I tried to connect to Integration Services with a test user profile, but I wasn't able to connect. However, when I connected with RDP to the server, I was able to connect successfully. Through further investigation, I have found the following information related to SQL Server:

1. Port 135 was opened in the firewall for Integration Services.
2. The SQL Browser was running.
3. An alias was created for SQL Server.
4. Using the netstat command I found that SSIS was not running under port 135.

Also, I have verified, through the netstat command, that SSIS was running under a dynamic port and each time I restarted SSIS, the service was running under different ports within the range of 49152 to 65535. For testing purposes, I opened this range of dynamic ports in the firewall and was able to connect to Integration Services remotely with a test user. However, company policy prohibits opening a range of ports in firewall.

Solution

After thorough investigation and research online, we took several steps to fix this issue, so we can connect to SSIS remotely. In summary:

1. Open new static port for SSIS in Windows firewall
2. Edit registry entry. HKEY_CLASSES_ROOT\AppID\{xxxxx-xxxx-xxxx-xxxxxx} (replace with the appropriate value corresponding to your server. The end of the post summarizes how to find the AppID).
3. Create new REG_MULTI_SZ
4. Name its Endpoints
5. Value = ncacn_ip_tcp,0,# where # is the new static SSIS port.
6. Restart SSIS services.

Below, the sample screenshot shows how the registry will look once the procedures, above, are followed.

Hopefully this article will aid the process of troubleshooting SSIS remote connection. Visit my blog for more topics

How to Find AppID

1. Type Dcomcnfg.exe to the command prompt and it will open the Component Services in a new window.
2. Expand Component Services- Computer and My Computer
3. Expand DCOM Config
4. Find MsDtsServer100
5. Right click MsDtsServer100 and select properties.
6. A new Property Window will open and you will see the AppID next to Application ID under General Tab

Here is the screenshot in our environment: