Securing the Content of SSIS Packages


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
    package destination).
  • msdb
    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
    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 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 MsDtsSvr.ini.xml
    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:

  • db_ssisoperator
    restricts permitted actions to viewing, executing, enumerating, and
    exporting all packages (effectively preventing all write operations).
  • db_ssisltduser
    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.
  • db_ssisadmin
    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 API specifications).
    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.
  • Encrypt sensitive
    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.
  • Encrypt sensitive
    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
    window).
  • 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).

»


See All Articles by Columnist

Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles