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














































Managing SSIS Security with Database Roles

MSSQLTips author Koen Verbeeck By:   |   Read Comments (6)   |   Related Tips: More > Integration Services Security
Problem

SQL Server 2012 introduced the new project deployment model for Integration Services. On the server side, this manifests in the SSIS catalog, the central point for managing SSIS projects, environments and parameters. But how can we assign permissions for various tasks to different users inside the catalog? How do we protect execution info of sensitive packages? This tip will guide you through the process of setting up security for the SSIS 2012 catalog.

Solution

An introduction to the project deployment model is out of scope for this tip. For those interested in more information, I gladly refer you to this excellent tip: SSIS Package Deployment Model in SQL Server 2012 (Part 1 of 2).

SSIS Database Role

If people want to access the catalog, they need to be added to the SSISDB database. The first step in setting up security is assigning the correct groups/users to database roles. There is only one database role however: ssis_admin. Users belonging to this role can do pretty much anything in the catalog, except dropping it. There is not much documentation about this role, but you can check the securables of this role in the properties window:

SSISDB database roles

Obviously, sysadmins have also full privileges in the catalog. If you want to give users less permissions, you add them to the SSISDB database, but you don't assign them to any role (implicitly they are added to the public role). As with all database security, it is advised to work with groups and not with individual users. For demonstration purposes though, I will work with one single user in the examples. In the following screenshot, I added a database user with the name Koen to the SSISDB:

Adding a database user

This user can now log into the SSISDB database, but he cannot see the internal tables. He can see all of the catalog views, but they won't return any data (more on that later).

Where have all the tables gone

SSIS Permissions

If we want to give users appropriate permissions (without just adding them to the ssis_admin role), we'll need to assign them to the correct securables. The catalog has three securable objects: projects, environments and packages. Let's illustrate with an example:

In the catalog, two folders are created: FolderA and FolderB. Each folder contains a different project.

Two projects sitting in a tree

Using Management Studio, we can easily assign permissions to the user created earlier. Let's give the user Koen read permissions to folder A. Don't forget to assign the permission Read Object, otherwise the project inside the folder will not be accessible.

Assigning permissions

When this user now opens the catalog, he can only see FolderA, but FolderB is hidden. The user can see the project and the packages, but he cannot modify or execute them.

FolderB has disappeared

An important permission is Manage Object Permission. With this permission you can delegate the administration of permissions to other users without adding them to the ssis_admin role. For more information about the different permissions, see the table in the Remarks section of this MSDN page.

Important note: the catalog security doesn't work well with SQL Server users. Try to use domain accounts/groups as much as possible. Using SQL Server authentication results in the following error message: An exception occurred while executing stored procedure 'some internal stored procedure'. The error message is: 'The operation cannot be started by an account that uses SQL Server Authentication. Start the operation with an account that uses Windows Authentication.'

A great feature of the catalog security is that the available catalog views in the SSISDB are filtered according to the permissions given to the user. For example, if the user Koen checks the view catalog.executions, he can only see information about FolderA, for which he has permissions.

A little more rowss

However, if a sysadmins or a user belonging to the role ssis_admin queries the same view, he retrieves all of the data.

Not so much rows

As you can see, there were multiple executions in FolderA, but the user can only see his own executions. This can become problematic if someone who is not an admin wants to follow-up the executions of the SSIS packages, as described in this Connect item. A work around could be to develop your own reporting framework on top of the SSIS catalog.

Remark: there was an issue with Windows Groups and the catalog security, but a fix has been released with SQL Server 2012 SP1 CU7.

T-SQL galore

One of the nicest features of the SSIS catalog is that you can do virtually everything that is possible through Management Studio with T-SQL scripts. This is also true for managing security of course. The most important stored procedure is probably catalog.grant_permission, which allows you to grant permissions to a securable object to users.

Conclusion

Security for the SSIS catalog is very intuitive and elegant to set-up. You can assign permissions on different objects to users and groups, but it works best with Windows Authentication. The catalog views are automatically filtered according to the permissions granted, but since users can only see their own executions reporting is not that straight forward. This might for example give issues in a production environment, where all the packages are executed with a single dedicated domain account.

Next Steps
  • For an introduction to the SSIS project deployment model, please refer to the following tip: SSIS Package Deployment Model in SQL Server 2012 (Part 1 of 2).
  • Try it out yourself! Create some dummy users in your environment, give them different permissions the catalog and see what effect it has on the various catalog views.
  • The MSDN article SSIS Catalog gives a good overview. It has a section on security and it contains a lot of links to different stored procedures you can use to automate your environment.


Last Update: 1/16/2014


About the author
MSSQLTips author Koen Verbeeck
Koen Verbeeck is a BI professional, specializing in the Microsoft BI stack with a particular love for SSIS.

View all my tips


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Thursday, January 16, 2014 - 10:38:21 AM - Thomas LeBlanc Read The Tip

Koen,

Excellent article. Thanks for showing the security and a good example.

Thomas

 


Monday, January 27, 2014 - 9:29:16 AM - DH Read The Tip

There still is the outstanding issue of successfully running the built-in SSIS reports if you are NOT a member of the ssis_admin group OR an sa. When the end-user does not belong to one or both of these groups but does belong to the db_datareader db role in SSISDB, the reports will run but return no data. These reports are extremely helpful in resolving errors with SSIS execution (much better than the lackluster troubleshooting tools from previous versions). I have found no way around this and the SP1 CU7 update does not resolve this.

In my case, we will have a production SQL Server and SSIS 2012 instance. The BI developers would find the the reports very useful to resolve errors in production. However, corporate security rules will not allow them to be a member of the ssis_admin database role OR sa fixed server role in production and read access to the database does not appear to work.

Ive run profiler while the reports execute. The reports execute several dynamic SQL statements and varying parameters depending on the report and how deeply you dive into error chain.

Any ideas?


Monday, January 27, 2014 - 10:05:12 AM - Dave M Read The Tip

It may not be the greatest solution but we added a role in the SSISDB called [SSISReportRead].  We then modified the all the catalog views to include this line at the end: 

OR (IS_MEMBER('SSISReportRead') = 1)

 

This will allow the members of this group to be able to access the data used by the reports.  I understand that when patching this could break, but we are willing to deal with that in order to not give too much access to production.

 

 

 

 


Monday, January 27, 2014 - 12:03:54 PM - Koen Verbeeck Read The Tip

There are indeed some shortcoming in the reporting layer of the SSISDB. I hope they make some necessary changes in the next release.


Monday, January 27, 2014 - 3:17:34 PM - DH Read The Tip

Thank you Dave M! I never would think to look at security baked into the view definition itself. My guess is that this part of SSIS 2012 was low on the priority scale. The more important part was probably exposing SSIS to SSMS, the actual database, and the Integration Services Catalog.


Tuesday, January 28, 2014 - 3:42:32 PM - Rachel Van den Berg Read The Tip

I have also come across the problem with developers needing to be able to run the built-in SSIS reports, but not allowed to give them full sa or SSIS_admin on the production systems. After some testing I have come up with the solution to give them the SSIS_admin with datawrite_deny permissions. This stops them from making any changes or when they execute a package the writing deny stops the execution from proceeding, but gives them the possibilities to see the reports.

I hope in the next version the possibility to run the built-in SSIS reports will be solved more elegant

 



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.