SQLSweet16!, Episode 9: Scripting Always Encrypted operations

[DataCAT team]

First published on MSDN on Oct 07, 2016

Authored by Sanjay Mishra

Authors: Arvind Shyamsundar, Sanjay Mishra, Subodh Patil ( iCertis ), Mukesh Singh ( iCertis ), Ravi Kumar ( iCertis )

Reviewers: Dimitri Furman, Benjin Dubishar, Raghav Kaushik, Jakub Szymaszek

Always Encrypted is one of the highly acclaimed features in SQL Server 2016. The key value prop in Always Encrypted is that SQL Server itself cannot decrypt the data as it will not have access to the ‘Column Master Key’ (CMK). This also poses a challenge for application developers / administrators as the only way to encrypt existing data is to essentially ‘pump it out’ into an application which has access to the CMK. Typically, this application for DBAs and developers is SQL Server Management Studio (SSMS), and using SSMS is acceptable when encrypting a few columns of data under human supervision. But in an environment with large number of tables and columns, or when the schema of the database is dynamic, or when the column encryption has to be triggered from a user application, using SSMS to do this manually is not an option.

iCertis is an early adopter of Always Encrypted in their application. The schema of the databases involved in this application is highly customizable by the end customer. Some columns in this schema might need to be encrypted using Always Encrypted and hence there is a need to (programmatically) automate the encryption from the application. This blog post shows how iCertis achieved that requirement.

Introducing the Always Encrypted PowerShell cmdlets

The July 2016 release of SSMS (and later versions) introduced a set of PowerShell cmdlets through a new ‘SqlServer’ module. This page describes the various capabilities that these cmdlets bring to the table. Of most interest to the specific scenario described above is the Set-SqlColumnEncryption cmdlet. In the post below, we will walk through the steps required to use this – first from a PowerShell session to test the capability, and then finally from a C# application which is using PowerShell Automation to invoke the cmdlets from an application.

As a side note it is worth knowing that the cmdlets in the ‘SqlServer’ PowerShell module can also be used for automating key setup and management (and are, in many ways, more powerful than SSMS - they expose more granular tasks, and thus can be used to achieve role separation and to develop a custom key management workflow – but that is likely a topic for a separate post!)

Encrypting data

Here is sample code which uses SMO classes to establish a connection to the database and then invokes the Always Encrypted PowerShell cmdlets to encrypt data in a column.
# Import the SqlServer module
Import-Module "SqlServer"

# Compose a connection string
$serverName = "SQLServerNetworkName\InstanceName"
$databaseName = "AETest"

$connStr = "Server=$serverName; Database=$databaseName; Integrated Security=true;"

# Connect to the database
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()

# Get an instance of the SMO Database class
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]

# Create a class to define the column(s) being encrypted and their CEK name.
# In this sample we are just encrypting one column.
$ces = New-SqlColumnEncryptionSettings -ColumnName "dbo.SampleTable.SampleColumn" -EncryptionType "Deterministic" -EncryptionKey "SampleCEK"
$cesarray = @()
$cesarray += $ces

# The most important step: encrypt the data
$database | Set-SqlColumnEncryption -ColumnEncryptionSettings $ces

Invoking the script from C#

In this section we show you how an application developer can invoke the above cmdlets from C# code. For simplicity we demonstrate how to do this with a C# console application. Once the project is opened, add a Nuget package for System.Management.Automation:

[caption id="attachment_4625" align="alignnone" width="2880"] Installing Nuget package for System.Management.Automation[/caption]

You can also use the following command from the Package Manager console in VS.NET:
Install-Package System.Management.Automation.dll
Once the reference to the PowerShell 3.0 library has been added, you can use code such as the sample code below to execute the PowerShell script (which has been added to the project folder as script.txt)
namespace SampleApp
{
using System;
using System.IO;
using System.Management.Automation;
using System.Management.Automation.Runspaces;

class Program
{
static void Main(string[] args)
{
PowerShell ps = PowerShell.Create();
using (var fs = new FileStream(@".\script.txt", FileMode.Open))
{
using (var sr = new StreamReader(fs))
{
var cmd = new Command(sr.ReadToEnd(), true);
var pipeline = ps.Runspace.CreatePipeline();
pipeline.Commands.Add(cmd);

try
{
var results = pipeline.Invoke();
}
catch (RuntimeException ex)
{
Console.WriteLine("Error executing script: exception details: " + ex.GetType().Name + "; " + ex.Message + "\r\n" + ex.StackTrace);
}
}
}
}
}
}
Note that the exception handling above will be useful in case the PowerShell script reports errors, for example if the CEK name is incorrect and so on. Such diagnostics are critical for production usage.

Lessons Learned

Here are some important learnings and considerations from this exercise:

• Currently the only supported way of getting this SqlServer PowerShell module is to install SSMS (as per above paragraph.) This is a constraint if deploying the application into an Azure App Service or such ‘unattended deployment’ environments. This will be addressed in due course by the SQL engineering team.


• Set-SqlColumnEncryption can take a very long time for a complex database schema with large number of tables, constraints etc. It will also take time when there is a large amount of data to be encrypted. In such cases, it is better to perform these operations on a background worker thread and not on a UI thread which may be subject to a request timeout setting.

If you are using or planning to use Always Encrypted, we would love to hear from you.