DCOM Permissions for SSIS service are Reset after applying a service pack or a cumulative update or a hotfix

Symptoms

Consider the following scenario: You have either a SQL Server 2005 or a SQL Server 2008 installation with SSIS component installed. You update your SQL Server installation with one of the following:

  • Service Pack
  • Cumulative Update
  • Hotfix

In this scenario,you will notice that the DCOM permissions for launching and accessing Integration servces are reset to default values. Additionally users who are not members of Administrators group on the SQL Server machine, will receive the following error message when using SQL Server Management Studio Object Explorer to connect to the Integration Services service: 

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.

Cause

Status: Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

Installation of the service packs, hotfixes, and cumulative updates reset DCOM permissions for launching and accessing Integration Services service. The permissions are reset to default permissions. If you have customized DCOM permissions, you will need to reapply the customization. 

Resolution

This problem was first corrected in the following builds. Service pack, Cumulative Update, and Hotfix Installations done prior to these builds will still exhibit the problem, and may require you to use one of the Workarounds.

- SQL Server 2005 Service Pack 4 or later
- SQL Server 2008 Service Pack 2 or later
- SQL Server 2008 R2 RTM or later

Workaround

You can use one of the following procedures to workaround the problem.

Wokraround 1:

Note: Use this procedure for scenarios where the service pack or other updates have already been completed.

  • 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, clickAdd.
    4. In the Select Users dialog box, type the user name underEnter the object names to select, and then click OK two times.
  • 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 clickProperties.
    4. In the MsDtsServer (SQL Server 2005) or MsDtsServer100(SQL server 2008) 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 underEnter the object names to select, and then click OK.
    8. In the Launch Permission dialog box, click the user name underGroup or user names.
    9. Under Permissions for UserName, click to select theAllow 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 (SQL Server 2005) or MsDtsServer100(SQL server 2008) Properties dialog box, clickCustomize under Access Permissions, and then clickEdit.
    12. In the Access Permission dialog box, click Add.
    13. In the Select Users or Groups dialog box, type the user name underEnter the object names to select, and then click OK.
    14. In the Access Permission dialog box, click the user name underGroup or user names.
    15. Under Permissions for UserName, click to select theAllow check box for the following permissions:
      • Local Access
      • Remote Access
    16. Click OK two times.
  • Restart the SQL Server Integration Services service. 
    Workaround 2:

    Note: Use this procedure if you are just getting ready to apply the update. For further information about the registry keys discussed in this procedure refer to theMore Information section below

     SQL 2005 Integration Services:

    1. At the command prompt, export the security settings for the MsDtsServer DCOM application using the following:
      • regedit /e C:\temp\ssis2005permissions.reg "HKEY_CLASSES_ROOT\AppID\{F38B7F09-979B-4241-80D9-2EADED02954F}"
    2. Run the setup program for the service pack or hotfix.
      • Note:This will remove any customization to the MsDtsServer security settings.
    3. At the command prompt, import the saved security settings for the MsDtsServer DCOM application using the following:
      • regedit /i C:\temp\ssis2005permissions.reg

     
    SQL 2008 Integration Services:

    1. At the command prompt, export the security settings for the MsDtsServer DCOM application using the following:
      • regedit /e C:\temp\ssis2008permissions.reg "HKEY_CLASSES_ROOT\AppID\{FAAFC69C-F4ED-4CCA-8849-7B882279EDBE}"
    2. Run the setup for the service pack or hotfix.
      • Note:This will remove any customization to the MsDtsServer security settings using the following:
    3. At the command prompt, import the saved security settings for the MsDtsServer DCOM application:
      • regedit /i C:\temp\ssis2008permissions.reg

    • More Information

    When a SQL server service pack or an update is installed, the Microsoft Installer patch logic in the patches sqlrun_dts.msp (2005) and sql_is.msp (2008) replaces the values in the registry keys which store the the DCOM Access Permissions for the SSIS Services (MsDtsServer and MsDtsServer100). Any customized access permissions saved will be lost and replaced with the default values when the service pack is installed.

    SQL Server 2005 Integration Services (MsDtsServer) service DCOM security settings are stored in the following registry key values:

    • HKEY_CLASSES_ROOT\AppID\{F38B7F09-979B-4241-80D9-2EADED02954F}\AccessPermission
    • HKEY_CLASSES_ROOT\AppID\{F38B7F09-979B-4241-80D9-2EADED02954F}\AuthenticationLevel
    • HKEY_CLASSES_ROOT\AppID\{F38B7F09-979B-4241-80D9-2EADED02954F}\LaunchPermission

     SQL Server 2008 Integration Services 10.0 (MsDtsServer100) service DCOM security settings are stored in the following registry key values:

    • HKEY_CLASSES_ROOT\AppID\{FAAFC69C-F4ED-4CCA-8849-7B882279EDBE}\AccessPermission
    • HKEY_CLASSES_ROOT\AppID\{FAAFC69C-F4ED-4CCA-8849-7B882279EDBE}\AuthenticationLevel
    • HKEY_CLASSES_ROOT\AppID\{FAAFC69C-F4ED-4CCA-8849-7B882279EDBE}\LaunchPermission

    These above mentioned registry values are viewable as permission settings in the MMC snapin for Component Services as discussed in the Workaround 1 underResolution section above. 

    Related links: