Once installed Microsoft SQL Server SSIS (Integration Services) on a Windows Server node, when you try connect to it remotely (SQL Server Integration Service) directly from SSMS you could see the following error:
TITLE: Connect to Server ------------------------------ Cannot connect to 10.1.32.66. ------------------------------ ADDITIONAL INFORMATION: Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&LinkId=20476 ------------------------------ The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (Microsoft.SqlServer.DTSRuntimeWrap) ------------------------------ The RPC server is unavailable. (Exception from HRESULT: 0x800706BA) (Microsoft.SqlServer.DTSRuntimeWrap) ------------------------------ BUTTONS: OK
To fix this issue we will have to proceed as follows:
- Login to the SQL Server host machine.
- Start "Windows Firewall" application:
- Choose "Inbound Rules" Tab:
- On the right side click on "New Rule":
On the first screen choose "Program":
On Second screen you'd have to search for SSIS executable, usually located at %ProgramFiles%\Microsoft SQL Server\130\DTS\Binn\MsDtsSrvr.exe (in the following case is related to SQL Server 2016):
Choose "Allow the Connection":
Uncheck "Public" and if necessary, uncheck "Private" too:
Name your new firewall rule and leave some free description for System Administrator and click Finish button:
That is it. Now you can reach and see your SQL Server Integration Service remotely in SQL Server Management Studio: