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