AZ-500 Secure compute, storage, and databases Practice Question
A company stores sensitive healthcare data in Azure SQL Database. They need to encrypt specific columns containing patient diagnosis codes so that even database administrators with the 'sysadmin' role cannot view the plaintext. The application must be able to perform equality searches (WHERE clauses) on the encrypted columns. Which encryption technology should they implement?
⚠ Common exam trap
Watch out — candidates often confuse encryption at rest (TDE) with client-side column-level encryption, failing to recognize that TDE does not protect data from privileged users who can run queries, while Always Encrypted does by keeping keys off the server.
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 (deterministic encryption)
Always Encrypted with deterministic encryption ensures that sensitive columns are encrypted at the client side, so the encryption keys are never revealed to the database engine, including sysadmin roles. Deterministic encryption generates the same ciphertext for the same plaintext, enabling equality searches (WHERE clauses) on encrypted columns without exposing plaintext data to the server.
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
Transparent Data Encryption (TDE) encrypts the database files and backups on disk, but it is not a defense against authorized database users—the database engine automatically decrypts data for queries using keys held in the system. Because the encryption keys are protected by the service master key and available to the database engine, a user with the sysadmin role can simply connect and issue SELECT statements to read all data in plaintext. TDE is therefore effective only against offline theft of physical files or backups, not against the requirement to prevent DBAs from reading sensitive healthcare data.
- ✓
Always Encrypted (deterministic encryption)
Why this is correct
Always Encrypted is the correct choice because it encrypts selected column data between the client application and the database engine, with the column encryption keys never being passed to or stored in SQL Database in plaintext. The client-side driver performs encryption and decryption, so the database engine only ever sees ciphertext; even a sysadmin with full server access cannot view the sensitive values without the client-held Column Master Key. Deterministic encryption is specifically suitable here because it allows equality comparison and inner join operations on the ciphertext, enabling indexed equality searches on fields like national identifiers or medical record numbers while still shielding the values from DBAs.
- ✗
Row-Level Security (RLS)
Why it's wrong here
Row-Level Security (RLS) only restricts the rows returned by queries based on a security predicate that references a user's context, typically via SESSION_CONTEXT or USER_NAME(). However, RLS does not encrypt data, and the predicate itself can be bypassed or altered by privileged users—a sysadmin can ignore the predicate, modify its definition, or run SELECT directly from a different security context. Additionally, because the data is stored in plaintext in the underlying rowstore, RLS offers no protection for column values at rest or when someone extracts them from backups.
- ✗
Dynamic Data Masking (DDM)
Why it's wrong here
Dynamic Data Masking (DDM) obfuscates the values in query results by applying masking functions at display time to users who do not have the UNMASK permission, but it does not encrypt the underlying data on disk or in memory. A sysadmin can query the base tables directly, grant himself the UNMASK permission, or simply bypass the masking rule by using an alternative access path such as a linked server or extended events. Because the original values remain fully available to anyone with elevated permissions, DDM should be considered a compliance-friendly presentation-layer control, not a security control that prevents privileged users from reading sensitive data.
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.