Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Configure SQL Server Security to Allow Only Browsing of SSIS Packages

MSSQLTips author Chad Churchwell By:   |   Read Comments (2)   |   Related Tips: More > 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

Computer Management

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.

Add AD Group to Local Group

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

Component Services

Locate the SSIS Service (for 2008 it is named MsDtsServer100, 2012 is called Microsoft SQL Server Integration Services 11.0) and Right Click --> Properties

MsDtsServer100 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)

Activation Permisisons

Click Edit in the Access Section, add the AD group, and Allow it Remote Access and select OK.

Access Permisisons

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.

Connection Error

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


Last Update: 3/20/2013


About the author
MSSQLTips author Chad Churchwell
Chad Churchwell is a SQL Server professional specializing in High Availability, Disaster Recovery, and Replication.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Wednesday, March 20, 2013 - 3:39:19 AM - Abhi Read The Tip

Good article Chad, Thanks..


Wednesday, March 20, 2013 - 3:06:39 PM - TonyC Read The Tip

Thank goodness for SQL Server 2012 and Integration Services Catalogs



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.