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
Integration Services, we have been relying primarily on
Business Intelligence Development Studio-based
projects when creating
packages. This approach conveniently auto creates the necessary folder
structure, including the
representing the content of a package, whose visual representation appears in
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
.dtsxextension. Most typically, such packages are generated when designing
Business Intelligence Development Studioprojects based on the
SQL Server Integration Servicestemplate. 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 Serveras the package destination).
msdbdatabase - 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
imagedatatype - 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
sysdtstasklog, serving a variety of supporting roles.
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
msdbdatabase 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 Servicesconfiguration file
MsDtsSvr.ini.xmlfile in the
Program FilesMicrosoft SQL Server100DTSBinn. In particular, you can point to a server hosting
msdbdatabase or a folder hosting
.dtsxfiles by assigning (respectively) their names to values of
<Folder>component. Note that you will need to restart
SQL Server Integration Servicesservice in order for the change to take effect. For more details regarding this procedure, refer to the
Configuring the Integration Services Servicearticle on the msdn Web site.
Methods involved in securing access to
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
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
.dtsx files need to be accessible via
network, you have an option of combining
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:
db_ssisoperatorrestricts permitted actions to viewing, executing, enumerating, and exporting all packages (effectively preventing all write operations).
db_ssisltdusergrants 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.
db_ssisadminheld by default by members of
Sysadminsfixed 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
stored procedure or graphical interface of
Server Management Studio), you have an option of defining your
own custom roles in
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
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
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
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
SQL Server Management Studio, when saving a package copy in
Business Intelligence Development Studio,
as well as available directly from the
window in its
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
Do not save sensitive data- prevents all data with the
Sensitiveattribute 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 cipheralgorithm. 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 Agentjobs). In addition, since the key is protected with the user's password, resetting it might render the package useless.
Encrypt sensitive data with password- employs the same encryption mechanism as the
Encrypt all with passwordlevel, 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
DTExecutility). 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.
Encrypt sensitive data with user key- mirrors the behavior of
Encrypt all with user keyprotection 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 Flowtab in the
SSIS Designer, select
Propertiesitem from the context sensitive menu, and check
ProtectionLevelentry in the
Rely on server storage and roles for access control- available only when using
SQL Serverstorage option, it does not encrypt package content, but instead relegates this responsibility to the role-based mechanism of
msdbdatabase (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
Development Studio does not natively support direct edits to
SQL Server resident packages (otherwise,
you will be forced to export them first via
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).