Post reply

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

  • 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

    ------------------------------

  • March 24, 2016 at 2:21 pm

    #1867622

    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.

  • March 28, 2016 at 4:47 am

    #1867926

    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

  • March 28, 2016 at 5:13 am

    #1867929

    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.

  • March 28, 2016 at 5:41 am

    #1867933

    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

  • March 28, 2016 at 6:30 am

    #1867936

    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.

  • March 29, 2016 at 6:40 am

    #1868198

    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

  • March 29, 2016 at 6:43 am

    #1868199

    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

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

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