SQL Server 2012 Integration Services – Implementing Package Security using Access Control

SQL Server 2012 Integration Services offers a wide range of powerful features that allow you to streamline and automate tasks involving data extraction, transformation, and loading. However, incorporating these features into your existing business intelligence framework frequently necessitates additional security measures ensuring that data which is being processed remains protected from unauthorized access. In this article, we will discuss several options that facilitate this objective.

In general, access control within the context of SQL Server-related technologies can be implemented by leveraging several independent security mechanisms. This principle also holds true in regard to SSIS packages and projects (as well as any auxiliary configuration and log data they consume or generate), although specifics depend on their storage and deployment methodology. (As a reminder, starting with SQL Server 2012, there exist two independent deployment models – legacy Package and newly introduced Project). In particular, in order to provide an appropriate level of security, you can choose from the following options:

  • NTFS permissions – allow you to restrict access to package files (in .dtsx format), their configurations, logs, and checkpoints (which record package execution status, including such potentially sensitive data as variable values) residing on an any NTFS-formatted volume (both within and outside of locations controlled by SSIS service). However, if this approach is not sufficient, it is possible to store configurations and logs in SQL Server tables (in an arbitrary database) instead and control their access using any of the methods described below . (Unfortunately these alternatives are not applicable to checkpoint files, which must be saved to file system).
  • SQL Server specific permissions– rely on primarily database-level roles (but also facilite security on the server and table levels). This approach gives you the ability to protect packages and their configurations, which are stored in either MSDB database (in the case of legacy Package Deployment Model) or SSISDB catalog (when relying on theProject Deployment Model).

    When using the Package Deployment Model, you have an option to restrict access to packages by leveraging fixed database-level roles pre-defined in the MSDB database, including db_ssisadmin (with administrative rights to all locally stored SSIS packages), db_ssisltduser (with the ability to execute SSIS packages to which permissions have been granted), and db_ssisoperator (allowed to execute, backup, and restore all SSIS packages). It is also possible to customize package-level roles that grant (respectively) read and write access, named Reader (including, by default, db_ssisadmin, db_ssisoperator, and a package creator) and Writer (including, by default, db_ssisadmin and a package creator) by using the Package Roles dialog box, displayed by selecting the Package Roles… item in the context sensitive menu of individual packages stored in MSDB database (represented by the MSDB subfolder of Stored Packages folder within Integration Services node) accessible from Object Explorer in SQL Server Management Studio (and available after connecting to Integration Services).

    When using Project Deployment Model, you have an option to restrict access to individual projects (along with the packages contained within them) by utilizing standard SQL Server-level logins and database roles. This functionality is available directly in the Permissions section of the Properties dialog box for each project (located within the SSISDB subfolder of the Integration Services Catalog folder within SQL Server node) accessible from Object Explorer in SQL Server Management Studio (available after connecting to Database Engine).

  • Package Level Encryption – based on the Protection Level property of a package, determines the extent to which its content is obfuscated and the mechanism used to implement it. This might apply to the entire package or be limited only to information deemed to be sensitive, such as passwords included in data connection strings, XML nodes generated by tasks, or variables that have been designed by their creators (either Microsoft or, in case of custom components, third-party developers) with the Sensitive attribute. (Note that this attribute is preassigned and can not be arbitrarily changed during package creation or editing). There are six different protection levels:
    • Do not save sensitive – removes sensitive information when the package is being saved. When the saved copy is opened, removed values (such as passwords) need to be added again in order to restore the package to its original state. This also means that its execution (without reverting changes applied during save) is likely to fail.
    • Encrypt all data with password– encrypts the entire package with a password specified during save using the Triple DES cipher algorithm with 192-bit key. Knowledge of the password is required in order to execute the package or to open it for editing in SQL Server Data Tools.
    • Encrypt all data with user key– similar to the previously described level, encrypts the entire package but instead of using a password, makes the process transparent by applying a user-specific key stored securely in the user’s personal profile (encryption employs Microsoft Data Protection API). However, it is important to remember that this also means that reopening the package in SQL Server Data Tools or executing it must be performed not only by the same security principal (an interactive user or a service) but also requires access to a copy of the personal profile. As a result, this level is not suitable for shared development efforts or transferring packages between systems (in such scenarios, employing password-based encryption is a more appropriate option).
    • Encrypt sensitive with password– uses the same encryption mechanism as the Encrypt all with password level, but applies it only to information that is considered to be sensitive. This also means that in order to successfully execute a package encrypted in this manner you will need to supply the password (otherwise, the execution will fail). On the other hand, you will be able to open such packages in SQL Server Data Tools even without password knowledge, but during this process, the protected information will be automatically removed.
    • Encrypt sensitive with user key– uses the same encryption mechanism as the Encrypt all with user key level, but applies it only to sensitive information. Execution of such packages without the encryption key being available (i.e. either by another user or by the original user without access to a copy of the personal profile) is bound to fail. It is, however, possible to open such packages for edits in the same circumstances, after all sensitive information is automatically removed. In this case, you will likely encounter warnings in the Error List window in SQL Server Data Tools, stating Failed to decrypt an encrypted XML node. Verify that the project was created by the same user. The project load will attempt to continue without the encrypted information or Failed to decrypt any sensitive data in project with a user key. You may not be the user who encrypted this project, or you are not using the same machine that was used to save the project. If the sensitive data is a parameter value, the value may be required to run the package on the Integration Services server. Note that this is the default protection level applied to packages created with SQL Server Data Tools.
    • Rely on server storage and roles for access control – does not perform encryption, but instead relegates the responsibility for securing the package content to a protection mechanism available in SQL Server Integration Services. This is the default setting applied when using Project Deployment Model to store packages in the SSISDB catalog. In such scenarios, Integration Services automatically encrypts packages during deployment, leveraging the password that you were prompted to provide when creating the catalog in SQL Server Management Studio (more specifically, that password was used to protect an autogenerated private key, which in turn is employed when encrypting packages being added to the catalog). Note that the key should be backed up (by using BACKUP MASTER KEY Transact-SQL statement, as described in SQL Server Books Online) since it might be required if the catalog is moved to another SQL Server instance. If you decide to export a project (into an .ispac file) from SSISDB catalog, its protection level is automatically set to EncryptSensitiveWithUserkey.

      When employing Package Deployment Model to import packages into MSDB database or SSIS Package Store you are prompted to specify the protection level that will apply to it (via the context sensitive menu of the Stored Packages subfolder of the Integration Services node in the Object Explorer window of SQL Server Management Studio). While the default is set to Keep protection level of the original package, you also have the ability to change it to any of the settings listed above (including Rely on server storage and roles for access control).

ProtectionLevel is a package-level property, set by default to EncryptSensitiveWithUserKey (in both Package Deployment Model and Project Deployment Model). You can change it to any of the values listed above during design stage (with the obvious exception of Rely on server storage and roles for access control). To apply the change (or examine the current configuration), while running SQL Server Data Tools, right-click on the empty area of the Control Flow tab in the Designer window, select the Properties item from the context sensitive menu, and scroll down to the ProtectionLevel entry in the Properties window).

Alternatively, you can accomplish the same objective by taking advantage of the dtutil.exe command line utility, which, in addition, facilitates automating encryption and decryption process for multiple packages. Its syntax in this case involves the use of the /ENCRYPT switch, combined with an integer representing a specific protection level, according to the following listing:

  • 0 – 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
  • 5 – Rely on server storage and roles for access control

This concludes our overview of the access control mechanisms available in SQL Server 2012 Integration Services. In our upcoming articles we will turn our attention to other SSIS security related features, including package signing.

See all articles by 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