By: Koen Verbeeck | Comments (10) | Related: 1 | 2 | > 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).
The solution presented in this tip is valid from SQL Server 2012 till the latest version, when using the project deployment model.
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:
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:
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).
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.
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.
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.
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.
However, if a sysadmins or a user belonging to the role ssis_admin queries the same view, he retrieves all of the data.
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.
About the author
Koen Verbeeck is a seasoned business intelligence consultant at AE. He has over a decade of experience with the Microsoft Data Platform in numerous industries. He holds several certifications and is a prolific writer contributing content about SSIS, ADF, SSAS, SSRS, MDS, Power BI, Snowflake and Azure services. He has spoken at PASS, SQLBits, dataMinds Connect and delivers webinars on MSSQLTips.com. Koen has been awarded the Microsoft MVP data platform award for many years.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
