🔐 EncryptionManager Service Documentation

Welcome to the comprehensive documentation for the EncryptionManager class in the IrbyImaging.UI.Services namespace. This service automates the setup of SQL Always Encrypted using Azure Key Vault as the master key store, and provides methods to encrypt individual columns in your database tables.


📑 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Class Diagram (UML)
  4. Key Functional Areas
    4.1. Constructor
    4.2. CreateMasterKey()
    4.3. dropObjects()
    4.4. createCMK()
    4.5. createCEK()
    4.6. GetEncryptedValue()
    4.7. EncryptColumn()
  5. Usage Examples & Code Snippets
  6. Error Handling & Return Types
  7. Third‐party Integrations
  8. Conclusion

🧐 Overview & Purpose

The EncryptionManager service is designed to:

  • Automate the creation of Column Master Keys (CMK) and Column Encryption Keys (CEK) in your Azure‑SQL database
  • Leverage Azure Key Vault (AKV) as the secure store for your CMK
  • Provide a simple method to encrypt existing columns in any table via T‑SQL ALTER TABLE … ADD ENCRYPTED

Whether you’re bootstrapping Always Encrypted on a fresh database or retrofitting encryption on legacy tables, this service encapsulates the boilerplate logic and Azure‑specific plumbing for you. 🎉


🛠 Dependencies & Technologies

Library / Package Purpose
Azure.Identity InteractiveBrowserCredential to authenticate to Azure Key Vault
Microsoft.Azure.KeyVault Core Key Vault client abstractions (for older SDK interoperability)
Microsoft.Data.SqlClient Connect to SQL DB & execute Always Encrypted T‑SQL commands
Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider Provider to integrate SQL Always Encrypted with Azure Key Vault
Microsoft.Extensions.Configuration Read connection strings & settings from appsettings.json
Microsoft.AspNetCore.Http & Microsoft.AspNetCore.Http.Extensions Detect runtime environment (localhost vs. Azure Web Apps)
System.Security.Cryptography Generate random CEK material (32‐byte key)
IrbyImaging.UI.Services (this file) EncryptionManager service

🗂 Class Diagram (UML)

/** Mermaid UML **/

classDiagram
    EncryptionManager "1" *-- "1" IrbyContext : _context
    EncryptionManager "1" *-- "1" IConfiguration : _configuration
    EncryptionManager "1" *-- "1" IHttpContextAccessor : _httpContextAccessor
    EncryptionManager ..> SqlConnection
    EncryptionManager ..> SqlColumnEncryptionAzureKeyVaultProvider
    EncryptionManager : +CreateMasterKey()
    EncryptionManager : +EncryptColumn(tableName, columnName, cekName)
    EncryptionManager : -dropObjects(...)
    EncryptionManager : -createCMK(...)
    EncryptionManager : -createCEK(...)
    EncryptionManager : -GetEncryptedValue(...)

🔑 Key Functional Areas

This section dives deep into each method, its role, and how it collaborates to provide end‑to‑end Always Encrypted support.

⚙️ Constructor

public EncryptionManager(
    IrbyContext context,
    IConfiguration configuration,
    IHttpContextAccessor http)
  • _context (IrbyContext): Your EF Core database context (not directly used for encryption, but available for future extension).
  • _configuration (IConfiguration): Reads connection strings for Dev/QA/Prod.
  • _httpContextAccessor (IHttpContextAccessor): Determines runtime URL to select the appropriate connection string.

🔑 CreateMasterKey()

Signature

public void CreateMasterKey()

Purpose

  1. Authenticate to Azure Key Vault via InteractiveBrowserCredential.
  2. Register the Azure Key Vault provider for SQL Always Encrypted.
  3. Resolve the proper connection string (Local, Dev, QA, Prod) by inspecting the current request URL.
  4. Open a SqlConnection and orchestrate:
    • Drop existing CMK/CEK objects
    • Create a new Column Master Key (CMK) pointing to AKV
    • Create a new Column Encryption Key (CEK) encrypted under the CMK

Flow Diagram (Sequence)

sequenceDiagram
    participant User
    participant EM as EncryptionManager
    participant AKVProv as SqlColumnEncryptionAzureKeyVaultProvider
    participant SQL as SqlConnection

    User->>EM: CreateMasterKey()
    EM->>AKVProv: new(provider), RegisterColumnEncryptionKeyStoreProviders(...)
    EM->>EM: pick connectionString by URL
    EM->>SQL: Open()
    SQL->>EM: Ready
    EM->>SQL: dropObjects()
    EM->>SQL: createCMK()
    EM->>SQL: createCEK()
    SQL-->>EM: Done
    EM-->>User: Completed

🗑 dropObjects()

private static void dropObjects(
    SqlConnection conn,
    string cmkName,
    string cekName)
  • Drops the Column Encryption Key (cekName) if it exists.
  • Drops the Column Master Key (cmkName) if it exists.

Important: Helps with idempotent runs during development.


🗝 createCMK()

