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.

Integration Services icon (small) Stay Up to Date with Integration Services

For the latest downloads, articles, samples, and videos from Microsoft, as well as selected solutions from the community, visit the Integration Services page on MSDN or TechNet:

For automatic notification of these updates, subscribe to the RSS feeds available on the page.