sqlorcl

Just another WordPress.com site

Integration Service is running on dynamic port can’t connect remotely

Recently one of our DBAs Installed a Named Instance of SQL server 2008 R2 x 64 enterprise edition to one of our test environments. Integration Services were also installed and incorporated to the installation. Due to the company’s security policy, we had to change the TCP/IP default port number in 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 Microsoft KB, to configure a method of connecting Integration Services Server remotely.

But the Developmental team members complained that they couldn’t connect to Integration Services from their local machine.

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 did the RDP to the server, I was able to connect Integration successfully. Through further investigation, I have found the following information related to SQL Server:

o Port 135 was opened in firewall for Integration Service.
o SQL Browser was running.
o Alias was created for SQL Server.
o Using netstat command I found that SSIS was not running under port 135.

Also, I have verified, through netstat command, that SSIS was running under dynamic port and each time I restarted the SSIS, the service was running under different ports within the range of 49152 to 65535. For testing purposes, I opened above range dynamic ports in firewall and was able to connect 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 the SSIS can connect 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,#
#= new static SSIS port.
6. Restart SSIS services.
Below, the sample screenshot shows how the registry will look once the procedures, above, are followed.
 

P1

Hopefully this article will aid the process of troubleshooting SSIS remote connection.

How to Find AppID
1. Type Dcomcnfg.exe to the command prompt and press enter ,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:

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: