4.3.4. Transparent Data Encryption (TDE) and Always Encrypted

💡 First Principle: Encryption protects data at rest. TDE encrypts the entire database transparently; Always Encrypted encrypts specific columns with keys never exposed to the database server.

Encryption Comparison

Scenario: You need to ensure certain columns cannot be decrypted by database administrators.

Answer: Use Always Encrypted. TDE protects data at rest but DBAs with access to the database can read decrypted data. Always Encrypted keeps keys on the client side—the database server never sees plaintext.

⚠️ Exam Trap: Thinking TDE protects against DBAs. TDE encrypts data files on disk but decryption is transparent to authorized database users. For protection against privileged database access, use Always Encrypted.

Always Encrypted Supported Operations

• Supported: Point lookups, equality comparisons (deterministic encryption)
• Not Supported: Range queries, LIKE operations, copying between columns