This document describes the sample usage of the Trust Services with SQL Server. Visit Trust Services Samples Download page to download samples.
The sample is designed with the following scenarios in mind: 

Database Backup

DBA has an on-premise database that contains sensitive data in clear text. He would like to utilize SQL Azure for backup, but due to privacy, security or regulatory concerns, he does not want the cloud database to expose sensitive data in clear text. He uses this sample to copy the database to SQL Azure, while enciphering sensitive columns using the Trust Services SDK and using Trust Services for key management. Later, he can use the same sample to restore the database from SQL Azure to an on-premise database, decrypting the enciphered columns.

SQL Data Hub

The Publisher has an on-premise database, that contains sensitive data in clear text. He wants to share it with one or more Subscribers using SQL Azure as a data hub. But, due to privacy, security or regulatory concerns, he does not want the cloud database to expose sensitive data in clear text. The Publisher uses Trust Services to define data policies, then uses the sample to copy the database to the SQL Azure database, while enciphering sensitive columns using the Trust Services SDK and using Trust Services for key management. He then uses Trust Services to authorize Subscribers (this allows them to decrypt sensitive data). A Subscriber uses the sample to copy cloud database to an on-premise database, decrypting the enciphered columns.

The sample demonstrates the following concepts of Trust Services:
  • Defining Trust Policies
  • Using URIs to identify SQL columns
  • Using SQL transform provided by Trust Services SDK
  • Configuring Trust Services Logging via a config file

Link to download the SDK: Trust Services SDK MSI . For specifics on the SDK APIs, refer to the Help file. 

Pre-Requisites

Self-Signed Certificates

You will need self-signed certificates for the Policy Administrator, Publisher and Subscriber available. The sample code uses a single certificate stored in the Windows Certificate Store, but it can be modified to support multiple parties.
You can create your own certificates: run the following command from a Visual Studio command prompt to create a self-signed certificate: 
        makecert -r -pe -n "CN=Azure.Trust.Sample" -sky exchange -ss my

SQL Server(s)

The sample uses two SQL Server databases, a trusted on-premise database, and an untrusted cloud database. To play with the sample (using example data), you can have both databases located locally or in the cloud - but we'll still call one of them "on-premise" (it will contain plain text data) and one "cloud" (it will contain encrypted data).

Trust Server

You will need to sign up for Trust Services Lab and create a Trust Server. Export the certificate, created for the Policy Administrator, above (only public key of course - .cer file) and upload it to the server you created using the Portal. This will allow you to connect to your Trust Server using this certificate. Please see Getting Started Tutorial for step-by-step instructions.

Client computer

Install the Trust Services SDK and Management Tool Labs msi. By default, this installs to C:\Program Files\Microsoft\Trust Services Lab SDK and Shell (x64)\. (or x86 on 32-bit OS). The dlls are placed in the bin directory.

Sample usage

Open CopyDatabase.sln using Visual Studio 2010. Add references to the Trust Services SDK dlls.

Create SQL Schema and sample data

First, let's create the SQL Server schema and data. Expand project folder SqlScripts. Connect to the on-premises database, and run the script LocalCreate.sql (you can use Visual Studio or SQL Management Studio) - this creates the sample schema. Then run LocalData.sql - this creates a couple of rows with sample data.

Now connect to the cloud database, and run script UntrustedCreate.sql. Notice how the cloud schema is different from on-premises schema. We'll treat SSN and EMail columns as sensitive, so they will be encrypted and thus their type in cloud DB is VARBINARY(MAX). Finally, UntrustedData.sql will be used later to query cloud data - if you run it now against cloud database, it will return zero rows.

Define Trust policy

Second, we'll connect to Trust Services Server and define Trust Policy using Trust Services Shell (a customized PowerShell). Start the Trust Services Shell from Start Menu > Trust Services (x64) > Trust Service Shell.

In Visual Studio project, expand folder Policy and open the file CreatePolicy.ps1. You'll need to modify the configuration variables in this file before you can run it. After that, you can execute the whole file, or copy it line by line to Trust Services Shell and run each command separately - the latter method will help you catch any errors. Now let's see what we need to configure in CreatePolicy.ps1:

$thumb is the thumbprint of the certificate that you've created or chosen above and uploaded to the Trust Server. You can find the thumbprint using the certmgr tool, or by running the following command (replace subject string if needed) in the Trust Services Shell or a "plain" PowerShell:
  dir cert:\currentUser\my | where {$_.Subject -eq "CN=Azure.Trust.Sample" }
Copy thumbprint to CreatePolicy.ps1. You should get something like:
 $thumb = "F2CDBEB7AF6D6D5F4D81209C5013DF07594DB1F9"
$server is the name of the Trust Server you've created in the steps above.
$svcUrl is the address of the Trust Services Web Service, find it on Trust Portal. Usually you will have https://trustservicesapi2.cloudapp.net/ here.
$connStr is the SQL connection string to the local on-premise database. When defining policy, this connection string will be used to read SQL schema information only - no data is being moved or read yet. As we discussed above, cloud database has different column data types and cannot be used here.
$namespace is the arbitrary namespace for the policy. To keep the metadata of the service (names of tables and columns) more secure, generate a GUID using Visual Studio tool GuidGen and use it as a policy namespace. Or keep the namespace EXAMPLE - your data is still secure (but your metadata is somewhat less secure).

Let's quickly look at the remaining code. First, it finds the certificate object and stores it in $cert variable. Then it creates SQL ECM (Edge Compliance Module - the object used to define and enforce policy). We create it with -Admin flag, so we'll be defining policy, not enforcing it. Finally, it defines the Trust Policy for each column.

The Trust Policy defines whether the data described by this policy should be encrypted or not. Policy also has associated authorizations - which define who will be able to decrypt data if the data is encrypted. The policy is identified by a URI.
All objects that belong to some policy are either encrypted or not, and have the same set of users who can decrypt the data. It is up to the application how it builds this URI and thus find policy for a particular object. But when you use SQL ECM that ships with Trust Services SDK - it assigns a policy per SQL Column - thus in all rows the column is either encrypted or not encrypted. The URI looks like db:NAMESPACE/DB-SCHEMA/TABLE/COLUMN (usual URI escaping rules apply, but that's not important for this sample). The sample does not explicitly create authorizations, because it uses a single Principal, and the person who creates the policy is implicitly authorized for the Policy in this release.

Now run it. If you get no errors, then you've defined the policy for your data. But no data has been moved yet.

Copy Data

Now let's compile, configure and run the actual C# code sample: it will read the data from the on-premise database and put it in the cloud database. Similar to the TS Shell, we need to configure it. The application configuration is stored in app.config. Open it and enter the same configuration values you used in CreatePolicy.ps1 file, plus a couple new ones:
UntrustedDB is the connection string for the cloud (untrusted) database.
Tables is a list of tables to copy. Note that we don't need to tell the sample what to do with each column - the SQL ECM will follow the policy you defined earlier.
ToUntrusted tells the sample to copy data from the on-premise (trusted) location to cloud (untrusted) location. Leave it as True for now.

While here, take a look at the remaining parts of the config file - it also shows how to configure ECM tracing - in this sample, it will log ECM events to EcmLog.txt file.

Now run the sample. If everything works right, you should see a success message. You can now connect to the cloud database and run UntrustedData.sql to check that the sensitive columns were indeed encrypted.

Copy Data Back

Now let's say we used the cloud data for backup, but corrupted or lost the on-premise data (delete or change its content to verify!) and now want to restore it back. Change ToUntrusted property in the app.config to False, rebuild the project and run it again. You should now have the original data back in the on-premise database.


Quick Links