Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Can't Connect Remotely to Integration Services

By Mohammad Hoque,

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:

 

 


Total article views: 3500 | Views in the last 30 days: 51
 
Related Articles
FORUM

Sql Server Integration Services.

Sql server Integration services.

FORUM

Integration Service and Oracle 8 whit ODBC connection

Integration Service and Oracle 8 whit ODBC connection Work on Integration Service but does not work ...

FORUM

SQL server integration Services(SSIS)

SQL server integration Services(SSIS)

FORUM

Sql server integration services.

How do u achieve data extraction using Sql server Integration services

FORUM

Sql server integration services.

How do u achieve data extraction using Sql server Integration services

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones