Like every other feature in SQL Server 2005 Integration Services (SSIS), the product's new security features are noticeably different from their DTS counterparts. SSIS still uses passwords and encrypts sensitive data, but the approach has changed substantially, simplifying automated package execution, protection, scheduling, and editing. SSIS security features fall into five functional categories: encryption, for securing packages or parts of packages; sensitive-data protection, for identifying and protecting passwords and other sensitive data; SQL Server roles, for controlling access to packages stored in SQL Server; digital code signing, for ensuring that a package hasn't changed; and integration of SQL Server Agent subsystems, for securely storing and executing packages. Let's take a closer look at these new security features and get some guidelines for how and when to use them. I'd like to extend special thanks to Sergei Ivanov, the Integration Services team developer who wrote these features, for answering all my questions and making sure I got the details right.
Encryption
SSIS lets you use either a password or the user key to encrypt an entire package or just parts of the package. Like your login password, an encryption password can be any password you create. In contrast, SSIS automatically generates user keys for all the users on a machine and makes the keys part of their profiles. The user key is an opaque number that that few users know about and fewer actually see. Using this number as an encryption key has some advantages and disadvantages that strongly suggest you should use it only in certain ways. Because it's basically transparent to all users, the user key is ideal when a user wants to protect a package that only that user will run. For example, when a developer is creating a package, the user key lets the developer work with a package without needing to repeatedly type in a password. The drawback to encrypting packages with a user key is that other users can't open the package unless they log in with the original package creator's credentials. Using a password is more flexible and can add secondary protection to packages, as I explain in a moment.
Sensitive Data Protection
SSIS lets you designate which of a component's properties are sensitive so that you can differentiate between normal package data and sensitive data. Generally when a property is marked sensitive, that property is a password. But the sensitive attribute can protect any information that the custom component writer deems too sensitive to be stored in clear text. To mark a property as sensitive, the component writes a Sensitive= True XML attribute to the property element when saving the component. SSIS detects that attribute on properties and can give special treatment to a component's sensitive-property values depending on the package-protection setting for the package.
Package-Protection Options
SSIS pulls together the concepts of sensitive-data protection and encryption by providing the six package-protection options you see in the sidebar "SSIS Package-Protection Levels," page 41. Figure 1 shows the dialog box you use to select the appropriate package-protection option whenever you save a package to a file or to SQL Server, whether you're using the SSIS Import/Export Wizard or SQL Server 2005 Management Studio. If you save with one of the "sensitive" options—Encrypt sensitive data with password, Encrypt sensitive data with user key, or Do not save sensitive data—the package XML will be visible in the designer, so you can recover the package if you forget the password or are no longer able to log in as the package's creator. This tidbit is good to know if you're ever concerned about impending layoffs, disgruntled package developers, or just long-term accessibility of packages and you're not too concerned about losing the intellectual property that your packages contain.
Encrypting the entire package is a safer way of protecting everything in the package, but it also carries more risk than protecting only the sensitive data. If a user-key password is lost or the person who wrote the package leaves and you can't log in with the original credentials, the package is lost for good. No one, including Microsoft, can do anything to recover the package in such a situation. For some packages, such a loss isn't a problem, but it's good to be aware of the risks associated with full encryption.
Roles
SSIS adds three new roles that affect the way you run packages in SQL Server Agent and access packages stored in SQL Server. The db_dtsoperator role is the most limited new role. Users in this role can only enumerate (i.e., determine which packages are available) and view existing packages; operators can't create or modify SSIS packages. The db_dtsltduser role lets users create and modify their own packages and enumerate existing packages. And the db_dtsadmin role lets users create, modify, enumerate, and view all packages. Systems administrators are automatically in the db_dtsadmin role.
You must be in one of these three roles to access SSIS packages. If you aren't, and you attempt to enumerate a package by opening the Packages node for the SSIS Server object explorer in SQL Server Management Studio, you'll get the error message that Figure 2 shows.
You can find the new SSIS roles in the Management Studio MSDB database node under the Security, Roles, Database roles node. These roles apply only to MSDB and control access to the sysdtspackages90 table through the stored procedures that Figure 3 shows. You can find sysdtspackages90 and the stored procedures for accessing it in MSDB.
If you look at the sysdtspackages90 table in MSDB, you'll see two columns of particular interest to this discussion. The readrole column specifies the logins or roles that have read access to the package. Readers can enumerate, execute, and export a package from SQL Server. The writerole column specifies the logins or roles that have write access to the package. Writers can delete, rename, edit, and save a package to SQL Server. By default, these two columns are null.
To open the Package Roles dialog box, right-click a package in the Packages node and select Package Roles, as Figure 4 shows. Figure 5 shows the Package Roles dialog box default settings. When the readrole column is null, all users in any SSIS package role can enumerate and read that package. When the writerole field is null, only users in the db_dtsadmin role and the package's creator can modify or delete the package. To prevent a role from viewing and executing a given package, you must remove the role from the Reader Role comma-separated list. You can also add other roles to the reader and writer role lists. For example, I can add the Agent User, Limited User, and Security Administrator roles to the package reader role by entering them in a comma-delimited list as SQLAgentUserRole, db_dtsltduser, and db_securityadmin. I can also assign users and roles to a package's writer role to let them perform write operations such as DELETE and MODIFY to that package only.
To better understand how these roles interact and see which role has what rights, take a look at Table 1, which shows the rights assigned to each role and the activities that a user in each role can do by default. The table shows that the db_dtsoperator role can't work with its own packages; this limitation exists because the db_dtsoperator role can create packages, so that role can't own any. The table also shows that the Writerole db_stsoperator can't import packages because you can't have a write role on a non-existent package. The Assigned User isn't really a role, but I included it because you can assign a SQL login or role to a package reader or writer role. For example, by default, the db_dtsoperator isn't assigned to the writer role and doesn't have write privileges for packages. However, operators you explicitly place in the writer role will have all write-oriented privileges for that package.
By now your head is probably swimming in roles, but you need to know about one more new role in SQL Server 2005: the SQLAgentUserRole. A user must be in this role to create SQL Server Agent jobs, and users in this role can manage only the jobs that they create. This role is important because it affects a common SSIS usage scenario. After they're built, tested, and deployed, many packages reside on a SQL Server box in a back room somewhere, and a SQL Server Agent job triggers them when they need to run. Because SQL Server Agent integration is important to the security of such packages, let's take a closer look at how it works.
SQL Server Agent Integration
SQL Server 2000 has one SQL Server Agent proxy account. Once enabled, any SQL Server Agent user can set up and execute job steps for the CmdExec subsystem. In addition, DTS doesn't provide good support for unattended execution scenarios. To execute DTS packages with passwords, you have to encrypt the entire command line and paste it into the parameters for CmdExec to pass to DTSRun.exe. To eliminate such problematic practices, Microsoft introduced multiple SQL Server Agent subsystems, which are dedicated environments for executing particular technologies such as ActiveX Scripts, Analysis commands or queries, and SSIS packages. By default, only the sysadmin has rights to create jobs. To grant other users the rights to create jobs, the sysadmin creates proxy accounts, which are essentially wrapped credentials that have SQL Server Agent subsystem associations. Proxy accounts let sysadmins grant job-creation permissions to other users while finely controlling the kinds of jobs those users can create.
Another problem in SQL Server 2000 DTS shows up when a user deploys a working package to the server but the package fails with errors showing that it didn't have access to certain resources. SQL Server 2005 Agent provides a new Run as capability that lets a job step run under the credentials of a user or proxy. This capability means a developer can now create a package under the same credentials that it runs under on the server in SQL Server Agent.