Upgrade your Internet Experience
Microsoft.com
Welcome Sign in
SQL Server TechCenter
Click to Rate and Give Feedback
TechNet
TechNet Library
SQL Server
SQL Server 2008
Operations
Administration
 Connecting to a Remote Integration ...

  Switch on low bandwidth view
Community Content
In this section
Statistics Annotations (3)
Collapse All/Expand All Collapse All
Other versions are also available for the following:
SQL Server 2008 Books Online (October 2009)
Connecting to a Remote Integration Services Server

Connecting to an instance of Integration Services on a remote server, from SQL Server Management Studio or another management application, requires a specific set of rights on the server for the users of the application.

Aa337083.note(en-us,SQL.100).gifImportant:
To manage packages that are stored on a remote server, you do not have to connect to the instance of the Integration Services service on that remote server. Instead, edit the configuration file for the Integration Services service so that SQL Server Management Studio displays the packages that are stored on the remote server. For more information, see Configuring the Integration Services Service.

 Connecting to Integration Services on a Remote Server
 To connect to Integration Services on a Remote Server

  1. Open SQL Server Management Studio.

  2. Select File, Connect Object Explorer to display the Connect to Server dialog box.

  3. Select Integration Services in the Server type list.

  4. Type the name of a SQL Server Integration Services server in the Server name text box.

    Aa337083.note(en-us,SQL.100).gifNote:
    The Integration Services service is not instance-specific. You connect to the service by using the name of the computer on which the Integration Services service is running.

  5. Click Connect.
Aa337083.note(en-us,SQL.100).gifNote:
The Browse for Servers dialog box does not display remote instances of Integration Services. In addition, the options available on the Connection Options tab of the Connect to Server dialog box, which is displayed by clicking the Options button, are not applicable to Integration Services connections.
 Eliminating the "Access Is Denied" Error

When a user without sufficient rights attempts to connect to an instance of Integration Services on a remote server, the server responds with an "Access is denied" error message. You can avoid this error message by ensuring that users have the required DCOM permissions.

 To configure rights for remote users on Windows Server 2003 or Windows XP

  1. If the user is not a member of the local Administrators group, add the user to the Distributed COM Users group. You can do this in the Computer Management MMC snap-in accessed from the Administrative Tools menu.

  2. Open Control Panel, double-click Administrative Tools, and then double-click Component Services to start the Component Services MMC snap-in.

  3. Expand the Component Services node in the left pane of the console. Expand the Computers node, expand My Computer, and then click the DCOM Config node.

  4. Select the DCOM Config node, and then select MsDtsServer in the list of applications that can be configured.

  5. Right-click on MsDtsServer and select Properties.

  6. In the MsDtsServer Properties dialog box, select the Security tab.

  7. Under Launch and Activation Permissions, select Customize, then click Edit to open the Launch Permission dialog box.

  8. In the Launch Permission dialog box, add or delete users, and assign the appropriate permissions to the appropriate users and groups. The available permissions are Local Launch, Remote Launch, Local Activation, and Remote Activation. The Launch rights grant or deny permission to start and stop the service; the Activation rights grant or deny permission to connect to the service.

  9. Click OK to close the dialog box.

  10. Under Access Permissions, repeat steps 7 and 8 to assign the appropriate permissions to the appropriate users and groups.

  11. Close the MMC snap-in.

  12. Restart the Integration Services service.
 To configure rights for remote users on Windows 2000 with the latest service packs

  1. Run dcomcnfg.exe at the command prompt.

  2. On the Applications page of the Distributed COM Configuration Properties dialog box, select MSDTSServer and then click Properties.

  3. Select the Security page.

  4. Use the two separate dialog boxes to configure Access Permissions and Launch Permissions. You cannot distinguish between remote and local access - Access permissions include local and remote access, and Launch permissions include local and remote launch.

  5. Close the dialog boxes and dcomcnfg.exe.

  6. Restart the Integration Services service.
 Connecting by using a Local Account

If you are working in a local Windows account on a client computer, you can connect to the Integration Services service on a remote computer only if a local account that has the same name and password and the appropriate rights exists on the remote computer.
 Delegation Is Not Supported

SQL Server Integration Services does not support the delegation of credentials, sometimes referred to as a double hop. In this scenario, you are working on a client computer, Integration Services is installed on a second computer, and SQL Server is installed on a third computer. Although SQL Server Management Studio successfully passes your credentials from the client computer to the second computer on which Integration Services is running, Integration Services cannot delegate your credentials from the second computer to the third computer on which SQL Server is running.

Integration Services icon (small)  Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.
Tags What's this?: Add a tag
Community Content   What is Community Content?
Add new content RSS  Annotations
This post is incomplete!      Casp0080 ... Thomas Lee   |   Edit   |   Show History

Even though you set the proper permissions on the MsDtsServer DCOM application as mentioned in the article, the final set of permissions applied on the DCOM application is controlled by the Access Limits on the COM Security of the computer.

In the Component Services configuration screen, right Click "My Computer" and select properties. Then select the COM Security Tab. You will see the following screen.




Note the circled text. Even though the security permission can be set on the individual DCOM application, the final permission set has to fall within the Limits specified with in the COM Security Configuration.
So click "Edit Limits.." and add the domain user or group to which the you want to give SSIS access. Ensure that you have given Remote Launch and Remote Activation permissions to the newly added User or Group. You have to do this in addition to the permissions specified in the Microsoft article.
This should allow you to access the SSIS service remotely.

Management Studio Object Explorer in SQL 2008 cannot connect to SQL 2005 Integration Services      Jason H - SQL   |   Edit   |   Show History

If you try to connect from a SSIS 2008 Management Studio to SQL 2005 Integration Services, it will fail. This is currently listed as a by-design scenario, and I will update this information if there is any hope of this changing.

TITLE: Connect to Server

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

Cannot connect to my2005server.

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

ADDITIONAL INFORMATION:

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

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

Connect to SSIS Service on machine "my2005server" failed:

Class not registered.

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

Connect to SSIS Service on machine "my2005server" failed:

Class not registered.

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

BUTTONS:

OK

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

Currently this has been brought up to the SQL Server product groups, on these threads for example:


https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=363922

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=363010

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=315076

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=412929


Flag as ContentBug
Managing packages in SQL Management Studio without connecting to the Integration Services service?      Hutch_21   |   Edit   |   Show History
The note at the top labeled "Important" states that there is a configuration setting that allows users to manage packages without connecting to the Integration Services service. There is also a link to a page about the Integration Services config file. But neither on that page or on this one is there any information about what this configuration setting is. Where is the information?!
Tags What's this?: Add a tag
Flag as ContentBug
© 2009 Microsoft Corporation. All rights reserved. Terms of Use | Trademarks | Privacy Statement
Page view tracker