Joe the Business Intelligence Guy

A journey through the design and delivery of business intelligence solutions.

SSIS 2012 Using SQL Authentication with Don’t Save Sensitive Successfully

New to SQL Server Integration Services 2012 is deployment as a project to the Integration Services Catalog (physically storing packages and metadata in the SSISDB database on an instance of a SQL Server database). This along with an enhancement for managing connection managers permits much easier configuration than previous methods and in one case in particular, data sources which require using passwords as part of the connection, gives us new found ease and power.

Example Scenario

SSIS packages to be deployed and run on a server that is being hosted and is on a different Windows domain than the primary data source are problematic due to domain trust impacts on windows authentication normally used by SSIS connection managers.

Others have solved this scenario by configuring one-way domain trust and continuing to use window authentication within their SSIS connection managers. While this method is impressive it is not permitted oftentimes when hosting via third-parties or just because it does have security implications that cause network administrators to hesitate. This post will demonstrate how we can leverage SQL Authentication and SSIS 2012 new features to successfully handle connectivity scenarios where windows authentication is not available.

Prerequisites

  • Create a SQL login account on the source and destination SQL Server (using the same password)
  • Assign only “public” server role to the new SQL login account
  • Assign (minimally) db_datareader database role on source SQL Server databases
  • Assign (minimally) db_datawriter database role on target SQL Server databases

Permissions assigned to the databases will vary depending on what the SSIS packages are doing. A common pattern of truncate and load, for example, will require the account also have db_ddladmin permission on the database in order to execute the TRUNCATE TABLE statement.

  • Implement all (related) connection managers in an SSIS 2012 solution as project type
  • Ensure SSIS 2012 project is configured as the project deployment model (unsupported for package or database deployment models)
  • Ensure SSIS 2012 project security – protection level is set (desired not required) as DontSaveSensitive

      SSIS2012_ProjectSecurity_DontSaveSensitive

  • Ensure SSIS 2012 package security – protection level is set (desired not required) as DontSaveSensitive

     SSIS2012_PackageSecurity_DontSaveSensitive

Implementation

With all the configuration of prerequisites completed let’s see how this works when deploying to the Integration Services catalog. Please note that no parameters (new to SSIS 2012) or environments (also new to SSIS 2012) or any other special configuration is required. Under the covers SSIS 2012 project deployment mode considers connection manager properties, parameters, and logging level as parameters and stores their information in the SSISDB database tables related to parameters (internal.object_parameters among others). As a result of this the SSIS catalog will automatically take care to save our sensitive password encrypted within the catalog enabling simple deployment and use.

The SSIS project is ready for deployment to the Integration Services catalog. Once deployed we can configure the project by opening the Integration Services Catalogs folder on our SQL Server instance (located below Databases, Security, and all other database objects and above SQL Server Agent) right-clicking on the project and selecting “Configure…”.

     SSIS2012_CatalogProjectConfigurationOption   

Once the Configure dialog is displayed, three things need to be setup:

     SSIS2012_CatalogConnectionManagerConfiguration

  1. Scope of the configuration change (All packages and project, Entry-point packages and project, deployed project, specific package)SSIS2012_CatalogConfigurationScope
  2. Select the Connection Managers tab and the desired Connection Manager
  3. Enter a Password by clicking the ellipsis button beside the Password property

Once the deployed SSIS project connection manager passwords have been entered via the Integration Services Catalog configuration the project is ready to be run.  An example of running using TSQL follows:

     SSIS2012_RunningPackageUsingTSQL

We can see what the configuration did under the covers by looking at some of SSISDB database tables. Most notably we can view [SSISDB].[internal].[object_parameters] to determine what this configuration stored and how it stored the sensitive password value that was set.

SSIS2012_Catalog_ObjectParametersForSensitive

Immediately we can note that object_type column values for connection managers is set to 20 which differs from our normal SSIS parameters that would also be stored in this table. The parameter name describes the SSIS object and corresponding property in which the value is stored for configuration. In the case of our connection manager this parameter_name takes the format of “CM.NameOfConnectionManager}.ConnectionManagerProperty}”

As we have a password stored, note that the sensitive column is flagged with a 1, the design_default_values is set to NULL (removed when deployed since we set protection level to DontSaveSensitive), the sensitive_default_value is set to an encrypted value (not readable), and lastly the value_set is set to 1 (meaning a default value has been configured).

Now we can easily manage these connections by rerunning the configuration or using the [catalog].[set_object_parameter_value] stored procedure located in the SSISDB database.

Wrap Up

This blog demonstrated how SSIS 2012 can manage sensitive property values such as connection manager passwords in an easy to understand, maintain and use manner while keeping them encrypted and safe. No special effort other than simple SSIS project deployment and subsequent project configuration from within the Integration Services Catalog is needed to ensure this works successfully.

This work is licensed under a Creative Commons license.
Advertisements

Written by Joe Salvatore

August 14, 2013 at 10:23 pm

%d bloggers like this: