Generate the encryption keys

Generate the Always Encrypted Column Master Key (CMK)

The CMK is protected by the nShield HMS.

  1. Log in to the client using the <domain>\dbuser, or a suitable security administrator account.

  2. Launch PowerShell and run the Generate_AECMK.ps1 script (shown below).

    $cngProviderName = "nCipher Security World Key Storage Provider"

$cngAlgorithmName = "RSA"

$cngKeySize = 2048

$cngKeyName = "AECMK"

$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)

    1. Run the following command:

      > PowerShell -ExecutionPolicy Bypass -File Generate_AECMK.ps1

      The following dialog appears.

      ps generate cmk create

    2. Select Next.

    3. Select the Operator Card Set Protection. Insert the OCS card in the HSM and select Next.

      ps generate cmk protection method

    4. Select the OCS and then Select Next.

      ps generate cmk token

    5. Select the HSM and select Finish.

      ps generate cmk hms

    6. Enter the OCS passphrase and select Next.

      ps generate cmk passphrase

    7. Select Finish.

      ps generate cmk card read

    A 2048-bit RSA key pair, called AECMK, has been generated. The key is encrypted in the HSM and then pushed to the requesting client server, where it is stored as an Application Key Token in the %NFAST_KMDATA%\local folder. That is, :\ProgramData\nCipher\Key Management Data\local.

  3. Verify the new key:

    C:\Users\Administrator.EXAMPLE>nfkminfo -k

Key list - 1 keys
 AppName caping               Ident user--e57798f862740453d02379579c1758ddfa2189db

  4. Display the information about the key by copy-pasting the key name above as follows:

    C:\Users\Administrator.EXAMPLE>nfkminfo -k caping user--e57798f862740453d02379579c1758ddfa2189db
Key AppName caping Ident user--e57798f862740453d02379579c1758ddfa2189db
 BlobKA length         1128
 BlobPubKA length      484
 BlobRecoveryKA length 1496
 name                  "AECMK"
 hash                  d9253d650283dafd8d62659f9fb74102b9edcf8c
 recovery              Enabled
 protection            CardSet
 other flags           PublicKey !SEEAppKey !NVMemBlob +0x0
 cardset               a165a26f929841fe9ff2acdf4bb6141c1f1a2eed
 gentime               2022-12-30 19:46:54
 SEE integrity key     NONE

BlobKA
 format                6 Token
 other flags           0x0
 hkm                   28ee9f7cfceba95992f1f3f31b39c8dba7cfa960
 hkt                   a165a26f929841fe9ff2acdf4bb6141c1f1a2eed
 hkr                   none

BlobRecoveryKA
 format                9 UserKey
 other flags           0x0
 hkm                   none
 hkt                   none
 hkr                   55c38c84103d95278fd54b6b5b3e67d614db8538

BlobPubKA
 format                5 Module
 other flags           0x0
 hkm                   c2be99fe1c77f1b75d48e2fd2df8dffc0c969bcb
 hkt                   none
 hkr                   none

Extra entry #1
 typecode              0x10000 65536
 length                60
Not a blob

Generate My Column Master Key (MyCMK) and My Column Encryption Key (MyCEK) with SSMS

This key will encrypt all subsequent Column Encryption keys (CEKs) in your database.

  1. Log in to the client using the <domain>\dbuser account.

  2. Launch Microsoft SQL Server Management Studio.

  3. Connect to the database on the remote SQL server:

    1. Select the Login tab and set it as follows:

      ssms server connection login

    2. Select the Connection Properties tab, as set as follows:

      ssms server connection properties

    3. Select the Always Encrypted tab and select Enable Always Encrypted:

      ssms server connection enable ae

    4. Select Connect.

  4. Using the Object Explorer, select the Security directory under the required database, then select Always Encrypted Keys > Column Master Key > New Column Master Key.

    generate mycmk new

  5. Enter the following information on the Column Master Keys dialog:

    1. Enter a Name, for example MyCMK.

    2. Select Key Storage Provider (CNG) from the Key store drop-down list and then Select a provider.

    3. Select nCipher Security World Key Storage Provider from the drop-down list.

      The AECMK key created in an earlier step appears in Name.

    4. Select OK to create a new key using the nShield HSM and CNG KSP.

      generate mycmk info

  6. Select Next.

    The newly-created MyCMK is created in the database under Security > Always Encrypted Keys > Column Master Keys.

    generate mycmk shown

  7. Using Object Explorer, select the Security directory under the required database. Select Always Encrypted Keys to expand it, then select New Column Encryption Key.

  8. Enter Name, select the CMK, then select OK.

    generate mycek info

  9. Present the OCS and then select Next.

    generate mycek load key

  10. Select the HSM and then select Finish.

    generate mycek select hsm

  11. Enter the passphrase and then select Next.

    generate mycek enter passphrase

  12. Select Finish after the OCS card reading completes.

    generate mycek card reading complete

    The newly-created MyCEK is in the database under Security > Always Encrypted Keys > Column Encryption Keys.

    generate mycek showing

Generate MyCMK and MyCEK with PowerShell

To generate MyCMK and MyCEK with PowerShell:

  1. Delete MyCEK and MyCMK in that order created above by right-clicking each key and selecting Delete.

  2. Launch PowerShell and run the Generate_MyCMK_and_MyCEK.ps1 script (below).

    # Import the SqlServer module.
Import-Module SqlServer

# Connect to database.
$ConnectionString = "Data Source=MS-SQL-AE-Srv.interop.com,1433;Initial Catalog=TestDatabase;Trusted_Connection=True;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Packet Size=4096;Application Name=`"Microsoft SQL Server Management Studio`""
$Database = Get-SqlDatabase -ConnectionString $ConnectionString

# Create a SqlColumnMasterKeySettings object for your column master key.
$cmkSettings = New-SqlCngColumnMasterKeySettings -CngProviderName "nCipher Security World Key Storage Provider" -KeyName "AECMK"

# Create column master key metadata in the database.
New-SqlColumnMasterKey -Name "MyCMK" -InputObject $Database -ColumnMasterKeySettings $cmkSettings

# Generate a column encryption key, encrypt it with the column master key and create column encryption key metadata in the database.
New-SqlColumnEncryptionKey -Name "MyCEK" -InputObject $Database -ColumnMasterKey "MyCMK"

    The command line is:

    > PowerShell -ExecutionPolicy Bypass -File Generate_MyCMK_and_MyCEK.ps1

Name
-----
MyCMK
MyCEK

  3. Present the OCS, select the HSM, and enter the passphrase.

  4. Check the newly-created MyCMK and MyCEK are present.