Many applications need access to sensitive information such as credit card numbers, Social Security numbers, or even just legal names and addresses. This data is usually stored in your database, and one of your options for securing that data is by using Always Encrypted to encrypt the data in those columns, both at rest and in transit. Setting up Always Encrypted in a database requires the following steps:

1. Generating a SSL Certificate.
2. Installing the SSL Certificate on all clients that need to decrypt those columns
3. Referencing that certificate with a SQL Server metadata object called a Column Master Key (CMK)
4. Installing a Column Encryption Key (or CEK) signed by the SSL certificates private key on the server
5. Encrypting the columns

In this article we will perform all these steps using PowerShell. While it is entirely possible to perform these actions though a combination of User or Computer Certificate MMC snap-ins and SSMS, knowing how to perform these steps in PowerShell is the first step for automating these steps. Even if you only have one production database that needs to use Always Encrypted, there could still be a need for automation.

First of all would be key rotation. This is the process of replacing the SSL Certificates with new ones and requires a similar sequence of steps to be performed. While we won't cover key rotation here, it's a task you want to be able to perform. Secondly, if there is a migration or addition of clients that need to decrypt the columns, then you need to perform some of these steps. Finally, if you want to setup a Continuous Integration and Continuous Deployment (CI/CD) system and are regularly setting up new dev or test databases, you will need to setup Always Encrypted during that process with PowerShell scripts.

All this being said, the MMCs and SSMS GUI do allow you to perform these tasks manually, and SSMS will even let you generate a PowerShell script of the encryption steps in place of actually doing them.

Our Example Scenario

In our scenario we have a .NET 4.6.2 web app in a cluster of two Azure VMs running IIS behind a load balancer. A copy of the app, listening on another URL, is running on an Azure App Service, which is the Azure Platform as a Service (PssS) analog to an IIS website. Both websites talk to the same Azure SQL Database. The application uses a library, called Log4Net, to log messages to a table in our database, called Logging.Log. There are two columns in that table that we want to encrypt: ClientIP and User. While these Azure resources are fictional, the source code for this application actually exists; it's called Always Encrypted Sample, and the PowerShell scripts contained within the repo are the PowerShell snippets for the basis of this article. This is the open source project I use for most of my Always Encrypted articles and training.

In the scenario, you can assume that the site was "lifted and shifted" from on premises VMs to Azure VMs initially, and there is a current project to migrate the application to an pure PaaS architecture. In reality, I want to illustrate key management both on a full Windows OS, and on an Azure App Service.

The architectural decision to send Log4Net logs directly to the database was twofold. First, it was a way to create a lot of data in a table with columns that could be considered sensitive. Secondly it was to use a library that made old school ADO.NET calls and predated Always Encrypted. This proved that (in some but not all cases) it is possible to use Always Encrypted without rewriting Application Code. I generally don't think your application logs belong in a database, and I don't think Log4Net is the best logging framework for .NET apps these days. However, this article is about Always Encrypted, not logging best practices.

Process Overview

Encrypting these columns requires a few steps. First are going to create a certificate on one VM. Then we are going to copy the certificate to the other VM and the Azure Web Application. After that, we are then going to create the CMK and CEK.  Finally, we will encrypt the two columns.

The steps will be as follows:

1. Open PowerShell on the first machine that will need the certificate. Ideally you don't want to do this on your desktop, but that's fine for demo purposes.
2. Create the certificate in the user certificate store.
3. Export the certificate and private key to a .pfx file.
4. Copy the .pfx to the other machine
5. Start a remote PowerShell session on the other client machine.
6. From the PowerShell remote session, import the certificate into the certificate store on that machine.
7. Use the Azure CLI to upload the certificate to the Azure Web Application.

There is an important security note here. Never install the certificate on the machine running the SQL Server. If you place the certificate on the SQL Server, someone with local admin access to the server, the ability to physically remove the server from a data center, or access to a system backup of the server will be able to decrypt the data.

I will also note that copying the certificate around can be avoided by using Azure Key Vault. That is another subject for another article.

Procedure

Fire up your PowerShell terminal and run the following code. I will be setting variables in some of the code snippets below that will be referenced in the future snippets so do this all in the same session.

Prerequisites

First of all let's make sure we have the latest Azure CLI and SqlServer module installed on our machine. For the former, visit the download page in a browser, or pull down https://aka.ms/installazurecliwindows with Invoke-WebRequest and use msiexec.exe to install it silently. For the latter, run the following PowerShell command.

Install-Module SqlServer -Scope CurrentUser -AllowClobber # See here regarding -AboutClobber https://dba.stackexchange.com/a/174717/1817

When the Azure CLI is installed it will add a command line executable called az.exe to our path. We can then invoke az from powershell, cmd.exe and bash.

