SQL Server Always Encrypted Implementation

🔒 SQL Server Always Encrypted: The Complete Security Implementation Guide

Always Encrypted is SQL Server's most powerful data protection feature, providing end-to-end encryption for sensitive data. Unlike TDE (Transparent Data Encryption), Always Encrypted keeps data encrypted both at rest AND in memory on the server, ensuring that even DBAs cannot see plaintext sensitive data. This comprehensive guide covers everything you need to implement Always Encrypted securely.

📊 Understanding Always Encrypted Architecture

How Always Encrypted Works

Data is encrypted on the client side before being sent to SQL Server. The server never sees the encryption keys or plaintext data.

+-------------------+                    +-------------------+
|    Client App     |                    |   SQL Server      |
|                   |                    |                   |
| [Plaintext Data]  | --> Encrypted -->  | [Encrypted Data]  |
| [Column Master    |                    | [Never sees       |
|  Key Access]      |                    |  plaintext]       |
+-------------------+                    +-------------------+

Key Components

Encryption Types

🔧 Setting Up Always Encrypted

Step 1: Create Column Master Key

-- Option 1: Windows Certificate Store (development/testing)
CREATE COLUMN MASTER KEY CMK_TestKey
WITH (
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = 'CurrentUser/My/XXXXXXXXXXXXXXXXXXXXXXXXXX'
);

-- Option 2: Azure Key Vault (production recommended)
CREATE COLUMN MASTER KEY CMK_AzureKeyVault
WITH (
    KEY_STORE_PROVIDER_NAME = 'AZURE_KEY_VAULT',
    KEY_PATH = 'https://mykeyvault.vault.azure.net/keys/AlwaysEncryptedKey/abc123...'
);

-- Option 3: Hardware Security Module (HSM) for highest security
CREATE COLUMN MASTER KEY CMK_HSM
WITH (
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CNG_STORE',
    KEY_PATH = 'HSM/My/CMK_HSM_Key'
);

Step 2: Create Column Encryption Key

-- Create CEK protected by the CMK
CREATE COLUMN ENCRYPTION KEY CEK_SensitiveData
WITH VALUES (
    COLUMN_MASTER_KEY = CMK_AzureKeyVault,
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x01... -- Generated value
);

-- Using SSMS wizard is recommended for creating actual encrypted value

Step 3: Create Table with Encrypted Columns

-- Create table with Always Encrypted columns
CREATE TABLE HR.Employees
(
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,

    -- SSN: Deterministic for lookups
    SSN CHAR(11) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH (
            ENCRYPTION_TYPE = DETERMINISTIC,
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
            COLUMN_ENCRYPTION_KEY = CEK_SensitiveData
        ) NOT NULL,

    -- Salary: Randomized for maximum security
    Salary DECIMAL(18,2)
        ENCRYPTED WITH (
            ENCRYPTION_TYPE = RANDOMIZED,
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
            COLUMN_ENCRYPTION_KEY = CEK_SensitiveData
        ) NOT NULL,

    -- DateOfBirth: Randomized
    DateOfBirth DATE
        ENCRYPTED WITH (
            ENCRYPTION_TYPE = RANDOMIZED,
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
            COLUMN_ENCRYPTION_KEY = CEK_SensitiveData
        ) NOT NULL,

    Email NVARCHAR(256) NOT NULL,
    HireDate DATE NOT NULL,
    DepartmentID INT NOT NULL
);

💻 Client Application Setup

.NET Application Configuration

// Connection string with Column Encryption Setting
string connectionString =
    "Data Source=myserver.database.windows.net;" +
    "Initial Catalog=HRDatabase;" +
    "Column Encryption Setting=Enabled;" +
    "Authentication=Active Directory Integrated;";

// For Azure Key Vault access
using Microsoft.Data.SqlClient;
using Microsoft.Data.SqlClient.AlwaysEncrypted.AzureKeyVaultProvider;
using Azure.Identity;

