Security Considerations for Integration Services
Security in SQL Server 2005 Integration Services (SSIS) is comprised of several layers that provide a rich and flexible security environment. Integration Services security combines the use of package level properties, SQL Server database roles, operating system permissions, and digital signatures.
SQL Server 2005 Integration Services (SSIS) implements security on the client and on the server using the following security features:
- Setting the ProtectionLevel property of the package to specify whether sensitive data should be protected by encryption, or whether the data should be removed before saving the package by.
- Setting the ProtectionLevel and PackagePassword properties of the package to protect packages by encrypting all or part of a package using passwords or user keys.
- Controlling access to packages by using SQL Server database-level roles.
- Securing the operational environment by protecting file locations and limiting access to packages in SQL Server Management Studio.
- Guaranteeing the integrity of packages by signing packages with certificates.
Sensitive Data
Integration Services supports the protection of sensitive data in packages. Typically, properties that are identified as sensitive contain information such as a password or a connection string. Integration Services sets the sensitive attribute of these properties and the sensitive attribute cannot be changed. If you write custom tasks, connection managers, or data flow components, you can specify which properties should be treated as sensitive by Integration Services.
Integration Services automatically detects sensitive properties and handles these properties according to the specified package protection level. For example, in a package that uses a protection level that encrypts sensitive information with a password, the values of all properties that have been identified as sensitive are encrypted.
Package Protection Levels
You can encrypt packages to help keep their property values secret by setting the protection level of the package. Packages include the ProtectionLevel property, which you can set according to the level of protection your package requires. For example, in a team development environment, a package can be encrypted using a password that is known to the team members who work on the package. For more information, see Setting the Protection Level of Packages.
For more information, see Integration Services Packages and Setting Package Properties.
Database-Level Roles
Integration Services includes the three fixed database-level roles db_dtsadmin, db_dtsltduser, and db_dtsoperator for controlling access to packages. A reader and a writer role can be associated with each package. You can also define custom database-level roles to use in Integration Services packages. Roles can be implemented only on packages that are saved to the msdb database in an instance of SQL Server.
For more information, see Integration Services Roles.
Package Storage
Integration Services packages can be saved to the file system as XML files, using the .dtsx file name extension, or to the msdb database in an instance of SQL Server 2005.
Saving the packages to msdb provides security at the server, database, and table levels. SQL Server 2005 packages are stored in the sysdtspackages90 table, and SQL Server 2000 packages are stored in the sysdtspackages table. In addition, packages that are saved to sysdtspackages90 and sysdtspackages are automatically backed up when you backup msdb. SQL Server 2005 packages stored in the sysdtspackages90 table are also protected by database-level roles.
If you do not store packages in msdb, make sure to secure the folders in the file system that contain package files.
For more information, see Saving Packages.
Package Configuration Storage
Package configurations can be saved to the file system or to a table in a SQL Server 2005 database. Configurations can be saved to any SQL Server 2005 database, not just the msdb database, allowing you to specify the database to serve as the repository of package configurations. You can also specify the name of the table that will contain the configurations, and Integration Services automatically creates the table with the correct structure.
Saving the configurations to a table provides security at the server, database, and table levels. In addition, configurations that are saved to SQL Server are automatically backed up when you backup the database.
If you do not store configurations in SQL Server, make sure to secure the folders in the file system that contain the package configuration files.
For more information, see Package Configurations.
Integration Services Folders
SQL Server Management Studio uses the SQL Server service to list running packages. It is important to restrict access to computers that run an SQL Server service to prevent unauthorized users from enumerating stored packages on local and remote and learning private information. For more information, see Protecting Access to Running Packages.
Files Used by Packages
Packages that have been configured to use configurations, checkpoints, and logging generate information that is stored outside of the package. This information may be sensitive and it should be protected. Checkpoint files can be saved only to the file system, but configurations and logs can be saved to the file system or to SQL Server database tables. Configurations and logs that are saved to SQL Server are protected by SQL Server security, but information written to files requires additional security. For more information, see Protecting Files Used by Packages.
Digital Signatures
You can sign a package with a certificate. You can configure the package to check the signature when the package is loaded and to issue a warning if the package has been altered. Packages include the CheckSignatureOnLoad property, which you set to True to require that the digital signature on the package be checked every time that the package is loaded. For more information, see Signing Packages with Certificates.
See Also
Tasks
Configuring a Windows Firewall for Integration Services Access
Concepts
SQL Server Integration Services