By: Chad Churchwell | Updated: 2013-03-20 | Comments (2) | Related: > Integration Services Configuration Options
Problem
I was recently approached by the DEV team and since they have no access in QA and Prod, they asked if they could be able to just see the package and folder structures through SSMS from their workstations. Since Integration Services connectivity is outside of SQL and controlled through the OS, I was unsure if this was possible since I did not see any "Read Only" properties when researching SSIS permissions.
Solution
The solution to this problem was developed on Windows Server 2008 R2 running SQL Server 2008 R2 Enterprise Edition. There are 3 different things that I did to get this to work, including local group permissions, DCOM Config settings, as well as MSDB permissions inside of SQL Server. There is a MSDN article related to this, but I had to more steps than were described in the article to get it working.
The following steps are performed on the remote server and I did this through a RDP session.
1. Add AD Group to "Distributed COM Users" local group
By going to Start --> Computer (Right Click) --> Manage, expand Configuration --> Local Users and Groups --> Groups
Right click "Distributed COM Users" and select "Add to Group" and add the AD group for your development team or whomever you are granting read only access to SSIS for.
2. Set DCOM Config Permissions
Since permissions for stopping/starting the SSIS Service as well as connectivity are maintained in the DCOM Config setting for the service, you have to add that group and set its access as well, followed by restarting the SSIS Service for the changes to take effect. As you will see all the permissions I set are "Remote" permissions, so they can connect from their workstations. Local would be RDP'd or sitting on the server, which surely will not be allowed.
Go to Start --> Administrative Tools --> Component Services OR Start --> Run and type "DCOMDNFG.exe" to bring up the UI.
Expand Component Services --> Computers --> My Computer --> DCOM Config
Locate the SSIS Service (for 2008 it is named MsDtsServer100, 2012 is called Microsoft SQL Server Integration Services 11.0) and Right Click --> Properties
Click Edit in the Launch and Activation Section, add the AD group, and grant it Remote Activation permission and select OK. (As an FYI, Launch is the permission to start/stop the SSIS Service, Activation is the ability to connect to the SSIS Service)
Click Edit in the Access Section, add the AD group, and Allow it Remote Access and select OK.
Close the Component Services MMC Plugin and restart the Integration Services Service through SQL Server Configuration Manager. This is required for the changes to take effect. Also, be careful to make sure no packages or jobs are running when you bounce the service, but I am sure everyone already knew that.
What we have done is granted that AD group the ability to connect to the service and after some testing I found this did not satisfy the original requirement, they could connect fine, but could not expand folders and see the underlying packages when they tried to expand the MSDB folder. We have one more step to perform.
3. Set MSDB Permissions
Since SSIS packages are stored in the MSDB database, I did not want to grant EXECUTE to the entire schema so we narrowed down to the minimum permissions needed to able to browse the folders and packages. I ended up putting these permissions into a role and granting that role to the AD group.
There are 3 system stored procedures that needed execute permission granted and they are listed below.
- sp_ssis_listfolders
- sp_ssis_listpackages
- sp_ssis_getfolder
USE msdb GO
CREATE ROLE SSISBrowser
GRANT EXECUTE ON sp_ssis_listfolders TO SSISBrowser GRANT EXECUTE ON sp_ssis_listpackages TO SSISBrowser GRANT EXECUTE ON sp_ssis_getfolder to SSISBrowser
EXEC sp_addrolemember N'SSISBrowser', N'YOURDOMAIN\ADGroupName' GO
At this point, the requesting group was able to connect through SSMS to Integration Services on the remote server, browse folder structures and packages. I also tested and verified they could not export, execute, or rename packages.
Next Steps
- Question and determine all access requests to make sure they are valid and comfortable to you as the DBA Gatekeeper
- Always grant least permission needed to perform the task, don't take the easy way out.
- Additional reading:
About the author
Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2013-03-20
