The DP-300 exam's data protection section asks which feature defends against which threat. Encryption at rest, encryption in transit, client-side encryption, and network isolation each address a different attack vector. Key management and DBA visibility differ across these features, and the exam probes those differences through scenario questions. Understanding which layer each technology protects makes it easier to pick the right tool for a given scenario.
TDE — Locking Data in a Safe
Imagine an attacker who physically breaks into a server room and walks out with a hard drive. If they connect that drive to another machine, can they read the data files? Transparent Data Encryption (TDE) is exactly what prevents that scenario.
TDE automatically encrypts data files, log files, and backup files at the page level inside Azure SQL Database. No application code changes are required, and the performance overhead is negligible. On Azure SQL Database, TDE is enabled by default. In the default configuration, Azure manages the encryption key using a Service-Managed Key. If compliance or audit requirements demand that your team control the key, you can store your own key in Azure Key Vault — an approach called Customer-Managed Key (CMK) or Bring Your Own Key (BYOK). With CMK, your operations team controls key rotation and revocation. Deleting the key blocks database access immediately.
Always Encrypted — A Sealed Envelope
Now imagine the threat is not an outsider but someone who has compromised a DBA account. TDE stops a person from walking off with a physical disk, but it does not prevent a legitimate query from returning plaintext data to a privileged user. Always Encrypted fills that gap.
With Always Encrypted, the client driver encrypts data before sending it to the server. Azure SQL Database stores and queries only the encrypted values; the decryption key never leaves the client side. Even if a DBA runs a direct SELECT statement, they see only encrypted bytes.
Two encryption types are available. Deterministic means that the same plaintext always produces the same ciphertext — equality comparisons (), GROUP BY, and JOIN work, but frequency analysis attacks are possible. Randomized means the same plaintext produces different ciphertext each time, which is more secure, but the column cannot be searched or sorted.
Always Encrypted with Secure Enclaves runs decryption inside a trusted execution environment (TEE) on the server side, enabling range queries (, ) even on Randomized columns while keeping plaintext away from database administrators.
Encryption in Transit — TLS and Connection Strings
Just as you would not want someone reading your bank PIN over your shoulder, traffic between a client and a database server must be encrypted against eavesdropping on the network.
Azure SQL Database enforces TLS 1.2 or higher by default. Setting in the connection string routes all data through an encrypted channel. Leaving — the default — means the client validates the server certificate, protecting against man-in-the-middle attacks. Developers sometimes flip this to when using self-signed certificates locally. That habit must never reach production because it disables certificate validation entirely.
Network Isolation — Private Link and Private Endpoint
If the goal is to block all access routed through the public internet, encryption alone is not enough. A building with a guarded entrance and badge-controlled doors is inherently safer than one where anyone can walk into the lobby.
Private Link and Private Endpoint give Azure SQL Database a private IP address inside a VNet. You can disable the public endpoint entirely, and all traffic travels only over the Microsoft backbone network. After configuring a Private Endpoint, setting the firewall rule to deny public network access makes the database unreachable from outside the VNet. Azure SQL Managed Instance integrates with a VNet by default, meaning there is no public endpoint from the start.
TDE vs Always Encrypted — What Is Actually Different
Both features are called encryption, but they defend against different threats and operate at different layers.
| Dimension | TDE | Always Encrypted | |:--|:--|:--| | Where encryption happens | Server (storage layer) | Client driver | | Key owner | Azure or operations team (CMK) | Application team | | DBA visibility | Can query plaintext | Sees ciphertext only | | Query capability | Unrestricted | Equality queries with Deterministic only | | Primary threat | Physical media theft, backup leaks | Privileged insider threat |
The two features are not mutually exclusive. A typical production setup applies TDE for broad at-rest protection and then adds Always Encrypted on the most sensitive columns, such as national ID numbers or card numbers, where even a trusted administrator should not see plaintext.
!TDE versus Always Encrypted
Encryption Scope and Key Management — How to Choose
Locking the whole house and keeping a personal safe inside are two different levels of protection. TDE is the house lock; Always Encrypted is the personal safe. Using both means neither an outside attacker nor an insider administrator can read the most sensitive data unencrypted.
On the key management side, TDE's Customer-Managed Key integrates with Azure Key Vault and gives the operations team full control over the key lifecycle. The column master key for Always Encrypted lives in a client-side certificate store or Azure Key Vault and is owned by the application team, not the database administrators. This separation of key ownership becomes important when both features are deployed together and teams need clear boundaries.
Trap Scenarios — What the Exam Likes to Ask
"The DBA must not be able to read column values even with a direct query." — The answer is Always Encrypted, not TDE. TDE does not restrict access for authenticated users querying through the database engine.
"Company policy requires the team to manage its own encryption keys." — Use TDE with Customer-Managed Key (BYOK) backed by Azure Key Vault. Service-Managed Key does not satisfy this requirement.
"A backup file was leaked and must not expose data." — TDE encrypts backup files, so the correct answer is TDE. Always Encrypted does not directly protect backup files.
"Block all public internet routing to the database." — Use Private Endpoint or Azure SQL Managed Instance. TLS protects the channel but does not remove the public path; Private Link removes it entirely.
Exam Key Takeaways
"Encrypt data at rest, no code changes" -- TDE "Backup and log files automatically encrypted" -- TDE "Self-managed key, Azure Key Vault integration" -- Customer-Managed Key (BYOK) "DBA cannot read plaintext, client-side encryption" -- Always Encrypted "Equality comparison supported, frequency analysis risk" -- Deterministic encryption "Cannot be searched, maximum security" -- Randomized encryption "Range and LIKE queries on Randomized columns" -- Always Encrypted with Secure Enclaves "Encryption in transit, certificate validation" -- TLS 1.2+, Encrypt=true "Block public endpoint, private IP address" -- Private Link / Private Endpoint "VNet-integrated by default, no public endpoint" -- Azure SQL Managed Instance "Defend against physical media theft" -- TDE "Defend against privileged insider threats" -- Always Encrypted
TDE = storage-layer lock, Always Encrypted = client-side seal, TLS = transit-path protection, Private Link = public-path removal.