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).
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:
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.
Before you start, you will need:
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:
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:
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:
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).
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.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.