This document describes the sample usage of the Trust Services for the scenario of a Windows Forms Application performing database report visualizations. The datasets to be consumed are moved from a trusted to an untrusted database with the sensitive information being encrypted. The reporting application needs fully utilize both clear text and encrypted information from the untrusted DB. Current demo contains a single party which holds the responsibilities for :

  • data encryption policy definition, specifying which columns in the database contains sensitive information

  • data manipulation by moving the data from trusted environment and protecting it into the untrusted one

  • consume the protected data

 

If needed, scenario can be evolved by separating the responsibilities for multiple parties, thus reducing the clearance of the consuming party.

Please visit Trust Services Samples page  to download this sample.

Prerequisites

 

Private X509Certificate

 

Such certificate can be obtained from a certificate authority or can be created as a self-signed certificate. The sample locates the certificate in the Windows Certificate Store, Current User\Personal folder. In order to explore certificates in this folder please run certmgr.msc to access certificate management console.

You can create your own certificates by running the following command from a Visual Studio command prompt and create a self-signed certificate:


makecert -r -pe -n "CN=Azure.Trust.Sample" -sky exchange -ss my

SQL Servers

 

The sample uses two SQL Server databases, one trusted with all data in clear text and one untrusted with sensitive data encrypted. For exercising the sample, both databases can be created on the same SQL Server instance which can be hosted “on-premise” server or even in development environment. For practical purposes the trusted database will be located “on-premise” with the untrusted database is located on public cloud infrastructure.

The current sample creates the trusted and untrusted databases, provisioning them with both schema and data according to SQL School demo DB.

Trust Server

 

You will need to sign up for Trust Services Lab and create a Trust Server. Export the public key for the certificate above (.cer file) using certmgr.msc management console and upload it to the server you created using the Trust Services Portal. This will allow you to connect to your Trust Server using current certificate. Please see Getting Started Tutorial for step-by-step instructions.

Trust Client SDK and SQL Server SDK

 

The machine running the sample needs to be equipped with Trust Services client installed by Trust Services SDK and Management Tool Labs msi. Additionally the sample uses SQL Management Objects (SMO) libraries part of SQL Server SDK. Please use the match SQL SDK version to the version of target SQL Servers. For SQL Azure please use SMO corresponding to SQL 2008 R2 SP1 or a newer version.

Sample Usage


1. Open SchoolReport.sln using Visual Studio 2010. Add references to the Trust Services SDK DLLs.

2. Update App.config file changing the fields for:

    • "thumbprint" – matching the X509Certificate
    • "trustServerName" - matching the server
    • "trustServiceURL" - matching the service URL advertised on Trust Services Web Portal
    • "TrustedDatabase" / "UntrustedDatabase" – pointing to the two databases to be utilized for the sample

 3 .Run application. This step performs following :

    • Creates original trusted (thus completely unencrypted) school DB fully populated with data
    • Creates untrusted DB with schema only (no data)
    • Creates encryption policies establishing what columns to encrypt according to configuration. Default configuration is set with columns [FirstName] and [LastName] of the [dbo].[Person] table to be considered sensitive and be encrypted in untrusted environment
    • For columns to encrypt updates their DB type to VARBINARY to support binary encrypted content
    • Copies the clear text from source trusted DB into protected data to untrusted DB

4. Request  consumption of protected  data by  clicking “Generate  Report” button
5. Updating the report parameters will change how protected dataset is reported


You can find more information about Trust Services here.