Knight's Microsoft SQL Server 2012 Integration Services 24-Hour Trainer

Chapter 56

Securing SSIS Packages

Once you deploy your packages, you 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 the deployment model. In the package deployment model, the easiest security model is when you deploy to the msdb database. When you deploy your packages with the project deployment model, security is easy and more robust.

Securing Packages in the Package Deployment Model

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, which is available to you if you choose to use the package deployment model. 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 filesystem must be protected with a password and with Windows security.

You can access package roles 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 56-1 enables you to choose the msdb role to be in the writer role and reader role:

Figure 56-1

• The writer role ...