.. hsm2-ms-sql-server-deploy-guide.rst .. _hsm2-ms-sql-server-deploy-guide-label: ==================================================== 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. .. 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 `_. Introduction to 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. 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: * By using the SSMS wizard, as described in :ref:`hsm2-ms-sql-ssms-cmk-cek-label`. * By running a PowerShell script, as described in :ref:`hsm2-ms-sql-powershell-cmk-cek-label`. 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 :ref:`hsm2-glossary-label`. 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. .. Note:: The 32-bit version of the YubiHSM KSP DLL is needed for use with SSMS. * Two (2) `YubiHSM 2 devices `_, one for deployment and one for backup in hardware. * Your organization policies may require key custodians to be available for the YubiHSM 2 deployment. For more information about key custodians and the associated ``M of N`` key shares, see :ref:`hsm2-key-split-custodians-guide-label`. 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 :ref:`hsm2-key-split-custodians-guide-label`. * :ref:`hsm2-install-tools-software-label` * :ref:`hsm2-config-primary-device-label` * :ref:`hsm2-configure-software-windows-label` 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. .. image:: /graphics/ex-yubihsm2-basic-config.png :scale: 100% :align: center **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 :ref:`hsm2-ms-sql-ssms-encryption-label`. .. Note:: 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). :Step 1: Create a test database. a. Launch Microsoft SQL Server Management Studio (SSMS) 2018. b. Right-click on the **Databases** icon. c. Select **New Database…**. d. Enter an appropriate name for the database. In this guide, a test database named “Sales3” is used for the tests with Always Encrypted in conjunction with YubiHSM 2. .. image:: /graphics/ex-test-database.png :scale: 100% :align: center **Figure - Example of test database** :Step 2: Create table: a. Expand **Databases > Sales3 > Tables**. b. Right-click on **Tables** and select **Create new table**. c. Add some columns, for example “Name”, “Address”, “ZipCode”, “City”, “Country”. d. Save the table and give it a name - “Table_Customers” for example. .. image:: /graphics/ex-test-table.png :scale: 100% :align: center **Figure - Example of test table** :Step 3: Add one or more rows to the table: a. Expand **Databases > Sales3**. b. Right-click on **Table** and selecting **New > Query…**. c. Use the SQL Query window to insert rows into the database table, for example, with the SQL query shown below. .. image:: /graphics/ex-sql-query-insert-values.png :scale: 100% :align: center **Figure - Example of SQL query to insert values into the table** .. _hsm2-ms-sql-ssms-cmk-cek-label: 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 :ref:`hsm2-ms-sql-powershell-cmk-cek-label` 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``. .. code-block:: bash $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. .. code-block:: bash 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. .. code-block:: bash 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. .. code-block:: bash $yubihsm> connect $yubihsm> session open $yubihsm> list objects $yubihsm> get objectinfo asymmetric-key Example output from the YubiHSM-Shell commands is shown in the screenshot below. .. image:: /graphics/master-key-listed-yubihsm.png :scale: 100% :align: center **Figure - New Column Master Key listed in YubiHSM** Assign the CMK to a Database ----------------------------- :Step 1: To assign the CMK for a database. a. Launch SSMS. b. 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**. .. image:: /graphics/assign-cmk.png :scale: 100% :align: center **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. a. Launch SSMS. b. 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. .. image:: /graphics/column-encryption-keys-ssms.png :scale: 100% :align: center **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: a. In the **Name** text field, enter an appropriate name for the CEK, for example ``CEK-YubiHSM-SSMS``. b. In the **Column master key** drop-down list, select the CMK that was generated on the YubiHSM, for example ``CMK-YubiHSM-SSMS``. .. image:: /graphics/create-column-encryption-key-ssms.png :scale: 100% :align: center **Figure - Create new Column Encryption Key with SSMS** :Step 4: Generate and verify the CEK. a. Press **OK**. To verify the success of the operation. b. Check to see whether the CEK is listed under **Always Encrypted Keys in SSMS**. .. image:: /graphics/column-masterkey-encryptionkey-ssms.png :scale: 100% :align: center **Figure - Column Master Key and Column Encryption Key in SSMS** .. _hsm2-ms-sql-powershell-cmk-cek-label: 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, :ref:`hsm2-ms-sql-ssms-cmk-cek-label`), 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. .. _hsm2-ms-sql-cmk-cng-provider-label: Create a CMK in the YubiHSM 2 with CNG Provider (KSP) ------------------------------------------------------ .. code-block:: bash $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 -------------------------- .. code-block:: bash Import-Module "SqlServer" Connect to your Database -------------------------- .. code-block:: bash $serverName = "" $databaseName = "" $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 -------------------------------------------- .. code-block:: bash $cmkSettings = New-SqlCngColumnMasterKeySettings -CngProviderName $cngProviderName -KeyName $cngKeyName Create CMK Metadata in Database ---------------------------------- .. code-block:: bash $cmkName = "CMK-YubiHSM-PS" New-SqlColumnMasterKey -Name $cmkName -InputObject $database -ColumnMasterKeySettings $cmkSettings -Verbose Generate CEK, Encrypt with CMK, and Create CEK Metadata in Database -------------------------------------------------------------------- .. code-block:: bash $cekName = "CEK-YubiHSM-PS" New-SqlColumnEncryptionKey -Name $cekName -InputObject $database -ColumnMasterKeyName $cmkName -Verbose Customize the Script --------------------- :Step 1: To customize this script, change the placeholders ``server name`` and ``database name`` to the actual values of the Microsoft SQL Server name and the database used. For the test database used in this example, the ``database name`` is set to ``Sales3``, while the ``server name`` should be set to the name of your Windows server. :Step 2: Save the PowerShell script file in a folder with an appropriate name, for example ``CreateColumnMasterAndEncryptionKeys-YubiHSM.ps1``. :Step 3: Execute the script. a. Launch a command prompt with administrator privileges b. Enter the PowerShell mode by typing ``PowerShell``. c. Navigate to the directory where the PowerShell script is located. d. Execute the script: .. code-block:: bash PS> .\CreateColumnMasterAndEncryptionKeys-YubiHSM.ps1 The PowerShell script generates the CMK and the CEK and displays the output from these operations. Output from the script given in :ref:`hsm2-ms-sql-cmk-cng-provider-label` is shown in the screenshot below. .. image:: /graphics/powershell-script-create-column-keys.png :scale: 100% :align: center **Figure - PowerShell script to create Column Master Key and Column Encryption Key** Validate Generation of the CMK and the CEK -------------------------------------------- :Step 1: After executing the PowerShell script. a. Switch back to SSMS. b. Expand the objects **Databases > Database-Name > Security > Column Master Key and Databases > Database-Name > Security > Column Encryption Key**. c. Right-click each object and select the **Refresh** option. The CMK and CEK that were generated by the PowerShell script appear in SSMS as ``CMK-YubiHSM-PS`` and ``CEK-YubiHSM-PS`` respectively. .. image:: /graphics/refreshed-column-keys-ssms.png :scale: 100% :align: center **Figure - Refreshed Column Master Keys and Column Encryption Keys in SSMS** :Step 2: Verify the presence of the asymmetric CMK in the YubiHSM 2 by running the following sequence of YubiHSM-Shell commands in a command prompt. .. code-block:: bash $yubihsm> connect $yubihsm> session open $yubihsm> list objects $yubihsm> get objectinfo asymmetric-key Example output for the YubiHSM-Shell commands is shown in the screenshot below. .. image:: /graphics/column-master-keys-yubihsm2.png :scale: 100% :align: center **Figure - Column Master Keys in YubiHSM 2** Encrypt Database Columns ========================= Database columns can be encrypted with PowerShell- or SSMS-generated keys. Encrypt Database Column with PowerShell-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. .. image:: /graphics/columns-tobe-encrypted.png :scale: 100% :align: center **Figure - 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: .. image:: /graphics/always-encrypted-wizard-intro.png :scale: 100% :align: center **Figure - Always Encrypted wizard: Introduction** :Step 3: Click **Next**. The Column Selection window of the Always Encrypted wizard appears: .. image:: /graphics/always-encrypted-wizard-column-selection.png :scale: 100% :align: center **Figure - 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. a. 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. b. Click **Next**, and the **Master Key Configuration** window in the Always Encrypted wizard appears. .. image:: /graphics/always-encrypted-wizard-masterkey-config.png :scale: 100% :align: center **Figure - Always Encrypted wizard: Master Key Configuration** :Step 5: In the **Master Key Configuration** window, click **Next**, since the master column key in the YubiHSM 2 will be used. The **Run Settings** window in the Always Encrypted wizard appears. .. image:: /graphics/always-encrypted-wizard-run-settings.png :scale: 100% :align: center **Figure - 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. .. image:: /graphics/always-encrypted-wizard-summary.png :scale: 100% :align: center **Figure - Always encrypted wizard: Summary** :Step 7: Review the settings in the **Summary** window and click **Finish**. The **Results** window appears: .. image:: /graphics/always-encrypted-wizard-results.png :scale: 100% :align: center **Figure - 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. Encrypt Database Column with SSMS-generated Keys -------------------------------------------------- To use the CMK and CEK that were generated in :ref:`hsm2-ms-sql-ssms-cmk-cek-label` 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. :Step 1: Expand the object **Database > Database-Name**. In our example the database name is ``Sales3``. :Step 2: Select **New Query** in the top menu. :Step 3: Type the SQL query in the example below and click **Execute**. .. code-block:: bash SELECT * FROM Table_Customers; .. image:: /graphics/checking-encrypted-columns.png :scale: 100% :align: center **Figure – Checking the encrypted columns** .. _hsm2-ms-sql-ssms-encryption-label: 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. :Step 1: Select **Connect Object Explorer** settings. a. Click the **Connect Object Explorer** icon. The Connect to Server window appears. b. Click **Options**. c. Select the **Always Encrypted** tab and select **Enable Always Encrypted** (column encryption). d. To make the changes take effect, click the **Disconnect** icon and then the **Connect** icon. .. image:: /graphics/enable-always-encrypted-ssms.png :scale: 100% :align: center **Figure – Enable Always Encrypted in SSMS** :Step 2: Select Enable Parameterization. a. In the main menu, click **Query** and from the drop-down list, select **Query options…**. The **Query Options** window appears. b. Select **Execution > Advanced**. c. Select the checkbox for **Enable Parameterization for Always Encrypted**. .. image:: /graphics/enable-parameterization-always-encrypted-ssms.png :scale: 100% :align: center **Figure – Enable Parameterization for Always Encrypted queries in SSMS** These are the basic database settings in Microsoft SQL Server and SSMS for testing Always Encrypted in conjunction with YubiHSM. :Step 3: To verify the settings. a. Expand the object **Database > Database-Name**. In our example the database name is ``Sales3``. b. Select **New Query** in the top menu again. c. Re-enter the SQL query in the example below and click **Execute**. .. code-block:: bash SELECT * FROM Table_Customers; When the SSMS settings take effect, the encrypted database columns are decrypted, and the values displayed in clear text as shown in the screenshot below. .. image:: /graphics/decrypted-values-database-columns.png :scale: 100% :align: center **Figure – Decrypted values in the database columns**