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