SQL Server SSIS security comprises of different layers that offers completely secured environment to these services. These layers consist of the following components:

1. Digital Signature
2. Package Properties
3. Database roles
4. Operating System Permissions

These components; when used together for implementing security measures, serves as a protective shield for the SQL packages.

To understand the concept of SSIS Security, it is important to understand the SQL Server Integration Services platform first. Here in the upcoming section, the information about SSIS platform has been discussed along with the attributes that it offers in SQL Server environment.

Understand the Concept of SQL Server Integration Services

SQL Server Integration Services is an important component associated with SQL Server normally used to execute wide variety of data migration operations.

This platform is been designed; taking into consideration the following two elements:

• Data Integration:An approach to combine data from distinct resources; hence presenting it into a unified and an organized manner.
• Workflow Application: Software applications that automate processes to some extent and sometimes require manual approval or customization\ modification of activities.

It allows performing following functions at higher level:

• Retrieving data via any source as well as load data components to any source along with defining workflow.
• Performing wide series of operations on data such as executing calculations; conversion to different types, and many more.

SSIS Security:An Overview

There are some concepts related to the SSIS Security concept; such as always use trusted mediums to launch the packages. In addition, before opening the package, identify the package source and this can be done via assigning certificates to the packages.

The benefits of assigning identity features to the packages are that unauthorized access to sensitive data can be controlled. It ensures control over configuration of packages stored under SQL Server. Moreover, Logs, configurations as well as checkpoint files can be secured. This platform offers integrity and security to the package’s information that is displayed by Integration Services in SQL Server Management Studio.

How to Detect Packages that are assigned with Digital Signatures?

In SQL Server, it is important to identify the packages that are assigned with digital signatures. To check the assigned digital signatures to a package;either of the following measures can be implemented:

• Set the “Check digital signature when loading a package” in ‘SQL Server Data Tools’ and this setting must be assigned at the design time prior to loading the specified package.
• Specify “/VerifyS[igned]”; when “dtexec” utility is being used for running the package.
• “BlockedSignatureStates” registry value can also be used; as it prevents the packages from uploading if it contains unauthorized or invalid signatures.

Description over SQL Server Integration Services Security Platform

In the upcoming sections, the various aspects related to Integration Services security are discussed in more descriptive manner to get better understanding over the features and functional measures offered by SQL Server Ssis Security concept.

Control Access over Data Components of Packages:

In order to restrict or limit the access to packages or any of its components; the packages are encrypted through applying “Protection Level”property to them. The level of security can be set as per requirement.

When these setting are assigned to the packages, the sensitive properties are automatically detected by the Integration Services. Further, these services handle or operate the properties as per the level of protection.

For the properties assigned to the sensitive data, the values are automatically encrypted by the Integration Services. The encrypted codes are assigned to the data. The encrypted data is displayed only when correct password is provided.

Control Access for Packages:

“Integration Services packages” can be stored in SQL Server’s msdb database or as XML files with .dtsx file extension into file system.

When the packages are stored to msdb databases; it helps in providing security to server, tables and databases as the information incorporated into them are crucial. These services in msdb database get stored in ‘sysssispackages’ table.

As in msdb database, ‘sysssispackages’ and ‘sysdtspackages’ tables are used to store the package; so when the database is backed up; it automatically backup the associated packages. Database-level roles can also be assigned to protect the packages stored under the specified database. Each package is also associated to reader and writer level roles.

For controlling the access to the packages; three database roles are applied to the packages; these are

• db_ssisadmin;
• db_ssisltduser; and
• db_ssisoperator

Note Instead of msdb database, if the file system is been used to store the packages; ensure that the files or folders that contain packages are completely secured.

Control Access to Files Containing Packages:

Packages that are assembled to use information associated to log, configurations and checkpoints; displays information stored in outside packages. The information encapsulated into these packages needs to be prevented due to its sensitive nature. There are some factors related to this approach and are mentioned herein:

• Checkpoint files must be stored into file system only.
• Logs and configuration files can be stored using either SQL Server DB tables or file system.
• SQL Server is responsible for the security parameters related to logs and configurations (saved within); whereas the checkpoint files saved under file system needs extra security provisions.

Make Notes:

• Configuration files can be stored to any database existing into SQL Server.
• When the database table containing the configuration settings is specified, the table with accurate structure is automatically created by the Integration Services.
• The configurations when stored in tables, it becomes easier to ensure database, tables and server level security.
• When the database is backed up, the configuration files saved under SQL Server are backed-up automatically.

Control Access to IS (Integration Services) Service

For listing the packages stored under SQL Server Management Studio, it uses the services of SQL Server. The access limitations are assigned, considering the authorization for editing and executing the packages. It is also important to impose access restrictions to the machine on which the SQL Server service runs upon.

Here, access and stop roles to the running packages are assigned to the members of the “Windows Administrative” group; whereas the users who are not the members of the respective group are eligible to access and eliminate the packages that are started by them only.

Conclusion:

Understanding the core aspects of SQL Server SSIS Security platform will help in preventing the packages of SQL Server from unauthorized access, via implementing appropriate security measures. The set of tools and services are used to construct, run and manage the packages. Moreover, the graphical presentationof the “Integration Services” service tool; rather than a code-based platform;makes it more scalable and easy to implement.