Use SSMS to Generate the CMK and CEK

The Microsoft SQL Server Management Studio (SSMS) provides tools for manual creation of the CMK and CEK. However, using a PowerShell script (see Use PowerShell Script to Generate the CMK and CEK will result in a uniform configuration and ensure no options are missed. Note that all the examples and screenshots in this document are based on different key names being used for the SSMS wizard and the PowerShell script.

Generate the CMK

Step 1:

To generate the CMK for a database, create and save the following PowerShell script to generate Always Encrypted Key. Save this script as AlwaysEncryptedKey-PS.ps1

$cngProviderName = "YubiHSM Key Storage Provider"
$cngAlgorithmName = "RSA"
$cngKeySize = 2048 # Recommended key size for column master keys
$cngKeyName = "AlwaysEncryptedKey-PS" # Name identifying your key in the KSP
$cngProvider = New-Object
System.Security.Cryptography.CngProvider($cngProviderName)
$cngKeyParams = New-Object
System.Security.Cryptography.CngKeyCreationParameters
$cngKeyParams.provider = $cngProvider
$cngKeyParams.KeyCreationOptions =
[System.Security.Cryptography.CngKeyCreationOptions]::OverwriteExistingKey
$keySizeProperty = New-Object
System.Security.Cryptography.CngProperty("Length",
[System.BitConverter]::GetBytes($cngKeySize),
[System.Security.Cryptography.CngPropertyOptions]::None);
$cngKeyParams.Parameters.Add($keySizeProperty)
$cngAlgorithm = New-Object
System.Security.Cryptography.CngAlgorithm($cngAlgorithmName)
$cngKey = [System.Security.Cryptography.CngKey]::Create($cngAlgorithm,
$cngKeyName, $cngKeyParams)
Step 2:

Run the AlwaysEncryptedKey-PS.ps script from a PowerShell Window with elevated/administrator permissions.

Step 3:

Once completed, verify the Network Location is set to Private or Domain. To do so, view the current Profile assigned to the Network Connection by using the command Get-NetConnectionProfile. If the Profile is set to Public change it to Private or Domain so that SQL can communicate properly with the YubiHSM. To change it, use the command Set-NetConnectionProfile -InterfaceAlias Ethernet1 -NetworkCategory "Private"

Validate Generation of the CMK

The presence of the asymmetric CMK in the YubiHSM 2 can also be validated by running the following sequence of YubiHSM-Shell commands in a command prompt:

$yubihsm> connect
$yubihsm> session open <slot-ID> <password>
$yubihsm> list objects <session-ID>
$yubihsm> get objectinfo <session-ID> <key-ID> asymmetric-key

Example output from the YubiHSM-Shell commands is shown in the screenshot below.

_images/8-new-column-master-key-listed-in-yubihsm.png

Figure - New Column Master Key listed in YubiHSM

Assign the CMK to a Database

Step 1:

To assign the CMK for a database, launch SSMS and expand Databases > Database-Name > Security > Always Encrypted Keys > Column Master Key. We use the example shown below, expanding the tree Databases > Sales3 > Security > Always Encrypted Keys > Column Master Key.

_images/8_1-Assigning-the-CMK.png

Figure - Assigning the CMK

Step 2:

Right-click on Column Master Keys, and select New Column Master Key… In the New Column Master Key window, enter the following values:

  • In the Name text field, enter an appropriate name for the CMK, for example, “CMK-YubiHSM-SSMS”.
  • In the Key Store drop-down list, select Key Storage Provider (CNG).
  • In the Select a provider drop-down list, select YubiHSM Key Storage Provider.
  • In the bottom field, select AlwaysEncryptedKey-PS.

Generate the CEK

The next task is to generate the CEK for a database.

Step 1:

Generate the CEK.

Launch SSMS and expand Databases > Database-Name > Security > Always Encrypted Keys > Column Encryption Key. In our example, expand the tree Databases > Sales3 > Security >Always Encrypted Keys > Column Encryption Key, which is illustrated in the screenshot below.

_images/9-column-encryption-keys-in-ssms.png

Figure - Column Encryption Keys in SSMS

Step 2:

Right-click Column Encryption Keys, and select New Column Encryption Key….

Step 3:

In the New Encryption Master Key window, enter the following values:

  1. In the Name text field, enter an appropriate name for the CEK, for example CEK-YubiHSM-SSMS.

  2. In the Column master key drop-down list, select the CMK that was generated on the YubiHSM, for example CMK-YubiHSM-SSMS.

    _images/10-create-new-column-encryption-key-with-ssms.png

    Figure - Create new Column Encryption Key with SSMS

Step 4:

Generate and verify the CEK.

Press OK. To verify the success of the operation, check to see whether the CEK is listed under Always Encrypted Keys in SSMS.

_images/11-column-master-key-and-column-encryption-key-in-ssms.png

Figure - Column Master Key and Column Encryption Key in SSMS