未加星标

SQLSweet16!, Episode 9: Scripting Always Encrypted operations

字体大小 | |
[系统(windows) 所属分类 系统(windows) | 发布者 店小二04 | 时间 2016 | 作者 红领巾 ] 0人收藏点击收藏

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 alrge 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:


SQLSweet16!, Episode 9: Scripting Always Encrypted operations

Installing Nuget package for System.Management.Automation

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.

本文系统(windows)相关术语:三级网络技术 计算机三级网络技术 网络技术基础 计算机网络技术

主题: PowerShellSQLC#SQL Server
分页:12
转载请注明
本文标题:SQLSweet16!, Episode 9: Scripting Always Encrypted operations
本站链接:http://www.codesec.net/view/484369.html
分享请点击:


1.凡CodeSecTeam转载的文章,均出自其它媒体或其他官网介绍,目的在于传递更多的信息,并不代表本站赞同其观点和其真实性负责;
2.转载的文章仅代表原创作者观点,与本站无关。其原创性以及文中陈述文字和内容未经本站证实,本站对该文以及其中全部或者部分内容、文字的真实性、完整性、及时性,不作出任何保证或承若;
3.如本站转载稿涉及版权等问题,请作者及时联系本站,我们会及时处理。
登录后可拥有收藏文章、关注作者等权限...
技术大类 技术大类 | 系统(windows) | 评论(0) | 阅读(29)