Creating Certificates

We are going to use self signed certificates for this example. Yes, I did say self signed certificate. Yes, that should probably bother you. Unfortunately, the client side drivers do not support chain verification or even check the expiration dates. So there is ZERO value in obtaining a certificate from a trusted authority, and you can tell your auditor that. You can probably set custom expiration dates if you have a process to scan your certificate store for expired certificates. This will remind you to rotate the certificates periodically.

The command to create the self signed certificate is New-SelfSignedCertificate.

$MasterKeyDNSName = 'CN=Always Encrypted Sample'
Write-Host "Creating Self Signed Certificate $($MasterKeyDNSName)"
$Cert = New-SelfSignedCertificate `
-Subject $MasterKeyDNSName `
-CertStoreLocation Cert:\CurrentUser\My ` 
-KeyExportPolicy Exportable `
-Type DocumentEncryptionCert `
-KeyUsage DataEncipherment `
-KeySpec KeyExchange
$CmkPath = "Cert:\CurrentUser\My\$($cert.ThumbPrint)"
Write-Verbose "Column Master Key Certificate Path: $($CmkPath)"

I will reiterate, we never want to run this command on the SQL Server, because it creates the possibility that the data can be decrypted from the SQL Server itself.

Note, if that command returns an error, like New-SelfSignedCertificate: A parameter cannot be found that matches parameter name 'Subject', then you need to update to a newer version of windows or use a different syntax. See this guide written in 2015 if you are stuck with older versions of Windows.

Look at the parameter and argument, -CertStoreLocation Cert:\Current\My. This means the certificate is being stored in the User Certificate Store, not the Machine Certificate Store. If you run your app from IIS Express or Kestral, or the app is a thick client app, it can read the cert. If you are running your app on IIS or as a Windows service, you will need to change the above snippet to -CertStoreLocation Cert:\LocalMachine\My or use psexec to create or import the certificate as the service account or App Pool account.

Deleting Certificates

We don't want to delete this certificate at this time. However, when you want to delete this cert, you can delete it by Subject or by thumbprint with the Remove-Item cmdlet.

# By Subject (non-unique descriptive name)
Get-ChildItem Cert:\CurrentUser\My |  Where-Object subject -eq $MasterKeyDNSName | Remove-Item
# By thumbprint (unique identifier in the certificate store)
Remove-Item "Cert:\CurrentUser\My\$($ThumbPrint)"

Copying the Certificate

If you want to copy the certificate to another machine, you need to export it from the machine you just created it on and import it on the other client. Export-Certificate and Import-Certificate sound like the obvious cmdlet choices, but those two cmdlets export and import the certificate without the private key. If you were to use a cert without a private key to encrypt your columns, you will find yourself receiving an error similar to the following.

New-SqlColumnEncryptionKey : Certificate specified in key path 'CurrentUser/my/B91A912CF632575F784CDE485DA37AB2F23DB6BA' does not have a private key to encrypt a column encryption key. Verify the certificate is imported correctly.

So, the correct cmdlets are Export-PfxCertificate and Import-PfxCertificate. These commands require either a password or a list of users on your domain that can open the certificate. I'm electing to use a password in my example.

# On the machine with the cert
$Password = [System.Web.Security.Membership]::GeneratePassword(24,5) | 
    ConvertTo-SecureString -AsPlainText -Force
$DestinationMachine = 'web2.mydomain.io'
$PfxName = ".\$($MasterKeySQLName).pfx"
$PfxDestination = ""\\$(DestinationMachine)\C$\Users\$($env:USERNAME)\$PfxName"
Export-PfxCertificate -CertStoreLocation Cert:\CurrentUser\My -FilePath $PfxName -Password $Password
# Now transfer the cert
Copy-Item -Path $PfxName -Destination $PfxDestination
# Use Invoke-Command to Import the cert on the destination machine.
Invoke-Command -ComputerName $DestinationMachine -ScriptBlock {
  Set-Location $env:USERPROFILE
  Import-PfxCertificate 
    -CertStoreLocation Cert:\CurrentUser\My 
    -FilePath $Using:PfxName 
    -Password $Using:Password
  Remove-Item $Using:PfxName
}

Now we  need to upload it to our Azure Application Service. We will use the Azure CLI we installed to do it.

az webapp config ssl upload --resource-group AlwaysEncryptedSample  --name "AlwaysEncryptedWeb" --certificate-file ".\$($PfxName)" --certificate-password "$Password"

Finally we need to clear our password variable and delete our PFX file.

$Password.Clear()
Remove-Item $PfxName

Creating our CMK and CEK

