While it is very common to store SSIS packages created using SQL Server Business Intelligence Development Studio in the file system, it is important to realize that there are other options. This article provides a comprehensive overview of all of the options, including their benefits and drawbacks, focusing on their ability to protect package content from unauthorized changes.
Throughout our presentations of the most important features available in the
latest implementation of SQL Server
Integration Services, we have been relying primarily on Business Intelligence Development Studio-based
projects when creating SSIS
packages. This approach conveniently auto creates the necessary folder
structure, including the XML
formatted .dtsx file,
representing the content of a package, whose visual representation appears in
the Designer interface.
While such simplicity has its appeal, it is important to realize that this is
not the only available package storage option. In this article, we will provide
a comprehensive overview of all of them, including their benefits and
drawbacks, focusing in particular on their ability to protect package content
from unauthorized changes.
In general, there are three different ways of storing SQL Server 2008 Integration Services packages
(this also applies to SQL Server 2008 R2):
file system - the most straightforward storage type, which
so far, we have been using consistently throughout our examples. As
mentioned above, the package definition resides in this case in an XML-formatted file with .dtsx extension. Most typically,
such packages are generated when designing Business Intelligence Development Studio projects
based on the SQL Server Integration
Services template. As we have demonstrated earlier, another
common method that results in creation of .dtsx files involves Import and Export
Wizard (although in this case, it is just as easy to choose the
SQL Server as the
database - considerably more popular in earlier implementations of SSIS (in particular, in its
predecessor known as Data
Transformation Services). This approach utilizes a
collection of dedicated tables to store package content and metadata. The
collection consists of sysssispackages
(hosting packages created in the current version of SQL Server Integration
Services in the packagedata
column of image
datatype - for more information about its format, refer to the Books Online) and sysdtspackages (included for
backward compatibility reasons) along with a number of auxiliary tables,
such as sysssislog, syssispackagefolders, ssydtscategories, sysdtspackagelog, sysdtssteplog, and sysdtstasklog, serving a variety of
SSIS Package Store
- unlike the first two options, it does not constitute a separate
location, but instead functions as a customizable view, representing a
combination of packages stored in the file system (pointing by default to Program FilesMicrosoft SQL Server100DTSPackages)
and the SSIS-specific msdb database tables (listed above)
on the local, default instance of SQL
Server. It is possible to modify its settings by adding
other locations. This is accomplished by modifying the Integration Services configuration
file in the Program FilesMicrosoft
SQL Server100DTSBinn. In particular, you can point to a server
hosting msdb database
or a folder hosting .dtsx
files by assigning (respectively) their names to values of <ServerName> and <StorePath> subcomponents of <Folder> component. Note that
you will need to restart SQL Server
Integration Services service in order for the change to take
effect. For more details regarding this procedure, refer to the Configuring the Integration Services Service
article on the msdn Web site.
Methods involved in securing access to SSIS
packages depend to large extent on the storage type. When using the file
system, protection can be facilitated by leveraging NTFS-based permissions (which implies
that the package store should reside on an NTFS-formatted
volume). The level of safety can be further enhanced by taking advantage of
encryption features built into the operating system, such as Encrypted File System and BitLocker. If .dtsx files need to be accessible via
network, you have an option of combining NTFS-
and share-level permissions.
Your choices are even more elaborate when dealing with packages residing in msdb database. For starters, msdb database includes three pre-defined
fixed database roles intended specifically for controlling access to its SSIS related tables:
restricts permitted actions to viewing, executing, enumerating, and
exporting all packages (effectively preventing all write operations).
grants its members the ability to create or import new packages (which, as
their owners, they can subsequently view, execute, export, and delete) and
enumerate existing ones.
held by default by members of Sysadmins
fixed server role; covers entire range of permissions necessary to fully
manage all packages (which includes executing, creating, enumerating,
exporting, importing, deleting, and viewing each, as well as defining and
changing package roles).
While these fixed roles are sufficient to provide uniform access to all SSIS packages (by associating them with
designated logins using sp_addrolemember
stored procedure or graphical interface of SQL
Server Management Studio), you have an option of defining your
own custom roles in msdb
database to be used in combination with the fixed ones. In this scenario, a
user needs to be assigned to both in order to obtain the desired level of
privileges. Once you have defined such roles, connect to Integration Services on the local
instance of SQL Server using
SQL Server Management Studio,
navigate through the subfolder hierarchy under the Stored Package folder, right-click on
the target package and select the Package
Roles... entry from its context sensitive menu. In the resulting
dialog box, assign the user-defined roles using the Reader Role and Writer Role listboxes.
Regardless of storage type, you also have the ability to protect the content
of packages from unauthorized viewing, even if effective file system
permissions or database roles grant such access. This second line of defense is
known as Package Protection Level,
which gets assigned when writing a package either to the file system or to the sysssispackages table in msdb database (carried out, for example,
when importing or exporting a package in Microsoft
SQL Server Management Studio, when saving a package copy in Business Intelligence Development Studio,
as well as available directly from the Properties
window in its Designer
interface). While this mechanism can be applied to the entire package (for
example, as a means to enforce intellectual property rights), its primary
purpose is to obfuscate confidential data (such as passwords) only. This
happens automatically based on the definition of SSIS components, marking relevant elements with the Sensitive attribute during package save.
In general, you can choose one of the following protection levels:
Do not save sensitive
data - prevents all data with the Sensitive attribute from being
saved, effectively removing it from the package definition. In order to
make the package fully functional, missing data needs to be re-added.
Encrypt all data with
password - uses an arbitrary password provided by the
package designer to encrypt entire package content, applying Triple DES cipher algorithm. The
password is required in order to open, import, export, or execute the
package. Since its content is obfuscated, attempting to view it directly
does not provide any meaningful insight into its structure.
Encrypt all data with
user key - similar to its predecessor, it obfuscates entire
package content, however rather than prompting for a password, it applies
a cryptography algorithm that leverages a master key stored in the Windows
profile of the user who requests encryption. (The master key, in turn, is
encrypted with a value derived from that user's password, following Data Protection APIspecifications).
Reversing this process (which needs to happen in order to view, import,
export, or execute the package) requires access to the same key.
Effectively, this option is not suitable in scenarios in which packages
are developed or executed by multiple users (and warrants special
considerations when running them as SQL
Server Agent jobs). In addition, since the key is protected
with the user's password, resetting it might render the package useless.
data with password - employs the same encryption mechanism
as the Encrypt all with password
level, but applies it only to sensitive data. In order to successfully
execute a package encrypted in this manner you will need to supply the
password (this can be automated by taking advantage of /DECRYPT switch of DTExec utility). Note that the
password is not required in order to open such packages in the Business Intelligence Development Studio,
although, in such cases, none of the protected entries will be preserved.
data with user key - mirrors the behavior of Encrypt all with user key
protection level, but applies it only to sensitive data. Attempting
execution of such packages without access to the symmetric key that was
used to encrypt it (for example, when launching it in the security context
of another user or without access to the profile where the key is stored)
will result in failure. On the other hand, it is possible to open the
package for edits under the same circumstances (although without the
ability to view sensitive data). It is important to realize that this is
the default protection level applied to packages created with Business Intelligence Development Studio.
(To modify it, right-click on the empty area of the Control Flow tab in the SSIS Designer, select Properties item from the context
sensitive menu, and check ProtectionLevel
entry in the Properties
Rely on server
storage and roles for access control - available only when
using SQL Server
storage option, it does not encrypt package content, but instead relegates
this responsibility to the role-based mechanism of msdb database (described above).
Your decision to choose a storage type should be based primarily on
functionality, convenience, and security factors. For example, during package
development, it might be advisable to use .dtsx
files, since Business Intelligence
Development Studio does not natively support direct edits to SQL Server resident packages (otherwise,
you will be forced to export them first via SQL
Server Management Studio). In addition, note that your choice
will affect the backup strategy (packages incorporated into msdb database are part of the regular SQL Server backups, while those hosted
in the file system will need to be included in the operating system backups).