You are currently offline, waiting for your internet to reconnect

Your browser is out-of-date

You need to update your browser to use the site.

Update to the latest version of Internet Explorer

Error message when you use SQL Server Management Studio to connect to SQL Server 2005 Integration Services from a client computer: "Connect to SSIS Service on machine '<SSISServer>' failed Access is denied"

Extended support for SQL Server 2005 ends on April 12, 2016

If you are still running SQL Server 2005 after April 12, 2016, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

SYMPTOMS
Consider the following scenario. A server is running Microsoft SQL Server 2005 Integration Services (SSIS). From a client computer, you use SQL Server Management Studio to connect to Integration Services on the server. To connect to Integration Services on the server, you use a user account that is not a member of the Administrators group on the server. In this scenario, you receive the following error message:
Cannot connect to SSISServer
Additional information:Failed to retrieve data for this request (Microsoft.SqlServer.SmoEnum)
Connect to SSIS Service on machine "SSISServer" failed: Access is denied.
Note In this error message, SSISServer is a placeholder for the name of the server or for the IP address of the server.

However, you can successfully connect to Integration Services on the server locally by using the same user account that you used to connect to Integration Services from the client computer.
WORKAROUND
To work around this problem, follow these steps:
  1. On the client computer, enable Windows Management Instrumentation (WMI) for remote administration. To do this, follow these steps:
    1. Click Start, click Run, type gpedit.msc, and then click OK.
    2. In the Group Policy Object Editor, expand Computer Configuration, expand Administrative Templates, and then expand Network.
    3. Expand Network Connections, expand Windows Firewall, and then click Domain Profile.
    4. Right-click Windows Firewall: Allow remote administration exception, and then click Properties.

      Note In Windows Vista, right-click Windows Firewall: Allow inbound remote administration exception.
    5. In the Windows Firewall: Allow remote administration exception Properties dialog box, click Enabled, and then click OK.

      Note In Windows Vista, the dialog box is the Windows Firewall: Allow inbound remote administration exception dialog box.
  2. On the server and on the client computer, specify that DCOM is available for all Microsoft COM applications. To do this, follow these steps:
    1. Click Start, click Run, type dcomcnfg, and then click OK.
    2. In the Component Services dialog box, expand Component Services, expand Computers, right-click My Computer, and then click Properties.
    3. In the My Computer Properties dialog box, click the Default Properties tab.
    4. On the Default Properties tab, click to select the Enable Distributed COM on this computer check box, and then click OK.
  3. On the server, add the user account to the Distributed COM Users group. To do this, follow these steps:
    1. Click Start, click Run, type lusrmgr.msc, and then click OK.
    2. In the Local Users and Groups dialog box, click Groups, and then double-click Distributed COM Users.
    3. In the Distributed COM Users Properties dialog box, click Add.
    4. In the Select Users dialog box, type the user name under Enter the object names to select, and then click OK two times.
  4. On the server, grant the appropriate permissions for the MsDtsServer DCOM application. To do this, follow these steps:
    1. Click Start, click Run, type dcomcnfg, and then click OK.
    2. In the Component Services dialog box, expand Component Services, expand Computers, and then expand My Computer.
    3. Expand DCOM Config, right-click the MsDtsServer object, and then click Properties.
    4. In the MsDtsServer Properties dialog box, click the Security tab.
    5. Under Launch and Activation Permissions, click Customize, and then click Edit.
    6. In the Launch Permission dialog box, click Add.
    7. In the Select Users or Groups dialog box, type the user name under Enter the object names to select, and then click OK.
    8. In the Launch Permission dialog box, click the user name under Group or user names.
    9. Under Permissions for UserName, click to select the Allow check box for the following permissions:
      • Local Launch
      • Remote Launch
      • Local Activation
      • Remote Activation
      Note UserName is a placeholder for the user name that you clicked in step h.
    10. Click OK.
    11. In the MsDtsServer Properties dialog box, click Customize under Access Permissions, and then click Edit.
    12. In the Access Permission dialog box, click Add.
    13. In the Select Users or Groups dialog box, type the user name under Enter the object names to select, and then click OK.
    14. In the Access Permission dialog box, click the user name under Group or user names.
    15. Under Permissions for UserName, click to select the Allow check box for the following permissions:
      • Local Access
      • Remote Access
    16. Click OK two times.
  5. Restart the SQL Server Integration Services service.
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
SSIS Access Denied Yukon 2005 SQL connect DCOM security local admin
Properties

Article ID: 940232 - Last Review: 11/27/2007 18:01:08 - Revision: 1.4

  • Microsoft SQL Server 2005 Workgroup Edition
  • Microsoft SQL Server 2005 Standard Edition
  • Microsoft SQL Server 2005 Developer Edition
  • Microsoft SQL Server 2005 Enterprise Edition
  • Microsoft SQL Server 2005 Standard X64 Edition
  • Microsoft SQL Server 2005 Enterprise X64 Edition
  • Microsoft SQL Server 2005 Standard Edition for Itanium-based Systems
  • Microsoft SQL Server 2005 Enterprise Edition for Itanium-based Systems
  • kbsql2005ssis kbtshoot kbexpertiseadvanced kbprb KB940232
Feedback