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.
