Always Encrypted in SQL Server: protect sensitive data end-to-end

Always Encrypted is a SQL Server (and Azure SQL) feature that encrypts sensitive data on the client side, ensuring the server never has access to the encryption keys. Ideal for PII data, card numbers and medical information.

Architecture

Client (application)            SQL Server
  ├─ Column Master Key (CMK)    ├─ Encrypted data (ciphertext)
  │   (Azure Key Vault / cert)  ├─ Encrypted Column Encryption Key (CEK)
  └─ Driver encrypts/decrypts   └─ Never sees data in plaintext

Encryption types

• Deterministic — same plaintext → same ciphertext. Supports equality and GROUP BY. Easier to attack via frequency analysis.
• Randomized — different ciphertext each time. More secure. Does not support search or sorting.

Configure via the SSMS Wizard

-- After configuring via the wizard, the column is encrypted:
-- SSMS with Always Encrypted enabled shows data in plaintext
-- A connection without the CMK shows binary ciphertext

-- Check encrypted columns
SELECT
    c.name AS column_name,
    t.name AS type,
    c.encryption_type_desc,
    c.encryption_algorithm_name
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.encryption_type IS NOT NULL;

Use in a .NET application

// Connection string with Always Encrypted
var connStr = "Server=...;Column Encryption Setting=Enabled;";

// The driver automatically encrypts parameters before sending
using var cmd = new SqlCommand("SELECT * FROM Customers WHERE tax_id = @taxId", conn);
cmd.Parameters.AddWithValue("@taxId", "123456789");  // encrypted by the driver

Conclusion

Always Encrypted is the most robust solution for protecting sensitive data in SQL Server. Encryption happens in the client driver, making data inaccessible even to database administrators with root access to the server — an increasingly common requirement in GDPR audits.