Recently tried to connect to Remote SQL Server Integration Service directly from SSMS and got 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
------------------------------
I've found very old blog post on how to fix this error (http://www.kodyaz.com/articles/ssis-integration-services-remote-connection-rpc-server-unavailable.aspx) and decided to make a refreshment.
Here is how it's go:
Step 1. Login to the SQL Server host machine.Step 2. Start "Windows Firewall" application:
Step 3. Choose "Inbound Rules" Tab:
Step 4. On the Right side click on "New Rule"
Step 5. On the first screen choose "Program":
Step 6. On Second screen you'd have to search for SSIS executable.
"C:\Program Files\Microsoft SQL Server\130\DTS\Binn\MsDtsSrvr.exe"
Step 7. Choose "Allow the Connection"
Step 8. Uncheck "Public" and if necessary, uncheck "Private" too.
Step 9. Name your new firewall rule and leave some description for System Administrator and click Finish button:
Step 10. That is it. Now you can see your SQL Server Integration Service in Management Studio:
