YubiHSM 2 for Microsoft SQL Server Deployment Guide
YubiHSM 2 for Microsoft SQL Server Guide
In a Microsoft SQL Server environment, the Column Master Key (CMK) must be protected in hardware. The YubiHSM 2 protects the CMK in hardware and guards the Microsoft SQL Server database encryption services.
This guide is intended to help systems administrators deploy YubiHSM 2 for use with Microsoft SQL Server in a Windows server environment. The expected outcome is that the Column Master Key (CMK) is created securely on a YubiHSM 2 and that a hardware-based backup copy of key materials has been produced.
These guidelines for deployment cover basic topics, so the instructions should be modified as required for your specific environment. It is assumed that you are familiar with the concepts and processes for working with Microsoft SQL Server. It is also assumed that the installation is performed on a single Microsoft SQL Server database, but the concept can be extended to more servers and databases.
Before deploying to production, we recommend that you install and test the Microsoft SQL Server installation and setup of the YubiHSM 2 in a test or lab environment.
For guidance on enabling the Microsoft SQL Server feature Always Encrypted in a production environment, see the Microsoft SQL Docs for Always Encrypted.
Introduction to Always Encrypted
Introduced in 2016, Microsoft SQL Server’s Always Encrypted feature enables the encryption of selected columns in a database.
The YubiHSM 2 requires Microsoft SQL Server 2017 and Microsoft SQL Server Management Studio (SSMS) 2018.
The Always Encrypted encryption mechanisms rely upon two cryptographic keys, described in detail in the Microsoft SQL Docs, Overview of Key Management for Always Encrypted. In brief:
- The Column Encryption Key (CEK) is a symmetric key used for encrypting the contents of a selected database column.
- The Column Master Key (CMK) is an asymmetric key that is used for protecting the encryption key. The CMK for Always Encrypted can be protected in a local key store, which is in the scope of this document, or in a centralized key store, which is not in scope.
A CMK can be stored in a local key store that supports Microsoft’s Cryptography Next Generation (CNG) API. To protect the CMK in hardware, a hardware security module (HSM) should be used. In this scenario, Always Encrypted accesses the HSM through the CNG API by using a key storage provider (KSP).
To protect the CMK in hardware, the YubiHSM 2 can be deployed as the local key store. Microsoft’s Always Encrypted accesses the YubiHSM 2 through the KSP that is provided with the YubiHSM software tools. With this setup, the Microsoft SQL Server Management Studio (SSMS) can be used to manage the CMK in the YubiHSM 2 device. This deployment guide describes two ways to generate the CMK and CEK in YubiHSM 2:
Prerequisites and Preparations
The audience of this document is an experienced system administrator with a good understanding of Microsoft SQL Server management. In addition, it is helpful to be familiar with the terminology, software, and tools specific to YubiHSM 2. As a primer for these terms, see the Glossary.
To follow the steps provided in this guide, the complete the following prerequisites:
- Microsoft Windows Server 2022 or higher, with Microsoft .NET Framework 4.8 or higher. The operating system should be installed in a secure computer network. The system administrator must also have elevated system privileges.
- Access to Microsoft SQL Server 2019 with SQL Server Management Studio (SSMS) 2018 or higher.
- YubiHSM 2 software and tools for Windows downloaded from the Yubico YubiHSM 2 Release page and available on the system to be used.
The 32-bit version of the YubiHSM KSP DLL is needed for use with SSMS.
Configuration for this Integration
For the integration described in this guide, the following hardware and software configuration was used:
- Microsoft Windows Server 2022.
- Microsoft .NET Framework 4.8.
- Microsoft SQL Server 2019.
- Microsoft SQL Server Management Studio (SSMS) 2018.
- Yubico YubiHSM v 2.1.2.
- Yubico YubiHSM software tools v 2021.12c. In particular, the 32-bit YubiHSM KSP DLL is needed for use with SSMS (which is a 32-bit application).
Basic Setup of YubiHSM 2 and SQL Server
Installing and Configuring YubiHSM 2
Install and configure the YubiHSM 2 device and software using the instructions in the following sections in the YubiHSM 2 with Key Storage Provider for Windows Server—Configure YubiHSM 2 Key Storage Provider for Microsoft Windows Server, see Key Splitting and Key Custodians.
When these instructions have been completed, the YubiHSM 2 should be configured with — for example — one domain with a wrap key (id
0x0002), an application authentication key (id
0x0003), and an audit key (id
0x0004). The configuration of the YubiHSM 2 can be inspected by using the YubiHSM-Shell in a command prompt as shown in the screenshot below.
Figure - Example of the YubiHSM 2 basic configuration
Creating a Test Database
Create a test database that will be used for the Always Encrypted deployment with YubiHSM 2. A test database can be downloaded from Microsoft’s official repository at Wide World Importers sample database v1.0 If you already have a Microsoft SQL Server database installed, you can skip ahead to Configure SSMS for Database Encryption.
At least one row with values needs to be inserted into the database table before the columns are encrypted (see the example of a test database below).
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
To generate the CMK for a database, create and save the following PowerShell script to generate Always Encrypted Key. Save this script as
$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)
Once completed, verify the Network Location is set to Private or Domain. To do so:
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.
Figure - New Column Master Key listed in YubiHSM
Assign the CMK to a Database
Generate the CEK
The next task is to generate the CEK for a database.
Use PowerShell Script to Generate the CMK and CEK
Instead of using SSMS to generate the CMK and CEK (as described in the foregoing section, Use SSMS to Generate the CMK and CEK), another option is to use PowerShell to generate the CMK and CEK. Microsoft has published a PowerShell script that can be used to generate the CMK in an HSM. The following instructions list the activities in the script, then describe how to modify that PowerShell script to generate the CMK in the YubiHSM 2 by calling its KSP.
Create a CMK in the YubiHSM 2 with CNG Provider (KSP)
$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)
Import SQL Server Module
Connect to your Database
$serverName = "<server name>" $databaseName = "<database name>" $connStr = "Server = " + $serverName + "; Database = " + $databaseName + "; Integrated Security = True" $connection = New-Object Microsoft.SqlServer.Management.Common.ServerConnection $connection.ConnectionString = $connStr $connection.Connect() $server = New-Object Microsoft.SqlServer.Management.Smo.Server($connection) $database = $server.Databases[$databaseName]
Create SQL CMK Settings Object for your CMK
$cmkSettings = New-SqlCngColumnMasterKeySettings -CngProviderName $cngProviderName -KeyName $cngKeyName
Create CMK Metadata in Database
$cmkName = "CMK-YubiHSM-PS" New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings -Verbose
Generate CEK, Encrypt with CMK, and Create CEK Metadata in Database
$cekName = "CEK-YubiHSM-PS" New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKeyName $cmkName -Verbose
Customize the Script
To customize this script, change the placeholders
For the test database used in this example, the
Save the PowerShell script file in a folder with an appropriate name, for example
Execute the script.
The PowerShell script generates the CMK and the CEK and displays the output from these operations. Output from the script given in Create a CMK in the YubiHSM 2 with CNG Provider (KSP) is shown in the screenshot below.
Figure - PowerShell script to create Column Master Key and Column Encryption Key
Validate Generation of the CMK and the CEK
Encrypt Database Columns
Database columns can be encrypted with PowerShell- or SSMS-generated keys.
Encrypt Database Column with PowerShell-Generated Keys
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 MK-YubiHSM-SSMS.
Verify Encrypted Database Column
To check that the columns have been encrypted.
Configure SSMS for Database Encryption
To configure Microsoft SQL Server and SSMS with the basic database settings needed for testing Always Encrypted in conjunction with YubiHSM 2, set SSMS to display the encrypted columns in clear text.