Always Encrypted Keys Management in SQL Server

This document provides a comprehensive guide to managing keys used with Always Encrypted in SQL Server. Always Encrypted allows you to encrypt sensitive data within client applications. Key management is a critical aspect of securing your data.

Key Concepts

• Master Key: A key that encrypts other keys. For Always Encrypted, this is typically a Column Master Key (CMK).
• Data Encryption Key: A symmetric key used to encrypt the actual data in database columns. This key is encrypted by the Column Master Key.
• Column Master Key (CMK): The master key that encrypts Column Encryption Keys (CEKs). CMKs are stored outside of SQL Server for enhanced security.
• Column Encryption Key (CEK): The key that is used to encrypt the data in the encrypted columns.

Supported Key Stores

Always Encrypted supports various secure key stores for your Column Master Keys:

• Windows Certificate Store
• Azure Key Vault
• HSM (Hardware Security Module)

Managing Column Master Keys (CMKs)

CMKs are managed in your chosen key store. The process typically involves:

1. Creating the CMK: Generate a new key within your key store or import an existing key.
2. Granting Access: Ensure the SQL Server service account or the client application has appropriate permissions to access the CMK.
3. Registering with SQL Server: Create a reference to the CMK in SQL Server using the CREATE COLUMN MASTER KEY T-SQL statement.

Example: Registering a CMK from Azure Key Vault

This example demonstrates registering a CMK stored in Azure Key Vault.

-- Connect to your SQL Server instance
USE YourDatabase;
GO

-- Define the key store provider and Key URI
DECLARE @KeyStoreProviderName NVARCHAR(128) = 'Azure Key Vault';
DECLARE @KeyPath NVARCHAR(4000) = 'https://mykeyvault.vault.azure.net/keys/mykey/version'; -- Replace with your Key Vault URI

-- Create the Column Master Key metadata
CREATE COLUMN MASTER KEY MyCMK
WITH
    KEY_STORE_PROCEDURER `sys.sp_keyset(KEY_STORE_PROVIDER_NAME, @KeyStoreProviderName, KEY_PATH, @KeyPath);
GO
            

Managing Column Encryption Keys (CEKs)

CEKs are managed by SQL Server. When you encrypt a column using Always Encrypted, SQL Server automatically creates a CEK, encrypts it with the specified CMK, and stores it in the database metadata. You can manage CEKs using:

• SQL Server Management Studio (SSMS): Through the Always Encrypted wizard or by directly querying metadata.
• T-SQL: Using statements like CREATE COLUMN ENCRYPTION KEY.

Example: Creating a CEK

This T-SQL statement creates a new CEK and associates it with a CMK.

-- Define the CMK name and the key store provider name
DECLARE @CMK_Name NVARCHAR(128) = 'MyCMK'; -- Name of the registered Column Master Key
DECLARE @KeyStoreProviderName NVARCHAR(128) = 'Azure Key Vault'; -- Provider for the CMK

-- Create the Column Encryption Key
CREATE COLUMN ENCRYPTION KEY MyCEK
WITH VALUES
(
    COLUMN_MASTER_KEY_NAME = @CMK_Name,
    KEY_STORE_PROVIDER_NAME = @KeyStoreProviderName
);
GO
            

Rotating Encryption Keys

Key rotation is essential for maintaining security. The process typically involves:

1. Creating a new CMK in your key store.
2. Registering the new CMK with SQL Server.
3. Creating a new CEK associated with the new CMK.
4. Updating your client applications to use the new CEK.
5. For existing encrypted columns, you may need to re-encrypt the data using the new CEK.

Key Store Providers

SQL Server interacts with key stores through provider interfaces. Common providers include:

• SQL Server Certificate Store Provider: For CMKs stored in the Windows Certificate Store.
• Azure Key Vault Provider: For CMKs stored in Azure Key Vault.
• HSM Provider: For CMKs managed by compatible Hardware Security Modules.

You can find specific instructions for configuring each provider in the official Microsoft documentation.