Prashanth Jayaram

How to configure Always Encrypted in SQL Server 2016 using SSMS, PowerShell and T-SQL

October 2, 2017 by

In an era of remote storage and retrieval of data, including the cloud, data security plays a vital role, especially since it’s vulnerable during the transit. Situations like database backup or copy from or to the cloud, there is always a risk of data exposure to outside world lurking around one corner or the other. We have seen a noticeable surge in the technologies around protection and security of data from the world full of unsafe hands. Efforts are being made to protect data at a very granular level of the encryption hierarchy. Protection of business data cannot be stressed upon more.

One way of inching towards the more secure transmission of data is to enable Always Encrypted on the database. We’ll look into the various options we have, including enabling this at granular levels; we’ll look at enabling this at the column level.

The Always Encrypted feature was available only on the Enterprise and Developer editions of SQL Server 2016. Later, this feature was made available on all editions, with SQL Server 2016 SP1. Always Encrypted has the ability to encrypt data even at the column level.

There are several ways to configure the Always Encrypted feature:

  • Using the Always Encrypted wizard
  • Configuring AE using SSMS
  • Create Master Key and Encryption Key using T-SQL and enabling encryption
  • Configuring Always Encrypted using PowerShell

Overview of the Always Encrypted Feature

Always Encrypted feature is a handshake mechanism used to encrypt and decrypt data. Encryption here is achieved using certificates, and can be done only by users with access to the relevant certificates. To make a database column Always Encrypted, you must specify the encryption algorithm and the cryptographic keys that are used to protect the data. Always Encrypted needs two keys:

  1. Column Encryption Key (CEK)
  2. Column Master Key (CMK)

A Column Encryption Key is used to protect and encrypt data in a column. A Column Master Key is used to protect the (one or more) column encryption keys. The information about the Column Master Key is stored in external key stores like:

  • Azure Key Vault: A key vault used to safeguard and manage cryptographic keys and secrets used for encryption and decryption of sensitive data within Microsoft Azure.
  • Windows Certificate Store: A certificate container built into Windows that stores and manages the certificates.
  • Hardware Security Module (HSM): A hardware device specially designed to securely store sensitive data

Selecting Deterministic or Randomized Encryption

Always Encrypted supports two types of encryption: randomized and deterministic

  • Deterministic encryption
    • The same encrypted Key for a given value is generated, every time.
    • Binary2 sort order collation must be used to setup deterministic encryption on a column.
    • Heuristically studying the patterns of the contents of the column could reveal the contents, thereby making it more susceptible to hacking
  • Randomized encryption
    • This method is more robust and secure, and the patterns are less likely to be predictable due to its random generation of the key for a given value.
    • The limitation with this type of encryption is that searching, join, group and, indexing is not possible

In an age of centralized or remote management of data, it is important that the enterprises add an abstraction layer to their data. This way, those who manage the data on a day-to-day basis, such as database administrators are not able to view or use the data. At the same time, those in the enterprise who own the data, have complete access to the data, even though they may not necessarily manage it.

Apart from being the layer of abstraction, Always Encrypted also ensures encryption of data during transit, thereby protecting it from sniffers—typically those involved in attacks such as Man in the Middle.

Configuring Always Encrypted

To set up Always Encrypted, we need to generate the following:

  1. Key metadata
  2. Encryption properties of the selected database columns, and/or encrypting the data that may already exist in columns that need to be encrypted.

However, not all of these are supported in T-SQL. Therefore, we need to use client-side tools, such as the SQL Server Management Studio or PowerShell to accomplish these tasks.

Task SSMS PowerShell T-SQL
Prototyping Column Master Key and Column Encryption Key Yes Yes No
Registering the Master key and Column Encryption Key metadata Yes Yes Yes
Table creation with column encryption Yes Yes Yes
Defining column encryption on an existing database columns Yes Yes No

Using SSMS

  • Use the Object Explorer to locate the database – SQLShackAlwaysEncrypted
  • Go to the Security tab
  • Select the Always Encrypted Keys option

  • Right-click and select New Column Master Key….
  • Enter the name of the Master Key SQLShackDemoCertificate
  • Specify Key store, (Windows Certificates Store in this case) for the current user or local machine certificate store, or the Azure Key Vault and then select a certificate from the list. You can even one by clicking the Generate certificate option.
  • Click OK

The above steps create a self-signed certificate and load it into the store.

Now, we need to distribute the certificate to all the client machines by using the Export and Import Certificates method.

  • Now, select New Column Encryption Keys.

  • Enter the name of the column encryption key SQLShackDemoCEK
  • Use the drop-down and select the Column Master Key SQLShackDemoCertificate
  • Click OK
  • Verify the Always Encrypted Keys

We have successfully completed the configuration. Now, it’s time to apply the encryption settings to the column(s) by browsing the table and selecting the needed column(s) for encryption.

  • Browse the Columns tab
  • Right-click the column and select Encrypt Column
  • Select the Encryption Type: Select either of the available options, since Always Encrypted supports two types of encryption: Randomized and Deterministic
  • Use the drop-down, and select the Column Encryption Key, which is already tied with the Column Master Key
  • Click Next

  • Click Next

  • Click default Proceed to finish radio button

  • Verify the summary of settings and click Finish

  • Validate the results

  • Query the table and view the encrypted column. In the below screenshot, the Servername column is encrypted.

In order to decrypt the column, the following settings should be enabled in the SSMS client

  • First, add Column Encryption Setting = Enabled in the Additional Connection Parameters in the SSMS Connect to Server window.

  • Now, query the table for the encrypted values

  • And voila!