// Register Azure Key Vault provider
var azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(
    new DefaultAzureCredential());

var providers = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>
{
    { SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider }
};

SqlConnection.RegisterColumnEncryptionKeyStoreProviders(providers);

// Use parameterized queries for encrypted columns
using (SqlConnection conn = new SqlConnection(connectionString))
{
    await conn.OpenAsync();

    // INSERT with encrypted data
    string insertQuery = @"
        INSERT INTO HR.Employees (FirstName, LastName, SSN, Salary, DateOfBirth, Email, HireDate, DepartmentID)
        VALUES (@FirstName, @LastName, @SSN, @Salary, @DOB, @Email, @HireDate, @DeptID)";

    using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
    {
        cmd.Parameters.AddWithValue("@FirstName", "John");
        cmd.Parameters.AddWithValue("@LastName", "Doe");
        cmd.Parameters.AddWithValue("@SSN", "123-45-6789");
        cmd.Parameters.AddWithValue("@Salary", 75000.00m);
        cmd.Parameters.AddWithValue("@DOB", new DateTime(1985, 6, 15));
        cmd.Parameters.AddWithValue("@Email", "[email protected]");
        cmd.Parameters.AddWithValue("@HireDate", DateTime.Today);
        cmd.Parameters.AddWithValue("@DeptID", 10);

        await cmd.ExecuteNonQueryAsync();
    }

    // SELECT with encrypted data (deterministic allows equality)
    string selectQuery = "SELECT * FROM HR.Employees WHERE SSN = @SSN";

    using (SqlCommand cmd = new SqlCommand(selectQuery, conn))
    {
        cmd.Parameters.AddWithValue("@SSN", "123-45-6789");

        using (SqlDataReader reader = await cmd.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                // Driver automatically decrypts
                Console.WriteLine($"Name: {reader["FirstName"]} {reader["LastName"]}");
                Console.WriteLine($"SSN: {reader["SSN"]}");  // Decrypted automatically
                Console.WriteLine($"Salary: {reader["Salary"]:C}");  // Decrypted automatically
            }
        }
    }
}

SSMS Configuration

-- To query encrypted data from SSMS, enable parameterization
-- Query > Query Options > Advanced > Enable Parameterization for Always Encrypted

-- Use parameterized variable declaration
DECLARE @SSN CHAR(11) = '123-45-6789';

SELECT FirstName, LastName, SSN, Salary
FROM HR.Employees
WHERE SSN = @SSN;

-- Note: SSMS must have access to CMK to decrypt

🔧 Encrypting Existing Data

Using the Always Encrypted Wizard (SSMS)

1. Right-click database in Object Explorer
2. Select Tasks > Encrypt Columns
3. Follow wizard to select columns and encryption types
4. Choose or create CMK and CEK
5. Wizard handles data migration

Using PowerShell

# Import SQL Server module
Import-Module SqlServer

# Get column encryption key definition
$cek = Get-SqlColumnEncryptionKey -Name "CEK_SensitiveData" `
    -ConnectionString "Server=myserver;Database=HRDatabase;Integrated Security=True"

# Define encryption settings for columns
$encryptionSettings = @(
    New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.SSN" `
        -EncryptionType "Deterministic" -EncryptionKey $cek,
    New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.Salary" `
        -EncryptionType "Randomized" -EncryptionKey $cek,
    New-SqlColumnEncryptionSettings -ColumnName "HR.Employees.DateOfBirth" `
        -EncryptionType "Randomized" -EncryptionKey $cek
)

# Encrypt columns (this reads data, encrypts, and writes back)
Set-SqlColumnEncryption -ColumnEncryptionSettings $encryptionSettings `
    -ConnectionString "Server=myserver;Database=HRDatabase;Integrated Security=True" `
    -MaxDop 4 `
    -LogFileDirectory "C:\Logs"

Manual Migration Script