private static void createCMK(
    SqlConnection conn,
    string cmkName,
    SqlColumnEncryptionAzureKeyVaultProvider provider)
  1. Calls provider.SignColumnMasterKeyMetadata(s_akvUrl, true) to generate an enclave signature.
  2. Builds a T‑SQL CREATE COLUMN MASTER KEY statement:
    CREATE COLUMN MASTER KEY [COLUMN_MASTER_KEY_WITH_AZURE_KEY_VAULT]
      WITH (
        KEY_STORE_PROVIDER_NAME = N'Microsoft.Data.SqlClient.AzureKeyVaultProvider',
        KEY_PATH = N'https://...vault.azure.net/keys/...',  
        ENCLAVE_COMPUTATIONS (SIGNATURE = 0x...)
      );
    
  3. Executes it over the SqlConnection.

🗝 createCEK()

private static void createCEK(
    SqlConnection conn,
    string cmkName,
    string cekName,
    SqlColumnEncryptionAzureKeyVaultProvider provider)
  1. Calls GetEncryptedValue(provider) to generate a random 32‑byte CEK and encrypt it under the CMK.
  2. Builds a T‑SQL CREATE COLUMN ENCRYPTION KEY statement:
    CREATE COLUMN ENCRYPTION KEY [COLUMN_ENCRYPTION_KEY_WITH_AZURE_KEY_VAULT]
      WITH VALUES (
        COLUMN_MASTER_KEY = [COLUMN_MASTER_KEY_WITH_AZURE_KEY_VAULT],
        ALGORITHM = 'RSA_OAEP',
        ENCRYPTED_VALUE = 0x...
      );
    
  3. Executes it on the database.

🧩 GetEncryptedValue()

private static string GetEncryptedValue(
    SqlColumnEncryptionAzureKeyVaultProvider provider)
  • Generates a 32‑byte random plaintext key (CEK) using RandomNumberGenerator.
  • Encrypts it via provider.EncryptColumnEncryptionKey(...).
  • Returns the encrypted bytes as a hex string prefixed with 0x.

🛡 EncryptColumn()

public void EncryptColumn(
    string tableName,
    string columnName,
    string cekName)

Purpose
Encrypts an existing column in a table with RANDOMIZED encryption:

ALTER TABLE {tableName}  
  ALTER COLUMN {columnName}  
  ADD ENCRYPTED  
    WITH (
      COLUMN_ENCRYPTION_KEY = [{cekName}],
      ALGORITHM = 'RSA_OAEP',
      ENCRYPT_TYPE = RANDOMIZED,
      PLAINTEXT_SIZE = 0
    );
  • tableName: Name of your target table (e.g. Users)
  • columnName: Name of the column to encrypt (e.g. SSN)
  • cekName: The CEK previously created (COLUMN_ENCRYPTION_KEY_WITH_AZURE_KEY_VAULT)

📝 Usage Examples & Code Snippets

1. Registering in ASP.NET Core DI

public void ConfigureServices(IServiceCollection services)
{
    // ... other services
    services.AddScoped<EncryptionManager>();
}

2. Creating Master & Encryption Keys

public class StartupTasks
{
    private readonly EncryptionManager _encryptionManager;

    public StartupTasks(EncryptionManager encryptionManager)
    {
        _encryptionManager = encryptionManager;
    }

    public void Run()
    {
        // ⚙️ Creates/Re‑creates CMK & CEK in your Azure‑SQL DB
        _encryptionManager.CreateMasterKey();
    }
}

3. Encrypting a Sensitive Column

public void ProtectUserSSN()
{
    // Encrypt the 'SSN' column in 'Users' table using the CEK we created
    _encryptionManager.EncryptColumn(
        tableName: "Users",
        columnName: "SSN",
        cekName: "COLUMN_ENCRYPTION_KEY_WITH_AZURE_KEY_VAULT");
}

🔧 Error Handling & Return Types

  • Return Type: All public methods return void.
  • Exceptions:
    • Azure.Identity exceptions when authentication fails.
    • SqlException for any database connectivity or T‑SQL errors.
    • CryptographicException if random‐number generation fails.
  • Recommended: Wrap calls in try/catch and log the error details. Example:
try
{
    _encryptionManager.CreateMasterKey();
}
catch (SqlException sqlEx)
{
    // Handle DB errors
}
catch (Exception ex)
{
    // Handle all other errors
}

🔌 Third‐party Integrations

  • Azure Key Vault: Stores your Column Master Key (CMK).
  • Azure.Identity (InteractiveBrowserCredential): Used for developer sign‑in (pop‑up browser). For production, swap with ManagedIdentityCredential or ClientSecretCredential.
  • Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider: Bridges SQL Always Encrypted with Azure Key Vault

🎯 Conclusion

The EncryptionManager class encapsulates the end‑to‑end setup of SQL Always Encrypted with Azure Key Vault. By centralizing key creation, registration, and column encryption logic, it lets you:

  • Quickly bootstrap encryption on a new database
  • Enforce consistent encryption policies across Dev/QA/Prod
  • Minimize boilerplate and reduce human error

With a few method calls, your sensitive data is protected at rest—ensuring compliance and peace of mind. Happy encrypting! 🔒✨