共用方式為

Using Hardware Security Modules with Always Encrypted

  • 發行項

Using Hardware Security Modules with Always Encrypted

In the examples from the previous articles on Always Encrypted, we demonstrated column master keys stored in Windows Certificate Store and in Azure Key Vault. In this article, we will introduce yet another option: storing column master keys in hardware security modules (HSMs).

HSM Primer and Introduction to HSM-protected Column Master Keys

A hardware security module (HSM) is a physical device that safeguards digital keys and performs cryptographic operations. These modules traditionally come in the form of a plug-in card or an external device that attaches directly to a computer or to the network. When you purchase an HSM, you also get software libraries implementing common APIs, such as Microsoft Crypto API and Cryptography API: Next Generation (CNG). Your applications can communicate with an HSM using those APIs. Libraries implementing Crypto API and CNG are respectively called Cryptographic Service Provider (CSP) and CNG providers.

.NET Framework 4.6.1 RC introduces two new column master key store provider classes,  SqlColumnEncryptionCspProvider and SqlColumnEncryptionCngProvider, which use the CSP and CNG providers to interact with an HSM to encrypt and decrypt column encryption keys with column master keys stored in an HSM. The below diagram illustrates the relationships between the new column master key store provider classes and the CSP/CNG cryptographic providers.

In the remainder of this article, we will demonstrate how to:

  1. Create a column master key and store it in an HSM.
  2. Generate the plaintext of a column encryption key and encrypt it with the column master key stored in the HSM.
  3. Create the metadata about both keys in the database, and use the keys to protect sensitive data in selected database columns.
  4. Implement an application that uses an HSM-protected column master key to access data in the protected columns.

We will use SqlColumnEncryptionCspProvider and a CSP provider (the steps for CNG are similar). If you do not have an HSM and its CSP provider, for testing/evaluation purposes you can use the default the Microsoft Enhanced RSA and AES Cryptographic Provider, which is available on each Windows machine.

Setup

Before you start, you will need:

  • A database either in SQL Server 2016 CTP3 (or later) or in Azure SQL Database V12.        
  • On a client machine, hosting your client applications:
    • .NET Framework 4.6.1 RC
    • A CSP Provider from your HSM vendor. Alternatively, you can use Microsoft Enhanced RSA and AES Cryptographic Provider.
  • SSMS (October 2015 preview – version 13.0.700.242 or later is recommended).

Step 1: Create a column master key in an HSM

The first step is to create a column master key inside an HSM. Your HSM administrator should be able to help you with that. One way to accomplish this task is to use key management tools that most HSMs come with. Alternatively, you can create a key programmatically using the CSP provider for the HSM. Note that you will need a permission to create the key in the HSM.

Here is how to do that in PowerShell.

Note: The file attachment in this article contains complete PowerShell code for all examples as well as the functionally equivalent code in C#. To compile the C# version in Visual Studio, you will need to install  .NET Framework 4.6.1 RC Targeting Pack (in addition to .NET Framework 4.6.1 RC).

 ## Get the type of the CSP Provider from the registry
 function Get-CSPProviderType {
 param(
 [string] $providerName
 )
 
 $cspProviderRegistryPath = "Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Cryptography\Defaults\Provider\" + $providerName
 $cspProviderType = Get-ItemPropertyValue -path $cspProviderRegistryPath -Name "Type"
 return [int]$cspProviderType
 }
 
 ## Generate the key by creating a new instance of RSACryptoServiceProvider. Persist the key in the container.
 function Create-CMKUsingCSP {
 param(
 [string] $cspProviderName, 
 [string] $keyContainerName
 )
 
 $cspProviderType = Get-CSPProviderType($cspProviderName)
 $keySize = 2048
 $cspParams = New-Object System.Security.Cryptography.CspParameters($cspProviderType, $cspProviderName, $keyContainerName)
 $rsaProvider = New-Object System.Security.Cryptography.RSACryptoServiceProvider($keySize, $cspParams)
 $rsaProvider.PersistKeyInCsp = $TRUE
 }

Three things to note about the above code:

  1. It generates a 2048-bit RSA key, which is actually a public-private key pair. Both keys are stored in a container inside the HSM. The name of the container is what identifies the RSA key inside the HSM. 2048 bytes is the recommended length of the key.
  2. The key is generated by constructing the RSACryptoServiceProvider object. In addition to the key size, the constructor needs CSP parameters: a CSP provider type, a CSP provider name, and a key container name.
  3. The CSP provider type is a numerical value that is stored in the provider registry key. Please note, that each CSP provider has a key in the registry under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Cryptography\Defaults\Provider. The Get-CSPProviderType helper function retrieves the provider type from the registry.

Step 2: Generate a column encryption key and encrypt it with an HSM-stored
column master key

