Microsoft SQL Server 2005 Integration Services

The security features in Integration Services have been designed much like the rest of the product. They have been factored to make it possible to support a wide range of customer requirements. Let's look at how to use the SSIS security features in different business scenarios.

Tier-Three Operations

Many large organizations (for example, MSN) encompass multiple operation tiers. Tier-one operators are typically less-technical employees who use step-by-step instructions for every operation. If a problem arises, they pick up the phone and call another tier for help. Tier-two or tier-three employees know systems more intimately and can usually troubleshoot common problems. Tier-three employees are typically quite technical; they might have created the systems, can diagnose difficult problems, and might be on call for troubleshooting serious problems.

Imagine that a tier-one operator, who shouldn't have write access to packages, needs the ability to change the SQL Server Agent schedules and sometimes the jobs that run the packages. The operator also needs to be able to run all the packages. You can give the operator the necessary access by making sure that

• All packages are stored in SQL Server.

• The packages' Reader role is db_dtsoperator.

• The packages' Writer role is NULL, which is the default access for db_dtsadmin.

• The operator is in the SQLAgentUserRole and the db_dtsoperator role.

• The operator is using any OS login credentials, including those in only the user role.

Segmenting Access to Packages Stored in SQL Server

As an observant reader, you will notice that only one role can be specified for the Reader and Writer roles. This is fine for simple access topologies in which all users fit into one of the three standard roles, but what if you want to segment access to classes of packages? For example, suppose you have two groups in your organization, the financial services group and the business intelligence support group, and you want each group to have write access to their own set of packages.

The solution is to create two new user-defined roles called db_finance_package_writers and db_bi_package_writers, one for each class of packages. Each role should be a member of the db_dtsoperator role and then specified as the Reader and Writer role for each package in the given class. So, for all packages that are only to be modified and executed by individuals in the financial services group, they should have as their Reader and Writer role, db_finance_package_writers. Likewise, all business intelligence support group packages should have db_bi_package_writers specified as their Reader and Writer roles.

Using this role topology, sysadmins and users in the db_dtsadmin roles can still read and write all the packages. But only those users in the db_finance_package_writers role can modify, enumerate, or execute the finance class of packages and, likewise, only the users in the db_bi_package_writers role have the same rights on the business intelligence class of packages.

Creating a User-Defined Role

The following steps show how to create a user-defined role.

1. Open SQL Server Management Studio.

2. Click Object Explorer on the View menu.

3. On the Object Explorer toolbar, click Connect, and then click Database Engine Services.

4. In the Connect to Server dialog box, provide a server name and select an authentication mode. You can use a period (.), (local), or localhost to indicate the local server.

5. Click Connect.

6. Expand Databases, System Databases, msdb, Security, and Roles.

7. In the Roles node, right-click Database Roles, and click New Database Role.

8. On the General tab, provide a name and, optionally, specify an owner and owned schemas and add role members.

9. Optionally, click Permissions and configure object permissions.

10. Optionally, click Extended Properties and configure any extended properties.

11. Click the OK button.

Creating Packages Whose Contents Are Invisible to Sysadmins

This typical ISV scenario occurs between users who aren't necessarily in the same company or aren't in the same company as the database administrator. Imagine that some employees are creating packages that contain sensitive data and intellectual property that they don't want to be visible to any other user or administrator. For example, the sysadmin might be a contractor or vendor in your company. Or, you might be saving packages to a SQL Server on your ISP's systems. If your customers are dealing with sensitive data and want to protect their passwords and be assured that nobody outside their group can view their packages, be sure that

• All packages are stored in SQL Server or the file system.

• The packages' Reader role is db_dtsltduser so that no other users can open the packages.

• Users save packages by using one of the password-encryption levels; the password is shared among the users who need it, but administrators don't have package access.

Packages Running on Only One Machine

Suppose you're concerned that sensitive packages might be taken from a given location and opened and plundered offsite. In this scenario, you want to ensure that the package can never be viewed or executed except on a given machine under a certain account. To set these restrictions, be sure that you do the following:

• Save the sensitive packages to SQL Server or the file system. When importing the package into SQL Server, use the Encrypt Sensitive Data with User Key option. You must be logged in or running Management Studio with Windows Run As under the same account that the package will be opened or executed under in SQL Server Agent, Development Studio, DTExec.exe, or another such package client.

• Remove all roles from the Reader and Writer roles except the username for the account under which the package will execute or be opened. Although it's not technically necessary because the package is encrypted with the user key and isn't readable even if others attempt to view it, replacing the roles with the name of the account hides the packages from other users.