Professional Microsoft® SQL Server® 2008 Administration

6.4. Applying Security to Integration Services

You have now looked at most of the important package administrative tasks, including creating, managing, deploying, and executing Integration Services solutions. Additionally, you have reviewed the major Integration Services service administrative tasks. This section describes the detailed security options available within Integration Services.

6.4.1. An Overview of Integration Services Security

Integration Services, like all of SQL Server, uses layers of security that rely upon different mechanisms to ensure the integrity of both the design of packages and the administration and execution of packages. SSIS security is found on both the client and the server, implemented with features such as the following:

• Package-protection levels to encrypt or remove sensitive information from the package.

• Package-protection levels with passwords to protect all or just sensitive information.

• Restricting access to packages with roles.

• Locking down file locations where packages may be stored.

• Signing packages with certificates.

Within packages, Integration Services generally defines sensitive data as information such as passwords and connection strings. You are not able to define what should and should not be considered sensitive by SSIS unless you do so within a custom-developed task.

Integration Services defines sensitive information as the following:

• Connection string password (Sensitive) or Whole connection string (All).

• Task-generated XML nodes ...