Database encryption best practices for ERP: Azure SQL vs Cosmos DB approaches

Cloud Platforms Microsoft Azure
, , , , , , , ,
1

We’re evaluating database encryption strategies for a new ERP implementation and I’m comparing Azure SQL Database vs Cosmos DB from an encryption and compliance perspective. Both services offer encryption at rest and in transit, but the implementation details differ significantly.

Azure SQL has TDE (Transparent Data Encryption) and Always Encrypted for column-level encryption. Cosmos DB has service-managed encryption keys with the option for customer-managed keys via Key Vault. We need to meet SOC2 and HIPAA compliance requirements, which mandate encryption of sensitive data with customer-controlled keys.

I’m trying to understand the practical differences in how these encryption approaches work, the operational overhead of managing customer-managed keys, and whether one approach provides better compliance positioning. We’re leaning toward Azure SQL for transactional ERP data, but some architects are advocating for Cosmos DB for its global distribution and automatic encryption features.

What are the real-world tradeoffs between these database encryption models for ERP workloads?

2

The column-level encryption is appealing for separating sensitive fields like salary data or SSN from general ERP data. But I’ve heard Always Encrypted has performance implications and requires application changes. Is the operational overhead worth it for compliance?

3

From an audit perspective, what matters is: Can you prove you control the encryption keys? Can you rotate keys without downtime? Can you revoke access instantly if needed? Do you have audit logs of all key access?

Both Azure SQL with TDE+CMK and Cosmos DB with CMK check these boxes. The audit trail comes from Key Vault logs, not the database service. Make sure you enable Key Vault diagnostic logging and send it to a SIEM or Log Analytics workspace. That’s where auditors will look for evidence of key access patterns and rotation schedules.

4

Always Encrypted does require application-side changes because encryption/decryption happens at the client, not the database. Your application needs the Always Encrypted driver and access to Key Vault. This adds latency (each query requires key retrieval) and complexity (key rotation procedures, certificate management).

For most ERP scenarios, TDE with customer-managed keys is sufficient. TDE encrypts the entire database transparently with no application changes needed. The data is encrypted at rest, you control the keys in Key Vault, and you can rotate keys on your schedule. This satisfies most compliance frameworks without the operational burden of Always Encrypted.

Reserve Always Encrypted for truly sensitive columns where you need defense against privileged user access (DBAs, cloud admins).

5

Based on this discussion and additional research, here’s my analysis of database encryption best practices for ERP workloads:

TDE and Always Encrypted (Azure SQL Database):

Azure SQL offers two primary encryption mechanisms:

Transparent Data Encryption (TDE):

  • Encrypts entire database at rest (data files, log files, backups)
  • Completely transparent to applications - no code changes required
  • Service-managed keys enabled by default, customer-managed keys (CMK) optional
  • Key stored in Azure Key Vault, database has identity-based access to retrieve key
  • Performance impact is negligible (< 3-5% overhead)
  • Recommended for: General-purpose ERP database encryption meeting compliance baselines

Always Encrypted:

  • Encrypts specific columns with encryption/decryption at client side
  • Protects data from high-privilege users (DBAs, cloud admins)
  • Requires application changes (Always Encrypted-enabled driver)
  • Two modes: deterministic (allows equality comparisons) and randomized (more secure)
  • Performance impact varies based on query patterns (10-30% for encrypted column operations)
  • Key hierarchy: Column Encryption Key (encrypts data) protected by Column Master Key (stored in Key Vault)
  • Recommended for: Highly sensitive columns (SSN, credit cards, salary) where zero-trust for DBAs is required

For ERP implementations, the typical pattern is TDE with CMK for the entire database, plus Always Encrypted for a small subset of highly sensitive columns. This balances security, compliance, and operational overhead.

Customer-Managed Keys (CMK) Strategy:

Both Azure SQL and Cosmos DB support CMK via Key Vault, but implementation differs:

Azure SQL with CMK:


-- Enable TDE with customer-managed key
ALTER DATABASE [ERP-Production]
SET ENCRYPTION ON
WITH (ENCRYPTION_KEY = 'ERP-TDE-Key' FROM KEY_VAULT 'vault-name');

Key rotation procedure:

  1. Create new key version in Key Vault
  2. Update database encryption protector to new key version
  3. Database automatically re-encrypts DEK (Database Encryption Key) with new KEK (Key Encryption Key)
  4. No downtime required

