AZ-500 Secure compute, storage, and databases Practice Question
A company has an Azure SQL Database that stores personally identifiable information (PII) in columns. They need to encrypt those columns so that only authorized applications can decrypt the data, and even database administrators cannot view the plaintext. Additionally, they need to support equality comparisons (WHERE clauses) on the encrypted columns. Which encryption technology should they use?
⚠ Common exam trap
A common mix-up: candidates confuse Always Encrypted with TDE, thinking TDE provides client-side encryption and column-level query support, but TDE only encrypts data at rest and does not prevent database administrators from seeing plaintext data in memory or during queries.
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 the correct choice because it encrypts PII columns at the client side, ensuring that even database administrators cannot view plaintext data. Deterministic encryption generates the same ciphertext for the same plaintext, which allows equality comparisons (WHERE clauses) on encrypted columns, meeting the requirement for query support.
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 deterministic encryption
Why this is correct
Always Encrypted with deterministic encryption encrypts PII client-side so the SQL engine and database administrators never see plaintext. It uses a deterministic algorithm where the same plaintext always produces the same ciphertext for a given column encryption key, enabling the server to perform equality comparisons in WHERE, JOIN, and GROUP BY clauses. This supports business queries that require filtering on PII (e.g., searching by social security number) while preserving confidentiality. However, deterministic encryption can reveal equality patterns and is less secure than randomized, but it remains the correct choice for applications needing strict DBA access control.
- ✗
Always Encrypted with randomized encryption
Why it's wrong here
Always Encrypted with randomized encryption generates a new random initialization vector for every encryption operation, so the same plaintext produces a different ciphertext each time. The SQL Server cannot compare ciphertext values, so it cannot execute equality searches, range queries, or joins on encrypted columns. While this mode offers the highest security, it breaks the application's core lookup functionality on PII, making it an unsuitable replacement. Only the client application can decrypt the data, but the queryability constraint directly conflicts with the stated requirement.
- ✗
Transparent Data Encryption (TDE)
Why it's wrong here
Transparent Data Encryption (TDE) encrypts the entire Azure SQL database at rest, including data and log files, protecting against theft of physical media and unauthorized backups. The database engine automatically decrypts table pages in memory when queried, so any principal with legitimate database permissions—including administrators—can read the underlying PII in plaintext. TDE does not prevent DBAs or malicious administrators from viewing sensitive data nor does it control access at the column level. Therefore, it fails to satisfy a requirement for confidentiality against database administrators.
- ✗
Dynamic Data Masking
Why it's wrong here
Dynamic Data Masking hides masked columns from unauthorized users by masking the values in query results, but it does not alter or encrypt the underlying stored data. Users with unmasked permission, such as a DBA or those with SELECT and ALTER permissions, can still view the actual PII directly from the table or bypass the masking by querying the underlying tables. It also does not protect data in motion or at rest, and masking rules can be overridden by privileged users. As a result, it does not meet the core encryption and DBA-access-prevention requirement.
Go deeper
Related to this question
About these practice questions
This AZ-500 question is part of Courseiva's 194-question bank — original exam-style content with full explanations and wrong-answer analysis, never real exam questions or exam 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.