Security Overview (Integration Services)
Security in SQL Server Integration Services consists of several layers that provide a rich and flexible security environment. These security layers include the use of digital signatures, package properties, SQL Server database roles, and operating system permissions. Most of these security features fall into the categories of identity and access control.
Identity Features
By implementing identity features in your packages, you can achieve the following goal:
** Ensure that you only open and run packages from trusted sources**.
To ensure that you only open and run packages from trusted sources, you first have to identify the source of packages. You can identify the source by signing packages with certificates. Then, when you open or run the packages, you can have Integration Services check for the presence and the validity of the digital signatures. For more information, see Using Digital Signatures with Packages.
Access Control Features
By implementing identity features in your packages, you can achieve the following goal:
** Ensure that only authorized users open and run packages**.
To ensure that only authorized users open and run packages, you have to control access to the following information:
Control access to the contents of packages, especially sensitive data.
Control access to packages and package configurations that are stored in SQL Server.
Control accesss to packages and to related files such as configurations, logs, and checkpoint files that are stored in the file system.
Control access to the Integration Services service and to the information about packages that the service displays in SQL Server Management Studio.
Controlling Access to the Contents of Packages
To help restrict access to the contents of a package, you can encrypt packages by setting the ProtectionLevel property of the package. You can set this property to the level of protection that your package requires. For example, in a team development environment, you can encrypt a package by using a password that is known only to the team members who work on the package.
When you set the ProtectionLevel property of a package, Integration Services automatically detects sensitive properties and handles these properties according to the specified package protection level. For example, you set the ProtectionLevel property for a package to a level that encrypts sensitive information with a password. For this package, Integration Services automatically encrypts the values of all sensitive properties and will not display the corresponding data without the correct password being supplied.
Typically, Integration Services identifies properties as sensitive if those properties contain information, such as a password or a connection string, or if those properties correspond to variables or task-generated XML nodes. Whether Integration Services considers a property sensitive depends on whether the developer of the Integration Services component, such as a connection manager or task, has designated the property as sensitive. Users cannot add properties to, nor can they remove properties from, the list of properties that are considered sensitive.If you write custom tasks, connection managers, or data flow components, you can specify which properties Integration Services should treat as sensitive.
For more information, see Setting the Protection Level of Packages.
Controlling Access to Packages
You can save Integration Services packages to the msdb database in an instance of SQL Server, or to the file system as XML files that have the .dtsx file name extension. For more information, see Saving Packages.
Saving Packages to the msdb Database
Saving the packages to the msdb database helps provide security at the server, database, and table levels. In the msdb database, Integration Services packages are stored in the sysssispackages table, whereas SQL Server 2000 DTS packages are stored in the sysdtspackages table. Because the packages are saved to the sysssispackages and sysdtspackages tables in the msdb database, the packages are automatically backed up when you backup the msdb database.
SQL Server packages stored in the msdb database can also be protected by applying the Integration Services database-level roles. Integration Services includes three fixed database-level roles db_ssisadmin, db_ssisltduser, and db_ssisoperator 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 or SQL Server 2000 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 Using Integration Services Roles.
Important
For important information about how to secure DTS packages, see Securing DTS Packages Stored in SQL Server.
Saving Packages to the File System
If you store packages to the file system instead of in the msdb database, make sure to secure the package files and the folders that contain package files.
Controlling Access to Files Used by Packages
Packages that have been configured to use configurations, checkpoints, and logging generate information that is stored outside the package. This information might be sensitive and 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 tables in a SQL Server database. Configurations and logs that are saved to SQL Server are subject to SQL Server security, but information written to the file system requires additional security.
For more information, see Controlling Access to Files Used by Packages.
Storing Package Configurations Securely
Package configurations can be saved to a table in a SQL Server database or to the file system.
Configurations can be saved to any SQL Server database, not just the msdb database. Thus, you are able to specify which database serves 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 makes it possible to provide security at the server, database, and table levels. In addition, configurations that are saved to SQL Server are automatically backed up when you back up the database.
If you store configurations in the file system instead of in SQL Server, make sure to secure the folders that contain the package configuration files.
For more information about configurations, see SSIS Package Configurations.
Controlling Access to the Integration Services Service
SQL Server Management Studio uses the SQL Server service to list stored packages. To prevent unauthorized users from viewing information about packages that are stored on local and remote computers, and thereby learning private information, restrict access to computers that run the SQL Server service.
For more information, see Controlling Access to the Integration Services Service.
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services