SQL Server 2005 Integration Services – Security – Part 28

Concern for security permeates all facets of life these days. While this trend can be easily recognized by reviewing practically any major technological change in recent years, its presence is particularly prominent in the area of database management systems. We have presented some of its most relevant characteristics introduced with the release of SQL Server 2005 in several articles published on the DatabaseJournal Web site. We also briefly described a few improvements in this area applicable to SSIS management in a recent installment of this series. Now it is time to cover the remaining features intended to protect confidentiality and integrity of your packages.


In general, SQL Server Integration Services security-related functionality can be grouped into two broad categories, dealing with:



  • protecting package content (as a whole or limited to its more sensitive portions) from edits or unauthorized access,
  • restricting the ability to execute packages (including preventing execution of packages that have been modified in an inadvertent or malicious manner).

As you might recall from our earlier discussions, packages can be saved as entries in sysdtspackages90 table of msdb database or as XML-formatted .dtsx files within file system. (While SSIS Package Store is a particular case of the latter, with Program FilesMicrosoft SQL Server90DTSPackages folder as the default data store location, both are practically identical in the context of protecting package content).


Depending on the destination you select, you will have slightly different types of security options available to you. The primary one is based on the ProtectionLevel package property, which takes on one of six possible values (which we already have introduced earlier): “Encrypt all with password”, “Encrypt all with user key”, “Encrypt sensitive with password”, “Encrypt sensitive with user key”, “Rely on server storage and roles for access control”, or “Do not save sensitive” (which might be appropriate when saving sensitive data in configuration files secured through alternate means, which we will describe shortly). Whenever a package is saved, the appropriate package level is applied (according to your selection, with “Encrypt sensitive with user key” being the default for packages created using Business Intelligence Development Studio).


If you decide to rely on encryption (which is employed by the first four options), you have a choice of using an arbitrarily assigned password or a private key that gets stored in your personal profile for this purpose. The decision should be dictated primarily by the purpose and usage patterns of the package. Passwords work fairly well when packages containing sensitive information are shared by multiple programmers during development stage or by several database administrators following deployment to production environment (password knowledge is required in order to open a package for modifications or to launch its execution). They also offer a convenient way to transfer packages via inherently insecure methods (for example, via Internet or removable media), as long as password transmission is handled separately. They can also be used when running packages as SQL Server Agent jobs (since typically, the account used by the SQL Server Agent Service is different from that of the package creator and hence, it does not have access to the decryption key stored in this user’s profile).


Keep in mind, however, that in such cases the password will appear in clear text as part of the command line associated with the step responsible for package execution (following /DECRYPT switch). To resolve this issue, store packages in the msdb database and take advantage of the “Rely on server storage and roles for access control” option or offload sensitive data to the appropriately protected configuration file (combined with “Do not save sensitive” setting). Encrypting with user keys, while convenient (since typing a password when opening or launching a package is no longer necessary), should be limited to situations where development or production support is performed by the same person and where there is no need for package sharing or scheduled executions.


With both types of protection (key and password based), you can limit the amount of encrypted data to values that are considered to be sensitive only (with “Encrypt sensitive with password” or “Encrypt sensitive with user key” settings) but keep in mind that what exactly belongs in this category (typically passwords, usernames, and connection strings) is determined based on predefined criteria, which you can not modify (although you can develop custom components in order to satisfy your unique requirements). Note that packages with sensitive information encrypted can be opened and launched by unauthorized users, but with the relevant portions automatically removed (which typically results in execution failure).


You can employ the DTUtil.exe Command Line utility to convert one protection level into another (to simplify the process, you can incorporate it in a batch file or a script). More specifically, you need to apply the /ENCRYPT switch with SQL or FILE parameter (depending on the location of the package) and the protection level code, which is expressed as an integer between 0 and 5 (where 0 designates “Do not save sensitive”, 1 “Encrypt sensitive with user key”, 2 “Encrypt sensitive with password”, 3 “Encrypt all with password”, 4 “Encrypt all with user key”, and 5 “Rely on server storage and roles for access control”). For example, the following creates a copy of XFileA.dtsx named XFileB.dtsx encrypting its sensitive information with the password Tru$tN01:

