Integration Services Security in SQL Server by Jeff Jones
Integration Services Security in SQL Server by Jeff Jones
Defining security for SQL Server Integration Services (SSIS) is a bit complex. I want to focus on the security required by a developer to create and manage their own packages and people that are not SysAdmins but need to manage all packages.
After a package has been developed using BI Development Studio, it must be deployed to a location for subsequent execution. This is a similar model to application dll’s and exe’s that must be deployed to an object library for execution. With SSIS you can deploy a package to any file system directory, the MSDB database or a special file system directory known by the Integration Services Service. When deploying to a file system directory (whether known by Integration Services Service or not), you must rely on OS file system security to control access to these packages. If you deploy to the MSDB database you have other mechanisms available to control access to packages.
The MSDB database has three predefined fixed database roles specifically designed for SSIS. They are db_dtsltduser, db_dtsoperator and db_dtsadmin. Each of the roles has execute permissions on a set of MSDB stored procedures that are used to manipulate packages located in the dbo.sysdtspackages90 table. You can also define your own custom database roles to further secure packages deployed to MSDB. The user needs a database user account in MSDB to be assigned to these roles.
Each package has a default assignment to both the readerrole and writerrole. The readerrole allows for enumerating a packaging (displaying its name), viewing a package’s definition, executing a package interactively, exporting a package definition and running a package under SQL Server Agent as a job. The writerrole allows for importing a package, deleting a package and changing package security roles.
When a package is placed in the MSDB database a default role assignment is made. The package’s readerrole is assigned to db_dtsadmin, db_dtsoperator and the creator of the package. The writerrole is assigned to db_dtsadmin and the creator of the package. The details for each role and what they can do is described in SQL Server 2005 Books Online under the “Integration Services Roles” topic.
For those developers that need to deploy packages to MSDB, enumerate and execute packages that they own, they need to a member of db_dtsltduser. Again they can only manipulate packages that they own.
If a person that is not a Sysadmin needs to administer all the packages stored in MSDB, they need to be in the db_dtsadmin role. All users with SysAdmin rights can see and do anything with packages.
To execute any package, the user must be in the db_dtsoperator role.
Additional user-defined database roles can be created in MSDB to provide an additional level of control. The additional roles work in conjunction with the fixed roles described above. By default, when a package is placed in the MSDB database, the owner of the package and the users in the db_dtsadmin role can view the package definition (via exporting), modify the package (via importing), and execute the package. Also users in the db_dtsoperator role can execute the package. When you assign a user-defined role as the readerrole, anyone in that user-defined role can view the package and execute it. When you assign a user-defined role as the writerrole, anyone in that user-defined role can update the package definition. The user must also be in the db_dtsltduser role. So users must be in both the user-defined role and the db_dtsltduser role to access packages.
For example, you have a development team creating Integration Services packages. They decide to deploy the packages to SQL Server MSDB for execution. The packages where created by different developers and therefore the packages have different creators/owners. We want the entire team to be able to read and potentially update the various packages. The DBA does not want to give the developers SysAdmin permissions nor the ability to read, update and execute all the packages in the MSDB database. One approach would be to:
1. Create a user-defined role in MSDB, place all the developers in that role.
2. Assign that role to the db_dtsltduser fixed database role. This allows the developer team to access packages that they created and own.
3. Assign each package the team created to the user-defined role created earlier.
This will allow all team members to access all associated packages no matter who created them.
There are other issues related to the encryption of sensitive data in the package that I will cover in another blog post.
You May Also Like
In this video, you will gain an understanding of Agile and Scrum Master Certification terminologies and concepts to help you make better decisions in your Project Management capabilities. Whether you’re a developer looking to obtain an Agile or Scrum Master Certification, or you’re a Project Manager/Product Owner who is attempting to get your product or … Continue reading Agile Methodology in Project Management
This is part 1 of our 5-part Office 365 free training course. In this Office 365 training video, instructor Spike Xavier introduces some of the most popular services found in Microsoft Office 365 including the Admin Portal and Admin Center. For instructor-led Office 365 training classes, see our course schedule: Spike Xavier SharePoint Instructor – … Continue reading An Overview of Office 365 – Administration Portal and Admin Center
In this Office 365 training video, instructor Spike Xavier demonstrates how to create users and manage passwords in Office 365. For instructor-led Office 365 training classes, see our course schedulle: Spike Xavier SharePoint Instructor – Interface Technical Training Phoenix, AZ 20347: Enabling and Managing Office 365