Securing DTS Packages Stored in SQL Server
For packages that were created in Microsoft SQL Server 2000 Data Transformation Services (DTS) and that are stored in the msdb database, there is a potential security issue. This concern focuses on the system stored procedures that accomplish most common tasks for these DTS packages. (An example of a system stored procedure that performs a common task is the sp_enum_dtspackages stored procedure that lists the DTS packages that are stored in the msdb database. Another example is the sp_get_dtspackage system stored procedure that loads a DTS package into a client application.) SQL Server 2000 Enterprise Manager, SQL Server Management Studio, and the DTS API all use these stored procedures to accomplish common tasks.
As soon as you understand the potential security issue that is associated with the DTS system stored procedures, you can then take the appropriate actions to reduce that concern.
Important
Data Transformation Services (DTS) is deprecated. For more information, see Data Transformation Services (DTS).
Understanding the Potential Security Issue
In versions of SQL Server ealier than SQL Server 2008, the Execute right on some of the DTS system stored procedures is granted to PUBLIC. By using this setting, anyone who can log on to the msdb database can do the following tasks:
Get a list of DTS packages.
Retrieve the packages themselves.
Save new packages.
However, only the owner of a package can modify an existing package by saving a new version of the package, or delete an existing package. (DTS modifies a package by saving a new version of the package. DTS does not overwrite the current version of the package.)
The potential security issue occurs if an application connects to SQL Server by using a login that owns DTS packages. In this scenario, there is the risk that a SQL injection attack could modify or delete existing packages.
Affected DTS System Stored Procedures
The following list identifies the DTS system stored procedures whose Execute right is granted to PUBLIC and which can cause a potential security issue:
sp_add_dtspackage
sp_drop_dtspackage
sp_dump_dtslog_all
sp_dump_dtspackagelog
sp_dump_dtssteplog
sp_dump_dtstasklog
sp_enum_dtspackagelog
sp_enum_dtspackages
sp_enum_dtssteplog
sp_enum_dtstasklog
sp_get_dtspackage
sp_get_dtsversion
sp_log_dtspackage_begin
sp_log_dtspackage_end
sp_log_dtsstep_begin
sp_log_dtsstep_end
sp_log_dtstask
sp_make_dtspackagename
sp_reassign_dtspackageowner
DTS System Stored Procedures that Are Not Affected
The following list identifies the DTS system stored procedures whose Execute right is not granted to PUBLIC:
sp_add_dtscategory
sp_drop_dtscategory
sp_enum_dtscategories
sp_modify_dtscategory
sp_reassign_dtspackagecategory
Although these system stored procedures have "dts" in their name, these procedures do not pose the potential security issue that this topic describes.
Reducing the Potential Security Issue
To increase the security of DTS packages, SQL Server 2008 has changed the default settings in the following circumstances:
New installation. When you perform a new installation of SQL Server 2008, the Execute right on the DTS system stored procedures is revoked for PUBLIC. Permissions to manage and run DTS packages are given only to and through the following Integration Services database-level roles:
db_ssisadmin
db_ssisltduser
db_ssisoperator
If you later import DTS packages into the msdb database in this instance of SQL Server 2008, only accounts that belong to these Integration Services roles will be able to manage and run the DTS packages successfully.
Upgrade. When you upgrade an earlier version of SQL Server to SQL Server 2008, the Execute right on the DTS system stored procedures is not revoked for PUBLIC. This preserves compatibility with existing applications. Upgrade also grants Execute permissions to the three Integration Services database-level roles that are listed earlier in this topic. As soon as possible, a system administrator should run the stored procedure that is described in the following section to revoke the Execute right from PUBLIC.
For information about Integration Services roles, see Using Integration Services Roles.
Using the New Stored Procedure sp_dts_secure
SQL Server 2008 includes a new system stored procedure, sp_dts_secure, for managing the security of DTS packages. This procedure has one required input parameter. This parameter either revokes or grants public Execute permissions on the DTS system stored pocedures:
To revoke public Execute permissions, and to grant access only to and through the Integration Services database-level roles, run the sp_dts_secure procedure with the parameter of 1:
sp_dts_secure 1
To restrict access to the DTS stored procedures to authorized users, run this procedure soon after you upgrade and apply the Integration Services database-level roles.
Note
When you perform a new installation of SQL Server 2008, Setup performs two actions on the DTS system stored procedures. Setup first revokes the Execute right for PUBLIC. Then, Setup grants Execute rights to the Integration Services database-level roles that are listed earlier in this topic. However, when you manually run sp_dts_secure 1, this stored procedure only revokes the Execute right on the DTS system stored procedures for PUBLIC. You must apply the Integration Services database-level roles separately.
To grant public Execute permissions on the DTS stored procedure, run the sp_dts_secure procedure with the parameter set to 0:
sp_dts_secure 0
You can use this procedure in a new installation scenario if you have to preserve compatibility with existing applications while you prepare to re-enable the security restrictions.
|
.gif "Integration Services icon (small)")
Stay Up to Date with Integration Services