DTUtil.exe /FILE XFileA.dtsx /ENCRYPT FILE;XFileB.dtsx;2;Tru$tN01

When saving packages to msdb database, it is possible to secure them without resorting to encryption (as indicated by the “Rely on server storage and roles for access control” protection level) leveraging instead fixed and custom database roles. The fixed ones, which are relevant from the point of view of SSIS functionality, are db_dtsadmin (with full administrative privileges to all SSIS packages stored in SQL Server), db_dtsoperator (permitted to view, enumerate, launch directly or schedule for execution, and export all SSIS packages – but not import, or delete them), and db_dtsltduser (which members are able to manage packages that they own by virtue of importing them into msdb database or to which they have been explicitly granted permissions). Permissions to load or save an individual package can be modified by associating custom-defined msdb database roles with its Reader or Writer roles (which populates entries in readrolesid and writerolesid columns for this package in the syddtspackages90 table of the msdb database). This is done from the Package Roles dialog box, which is invoked using the context sensitive menu of packages listed under the Stored Packages node in Object Explorer of SQL Server Management Studio. Users can be added to or removed from any of msdb database roles using its Properties dialog box. (To display it, select the Properties item from the context sensitive menu of the node representing the role you want to manage under the DatabasesSystem DatabasesmsdbSecurityRolesDatabase Roles folder in the Object Explorer window of SQL Server Management Studio – after connecting to Database Engine on the target server).


Even though database roles-based security can be assigned only to packages stored in msdb database, it is possible to use a very similar (at least from the conceptual point of view) mechanism when dealing with .dtsx files. NTFS volumes (used almost exclusively by Windows operating system installations these days) offer file and folder level permissions, in the form of Access Control Lists (ACLs), which consist of entries defining the type of activities (including reading and writing) allowed by individual Windows user and group accounts. It is important to note that these permissions can be applied not only to the packages, but also configuration, checkpoint, or log files, which might contain sensitive information you do not want to keep protected. For example, checkpoint files, which characteristics we have reviewed in our recent article, preserve information about the package state including variable values, while log files might include such details as T-SQL statements launched as part of Execute SQL Tasks. In particular, you should pay attention to protecting configuration files since they frequently serve as permanent storage for such information as login credentials (as part of connection strings). You might further increase security of this type of data by loading it into Windows Registry (where Access Control Lists can be used to guard individual keys) – although you should avoid using for this purpose HKEY_CURRENT_USER registry hive, since this would cause problems when running packages in the security context other than that of their creator. Alternatively, you can store configuration parameters (as well as record log entries) in an arbitrary SQL Server database table and protect it with the desired database-level permissions.


While restricting rights to modify and execute packages to designated groups and users gives you a significant degree of protection, you can further enhance it by preventing already running packages from being viewed or stopped by unauthorized users. This functionality is facilitated by SQL Server Integration Services service, which enumerates all packages residing in msdb database as well as SSIS Package Store and monitors their execution (which is represented by the content of Stored Packages and Running Packages folders in the Integration Services node within the Object Explorer window of SQL Server Management Studio). By default, members of local Windows Administrators group have the ability to view and stop all currently running packages (while non-privileged users can perform the same actions for packages they launched). To minimize the potential exploit of this feature, you should limit access to systems where SQL Server Integration Services service is installed and limit to a minimum the number of accounts with administrative privileges.


In cases where the precautions we described above do not suffice and your packages are inadvertently or maliciously modified, you can still protect yourself against their negative impact by taking advantage of digital signatures. We will describe the basics concepts behind this technology as well as its SQL Server 2005 Integration Services implementation in another article of this series.


» 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.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles