Database columns can be encrypted with PowerShell- or SSMS-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.
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.
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