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 the 'sysadmin' role cannot view the plaintext data. Additionally, they need to support equality comparisons (WHERE clauses) on the encrypted columns. Which encryption technology should they implement?
⚠ Common exam trap
Watch out — candidates often confuse Dynamic Data Masking with encryption, thinking it prevents privileged users from seeing data, when in fact it only masks output and does not protect data at rest or from direct queries by sysadmins.
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 deterministic encryption
Always Encrypted with deterministic encryption is correct because it encrypts specific columns at the client side, ensuring that even database administrators with 'sysadmin' role cannot view plaintext data. Deterministic encryption generates the same ciphertext for identical plaintext values, which allows equality comparisons (WHERE clauses) on encrypted columns, meeting both requirements.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Transparent Data Encryption (TDE)
Why it's wrong here
TDE performs automatic page-level encryption and decryption inside the database engine, so data is encrypted on disk and in backups but transparently decrypted for any running query. Because SQL Server holds the certificate and database encryption key, anyone with sysadmin or CONTROL DATABASE permissions can retrieve those secrets and view plaintext. Therefore, TDE protects against theft of physical media but not against a DBA or attacker with administrative access, making it insufficient for securing sensitive patient data from insiders.
- ✗
Always Encrypted with randomized encryption
Why it's wrong here
Always Encrypted with randomized encryption generates a fresh random initialization vector for every encryption operation, causing the same plaintext to produce different ciphertexts each time. While this offers strong protection against frequency analysis and pattern matching, it forces the server to perform equality comparisons, joins, groupings, and indexed lookups by decrypting all rows on the client, which is not practical for patient lookup queries. In a healthcare database that must repeatedly find records by exact identifiers, randomized encryption is cryptographically superior but operationally unusable.
- ✓
Always Encrypted with deterministic encryption
Why this is correct
Always Encrypted with deterministic encryption derives a fixed initialization vector from the plaintext value, so identical plaintexts always yield identical ciphertexts, enabling the server to perform equality comparisons, joins, and exact-match lookups without ever seeing the plaintext. The column encryption key is stored and used only on the client side (for example, in Windows Certificate Store or Azure Key Vault), meaning SQL Server and DBAs see only opaque ciphertext and cannot decrypt the data. This achieves the dual goal of secure patient data protection while retaining the ability to query by known identifiers.
- ✗
Dynamic Data Masking
Why it's wrong here
Dynamic Data Masking alters query results at presentation time by substituting a mask, such as 'XXX-XX-1234', for unmasked sensitive values, but the underlying plaintext remains fully stored in the database. Users with UNMASK permission or direct table access can see the real data, and cleverly constructed queries using implicit conversions or arithmetic can bypass the masking rules in some cases. Because it does not encrypt anything at rest and leaves DBAs and privileged users with plaintext, DDM fails to protect patient data from insiders and cannot meet encryption-based compliance requirements.
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.