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.
|
Important
|
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. |
Introduced in 2016, Microsoft SQL Server’s Always Encrypted feature enables the encryption of selected columns in a database.
Note: 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. In order 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).
In order 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:
By using the SSMS wizard, as described in Use SSMS to Generate the CMK and CEK
By running a PowerShell script, as described in Use PowerShell Script to Generate the CMK and CEK.