Cosmos DB with CMK:

  • Enable CMK at account creation or update existing account
  • Point to Key Vault URI and grant Cosmos DB managed identity access
  • All data, indexes, and backups encrypted with CMK automatically
  • Key rotation: Update Key Vault key version, Cosmos DB automatically uses new version
  • No manual intervention required

Compliance Certifications:

For SOC2 and HIPAA compliance:

Required Controls:

  • Encryption at rest with customer-managed keys (both services support this)
  • Encryption in transit with TLS 1.2+ (both services enforce this)
  • Key access audit logging (Key Vault diagnostic logs to Log Analytics)
  • Key rotation procedures documented and tested (quarterly recommended)
  • Principle of least privilege for key access (RBAC on Key Vault)
  • Backup encryption with same key protection (both services include this)

Azure SQL Advantages for Compliance:

  • Always Encrypted provides additional control for high-sensitivity data
  • Dynamic data masking for non-production environments
  • SQL Audit logs capture all database operations
  • Row-level security for multi-tenant ERP scenarios

Cosmos DB Advantages for Compliance:

  • Simpler key management (no separate CMK per database)
  • Automatic encryption of all components (no configuration required)
  • Built-in geo-replication with consistent encryption
  • Lower operational overhead for multi-region deployments

Operational Overhead Comparison:

Azure SQL TDE + CMK:

  • Initial setup: 2-3 hours (Key Vault, managed identity, TDE enablement)
  • Key rotation: 30 minutes quarterly (automated scripts recommended)
  • Monitoring: Key Vault access logs, database encryption status
  • Backup considerations: Automated backup encryption included

Azure SQL Always Encrypted:

  • Initial setup: 1-2 weeks (application code changes, driver updates, testing)
  • Key rotation: 2-4 hours per rotation (requires application restart)
  • Monitoring: Column encryption key usage, application-side decryption metrics
  • Development overhead: All queries against encrypted columns require special handling

Cosmos DB CMK:

  • Initial setup: 1-2 hours (Key Vault, managed identity, CMK enablement)
  • Key rotation: Automatic (Cosmos detects new key version)
  • Monitoring: Key Vault access logs
  • Backup considerations: Fully automated

My Recommendation for ERP Workloads:

Use Azure SQL Database with TDE and customer-managed keys for core ERP transactional data. This provides:

  • ACID transactions required for financial data
  • Compliance-grade encryption with customer control
  • Minimal operational overhead
  • Familiar relational model for ERP schemas

Add Always Encrypted for specific sensitive columns only if:

  • Regulatory requirements mandate protection against privileged user access
  • You have engineering resources to modify application code
  • The performance impact is acceptable for your query patterns

Reserve Cosmos DB for ERP scenarios requiring:

  • Global distribution with low-latency reads (multi-region ERP deployments)
  • Flexible schema for rapidly evolving data models
  • Massive scale (millions of transactions per second)

For most traditional ERP implementations, Azure SQL with TDE+CMK provides the optimal balance of security, compliance, and operational simplicity. The encryption is transparent, the compliance story is clear, and the operational overhead is manageable. Add Always Encrypted selectively based on specific regulatory requirements rather than as a default approach.

6

David’s point about audit logging is crucial and often overlooked. Let me add that you also need to document your key rotation procedures and actually test them. Many organizations enable CMK for compliance checkboxes but never rotate keys, which defeats the purpose. Azure Key Vault supports automated key rotation for some scenarios, but you need to ensure your database services are configured to handle rotation gracefully without downtime.

7

For SOC2 and HIPAA, the critical requirement is demonstrating that you control the encryption keys, not just that encryption exists. Both Azure SQL and Cosmos DB support customer-managed keys (CMK) via Key Vault, so they’re both compliant-capable. The difference is in how granular you can get with encryption.

Azure SQL’s Always Encrypted lets you encrypt specific columns so even DBAs can’t see sensitive data. Cosmos DB encrypts the entire database container, which is simpler but less granular. For ERP data with mixed sensitivity levels, column-level encryption might be valuable.

8

From the Cosmos DB side, encryption is much simpler but less flexible. All data is encrypted at rest by default using service-managed keys. If you need customer-managed keys for compliance, you enable CMK in Key Vault and point Cosmos to your key. Done. No application changes, no performance impact, automatic encryption of all data including indexes and backups.

The tradeoff is you can’t do column-level encryption or client-side encryption like Azure SQL’s Always Encrypted. It’s all-or-nothing encryption at the account level. For ERP transactional data where you need relational integrity and ACID transactions, Azure SQL is usually the better fit anyway. Cosmos shines for globally distributed, schema-flexible scenarios.