-- For large tables, consider batch processing
-- Step 1: Create new encrypted table
CREATE TABLE HR.Employees_Encrypted
(
    EmployeeID INT NOT NULL,
    FirstName NVARCHAR(50) NOT NULL,
    LastName NVARCHAR(50) NOT NULL,
    SSN CHAR(11) COLLATE Latin1_General_BIN2
        ENCRYPTED WITH (
            ENCRYPTION_TYPE = DETERMINISTIC,
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
            COLUMN_ENCRYPTION_KEY = CEK_SensitiveData
        ) NOT NULL,
    Salary DECIMAL(18,2)
        ENCRYPTED WITH (
            ENCRYPTION_TYPE = RANDOMIZED,
            ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
            COLUMN_ENCRYPTION_KEY = CEK_SensitiveData
        ) NOT NULL,
    -- ... other columns
);

-- Step 2: Use client application to copy data
-- Data must flow through client for encryption

-- Step 3: Rename tables
BEGIN TRANSACTION;
    EXEC sp_rename 'HR.Employees', 'Employees_Old';
    EXEC sp_rename 'HR.Employees_Encrypted', 'Employees';
COMMIT;

📊 Always Encrypted with Secure Enclaves (SQL Server 2019+)

What Are Secure Enclaves?

Secure enclaves allow encrypted data to be processed inside a protected memory region on the server, enabling richer query operations on encrypted data.

Additional Supported Operations with Enclaves

Configuring Secure Enclaves

-- Check if enclave is available
SELECT [name], [value], [value_in_use]
FROM sys.configurations
WHERE name = 'column encryption enclave type';

-- Enable VBS enclave (requires Windows Server 2019+)
EXEC sp_configure 'column encryption enclave type', 1;
RECONFIGURE;

-- Create enclave-enabled CMK
CREATE COLUMN MASTER KEY CMK_Enclave
WITH (
    KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
    KEY_PATH = 'CurrentUser/My/EnclaveKeyThumbprint',
    ENCLAVE_COMPUTATIONS (SIGNATURE = 0x...)
);

-- Create enclave-enabled CEK
CREATE COLUMN ENCRYPTION KEY CEK_Enclave
WITH VALUES (
    COLUMN_MASTER_KEY = CMK_Enclave,
    ALGORITHM = 'RSA_OAEP',
    ENCRYPTED_VALUE = 0x...
);

Using Enclave with Randomized Encryption

-- With enclave, you can use range queries on randomized columns
SELECT FirstName, LastName, Salary
FROM HR.Employees
WHERE Salary BETWEEN @MinSalary AND @MaxSalary;

-- Pattern matching on encrypted strings
SELECT *
FROM HR.Employees
WHERE SSN LIKE @SSNPattern;

-- Sorting encrypted data
SELECT FirstName, LastName, Salary
FROM HR.Employees
ORDER BY Salary DESC;

✅ Best Practices

Key Management

Column Selection

-- Good candidates for Always Encrypted
-- SSN, Credit Cards, Bank Accounts, Medical Records
CREATE TABLE Financial.CreditCards
(
    CardID INT PRIMARY KEY,
    CustomerID INT NOT NULL,
    -- Always encrypt highly sensitive data
    CardNumber CHAR(16)
        ENCRYPTED WITH (ENCRYPTION_TYPE = DETERMINISTIC, ...),
    CVV CHAR(4)
        ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, ...),
    ExpirationDate CHAR(5)
        ENCRYPTED WITH (ENCRYPTION_TYPE = RANDOMIZED, ...),
    -- Less sensitive - may not need encryption
    CardType VARCHAR(20),
    IsActive BIT
);

Performance Considerations

• Use deterministic encryption only when equality operations are needed
• Index deterministic encrypted columns for better performance
• Batch operations for bulk data encryption
• Test query performance with encrypted columns

⚠️ Warning: Always Encrypted adds overhead. Only encrypt truly sensitive columns. Encrypting everything will severely impact performance.

🔧 Key Rotation

Rotate Column Master Key

