Column Encryption Keys (CEK)

Column Encryption Keys (CEKs) are symmetric keys used by Always Encrypted to encrypt data in sensitive columns. They are managed independently from Column Master Keys (CMKs), which are used to protect the CEKs.

Understanding Column Encryption Keys

In Always Encrypted, data in sensitive columns is encrypted using a Column Encryption Key (CEK). This CEK is a symmetric key, meaning the same key is used for both encryption and decryption.

The security of your encrypted data relies heavily on the secure management of these CEKs. Always Encrypted provides mechanisms to protect CEKs using a hierarchy of trust. The CEK itself is protected by a Column Master Key (CMK).

Key Characteristics of CEKs:

• Symmetric Encryption: Uses a single key for both encrypting and decrypting data.
• Column-Specific: Each CEK is typically associated with one or more columns that share the same encryption settings.
• Protected by CMKs: CEKs are never stored directly in the database in plaintext. Instead, they are encrypted by a Column Master Key (CMK).
• Managed Independently: CEKs can be rotated and managed separately from CMKs.

How CEKs Work with Always Encrypted

When data is encrypted in a sensitive column:

1. The client application retrieves the CEK.
2. The CEK is used to encrypt the plaintext data.
3. The encrypted data is sent to SQL Server.

When encrypted data is decrypted:

1. The client application retrieves the CEK.
2. The CEK is used to decrypt the ciphertext data.
3. The plaintext data is returned to the application.

Crucially, SQL Server itself does not have direct access to the CEK. The client application, using the Always Encrypted driver and access to the CMK, performs the encryption and decryption operations.

Managing Column Encryption Keys

Managing CEKs involves creating them, associating them with columns, and potentially rotating them.

Creating a CEK:

CEKs are created and managed outside of SQL Server, typically within your application or using dedicated key management tools that interact with your CMK store (e.g., Azure Key Vault, Windows Certificate Store).

The process usually involves:

1. Ensuring a Column Master Key is available and accessible.
2. Generating a new symmetric key (the CEK).
3. Encrypting this new CEK using the CMK.
4. Storing the encrypted CEK in SQL Server metadata.

Associating a CEK with a Column:

Once a CEK is created and stored (in its encrypted form), it needs to be associated with the specific column(s) it will protect. This is done by creating a Column Encryption Key (CEK) metadata object in the database. This object stores information about the CEK, including its encrypted form and the details of the CMK that protects it.

Example of CEK Metadata Object (Conceptual):

CREATE COLUMN ENCRYPTION KEY MyCEK
WITH VALUES
(
    -- Key value encrypted by the CMK
    ENCRYPTED BY 'YourColumnMasterKeyName'
    DATA_TYPE = 'PLAINTEXT', -- Placeholder for actual encrypted value
    KEY_STORAGE_PROVIDER = 'AZURE_KEY_VAULT' -- Or 'WINDOWS_CERTIFICATE_STORE' etc.
);
            

Key Rotation:

Regularly rotating your CEKs is a best practice for enhanced security. This involves creating a new CEK, encrypting it with the CMK, and updating the CEK metadata object in SQL Server. The older CEK remains in use for existing encrypted data until it is also re-encrypted with the new CEK.

Key Storage Providers

Always Encrypted supports various Key Storage Providers (KSPs) for managing your Column Master Keys. The CEK metadata object in SQL Server references the KSP used by its associated CMK.

Common KSPs include:

• Azure Key Vault
• Windows Certificate Store
• CNG KSPs (Cryptography API: Next Generation)
• HSM KSPs (Hardware Security Module)

Next Steps

To implement Always Encrypted effectively, you'll need to:

• Understand and configure Column Master Keys (CMKs).
• Choose and configure an appropriate Key Storage Provider (KSP).
• Implement client-side encryption and decryption using Always Encrypted enabled drivers.
• Plan your key rotation strategy.