SSMS connecting to SSIS with message Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

Question

Post reply

SSMS connecting to SSIS with message Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

richieStick

Old Hand

Points: 361
More actions
March 23, 2016 at 7:37 am

#317215

Hi
Full message below but basically I have tried all the DCOM things in this article and still no joy
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/8833d78b-a4c9-4743-ad0b-a3d17bdd3598/connecting-to-ssis-through-ssms-without-built-in-administrator?forum=sqlsetupandupgrade
Using Windows Server 2012 & SQL Server 2012 Enterprise
I'm a Domain Admin and SQL sysadmin so not sure what the issue is.
Any help greatly appreciated.
Rich
TITLE: Connect to Server
------------------------------
Cannot connect to server-x
------------------------------
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
------------------------------

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jon.Morisi SSChampion Points: 12846 More actions · Answer 1

What are you trying to do exactly?

Here's some related links:L

Windows Server: Troubleshooting "RPC Server Unavailable" Errors

Connecting to remote SQL Server Integration Service from SQL Server management Studio[/url]

RPC Service not running and port ranges have gotten me before.

richieStick Old Hand Points: 361 More actions · Answer 2

Hi

We have a secure network with vlans and multiple firewalls as well as the windows firewall on the servers. All I'm trying to do is connect to the integration server using the management studio from my PC - I can connect if I'm on the server using the same credentials.

I suspect that the firewalls are the issue and DCOM being the problem.

I'm investigating this and will come back with the results.

Rich

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 3

Another thought. Have you verified that the server accepts remote connections?

That's my suspicion if the only time it works is when you are on the server itself. On your desktop, log into SSMS (not SSIS) and try to connect to the server. See if it gives you a "server may not be set up for remote connections" kind of error or if you can log in.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

richieStick Old Hand Points: 361 More actions · Answer 4

Hi Brandie

Yes, I can connect to the server if I want to connect to the database server using SSMS with no issues using the same admin credentials.

Thanks anyway

Rich

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 5

Last question then. Are you using Bulk Insert tasks that are failing or is it the whole package with other tasks that are failing on connection?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

richieStick Old Hand Points: 361 More actions · Answer 6

Hi Brandie

When you are using MS SQL Server Management Studio, one of the options is to connect to the Integration Server. It is this that I was having problems with. When I chose the Database Server it was fine. Nothing to do with any Packages or Visual Studio.

The problem turned out to be firewalls and DCOM (we have strict security).

Thanks anyway.

Rich

richieStick Old Hand Points: 361 More actions · Answer 7

Jon.Morisi (3/24/2016)
What are you trying to do exactly?
Here's some related links:L
Windows Server: Troubleshooting "RPC Server Unavailable" Errors
Connecting to remote SQL Server Integration Service from SQL Server management Studio[/url]
RPC Service not running and port ranges have gotten me before.

It was the firewalls and DCOM. Security is such that we can't do much without a rule in the firewall. Trouble is that DCOM isn't firewall friendly and SSMS uses DCOM to talk to the Integration Server. Adding port 135 and the Integration Service Program wan't enough. Had to allow all ports to be able to connect from specific PCs.

Rich