Encrypt Database Columns

Database columns can be encrypted with PowerShell- or SSMS-generated keys.

Encrypt Database Column with PowerShell-generated Keys

Step 1 To encrypt a database column, expand the database’s columns: Databases > Database-Name > Tables > Table-Name > Columns. Our example expands the tree Databases > Sales3 > Tables > dbo.Table_Customers > Columns, as shown in the screenshot below.


Figure 15 - Expanded columns to be encrypted

Step 2 Right-click the column to be encrypted and select Encrypt Column… In our example, right-click the table Name and select Encrypt Column… The Introduction window in the SSMS Always Encrypted wizard appears:


Figure - 16 Always Encrypted wizard: Introduction

Step 3 Click Next. The Column Selection window of the Always Encrypted wizard appears:


Figure 17 - Always Encrypted wizard: Column Selection

Step 4 In this example, the CEK that was generated with the PowerShell script is used for encrypting the database column. In the Column Selection window, select the Encryption Key named CEK-YubiHSM-PS. The Encryption Type can be set to either Deterministic or Randomized; in this example Deterministic is selected. Click Next, and the Master Key Configuration window in the Always Encrypted wizard appears.


Figure 18 - Always Encrypted wizard: Master Key Configuration

Step 5 In the Master Key Configuration window, click Next button, since the master column key in the YubiHSM 2 will be used. The Run Settings window in the Always Encrypted wizard appears.


Figure 19 - Always Encrypted wizard: Run Settings

Step 6 In the Run Settings window, select Proceed to finish now (unless you want to generate a PowerShell script to run later) and click Next. The Summary window in the Always Encrypted wizard appears.


Figure 20 - Always encrypted wizard: Summary

Step 7 Review the settings in the Summary window and click Finish. The Results window appears:


Figure 21 - Always encrypted wizard: Results

When the column encryption operation succeeds, the word “Passed” is displayed in the Details column of the relevant row in the Results window.

Encrypt Database Column with SSMS-generated Keys

to use the CMK and CEK that were generated in Use SSMS to Generate the CMK and CEK, follow the instructions above for encrypting a database column with PowerShell-generated keys (Encrypt Database Column with PowerShell-generated Keys), but select a different column (for example, Address) and use the column encryption key CEK-YubiHSM-SSMS and the related column master key CMK-YubiHSM-SSMS.

Verify Encrypted Database Column

To check that the columns have been encrypted, expand the object Database > Database-Name (in our example the database name is Sales3). Then select New Query in the top menu. Type the SQL query in the example below and click Execute.

SELECT * FROM Table_Customers;

Figure 22 – Checking the encrypted columns