Always Encrypted Key Management

Overview of Key Management

Always Encrypted relies on cryptographic keys to protect sensitive data. Effective key management is crucial for maintaining the security and usability of your encrypted data. This involves understanding how keys are stored, accessed, and rotated.

There are two primary types of keys involved:

  • Column Master Key (CMK): This is the root key that encrypts the Column Encryption Keys. It is stored outside of SQL Server, typically in a trusted key store like Windows Certificate Store, Azure Key Vault, or a Hardware Security Module (HSM).
  • Column Encryption Key (CEK): This key encrypts the actual data within the database columns. It is encrypted using a CMK.

Column Master Keys (CMKs)

CMKs are the master keys that protect your sensitive data. SQL Server does not store or manage CMKs directly. Instead, it interacts with an external key store provider.

Key Store Providers

  • Windows Certificate Store: Ideal for on-premises deployments or environments where you manage certificates.
  • Azure Key Vault: A cloud-based key management service offered by Microsoft Azure, providing robust security and scalability for cloud-native applications.
  • Hardware Security Module (HSM): For the highest level of security and compliance requirements, HSMs provide dedicated hardware to protect cryptographic keys.

Managing CMKs

  • Provisioning: Create your CMK in the chosen key store.
  • Access Control: Configure permissions to control which users and applications can access the CMK.
  • Rotation: Periodically rotate your CMKs to enhance security. This involves creating a new CMK and re-encrypting CEKs with the new CMK.

Column Encryption Keys (CEKs)

CEKs are used to encrypt the data within your database columns. Each encrypted column uses a CEK. CEKs are themselves encrypted by a CMK.

Key Management Operations for CEKs

  • Creation: Generate a CEK.
  • Encryption: Encrypt the CEK using a selected CMK.
  • Decryption: Decrypt the CEK using the associated CMK to access the data.
  • Storage: CEKs are stored within the SQL Server database itself, encrypted by the CMK.

When you set up Always Encrypted for a column, SQL Server handles the creation and encryption of the CEK automatically, using the CMK you specify.

Key Rotation

Key rotation is a fundamental security practice. For Always Encrypted:

  • CMK Rotation: This is the primary rotation mechanism. You create a new CMK in your key store, and then update your SQL Server configuration to use the new CMK to encrypt existing and new CEKs.
  • CEK Rotation: This happens implicitly when a CMK is rotated. Existing CEKs will be re-encrypted with the new CMK. You can also choose to re-encrypt data with a new CEK if needed, though this is less common than CMK rotation.

The process typically involves:

  1. Creating a new CMK in your key store.
  2. Updating the Always Encrypted configuration in SQL Server to point to the new CMK for the relevant CEKs. SQL Server will then automatically re-encrypt the CEKs using the new CMK.

Best Practices

  • Use Azure Key Vault or HSMs for storing your CMKs for production environments.
  • Implement strict access control to your CMKs. Only grant access to necessary principals.
  • Regularly rotate your CMKs according to your organization's security policies.
  • Back up your CMKs securely. Loss of a CMK can lead to permanent data loss.
  • Use separate CMKs for different applications or environments for better isolation.
  • Document your key management procedures thoroughly.

Example Workflow (Conceptual)

Let's consider encrypting a 'CreditCardNumber' column using Always Encrypted with Azure Key Vault:

  1. Create CMK in Azure Key Vault: Generate a new cryptographic key in your Azure Key Vault.
  2. Grant Access: Grant the SQL Server Managed Identity (or service principal) permissions to "Get" and "Wrap Key" operations on the CMK in Azure Key Vault.
  3. Configure Always Encrypted in SQL Server:
    • Define the Column Master Key in SQL Server, pointing to your Azure Key Vault CMK.
    • Define the Column Encryption Key, specifying that it should be encrypted by the previously defined Column Master Key.
    • Encrypt the 'CreditCardNumber' column using this Column Encryption Key.
  4. Client Application: The client application (with the appropriate .NET Data Provider for SQL Server that supports Always Encrypted) connects to SQL Server. It automatically handles fetching the CEK (by calling Azure Key Vault via the CMK) and performing encryption/decryption locally.
  5. Key Rotation: When it's time to rotate, create a new CMK in Azure Key Vault, update the Column Master Key definition in SQL Server to point to the new CMK, and SQL Server will re-encrypt the CEK.