Have you ever encountered an “Access Denied” error message when trying to connect to a remote instance of SQL Server Integration Service from Management Studio? If so, you’re not alone. This issue can be frustrating, but fortunately, there is a solution.

Let’s take a look at a scenario where a business intelligence developer was facing this problem. They had been developing and troubleshooting complex SSIS packages on their local workstation and recently migrated the application to a development server. However, when they tried to connect to the server from SQL Server Management Studio, they kept receiving the “Access Denied” error message.

Initially, the developer’s Windows account was added as a user in the msdb database and assigned to the db_ssisadmin role, but this didn’t resolve the issue. They were even made a db_owner in the msdb database and assigned to the sysadmin server role, but still no luck. It became clear that the problem was not with SQL Server itself.

Upon closer examination of the error message, it was discovered that certain permissions needed to be enabled against the DCOM component for Integration Service to be accessed remotely. In this case, the Integration Service was part of a SQL Server 2008 R2 installation hosted on a Windows 2008 R2 server.

To resolve the issue, the following steps were taken:

DCOM User Group Membership

1. Remotely logged into the server where SQL Server Integration Service was running.
2. Started the Server Manager and accessed the “Distributed COM Users” group properties.
3. Added the Windows account of the developer to this group.

DCOM Component Permission

1. Started the Windows Component Services Applet.
2. Navigated to the DCOM Config node under Component Services > Computers > My Computer.
3. Searched for the Component named MsDtsServer100 (for SQL Server 2008 DTS Server).
4. Modified the properties of the Component, ensuring that the “Run application on this computer” checkbox was turned on in the Location tab.
5. Customized the memberships under both the “Launch and Activation Permissions” and “Access Permissions” sections, adding the developer’s Windows AD account.
6. Enabled the “Remote Activation” checkbox for Launch and Activation Permission and the “Remote Access” checkbox for Access Permission.
7. Applied the settings and restarted Integration Services from SQL Server Configuration Manager.

With these configurations in place, the developer was able to successfully connect to the SSIS service from their workstation. However, they encountered a new error message when trying to expand the msdb node where packages had been deployed. The error message indicated that the EXECUTE permission was denied on the object ‘sp_ssislistfolders’ in the msdb database.

To resolve this issue, the developer’s Windows account was granted the db_ssisadmin role in the msdb database. This role provides the necessary permissions to manage and troubleshoot SSIS packages.

It’s important to remember a few key points when granting users permission to remotely connect to Integration Services:

• Use least privileged access to ensure security.
• Restarting Integration Services in a production system can potentially cause application outages.
• DCOM permissions for SSIS service may reset after applying Service Packs, Cumulative Updates, or Hotfixes to the SQL Server instance.

By following these steps and considering these points, you can resolve the “Access Denied” error when connecting to SQL Server Integration Service and ensure smooth operation of your SSIS packages.

Remember, it’s always important to investigate the root cause of an issue rather than resorting to guesswork. In this case, understanding the DCOM permissions and making the necessary configurations proved to be the solution.

Happy coding!