Securing the Content of SSIS Packages

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