This document describes the sample usage of the Trust Services PowerShell for a scenario where a Trust Services Policy Administrator needs to add policies for all columns in a SQL Server database. Visit Trust Services Samples Download page  to download samples.


Self-Signed Certificate

You will need a self-signed certificate for the Policy Administrator. You can create your own certificate by running the following command from Visual Studio command prompt:

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

SQL Server

You will need to create a SQL Server database for this sample with any number of tables and columns.

Trust Server

You will need to sign up for Trust Services Lab and create a Trust Server. Export the certificate above (only public key - .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. SDK can be downloaded here.

Sample usage

The sample contains two PowerShell scripts - CreatePoliciesFile.ps1 and AddPolicies.ps1. The first script will get a list of tables and columns from the SQL Server database, create a URI for each column, and write each URI with encryption flag "clear" to a file in CSV format. You will need to open this file and change encryption flag to "encrypted" for the columns that must be encrypted. The second script reads the URIs from the CSV file created by the first script and executes Add-DataPolicy command for each URI.

    1. Open Trust Service Shell:

Start -> All Programs -> Trust Services (x64) or Trust Services (x86) -> Trust Service Shell

If PowerShell configuration does not allow running PowerShell scripts, run the following command from administrator PowerShell window:

Set-ExecutionPolicy Unrestricted

    2. Run CreatePoliciesFile.ps1:

.\CreatePoliciesFile.ps1 -connectionString "Data Source=ExampleSQLServer;Integrated Security=SSPI;" -databaseName "Sales" -outputFile "d:\Policies.csv" -policyNamespace "sample"

The following parameters must be specified:

connectionString – connection string to the SQL Server that hosts the database for which policies will be defined.
databaseName – name of the database for which policies will be defined.
policyNamespace – namespace of the Trust Services policy.
outputFile – full name of the output file. 

Output file example:
                              db:SampleNamespace/dbo/MyTable/Name, clear
                              db:SampleNamespace/dbo/MyTable/CreditCard, clear
                              db:SampleNamespace/dbo/MyTable/Address, clear

    3. When the output file is generated, you will need to review the file and modify the encryption flag for the columns that need to be encrypted:

                              db:SampleNamespace/dbo/MyTable/Name, clear
                              db:SampleNamespace/dbo/MyTable/CreditCard, encrypted
                              db:SampleNamespace/dbo/MyTable/Address, clear

    4. Run AddPolicies.ps1:

.\AddPolicies.ps1 -fileName "d:\Policies.csv" -certificateThumbprint "CBD98CE6C208125E164F4A4F125BAD7B3DE0D9E3" -trustServerName "wpccyexb2e" -trustServiceUrl "" -connectionString "Data Source=ExampleSQLServer;Initial Catalog=MyDatabase;Integrated Security=SSPI;"  

The following parameters must be specified:

filename – full name of the file that contains policies.
certificateThumbprint - Thumbprint of the certificate used by Trust PowerShell snap-in. This certificate must be already uploaded to Trust Server using Trust Services WebPortal.
trustServerName - Name of the Trust Server. Trust Server must be already created using the Trust Services WebPortal.
trustServiceUrl - The URL of the Trust Services. URL can be obtained from the Trust Services Web Portal.
connectionString - Connection string to the SQL Server database for which policies will be created.

Quick Links