AZ-500 Secure compute, storage, and databases Practice Question
A healthcare organization stores sensitive patient data in Azure SQL Database. They need to encrypt specific columns containing medical history so that even database administrators with highly privileged roles, such as 'sysadmin', cannot view the plaintext data. Additionally, they need to support complex queries on the encrypted data, including pattern matching and range comparisons. Which encryption technology should they implement?
⚠ Common exam trap
It's easy for candidates to confuse Transparent Data Encryption (TDE) with column-level encryption, assuming TDE protects data from privileged users, but TDE only protects data at rest and does not prevent authorized database users from reading plaintext data.
Answer choices
Why each option matters
Answer the question above first, then reveal the full breakdown to understand why each option is right or wrong.
Correct answer & explanation
✓
Always Encrypted with secure enclaves
Always Encrypted with secure enclaves is correct because it encrypts specific columns at the client side, ensuring that even database administrators with sysadmin privileges cannot view the plaintext data. The secure enclave feature allows computations (such as pattern matching and range comparisons) to be performed on the encrypted data inside a trusted execution environment, which is required by the question's need for complex queries on encrypted columns.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✓
Always Encrypted with secure enclaves
Why this is correct
Always Encrypted with secure enclaves performs client-side column encryption, so the database engine only ever processes ciphertext while the encryption keys are held outside SQL Server. The enclave—a trusted hardware environment such as Intel SGX inside Azure Confidential Computing—enables rich operations like pattern matching, range comparisons, and sorting without ever exposing plaintext to the database process. This makes it the only option that both prevents database administrators from seeing data and supports computed queries over encrypted columns.
- ✗
Transparent Data Encryption (TDE)
Why it's wrong here
Transparent Data Encryption (TDE) encrypts the physical database files, backups, and transaction logs at rest, but the database engine automatically decrypts page data as it is loaded into memory for query execution. As a result, any user or DBA with normal SELECT or CONTROL permissions sees original plaintext, because decryption is transparent to the engine. TDE is designed to protect against media theft, not privileged access, and it cannot evaluate predicates, perform joins, or run pattern matches against encrypted values.
- ✗
Dynamic Data Masking
Why it's wrong here
Dynamic Data Masking applies column-level masking functions such as partial() or email() to the query results, so non-privileged users see obfuscated output while the underlying data remains fully unencrypted in storage and memory. Users with the UNMASK permission or higher roles can simply run a SELECT and retrieve true values, and masking can sometimes be bypassed through data inference or side-channel queries. It is a presentation-layer authorization tool, not an encryption mechanism, and provides no confidentiality at rest or support for computations over ciphertext.
- ✗
Row-Level Security
Why it's wrong here
Row-Level Security uses a security predicate—typically an inline table-valued function—that the query optimizer automatically applies as a filter to restrict which rows are returned to a particular user. While this enforces fine-grained row scoping, the data values are stored as plaintext and the filter only applies during query execution. A principal with ALTER, CONTROL, or db_owner permission can modify or disable the predicate or read the table in ways that bypass the policy, so it grants no protection against privileged users and no column-level encryption.
Go deeper
Related to this question
About these practice questions
Courseiva writes every AZ-500 question from scratch — 194 in total, each with an explanation and a wrong-answer breakdown. None are copied from real exams or dumps. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This AZ-500 practice question is part of Courseiva's free Microsoft certification practice question bank. Courseiva provides original exam-style practice questions with explanations, topic-based practice, mock exams, readiness tracking, and study analytics to help learners prepare for the AZ-500 exam.