none
Can't connect to SSIS error message RRS feed

  • Question

  • Hello we have SQL Server 2016 installed on one of our Windows Server 2016 and also installed/enabled SSIS on same server.

    User is trying to connect via Management Studio as "Integration Services" for Server type in drop down but getting following error message:

    Cannot connect to OurSQLServer2016WithSSISInstalled.

    Connecting to the Integration Services service on the computer "OurSQLServer2016WithSSISInstalled" failed with the following error: "The RPC server is unavailable.".

    This error occurs when the computer host name does not exist, or cannot be reached.

    The user mentions he's outside of our network so would we need our Network team to whitelist his IP or anything else from my DBA team SQL Server perspective I'd need to do?

    Thanks in advance.

    Thursday, November 7, 2019 1:57 AM

Answers

  • Hi techresearch7777777,

    Glad to hear that everything is clear now.

    SSIS has two distinct environments:

    1. Development environment, in Visual Studio (VS) plus SSDT on a dev. machine.
    2. Run-time environment, on a server with SQL Server instance installed.

    SSMS could be installed anywhere to manage SQL Server and SSIS.

     
    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.

    Friday, November 15, 2019 9:22 PM

All replies

  • Hi techresearch7777777,

    There is no need in Integration Services service. It is a leftover for backward compatibility with old versions of SSIS 2005 - 2008R2. Starting from SSIS 2012 onwards SSIS needs SQL Server instance and SSISDB Catalog.

    Please see below.

    Thursday, November 7, 2019 2:13 AM
  • Thanks Yitzhak for your reply.

    So what needs to be done for user to be able to connect to SSIS then?

    Cheers.

    Thursday, November 7, 2019 12:37 PM
  • Hi techresearch7777777,

    You can follow this link to troubleshoot your issue -> https://blogs.msdn.microsoft.com/dataaccesstechnologies/2010/01/28/connecting-to-remote-sql-server-integration-service-from-sql-server-management-studio/

    Somehow the client is not able to reach the integration services instace (this can be because the port is not opened or the client doesn't have the connection to the destination server).

    Hope this helps.

    regards.

    Thursday, November 7, 2019 1:37 PM
  • Thanks Yitzhak for your reply.

    So what needs to be done for user to be able to connect to SSIS then?

    Cheers.

    You need to connect to a regular Database Engine in SSMS.

    SPACESHIP line at the top on the screen shot.

    Thursday, November 7, 2019 2:12 PM
  • Hi,

    To open connect to Integration Services Service:

      1.Open SQL Server Management Studio with Administrator.

      2.Click Object Explorer on the View menu.

      3.On the Object Explorer toolbar, click Connect, and then click Integration Services.

      4.In the Connect to Server dialog box, provide a server name. You can use a period (.), (local),

         or localhost to indicate the local server.

      5.Click Connect.

    Please refer to Connect to Integration Services.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, November 8, 2019 1:39 AM
  • Thanks Yitzhak for your reply.

    So what needs to be done for user to be able to connect to SSIS then?

    Cheers.

    Hi techresearch7777777,

    User just needs to connect to relational Database Engine in SSMS. A screen shot from my machine shows that I am connected to the SPACESHIP server. And Integration Services Catalogs is an entry for SSIS.

    Please ignore Gonzalo's link as it is applicable just for the very old and out of support versions of SSIS 2005 - 2008R2.

    Same with Mona's suggestion. It is N/A.

    Friday, November 8, 2019 5:26 AM
  • Thanks for the replies appreciate it.

    Turns out I myself with highest permissions Windows Server 2016 Administrators server level role & DBA sysadmin role am not able to connect via Management Studio for SSIS on that local server with following error:

    Connecting to the Integration Services service on the computer "OurSQLServer2016" failed with the following error: "Access is denied."

    By default, only administrators have access to the Integration Services service. On Windows Vista and later, the process must be running with administrative privileges in order to connect to the Integration Services service. See the help topic for information on how to configure access to the service.

    Believe SSIS is disabled by default after it gets installed.

    What needs to be done to enable it?

    Thanks.

    Friday, November 8, 2019 8:00 PM
  • Hi,

    Please refer to Create the SSIS Catalog and Create SSIS Catalog.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Tuesday, November 12, 2019 9:52 AM
  • Is it possible to connect to SSIS via Management Studio using a SQL Login & PW instead of Windows Domain account ?

    Thanks.

    Friday, November 15, 2019 6:34 PM
  • Is it possible to connect to SSIS via Management Studio using a SQL Login & PW instead of Windows Domain account ?

    Hi techresearch7777777,

    You continued to chase a Chimera. What does meaning of To chase a chimera?

    Allow me to explain it again.

    Microsoft SQL Server Management Studio (SSMS) is independent/detached product from the actual SQL Server engine.

    It doesn't come as a part of the SQL Server installation. The reason for that is very simple. It is possible to  install one single SSMS and manage through it many different instances with different versions of SQL Server: 2005, 2008, …, 2019.

    So it is possible to have a latest version of SSMS installed to manage obsolete and out of support SSIS 2005 or 2008. In such case you do need to connect to Integration Services service.

    On the other hand, if you are on SQL Server instance starting from 2012 onwards there is no need in Integration Services service. That service just doesn't exists.

    Connect/Integration Services... becomes a leftover for backward compatibility with old versions of SSIS 2005 - 2008R2. Starting from SSIS 2012 onwards SSIS needs SQL Server instance and SSISDB Catalog and nothing else.

    User just needs to connect to relational Database Engine in SSMS. A screen shot from my machine shows that I am connected to the SPACESHIP server. And Integration Services Catalogs is an entry for SSIS.

    Friday, November 15, 2019 7:09 PM
  • Interesting so the option to connect to SSIS via Management Studio is just for backwards compatibility that is obsolete.

    Developer user just needs SSIS DB established, access to SQL DB engine Login or Windows account, and Visual Studio and doesn't need to connect into SSIS via Management Studio at all.

    Thanks Yitzhak for clarifying, much appreciated.

    Friday, November 15, 2019 9:19 PM
  • Hi techresearch7777777,

    Glad to hear that everything is clear now.

    SSIS has two distinct environments:

    1. Development environment, in Visual Studio (VS) plus SSDT on a dev. machine.
    2. Run-time environment, on a server with SQL Server instance installed.

    SSMS could be installed anywhere to manage SQL Server and SSIS.

     
    Please don't forget to click "Mark as Answer" the response(s) that resolved your issue. This can be beneficial to other community members reading this thread.

    Friday, November 15, 2019 9:22 PM