Next, we need to generate a column encryption key and encrypt it with the specified column master key. Here is the PowerShell code for that:

 function Create-EncryptedCEKUsingCSP {
 param(
 [string] $cspProviderName, 
 [string] $keyContainerName
 )
 ## Generate a plaintext CEK
 $plaintextCEKLength = 32
 $cekPlaintext = new-object "System.Byte[]" $plaintextCEKLength 
 $rnd = New-Object System.Security.Cryptography.RNGCryptoServiceProvider
 $rnd.GetBytes($cekPlaintext)
 
 ## Encrypt the CEK
 $cmkKeyPath = $cspProviderName + "/" + $keyContainerName
 $encryptionAlgorithm = "RSA_OAEP"
 $cmkprov = New-Object System.Data.SqlClient.SqlColumnEncryptionCspProvider
 $cekCiphertext = $cmkprov.EncryptColumnEncryptionKey($cmkKeyPath, $encryptionAlgorithm, $cekPlaintext)
 $ciphertextInHex = ConvertBytesToHexString($cekCiphertext) 
 Write-Host($ciphertextInHex) 
 }

The key points about the above code are:

  1. A plaintext column encryption key is a random number of 32 bytes or 256 bits, which is the required length of column encryption keys for Always Encrypted.
  2. To encrypt the column encryption key, the above code calls the EncryptColumnEncryptionKey method of the SqlColumnEncryptionCspProvider provider class.
  3. In addition to the column encryption key to be encrypted and the encryption algorithm (it must be "RSA_OAEP"), the EncryptColumnEncryptionKey method requires a key path of the column master key, which consists of the CSP
    provider name and the name of the column master key container.
  4. The function outputs the encrypted column encryption key to the console. You need to take note of that value – you will need it later.

Step 3: Set up the keys in the database and configure encrypted columns

Now, you are ready to create metadata objects for both column master key and the column encryption key in the database. Issue the following T-SQL DDL using SSMS:

 CREATE COLUMN MASTER KEY [CMK1]
 WITH
 (
 KEY_STORE_PROVIDER_NAME = N'MSSQL_CSP_PROVIDER',
 KEY_PATH = N'Microsoft Enhanced RSA and AES Cryptographic Provider/AlwaysEncryptedCMK'
 
 )
 
 CREATE COLUMN ENCRYPTION KEY [CEK1]
 WITH VALUES
 (
 COLUMN_MASTER_KEY = [CMK1],
 ALGORITHM = 'RSA_OAEP',
 ENCRYPTED_VALUE = 0x0190…
 )

Note that:

  1. The KEY_STORE_PROVIDER_NAME property of the column master key entry in the database is set to 'MSSQL_CSP_PROVIDER'. This is the name the SqlColumnEncryptionCspProvider provider instance is pre-registered in ADO.NET with.
  2. For ENCRYPTED_VALUE, you need to paste in the exact ciphertext of the column encryption key, generated in the step 2.

Now, you can reference the defined column encryption key, when you set up encrypted columns in the database.

 CREATE TABLE [dbo].[Patients](
 [PatientId] [int] IDENTITY(1,1), 
 [SSN] [char](11) COLLATE Latin1_General_BIN2 
 ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, 
 ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
 COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL,
 [FirstName] [nvarchar](50) NULL,
 [LastName] [nvarchar](50) NULL, 
 [MiddleName] [nvarchar](50) NULL,
 [StreetAddress] [nvarchar](50) NULL,
 [City] [nvarchar](50) NULL,
 [ZipCode] [char](5) NULL,
 [State] [char](2) NULL,
 [BirthDate] [date] 
 ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, 
 ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256', 
 COLUMN_ENCRYPTION_KEY = CEK1) NOT NULL
 PRIMARY KEY CLUSTERED ([PatientId] ASC) ON [PRIMARY] 

 )

As an alternative to creating a new table using T-SQL, if you have an existing table with column you want to encrypt, you can use the Always Encrypted wizard, we discussed in this blog post. You just need to make sure SSMS is running on a machine with .NET Framework 4.6.1 installed, and then point the wizard to CEK1 (a metadata object referencing the column encryption key you generated in step 2).

Step 4: Query encrypted columns using HSM-protected column master key

Finally, you need an application that queries the columns protected with a column master key, you created. The good news is that you can use the console app from the first blog article: Getting Started with Always Encrypted. No changes specific to the use of the SqlColumnEncryptionCspProvider are required.

Final Remarks

By following the above steps, you generated a column master key and a column encryption key, you defined both keys in the database, and you implemented an application using both keys and accessing encrypted database columns. In reality, for a production application, these steps will likely be the responsibility of individuals assuming different roles in your organization. Role separation is important to maximize the benefits of Always Encrypted. Here is how the key management steps can be allocated to various roles:

Step Responsible Role
Generating column master key in an HSM. Security Administrator
Generating/encrypting column encryption key. Security Administrator or Application Operations
Defining column/master encryption key in the database. DBA

After generating a column master key, the Security Administrator would also generate and encrypt a column encryption key. Alternatively, the Security Administrator can grant a permission to access and use the column master key to the Application Operations team, who would generate and encrypt the column encryption key. The Security Administrator or Application Operations team would then pass the column master key location information (key path) and the encrypted value of the column encryption key to the DBA, who would issue T-SQL statements to created key metadata in the  database.

AlwaysEncryptedKeysUsingCSP.zip

Comments

  • Anonymous
    March 29, 2019
    hey guys, i have one question about this:In sql is possible to have HSM protected Certificates used by SQL instance on premises? are any constraints on this one?(note: you can also contact me with v-ducanu@microsoft.com)