The Designing a strategy for Data Distribution module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:

  1. Design security for accessing packages

Exercise 1 - Design security for accessing packages

Design security for accessing 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).

Packages can be saved in three types of storage locations:

  • as entries in the msdb database,
  • as XML-formatted .dtsx files within the file system, or
  • as an SSIS Package Store.

All are practically identical in the context of protecting package content.

SSIS is designed to be secure. One aspect of this is that any “sensitive” information that is part of the package needs to be protected. And by protected, we mean never, ever stored in clear text. Depending on the destination selected, there are slightly different types of security options available. The primary one is based on the ProtectionLevel package property, which takes on one of six possible values:

  • "Don’t save sensitive" (strips out the "sensitive" information, like passwords),
  • "Encrypt sensitive with user key" (encrypts the "sensitive" information like passwords with a specific user key. Forces that user to be the one to run the package. If a different user wants to run the package, the user will have to provide proper credentials),
  • "Encrypt sensitive with password" (encrypts "sensitive" information, like passwords, with an "external" password that is required by ALL users before the package will run),
  • "Encrypt all with password" (encrypts the entire package with an "external" password that is required by ALL users before it will run, or open, or edit, etc.),
  • "Encrypt all with user key" (default - encrypts the entire package with a specific User key which means that only that user can open it, run it, edit it, etc.),
  • "Server storage" (rely on SQL Server storage and roles for access control. Does not perform encryption, but instead leaves protection of the package content to the permissions mechanism built into SQL Server. This option is applicable only when storing the package in the msdb database.)

Whenever a package is saved, the appropriate package level is applied (according to the selection made.)

By default, SSIS uses the Data Protection API (DPAPI) to encrypt any sensitive data with the user key of the user who saves the package (every Windows user has a user key.) Then, when the user attempts to open the package again, the encrypted data is automatically decrypted without any additional effort on the user’s part.

Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.