# Create new CMK in Key Vault
# Update SQL Server metadata

$newCMK = New-SqlAzureKeyVaultColumnMasterKeySettings `
    -KeyUrl "https://myvault.vault.azure.net/keys/NewCMK/version123"

New-SqlColumnMasterKey -Name "CMK_New" `
    -ColumnMasterKeySettings $newCMK `
    -ConnectionString $connString

# Re-encrypt all CEKs with new CMK
Invoke-SqlColumnMasterKeyRotation `
    -SourceColumnMasterKeyName "CMK_Old" `
    -TargetColumnMasterKeyName "CMK_New" `
    -ConnectionString $connString

# After verification, remove old CMK
Remove-SqlColumnMasterKey -Name "CMK_Old" `
    -ConnectionString $connString

Rotate Column Encryption Key

# Rotation involves re-encrypting all data
# Plan for maintenance window

# Create new CEK
New-SqlColumnEncryptionKey -Name "CEK_New" `
    -ColumnMasterKeyName "CMK_Current" `
    -ConnectionString $connString

# Update column encryption settings to use new CEK
$newSettings = @(
    New-SqlColumnEncryptionSettings `
        -ColumnName "HR.Employees.SSN" `
        -EncryptionType "Deterministic" `
        -EncryptionKey "CEK_New"
)

# Re-encrypt data (requires maintenance window)
Set-SqlColumnEncryption `
    -ColumnEncryptionSettings $newSettings `
    -ConnectionString $connString

📈 Monitoring and Troubleshooting

View Encrypted Column Metadata

-- View all encrypted columns
SELECT
    SCHEMA_NAME(t.schema_id) AS SchemaName,
    t.name AS TableName,
    c.name AS ColumnName,
    cek.name AS CEKName,
    cmk.name AS CMKName,
    c.encryption_type_desc,
    c.encryption_algorithm_name
FROM sys.columns c
INNER JOIN sys.tables t ON c.object_id = t.object_id
INNER JOIN sys.column_encryption_keys cek ON c.column_encryption_key_id = cek.column_encryption_key_id
INNER JOIN sys.column_encryption_key_values cekv ON cek.column_encryption_key_id = cekv.column_encryption_key_id
INNER JOIN sys.column_master_keys cmk ON cekv.column_master_key_id = cmk.column_master_key_id
WHERE c.encryption_type IS NOT NULL
ORDER BY t.name, c.name;

-- View CMK details
SELECT
    name AS CMKName,
    key_store_provider_name,
    key_path,
    allow_enclave_computations
FROM sys.column_master_keys;

Common Issues

-- Error: "Operand type clash"
-- Cause: Using non-parameterized query on encrypted column
-- Solution: Always use parameterized queries

-- Error: "Cannot perform this operation on encrypted column"
-- Cause: Trying unsupported operation on randomized column
-- Solution: Use deterministic encryption or enable secure enclaves

-- Error: "Column master key not found"
-- Cause: Application doesn't have access to CMK
-- Solution: Grant application access to Key Vault/certificate

Conclusion

Always Encrypted provides the strongest protection for sensitive data in SQL Server. Key takeaways:

• Client-side encryption - Server never sees plaintext or keys
• Choose encryption type wisely - Deterministic for lookups, randomized for max security
• Use Azure Key Vault - Production-grade CMK storage
• Consider secure enclaves - For richer query support on encrypted data
• Plan key rotation - Regular rotation is essential for security

With proper implementation, Always Encrypted ensures sensitive data protection even from privileged database users.

About the Author

Jamaurice Holt is a Cloud Database Expert and AWS Solutions Architect with over 10 years of experience in database optimization, high availability, and cloud migrations. Specializing in SQL Server, SQL optimization, and enterprise database solutions.

Related Articles

• SQL Server Transparent Data Encryption (TDE)
• SQL Server Security: Protecting Your Database
• SQL Server Performance Tuning Guide

Need help with SQL Server security? Contact us for expert database consulting.