Always Encrypted–PowerShell Scripts

ByHariharan Rajendran

Always Encrypted–PowerShell Scripts

We have discussed that how to create a Always encrypted columns and steps to create custom column master key & column encryption key.

This article explains about the creation of keys with the help of PowerShell scripts.

Latest PowerShell Cmdelt’s update from Microsoft, https://blogs.technet.microsoft.com/dataplatforminsider/2016/06/30/sql-powershell-july-2016-update/

Pre-Requisites:

Since Always Encrypted feature introduced in SQL Server 2016, we need to upgrade our SQLPS.exe. If you use the older version of SQLPS then that will not recognize the some of the below cmdlets. So make sure to upgrade SQLPS.exe first.

You can upgrade by installing the latest version of SSMS (SQL Server management studio) 2016.

Check for the below modules installed in PowerShell,

#List directory wise installed modules

Get-Module –ListAvailable

image

Check for SQLASCMDLETS & SQLPS. If there is no modules then make sure you have SQLPS.exe file available in below location

C:\Program Files (x86)\Microsoft SQL Server\130\Tools\PowerShell\Modules

# Import the SQL Server Module.
Import-Module Sqlps -DisableNameChecking;

 

Steps to Create a Column Master Key & Column Encryption Key.

In the Below script, we need to specify following information,

  • Server Name
  • Database Name
  • Column Master Key Name
  • Column Encryption Key Name

First Connect to the Server and Database using PowerShell Script

# Connect to your database.
$serverName = “<server name>”
$databaseName = “<database name>”

$connStr = “Server = ” + $serverName + “; Database = ” + $databaseName + “; Integrated Security = True”
$connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection
$connection.ConnectionString = $connStr
$connection.Connect()
$server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection)
$database = $server.Databases[$databaseName]

As we know, we have different key stores available. This below script using “Windows Certification Store – Current User”.

# Create a column master key in Windows Certificate Store.
$cert1 = New-SelfSignedCertificate -Subject “AlwaysEncryptedCert” -CertStoreLocation Cert:CurrentUser\My -KeyExportPolicy Exportable -Type DocumentEncryptionCert -KeyUsage DataEncipherment -KeySpec KeyExchange

In the above script, you can check the “CertStoreLocation”. To identify the location, you can check in T-SQL Statement to create master key. I just highlighted in below script for understanding purpose.

USE [Sample_Encrypt]
CREATE COLUMN MASTER KEY [test1]
WITH
(
    KEY_STORE_PROVIDER_NAME = N’MSSQL_CERTIFICATE_STORE’,
    KEY_PATH = N’CurrentUser/My/55A4DDAF58D274547B9C3BA5ABA154662CDF62D0′
)

GO

# Create a SqlColumnMasterKeySettings object for your column master key.
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings -CertificateStoreLocation “CurrentUser” -Thumbprint $cert1.Thumbprint

# Create column master key metadata in the database.
$cmkName = “<cmk name>”
New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings

# Generate a column encryption key, encrypt it with the column master key and create column encryption key metadata in the database.
$cekName = “<cek name>”
New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKey $cmkName

Verify the keys in SQL Server Database Security folder.

About the Author

Hariharan Rajendran author

Hariharan Rajendran is a Microsoft Certified Trainer and Lead BI Solutions Consultant with 8+ years of experience in Database, BI and Azure platforms. Hariharan is also an active community leader, speaker & organizer and leads the Microsoft PUG (Power BI User Group – Chennai), SQLPASS Power BI Local Group – Chennai and an active speaker in SQL Server Chennai User Group and also a leader in Data Awareness Program worldwide events. Hariharan also frequently blogs (www.dataap.org/blog), provides virtual training (on ad-hoc basis) on Microsoft Azure, Database Administration, Power BI and database development to worldwide clients/audience.

Comments Are Closed!!!