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
BI Development Studio, SQL Security, SQL Server, SQL Server Integration Services, SSIS
A Simple Introduction to Cisco CML20 3698 0
Mark Jacob, Cisco Instructor, presents an introduction to Cisco Modeling Labs 2.0 or CML2.0, an upgrade to Cisco’s VIRL Personal Edition. Mark demonstrates Terminal Emulator access to console, as well as console access from within the CML2.0 product. Hello, I’m Mark Jacob, a Cisco Instructor and Network Instructor at Interface Technical Training. I’ve been using … Continue reading A Simple Introduction to Cisco CML2
How to Build in a PSMethod to your PowerShell Code0 68 0
In this video, PowerShell instructor Jason Yoder shows how to add Methods (PSMethod) to your code using free software that’s added into the PSObject. For instructor-led PowerShell courses, see our course schedule. Microsoft Windows PowerShell Training Download the Building Methods PowerShell script</a> used in this video. <# ╔══════════════════════════════════════════════════════════════════════════════╗ ║ ║ ║ Building Methods ║ ╟──────────────────────────────────────────────────────────────────────────────╢ … Continue reading How to Build in a PSMethod to your PowerShell Code
Configuring Windows Mobility Center and How to Turn it On and Off1 1411 1
Video transcription Steve Fullmer: In our Windows training courses, we often share information about the Windows 8.1 Mobility Center. Mobility Center was introduced for mobile and laptop devices in Windows 7. It’s present and somewhat enhanced in Windows 8. Since we don’t have mobile devices in our classrooms, I decided to take a little bit … Continue reading Configuring Windows Mobility Center and How to Turn it On and Off