Using PowerShell

Let us now go through the step-by-step procedure of configuring Always Encrypted using PowerShell.

On Windows 2016, the creation of New-SelfSignedCertificate is pretty straight forward, and it comes with a long list of parameters. The example which I’m walking through is done on Windows 2012. This has a significant limitation on the type of certificate that can be created using PowerShell and the APIs. The New-SelfSignedCertificateEx is an enhanced version of Windows 2012 New-SelfSignedCertificate cmdlet.

Step 1: Certificate Management

The first step is to create a self-signed certificate with all the necessary information related to loading it into the certificate store of the current user context.

  • Create certificate with KeyUsage as DataEncipherment and a friendlyname
  • The list of other parameters used in the self-signed certificate creation process is explained below

After downloading, create the function New-SelfsignedCertificateEx and call the function with the available parameters as shown below

  1. The KeyUsage — this parameter defines the purpose of the public key contained in the certificate. It’s a way of providing restrictions on the operations that can be performed by the public key. With DataEncipherment, the public key is used to encrypt user data, apart from the cryptographic keys.
  2. SignatureAlgorithm —the default ‘SHA1’ algorithm is used.
  3. FriendlyName — specifies a friendly name for the certificate.
  4. StoreLocation — specifies the location to store self-signed certificate. Possible values are ‘CurrentUser’ and ‘LocalMachine’. ‘CurrentUser’ store is intended for user certificates; computer (as well as CA) certificates are usually stored in the ‘LocalMachine’ store.

Let’s proceed further.

  • Locate the newly-created certificate SQLShack Demo Encryption certificate

  Get-ChildItem -Path cert:\CurrentUser\My | Where-Object {($_.FriendlyName -eq ‘SQLShack Demo Encryption
 certificate’) }

  • Export the certificate using the Export-Certificate cmdlet

 PS C:\Windows\system32> $SQLShackCertificate = Get-ChildItem -Path cert:\CurrentUser\My | Where-Object  {($_.FriendlyName -eq ‘SQLShack Demo Encryption certificate’) }
 PS C:\Windows\system32> $SQLShackCertificate | Export-Certificate -FilePath  “F:\PowerSQL\SQLShackAEPublic.cer” -Force

We can copy the certificates to all intended client machines by manually copying the files. To install the certificate, right-click and select install the certificate and follow the instructions. Alternatively, we can also use the Import-certificate cmdlet to import the certificates.

Step 2: Import SQL Server module

The SqlServer module is an external package. Hence this has to be installed as a separate package. You can download and install SqlServer in very few simple steps.

Import-Module SqlServer

Step 3: Define the Connection String

This step is to prepare the SQL Server connection string and prepare the database to be Always Encrypted. Once the SQL Server module is loaded, it’s very simple and straight forward to define the connection string and use it.

Step 4: Create Master Column Encryption Key (CMK)

Create a ColumnMasterKeySettings object using the New-SqlCertificateStoreColumnMasterKeySettings cmdlet. This loads the certificate metadata into a variable called $ColumnMasterKeySetting. This setting variable is referred while creating the ColumnMasterKey.

To create the ColumnMasterKey (CMK), the cmdlet New-SqlColumnMasterKey is used, which requires the following references as its parameters

  • Name of the CMK
  • Database
  • CMK settings

That’s it; we have our Column Master Key now.

Step 5: Configure the Column Encryption Key (CEK)

Let’s now proceed to create the Column Encryption Keys. The .NET driver enables the use of Column Encryption Keys to encrypt and decrypt the data during data exchange between the client and the SQL Server. The driver provides the extra layer of protection in order to secure the data during interchange. The New-SqlColumnEncryptionKey cmdlet is being used to create the Column Encryption Key. This requires three input parameters:

  • CEK (Column Encryption Key) name
  • Database deference
  • CMK Name

Step 6: Migrate the schema

It’s time to integrate the columns with the Always Encrypted feature now. In the following steps, the column, name, and the database databases are encrypted using SQLShack_CEK_1 Column Encryption Key.

Step 7: Encryption validation

We’re all set for the testing and validation of the data. Let’s try to access the data by setting two connection strings, one with Column Encryption setting and the other without the Encryption setting.

We can see that the Name column is encrypted and the contents look like a series of numbers as shown below. We can decrypt the column by setting Column Encryption Setting to Enabled. Now, the data in the Name column is readable.

Did that succeed? Let’s now try connecting, using a connection string set to use the Column Encryption Setting.

Using T-SQL

This section talks about using T-SQL to create Column Master Key and Column Encryption Key along with creating encrypted columns in a table. The following are the three tables that are very important to get the required key information to create Master and Column encryption keys.

Step 1: Create Column Master Key

To get the provider name and the key path details, query the system view sys.column_master_keys

Use the create column master key DDL to define the SQLShackAECMK master key

Step 2: Create Column Encryption Key

To get encrypted_value and algorithm details, query the following system views sys.column_encryption_key_values and sys.column_encryption_keys

Use the create column encryption key DDL to create the SQLShackAECEK CEK key

Step 3: Create Encrypted Columns table

Use the create table DDL with a few additional configurations in the column definition

That’s all.


In this article, we saw various ways to configure and enable the Always Encryption feature. Since data to the database flows from various sources, this feature gives an added security to the data and safeguards the same from various potential risks.

However, before proceeding with this, I recommend that all of the implications are fully understood, and the feature details are known.

Prashanth Jayaram