Security in SSIS

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.

Digital Signing

Digital signing is a new feature in SSIS that lets you sign a package with a code-signing certificate. With this certificate, you can detect whether a package has changed in some way. Whether the change was malicious or inadvertent, digital signing lets you ensure that the package you run today is the same package you deployed yesterday.

However, systems don't remain static over time. Hard drives go out, machines become deprecated, and database names and locations change. You might think that updating a package to reflect those changes means you'll need to sign the package again. But if the package is designed to effectively use package configurations, you won't need to re-sign the package. Because you create configurations after load time, changing configurations to reflect changes in your system and keep packages running correctly won't corrupt the package signing. Thus, digital signing ensures that the package remains untouched, and configurations isolate the package from the constantly changing environment.

Putting It All Together

Now that I've explained SSIS's new security features, let's look at how to use them in different business situations. The following are some typical scenarios a DBA might want to enable.

Tier-three operations. Many large organizations (e.g., 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

Creating packages whose contents are invisible to sysadmins. A common business problem arises when users who are in different companies or are in a different company than the DBA want to share information. 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 want to protect their passwords so that nobody outside their group can view their packages, be sure that

Packages running on only one machine. Say 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 save the sensitive packages to SQL Server or 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 the Run as capability 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. Also be sure that you 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 won't be readable even if others attempt to view it, replacing the roles with the name of the account hides the packages from other users.

Simplify Your Packages

The new security features in Integration Services simplify all the ways you work with packages. Better integration with SQL Server Agent yields finer control and granularity for configuring Agent package-execution schedules. And more options for encrypting the package make it possible to custom fit your package security to the requirements of your operation.