3.3.1. Security Features (TDE, Always Encrypted, Masking)

💡 First Principle: Each security feature protects against a specific threat. Understanding which threat each feature addresses helps you build appropriate defenses without over-engineering or leaving gaps.

Transparent Data Encryption (TDE)

💡 First Principle: TDE encrypts the entire database, backups, and transaction logs at rest using a symmetric key called the Database Encryption Key (DEK). This happens automatically with zero application changes—"transparent" means neither the application nor users notice any difference.

• Enabled by default on Azure SQL Database and Azure Synapse Analytics
• Encrypts data files, log files, and backups
• Uses AES-256 encryption algorithm
• Can use service-managed keys or customer-managed keys (BYOK)

⚠️ Exam Tip: TDE protects data at REST only. It does NOT encrypt data in transit or data in memory during query processing. A backup stolen from Azure Storage is unreadable, but a DBA running queries sees plain text.

Always Encrypted

💡 First Principle: Always Encrypted protects sensitive data by ensuring encryption keys never leave the client application. The database engine itself cannot decrypt the data—even database administrators cannot see the plaintext. This is the only way to protect data from insider threats.

• Encryption/decryption happens in the client driver, not the database engine
• Two encryption types: Deterministic (allows equality comparisons) and Randomized (more secure, no comparisons)
• Requires application code changes to use enabled client drivers
• Ideal for: credit card numbers, SSNs, salary data, medical records

TDE vs. Always Encrypted Comparison

Dynamic Data Masking

💡 First Principle: Dynamic Data Masking hides sensitive data in query results for non-privileged users without changing the actual stored data. It's obfuscation for display, not true encryption—a convenience layer, not a security boundary.

• Policy-based feature—no application changes needed
• Masking functions: Default (full mask), Email, Custom string, Random number
• Users with UNMASK permission see actual data

⚠️ Exam Tip: Dynamic Data Masking is NOT encryption. It's obfuscation for display purposes only. A determined user with direct database access could potentially bypass it. Never rely on masking as your only protection for truly sensitive data.

Azure CLI Examples: Security Configuration

# Check TDE status on Azure SQL Database
az sql db tde show --resource-group myRG --server myServer --database myDB

# Enable TDE with customer-managed key
az sql db tde set --resource-group myRG --server myServer --database myDB --status Enabled

# Configure firewall rule for Azure SQL
az sql server firewall-rule create --resource-group myRG --server myServer \
  --name AllowMyIP --start-ip-address 203.0.113.1 --end-ip-address 203.0.113.1