SQL Server 2000 Security - Part 11 - DTS Security
September 14, 2004
In this article of our series presenting SQL Server 2000 Security, we are turning our attention towards Data Transformation Services. If you are not familiar with this topic, it might be helpful to first review its coverage on the DatabaseJournal Web site. The referenced information gives a fairly comprehensive discussion of its principles and the majority of its features - we will focus here strictly on the security related aspects of this technology.
The primary purpose of Data Transformation Services is simplifying transfer of data between different types of data stores, accommodating at the same time its potential modifications. The number of possible configurations supported in SQL Server 2000 DTS implementation is impressive, covering any OLE DB and ODBC compliant data stores and a large number of customizable transformations, including execution of executables, T-SQL or VBScript based scripts. All of the components constituting a single logical unit of work (such as connections to data stores or tasks performed during transformation) can be combined together and saved for later execution or modifications into a DTS package into four different locations (SQL Server, Structured Storage File, Visual Basic File, or Metadata Services), each with unique set characteristics.
In the first case, an entry corresponding to each new package (or a new version of a modified package) is created in the sysdtspackages table of the MSDB database, which contains such information as, for example, package name, unique identifier, owner information, creation date, and package parameters in the binary format. (There are also other associated tables, such as sysdtspackagelog, sysdtssteplog, sysdtscategories, or sysdtstasklog, which store information on package execution, providing that the "Log package execution to SQL Server" property has been set - this is done from the Logging tab of the DTS package Properties dialog box). Information stored in the DTS related tables provide the ability to access, modify, and execute the package from within the SQL Server Enterprise Manager window (using Data Transformation Services node). If, besides these capabilities, you want to be able to transfer a package easily between servers, you should consider the possibility of saving your package into a Structured Storage file. This results in the creation of a .DTS file, which can be subsequently copied to another computer and accessed with the Open Package... option from the Data Transformation node, (although you can also save your package to another SQL Server directly using the Save DTS Package dialog box from the DTS Designer interface, as long as the target server, is available at that moment). Using Visual Basic File format allows you to manage packages in the form of .BAS file within Visual Basic IDE (Integrated Development Environment) and enhancing their functionality by manipulating the DTS object model with programming methods. In order to save a package to Meta Data Services, you need to first enable this option. This is done from the Package Properties dialog box, displayed after selecting the Properties item from the context sensitive menu of the Data Transformation Services node in SQL Server Enterprise Manager. From there, you need to mark the checkbox labeled "Enable Save To Meta Data Services". This will activate another option in the Location listbox when saving a DTS package. The benefit of storing packages this way is the ability to keep track of metadata information, such as version and lineage for each package. We will be exploring this functionality in more details in our next article.
The most common way to create DTS packages is with the DTS Designer graphical interface, accessible from the SQL Server Enterprise Manager (under Data Transformation Services node). Permissions to carry this task are granted by default to all logins, regardless of database level permissions (although ultimately, level of privileges required to execute a package depends on the way in which execution takes place and the degree of protection on resources that need to be accessed). This results from the fact that all stored procedures involved in package creation reside in MSDB database with "Execute" permissions granted to the public database role. Since MSDB database contains the Guest account, anyone logged on to SQL Server, can take advantage of it. This also means that without extra precautions, all logins are allowed to open existing packages, examine their content, modify them, and, if desired, save changes using a new name (the ability to overwrite existing packages is limited to members of SysAdmin server role and original package creator).
If you want to alter this default behavior and restrict the ability to create new or modify existing packages, you can modify permissions on the sp_add_dtspackage, sp_enum_dtspackages, and sp_get_dtspackage stored procedures in the MSDB database. The first one contains T-SQL code for creating DTS packages, the second one provides ability to list them and the third one is used for retrieving them. By removing permissions to run them from the public role, you can restrict these activities to specific fixed or custom database roles.
Another possibility to secure packages saved to SQL Server or a Structured Storage file (but not to Meta Data Services or a Visual Basic file) is by assigning owner and user passwords. This is done from the DTS Designer interface, by selecting the Save As... item from the Package top level menu. In the resulting "Save DTS" Package dialog box, you can type in the owner and user password (note that you cannot set a user password without defining the owner password). An owner password secures opening and modifying the content of a package; knowledge of the user password allows its execution (you are prompted for them whenever you try to execute or open the package to which they were assigned). Keep in mind that there is no direct way to change these passwords. The only way to accomplish this is to save the package using a different name, then delete original package, and finally rename back the new one (this is clearly much more complicated than it should be).
Each package has an owner, which is the login that created it (or saved an existing one to a new server - when transferring packages between computers). Identifying information is recorded in the syspackages table of the MSDB databases in the owner and owner_sid columns. Only package owners (and members of Sysadmin fixed server role) have the ability to alter packages and save them with the same name (they are still, however, required to provide passwords in order to open or execute their packages).
Separate security-related considerations come into play when executing packages. This can be done either directly from the DTS Designer interface in SQL Server Enterprise Manager (using the Execute item from the top level Package menu or with the F5 key), with DTSRun command line utility (as well as its graphical interface version DTSRunUI), or by running compiled Visual Basic code. Regardless of the method, permissions required to complete execution successfully are evaluated at run time. In general, the outcome of this evaluation depends on the type of authentication method designated for accessing various package components (for example, in order to extract or store data, it is necessary to provide either Windows token or other, data source-specific credentials). When a package is launched interactively, it runs in the security context of the logged-on account. If Windows integrated authentication is implemented, the permissions granted to this account determines whether the package will complete or fail (and is not affected in any way by the level of privileges held by the package creator). However, if data store connection information has been recorded within a package (which might be the case with SQL Server Authentication), this could allow less-privileged accounts to query or manipulate objects to which they typically have no access. You need to consider carefully which type of authentication suits your environment and security requirements.
You can also schedule execution of DTS packages using SQL Server Agent jobs. This ability is granted by default to the public group, based on permissions to execute sp_add_job and sp_add_jobschedule stored procedures in the MSDB database (this applies to all SQL Server Agent jobs, not just the ones that invoke DTS packages). Typically, it is recommended to restrict access to them to a limited number of privileged logins. While jobs typically execute in the security contexts of their owner, in the case of DTS packages this might be different. The reason is the fact that a task that contains invocation of a DTS package accomplishes this by the running DTSRun command line utility, which involves, in turn, the use of CmdExec. If you limit permissions to launch CmdExec jobs to members of the SysAdmin fixed server role (which is the default behavior), jobs will still execute in the same security context as their owners. However, you can change this default in the SQL Server Agent Properties dialog box, accessible by selecting the Properties option from the SQL Server Agent node under the Management folder in the SQL Server Enterprise Manager console. In the Job System tab, you need to clear the checkbox next to the "Only users with SysAdmin privileges can execute CmdExec and ActiveScripting job steps" and provide a Proxy Account name and password. The proxy account provides the security context for execution of DTS packages, so ensure that it has access to all relevant data stores, file system paths or COM components, which might be used by ActiveX scripting tasks. Remember, however, that at the same time you allow users without SysAdmin privileges to run potentially dangerous ActiveScripting jobs.
What further complicates scheduling DTS packages is the fact that content of the corresponding SQL Server Agent jobs includes SID of the SQL Server Agent account as part of the encrypted string passed to the DTSRun command. This creates a problem if the password of the SQL Server Agent account is changed, since it forces you to recreate each package (existing packages will fail due to password mismatch). Also, keep in mind that the SQL Server Agent service needs to be running in order for scheduled job to be launched.
In the next article of this series, we will delve deeper into one of the topics mentioned earlier - Meta Data Services and its auditing capabilities.