Overview

SSISSample shows how to integrate Trust Services into SSIS workflows.

Please visit Trust Services Samples  page to download this sample.

This sample consists of a custom SSIS dataflow component that can encrypt/decrypt database columns based on the policies set in Trust Services.

SQL Server Integration Services (SSIS) is a platform for building enterprise-level data integration and data transformations solutions. SSIS has a rich set of built-in tasks and transformations, as well as tools for constructing packages using them. SSIS packages can be built to address complex business needs, for e.g. extract and transform data from a wide variety of sources such as XML data files, flat files, and relational data sources, and then load the data into one or more destinations. When the destination for the data is a cloud based platform, e.g. SQL Azure, there may be a need to secure sensitive data by encrypting it.

Microsoft Codename "Trust Services" (TS) is an application-level encryption framework that can be used to protect sensitive data stored on the Windows Azure Platform. The solution demonstrated here, integrates SSIS with TS, to enable use of SSIS packages to load/download data to/from SQL Azure by encrypting/decrypting sensitive data based on the corresponding TS policies.

An SSIS package is organized collection of connections, control flow elements, data flow elements, event handlers, variables, and configurations, that one can assemble using either the graphical design tools that SQL Server Integration Services provides, or build programmatically. In particular, a data flow package consists of the sources and destinations that extract and load data, the “transformations” that modify and extend data, and the paths that link sources, transformations, and destinations.

The sample data flow packages are graphically assembled using Visual Studio 2010, and demonstrates simple ETL (Extract-Transform-Load) scenarios - load data from a local/on premise database (MS SQL Server) to a remote database (MS SQL Server). It is assumed, that the data in the database has sensitive columns, for e.g. social security numbers, so it must be transformed - encrypted when loaded into the remote database, and decrypted when loaded into the local database. The sample uses a custom dataflow “transformation” element implemented using the Trust Services SDK. It integrates with TS to encrypt/decrypt data in a database column, based on the policy defined on it, and based on whether the data is flowing to a Trusted or UnTrusted domain. Policy for each database column is defined offline, i.e. prior to running the package. When the package is assembled, the custom dataflow component, "Trust Services DB Col Transform", is added in the path that links the data source from which data is loaded and the data destination to which the data is loaded. The appropriate policy context - ToTrusted or ToUntrusted - needs to be set in the dataflow element, and the input columns need to be mapped to the output column.

Installation

To compile and use the custom SSIS dataflow component in your workflows:
1. Install SQL 12 SSIS
2. Install the Trust Services SDK
3. Open SSISSample.sln in Visual Studio 2010.
4. The custom SSIS dataflow task in implemented as the project TrustServicesDBTransform
5. Add references to SQL 12 SSIS DLLs and the Trust Services DLLs to the project.
(a) SSIS DLLs are:
Microsoft.SqlServer.DTSPipelineWrap.dll
Microsoft.SqlServer.DTSRuntimeWrap.dll
(b) Trust Services DLLs are:
Microsoft.SqlAzure.Trust.EdgeComplianceModule.dll
Microsoft.SqlAzure.Trust.SqlEdgeComplianceModule.dll
Microsoft.SqlAzure.Trust.SqlEdgeComplianceModule.ObjectModel.dll
Microsoft.SqlAzure.Trust.TfsClientServerCommon.TrustServiceObjectModel.dll
6. Compile the project.
7. Copy the DLL (TrustServicesDBTransform.dll) to the PipelineComponents folder
%PROGRAMFILES%Microsoft SQL Server\110\DTS\PipelineComponents
8. Install the DLLs (Trust Services DLLs and TrustServicesDBTransform.dll) in the GAC, using gacutil.exe
(a) Open a Visual Studio Command Prompt as administrator
(b) Run "gacutil /i path_to_DLL"

Now the component is ready to be used in SSIS workflows.

Preparation for Running SSIS Workflows

To run the example SSIS workflows:

