Security

The only login option you have to connect to the SSIS service is to use your Active Directory account. After you connect, you’ll only see packages that you are allowed to see. This protection is accomplished based on package roles. Package roles are only available on packages stored in the msdb database. Packages stored on the file system must be protected with a password.

Package roles can be accessed by right-clicking a package that you want to protect and selecting Package Roles. The Package Roles dialog box, shown in Figure 9-12, enables you to choose the role that will be in the Writer Role and Reader Role. The Writer Role can perform administration-type functions (such as overwriting a package with a new version, deleting a package, managing security, and stopping the package from running). The Reader Role can execute and view the package. The role can also export the package from Management Studio.

Figure 9-12: The Package Roles dialog box

Package roles use database roles from the msdb database. By default, people who are in the db_dtsadmin and db_dtsoperator database roles, or the creator of the package, can be Readers. The Writer Role is held by members of the db_dtsadmin database role, or the creator of the package, by default. When you select the drop-down list box in the Package Roles dialog box, you can change the package role from the default one to another customized role from the msdb database.

You may want to customize a group of people who are the only ones who can execute the accounting set of packages. Let’s do a quick example to secure a package to a role called Accounting for the Writer and Reader package roles.

First, open Management Studio and connect to your development or local database engine instance. Then, expand System Databases msdb Security, right-click Roles, and select New Role. This opens the Database Role - New dialog box, shown in Figure 9-13. You will, of course, need the appropriate security to create a new database role.

Figure 9-13: The Database Role - New dialog box

Name the role AccountingExample and make your own login a member of the role by clicking the Add button. Additionally, make your own user name an owner of the role. You may have to add your login as a user to the msdb database prior to adding the role if it’s not there already.

You’re now ready to tie this role to a package. In Management Studio, connect to Integration Services. Right-click any package stored in the msdb database and select Package Role to secure the package. For the Writer and Reader roles, select the newly created AccountingExample role and click OK. Now, only users in the AccountingExample role will be able to perform actions to the package. However, note that if you’re a sysadmin role, you will still be able to perform all functions in SSIS such as executing and updating any package, and bypassing the package role.

If your packages are stored on the file system, then you must set a package password on the package to truly secure the package. You can enforce security as well by protecting the directory with NTFS security on the file or folder where your packages are stored.

To set a package password in BIDS, you can set the ProtectionLevel property to EncryptSensitiveWithPassword and type a password for the PackagePassword property. You can also set a package password using a utility called DTUtil.exe, which is discussed later in this chapter.