Knight's 24-Hour Trainer: Microsoft® SQL Server® 2008 Integration Services

Chapter 44. Securing SSIS Packages

Once you deploy your packages, you are going to want to prevent those who aren't authorized from executing the packages. That's the focus of this lesson. The way you lock down your packages depends on where your packages are installed: the msdb database or the file system.

However, before you can dive into the topic of securing the package execution, you must first understand a few things about connecting to the SSIS service. The only login option for connecting to the SSIS service is to use your Active Directory account. Once you connect, you see only packages that you are allowed to see. This protection is accomplished based on package roles. Package roles are available only on packages stored in the msdb database. Packages stored on the file system must be protected with a password and with Windows security.

Package roles can be accessed in Management Studio by right-clicking a package that you want to protect and selecting Package Roles. The Package Roles dialog box shown in Figure 44-1 allows you to choose the msdb role to be in the writer role and reader role.

• The writer role can perform administration-type functions such as overwrite a package with a new version, delete a package, manage security, and stop the package from running.

• The reader role can execute and view the package. The reader role can also export the package from Management Studio.

Figure 44-1. Figure 44-1

Package roles use database roles from the msdb database. By default, people ...