Prepare databases and data:
---
1. Create three databases SSIS_EXAMPLE, SSIS_EXAMPLE1, SSIS_EXAMPLE2 on the SQL servers you will use for this sample.
(a) SSIS_EXAMPLE simulates trusted on-premise database. This can be created on a local SQL server A.
(b) SSIS_EXAMPLE1 simulates the untrusted cloud database. This can be created as a SQL Azure database. For purposes of running this sample it can also be created on local SQL server A.
(c) SSIS_EXAMPLE2 simulates trusted on-premise database. This can be created on a local SQL server B. For purposes of running this sample it can also be created on local SQL server A.
2. Using SQL scripts under HelperFiles directory create the needed database tables.
(a) CreateTrustedAccountEXAMPLE.sql creates the TrustedAccounts table in the SSIS_EXAMPLE database.
(b) CreateUntrustedAccountEXAMPLE1.sql creates the UntrustedAccounts table in the SSIS_EXAMPLE1 database.
(c) CreateTrustedAccountEXAMPLE2.sql creates the TrustedAccounts table in the SSIS_EXAMPLE2 database.

Prepare trust server:
---
2. Login to trust portal
3. Create trust server
4. Choose an existing certificate or create certificate:
   makecert -r -pe -n "CN=Trust.Sample.CopyDB" -sky exchange -ss my
   certmgr -put -c -n Trust.Sample.CopyDB -s my Trust.Sample.CopyDB.cer
5. Upload your public certificate to the trust portal

Prepare trust policy:
---
6. Edit CreatePolicy.ps1: enter trust server name, policy namespace, certificate thumbprint and local connection string
7. Start Trust Services Shell shortcut in Start Menu and execute CreatePolicy.ps1

Running SSIS Workflows

Run SSIS example: TestTSDBToUntrusted.dtsx (this sample copies data from TrustedAccounts table to the UntrustedAccounts table while applying the appropriate data policy to the SSN and Email columns)
---
8. Open the TestTSDBToUntrusted.dtsx package.
9. Go to the Data Flow view and Edit the "Trust Services DB Col Transform" component - enter trust server name, certificate thumbprint and policy namespace
10. Open the Source and Destination components and update the connection string to point to the SSIS_EXAMPLE\TrustedAccounts and SSIS_EXAMPLE1\UntrustedAccounts database tables.
11. Execute the package and examine the contents of SSIS_EXAMPLE1\UntrustedAccounts table

Run SSIS example: TestTSDBToTrusted.dtsx (this sample copies data from UntrustedAccounts table to the TrustedAccounts table while applying the appropriate data policy to the SSN and Email columns)
---
8. Open the TestTSDBToTrusted.dtsx package.
9. Go to the Data Flow view and Edit the "Trust Services DB Col Transform" component - enter trust server name, certificate thumbprint and policy namespace
10. Open the Source and Destination components and update the connection string to point to the SSIS_EXAMPLE1\UntrustedAccounts and SSIS_EXAMPLE2\TrustedAccounts database tables.
11. Execute the package and example the contents of SSIS_EXAMPLE2\TrustedAccounts table

To run SSIS example: TSScriptToUntrusted.dtsx (this sample does the same work as the TestTSDBToUntrusted.dtsx package, i.e. it copies data from TrustedAccounts table to the UntrustedAccounts table while applying the appropriate data policy to the SSN and Email columns, using a Script component that is hardcoded to work with the TrustedAccounts and UntrustedAccounts table schemas, rather than using the TrustServicesDBTransform dataflow component that works with any database schema.)
1. Open the TSScriptToUntrusted.dtsx package
2. Go to the Data Flow view and Edit the configuration variables of the SCR_TransformToUntrusted component (this is the custom script component) to correspond to your runtime (i.e. machine and account information specified during policy creation) 
(a) tsTspaThumbprint = certificate thumbprint of TS policy admin
(b) tsDuThumbprint = certificate thumbprint of data user
(c) tsPolicyNamespace = policy namespace
(d) tsServerName = name of your TS server
3. Open the Source and Destination components (called TrustedAccounts and UntrustedAccounts respectively) and update the connection string to point to SSIS_EXAMPLE\TrustedAccounts and SSIS_EXAMPLE1\UntrustedAccounts database tables.
4. Execute the package and examine the contents of the SSIS_EXAMPLE1\UntrustedAccounts table.

Work through the CopyDatabase sample and GeneratePolicyTemplate samples to see how to setup database policies using the Trust Services powershell cmdlets, to create SSIS packages using the TrustServiceDBTransform dataflow component with your own databases.


You can find more information about Trust Services here.