Connecting to remote SQL Server Integration Service from SQL Server management Studio
Today I am trying to cover a common issue that we face while trying to connect to a remote SSIS service.
Sometimes, it happens that when you try to connect to SSIS using SSMS on the machine where SSIS is running and it connects just fine.
But when you use an SSMS that is on a client machine (in fact any remote machine) to connect to the same SQL Server Integration Services you may get following error.
ERORR STACK
==================================
Cannot connect to <Server_Name>.
===================================
Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
------------------------------
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server\&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()
===================================
The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (Microsoft.SqlServer.DTSRuntimeWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)
at Microsoft.SqlServer.Dts.SmoEnum.DTSEnum.GetData(EnumResult erParent)
at Microsoft.SqlServer.Management.Smo.Environment.GetData()
at Microsoft.SqlServer.Management.Smo.Environment.GetData(Request req, Object ci)
at Microsoft.SqlServer.Management.Smo.Enumerator.GetData(Object connectionInfo, Request request)
at Microsoft.SqlServer.Management.Smo.Enumerator.Process(Object connectionInfo, Request request)
===================================
The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (Microsoft.SqlServer.DTSRuntimeWrap)
------------------------------
Program Location:
at Microsoft.SqlServer.Dts.Runtime.Wrapper.ApplicationClass.GetServerInfo(String bstrMachineName, String& serverVersion)
at Microsoft.SqlServer.Dts.Runtime.Application.GetServerInfo(String server, String& serverVersion)
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/or UDP ports but DCOM is different. DCOM dynamically assigns (at run time) 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 cause 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. (which is not at all an acceptable solution in most Customer scenarios)
In some cases we might have a hardware fire wall in place and we can’t leave such a large number of ports open. No system admin will love opening ports from 1024 and 65535 on the firewall unless he reports to you.
John has a nice blog which speaks of the ways to configure a port range for DCOM:
https://blogs.msdn.com/john_daskalakis/archive/2009/02/05/9397926.aspx
An alternate way to restrict the ports on which SSIS on the server will try to create a connection is discussed in the following MSDN article; please refer to the section “Restricting the Range of TCP Ports” https://msdn.microsoft.com/en-us/library/ms809327.aspx
Microsoft also has a knowledge base article that explains the same and also goes beyond with another resolution of configuring static DCOM ports for specific applications: https://support.microsoft.com/kb/217351
Author : Angshuman(MSFT) , SQL Developer Engineer, Microsoft
Reviewed by : Debarchan (MSFT) , SQL Developer Engineer, Microsoft