Problem
When I try to connect to a remote instance of SQL Server Integration Service from Management Studio in my workstation, I receive an “Access Denied” error message. My Windows account is a member of the db_ssisadmin role in the msdb database of this server but I still can’t access the service.
Solution
One of the business intelligence developers in my team once came to me looking for an answer to this problem. He had been developing and troubleshooting a group of complex SSIS packages in his local workstation. The application had been migrated to a development server only a few days ago and he was trying to connect to it from SQL Server Management Studio. Unfortunately he was not having much luck.
The error message looked like the following:
Like so many other DBAs, my first instinct was to resolve the issue rather than investigating its root cause. I just wanted the problem to go away so I started with some elevated permissions, hoping my wild guesswork would help. Initially I tried to resolve the issue by adding his Windows account as a user in the msdb database and then assigning the account under db_ssadmin role. This did not help. Next he was made a db_owner in the msdb database, this did not help either. Going against all security best practices and knowing I was committing a cardinal sin, I assigned his account to the sysadmin server role. Still no luck.
So SQL was not the issue. Promptly rolling back his elevated permissions, I decided to look at the Windows side of things. Once his Windows AD account was added as a local Administrator in the development box, things looked okay. I did not want him to be an administrator even in a development box, so I started to look at the error message closely and tried to make some sense of it. I was pretty sure there was a better solution than making someone a Windows or SQL admin.
As it turns out, a user needs to have certain permissions enabled against the DCOM component for Integration Service to remotely access it with a tool like Management Studio. In this case the Integration Service was part of a SQL Server 2008 R2 installation and was hosted in a Windows 2008 R2 server.
These are the steps I followed:
DCOM User Group Membership
- Remotely logged into the server where SQL Server Integration Service was running.
- Started the Server Manager and brought up the “Distributed COM Users” group properties. To get there, start the Server Manager (usually the first icon in the application bar in Windows 2008). From the left pane, expand the Configuration node, then “Local Users and Groups” and then Groups. The following images show this:
- I then added the Windows account of the developer to this group.
DCOM Component Permission
- Next, I started the Windows Component Services Applet. To get there, you can navigate from Start > All Programs > Administrative Tools > Component Services. From the left pane, I expanded the DCOM Config node under Component Services > Computers > My Computer:
- Once the node was expanded, I searched for the Component named MsDtsServer100. Note the number 100 at the end of it: we were looking at a SQL 2008 DTS Server here.
- Right clicking the icon and selecting Properties from the pop-up menu brought up a dialog box. First, in the Location tab, I ensured the checkbox “Run application on this computer” was turned on:
- Next, the Security tab looked like the following:
- This is the crux of the solution. First I made sure the radio button “Customize” was selected under both the “Launch and Activation Permissions” and “Access Permissions” sections. Next, I was customizing their memberships. Clicking on the Edit buttons brought up the memberships properties window which looked like the following:
- In both the cases, clicking the Add button allowed me to add the developer’s Windows AD account under “Group or user names” section:
- For the Launch and Activation Permission, I ensured the Windows account had the checkbox for “Remote Activation” turned on. Similarly the checkbox “Remote Access” was turned on for Access Permission. Both these permissions ensure the user can connect to the DCOM application remotely. Once the settings were applied, I clicked the OK buttons to get back to the main window of Component Services.
- To wrap up the whole thing, I then restarted Integration Services from SQL Server Configuration Manager. This was not a problem as the server was used for development purposes only.
Fine Tuning SSIS Access: Execute Permission Denied
With the configuration done at the server, my colleague was able to connect to the SSIS service from his workstation. There was no error message and his login attempt was successful. However, a different error message started to pop-up as he tried to expand the msdb node where packages had been deployed. The error message looked something like the following: The EXECUTE permission was denied on the object ‘sp_ssislistfolders’, database ‘msdb’, schema ‘dbo’
Now this error message is displayed when the Windows account does not have permissions on the msdb database hosting the SSIS package. There are three SSIS related roles in msdb database as shown below:
Since this was a development box and the user was going to manage and troubleshoot the packages, I decided to grant his Windows account db_ssisadmin role. The connectivity issue was finally resolved with that.
Final Words
Some points need to be remembered when granting users permission to remotely connect to Integration Services:
- It’s best to use least privileged access. For example, the Remote Launch permission gives the user ability to remotely stop / start Integration Services. This may not be required if the user is not an administrator. Similarly, the db_ssisadmin role has elevated permissions meant for administrative accounts.
- Restart of Integration Services in a production system can potentially mean an outage of applications using SSIS.
- Most importantly, unless you are running SQL 2005 with SP4 or SQL 2008 with SP2 or later, DCOM permissions for SSIS service will reset after a Service Pack, Cumulative Update or Hotifix has been applied to the SQL Server instance. Microsoft has confirmed this as a problem for the versions prior to these releases. So if your DBAs have upgraded a SQL Server 2005 from a pre-SP4 Service Pack or a SQL 2008 from SP1, you may wish to recheck any custom DCOM configurations applied to the MsDtsServer component.
Next Steps
- Learn more about Integration Services related database roles in msdb.