Now that  we have created our certificate, we need to create two objects in our database: the CMK and the CEK. The CMK  is just a pointer in the database to the certificate we stored on our Windows host. The CEK is the key that actually encrypts and decrypts the column data. However, this key is not stored in plain text in the database. The plaintext value of this key is encrypted by the certificate's private key. That encrypted copy of the key is what actually gets stored in the CEK. When the database driver wants to encrypt or decrypt column data, it uses the CMK to locate the SSL Certificate and private key. The client driver then uses the private key to decrypt the CEK, which it subsequently uses to encrypt and decrypt column values.

$Database = Get-SqlDatabase -ConnectionString $ConnectionString
$Cert = (Get-ChildItem Cert:\CurrentUser\My |  Where-Object subject -eq 'CN=Always Encrypted Sample Cert') | Select-Object Thumbprint -First 1
$cmkSettings = New-SqlCertificateStoreColumnMasterKeySettings 
  -CertificateStoreLocation "CurrentUser" 
  -Thumbprint $Cert.Thumbprint
$Database | New-SqlColumnMasterKey -Name $MasterKeySQLName -ColumnMasterKeySettings $cmkSettings
$Database | New-SqlColumnEncryptionKey -ColumnMasterKey $MasterKeySQLName -Name 'Logging'

Encrypting the Columns in the Table

It's finally time to encrypt the User and ClientIP columns of the Logging.Log table. For each column we want to encrypt, we create a SqlColumnEncryptionSettings object with New-SqlColumnEncryptionSettings. Then we pass those objects as an array to Set-SqlColumnEncryption.

$LogSchema = 'Logging'
$encryptionChanges = @()
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName "$($LogSchema).Log.User" -EncryptionType Deterministic -EncryptionKey $LogColumnKeyName
$encryptionChanges += New-SqlColumnEncryptionSettings -ColumnName "$($LogSchema).Log.ClientIP" -EncryptionType Deterministic -EncryptionKey $LogColumnKeyName
$Database | Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionChanges

Note that the Set-SqlColumnEncryption cmdlet is an expensive operation.  Its performing an ALTER TABLE ALTER COLUMN; and it is encrypting every value from that column as it goes. Do your homework and run tests before doing this on production.

Checking Our Work

If you want to see the CMKs and CEKs in your current database, then look at these DMVs: sys.column_master_keys, sys.column_encryption_keys, and sys.column_encryption_key_values. The following two queries will show you the CMK and CEK you just created.

SELECT * FROM sys.column_master_keys;
SELECT k.*, encrypted_value, encryption_algorithm_name 
    FROM sys.column_encryption_keys k 
    INNER JOIN sys.column_encryption_key_values kv
        ON k.column_encryption_key_id=kv.column_encryption_key_id;

The results should be:

The DLL to create the Logging.Log table before we applied encryption looked like this:

CREATE TABLE [Logging].[Log] (
    [Id] [int] IDENTITY (1, 1) NOT NULL
        CONSTRAINT PK_Log PRIMARY KEY  CLUSTERED,
    [Date] [datetime] NOT NULL,
    [Thread] [varchar] (255) NOT NULL,
    [Level] [varchar] (50) NOT NULL,
    [Logger] [varchar] (255) NOT NULL,
    [User] [nvarchar] (50) NULL,
    [ClientIP]
        [nvarchar](45) -- Why 45? Because IPv6 Why Nvarchar? Because Log4Net
        NULL,
    [Message] [varchar] (4000) NOT NULL,
    [Exception] [varchar] (2000) NULL
);
CREATE INDEX IX_Logging_Log_Date ON Logging.Log([Date]) INCLUDE (Message,Exception);

Note that User and ClientIP are simple NVARCHAR columns.

If you script out the DDL for the Logging table, the User and ClientIP columns will look like the following:

  [User] [nvarchar](50) 
  COLLATE Latin1_General_BIN2 
  ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = [LogColumnsKey],
    ENCRYPTION_TYPE = Deterministic, 
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
  )
  NULL,
  [ClientIP] [nvarchar](45)
  COLLATE Latin1_General_BIN2 
  ENCRYPTED WITH (
    COLUMN_ENCRYPTION_KEY = [LogColumnsKey],
    ENCRYPTION_TYPE = Deterministic,
    ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
  )
  NULL,

Two things of note is that the collation was changed from the default Latin1_General_CI_AI to its binary equivalent of Latin1_General_BIN2. Without getting too much into the weeds of collation, encrypted columns must use a binary encryption, which means that string comparison operations (e.g User = 'López') are case sensitive and accent mark insensitive.

Conclusion

Always Encrypted is a great feature for protecting sensitive data in your database. However, its key design feature of handing the encryption client side means it cannot be managed in pure T-SQL. PowerShell is the best tool for the job whether you are managing Always Encrypted ad hoc from the command line, or as part of an automation framework such as CI/CD.