Security Of SSIS Packages

The security of SSIS packages has changed because of the architecture changes in Integration Services. In the previous version of SSIS, you could deploy it to the MSDB or the file system. These options are still available but are older techniques that should be changed to use the project deployment model to take advantage of the security options. The SSIS catalog can be found in the standard database connection in SSMS, as shown in the below screenshot. At the top, you can see the SSIS catalog and the folder structure. The bottom section of the figure shows the Integration Services connection and the packages in the MSDB. You can also see the file system folder here.

Learn how to use SSIS, from beginner basics to advanced techniques. Enroll for Free "SSIS Training" Demo!                                                                                                                                                      

SSIS Security

Securing the SSIS Catalog

It is easy to set and control security in the SSIS catalog either with the GUI interface or using T-SQL. The types of authentication supported by SSIS are Windows Authentication and SQL Server Authentication.

The SQL Server Integration Services administration role is named ssis_admin. This role has permission to view and modify all the objects in the SSIS catalog. Members of this role can create folders, move objects, delete objects, create and modify environments, and perform configuration changes to the catalog.

The SSIS catalog separates all the projects and objects into folders. Although these folders are great for organizing SSIS objects, they are also securable objects. You can grant users permission to a folder without adding them to the ssis_admin role. To give a user access to a folder, grant the user Manage_Object_Permissions on the appropriate folder.

Managing Security with the GUI

You can control the security of the folder in the SSIS catalog using the SSMS GUI. If you have been following along with the previous examples in Administering SSIS Topic, you should have at least two folders in the SSIS catalog, one named AdminDemo and the other named DataTapDemo.

Right-click on the DataTapDemo folder and select Properties. Click the Permissions option on the left side. In the top-right corner, click Browse and select the user or role to whom you want to give access to the folder. In the bottom section, you can control the permissions at a granular level, as shown in the below screenshot. Users and roles can be added at the top of this dialog. If you want to grant a group of users in role access to the folder, you can do it here. Place a check next to all the permissions you want to grant or deny for the user or role.

The permissions available at the bottom of the dialog are explained in the below screenshot.

SSIS Catalog Permissions

There are only five securable objects in the SSIS catalog (see below screenshot). The permissions in the preceding table can be applied to these objects. The Supported Object Types column shows which objects can receive which permissions. The following table contains the list of objects, their types, and a description of the permissions it controls.

SSIS Catalog Object Security

Encryption of the catalog is handled with a master key. The SSISDB is just like any other database and therefore uses a master key, which is created when the catalog is created. This was covered in the “Setting the SSIS Catalog Properties” section earlier in Administering SSIS Topic.

Package level and project level parameters have a Boolean-sensitive property. When this property is set to true, the parameter value is encrypted in the SSISDB. When you query a sensitive parameter, it will show a null value. Other than this, the parameters behave like any nonsensitive parameters. The sensitive parameters work great for storing passwords or entire connection strings.

Managing Security with T-SQL

Five stored procedures can be used to manage security in the SSIS catalog. If you prefer running T-SQL, or you have a custom application you want to use to manage security, then scripting out your security changes is the way to go. The below screenshot shows the five stored procedures used to manage security.

SSIS Catalog Security Stored Procedures

Legacy Security

If you decide to deploy your packages to the MSDB or file system, then you are using the package deployment model and the older version of security. The only login option for connecting to the SSIS service is to use your Active Directory account. Once you connect, you’ll see only the packages that you are allowed to see. This protection is accomplished based on package roles. Package roles are available only on packages stored in the MSDB database. Packages stored on the file system must be protected with a password.

                          

Frequently Asked SSIS Interview Questions & Answers

 

Package roles can be accessed in Management Studio by right-clicking a package that you wish to protect and selecting Package Roles. The Package Roles dialog, shown in below screenshot, enables you to choose the MSDB role that will be in the writer role and reader role. The writer's role can perform administrative-type functions such as overwriting a package with a new version, deleting a package, managing security and stopping the package from running. The reader role can execute packages, view packages, and export packages from Management Studio.

Package roles use database roles from the MSDB database. By default, the creator of the package and people who are in the db_dtsadmin or db_dtsoperator database roles can be a reader. The writer role is held by members of the db_dtsadmin database role or the creator of the package by default. When you select the drop-down box in the Package Roles dialog, you can change the packaging role from the default to another customized role from the MSDB database.

You may want to assign a group of people to a customized role to ensure that they are the only ones who can execute a set of packages. For instance, the following short example secures a package to a role called Accounting for the writer and reader package role.

First, open Management Studio and connect to your development or local database engine instance. Then, expand System Databases ⇒ MSDB ⇒ Security and right-click Roles, selecting New Role. This opens the New Database Role dialog (shown in below screenshot). Of course, you need the appropriate security to create a new database role.

Name the role AccountingRole and make your own login a member of the role by clicking the Add button. Additionally, make your own username an owner of the role. You may have to add your login as a user to the MSDB database prior to adding the role if it isn’t there already.

You’re now ready to tie this role to a package. In Management Studio, connect to Integration Services. Right-click any package stored in the MSDB database and select Package Role to secure the package. For the writer and reader roles, select the newly created AccountingRole role and click OK. Now, packages of the AccountingRole role will be able to perform actions on the package. If you’re a member of the sysadmin role for the server, you will be able to perform all functions in SSIS, such as execute and update any package and bypass the package role.

If your packages are stored on the file system, you must set a package password on the package to truly secure it. You can also enforce security by protecting the directory with Windows Active Directory security on the file or folder where your packages are stored. To set a package password in SQL Server Data Tools, set the ProtectionLevel property to EncryptSensitiveWithPassword and type a password for the PackagePassword property. You can also set a package password using a utility called DTUtil.exe, which was covered in the “Command-Line Utilities” section earlier in the Administering SSIS Topic.

To connect to a package store, the SSIS service must be started on the given server. Additionally, you must have TCP/IP port 135 open between your machine and the server. This is a common port used for DCOM, and many network administrators will not have this open by default. You also need to have the SQL Server database engine port open (generally TCP/IP port 1433) to connect to the package store in the MSDB database.

List of Related Microsoft Certification Courses:

SSRS	Power BI
SSAS	SQL Server
SCCM	SQL Server DBA
SharePoint	BizTalk Server
Team Foundation Server	BizTalk Server Administrator