Secure compute, storage, and databases →hardMultiple ChoiceObjective-mapped

AZ-500 Secure compute, storage, and databases Practice Question

A company uses Azure SQL Database to store personally identifiable information (PII). They need to encrypt specific columns containing social security numbers so that even database administrators with the 'db_owner' role cannot view the plaintext. The application must be able to perform equality searches on the encrypted columns. Which encryption technology should they implement?

⚠ Common exam trap

Many exam-takers confuse encryption at rest (TDE) with client-side column encryption, mistakenly believing TDE protects against privileged users, but TDE only protects against physical theft of the database files, not against authorized database access.

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 specific columns at the client-side, ensuring that even database administrators with db_owner cannot view plaintext data. Deterministic encryption generates the same ciphertext for a given plaintext value, enabling equality searches (e.g., WHERE SSN = '123-45-6789') directly on the encrypted column without decrypting the data on 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 protects data at rest by encrypting the physical database files, backup files, and transaction logs. However, TDE is transparent to the SQL Server Database Engine: when a query accesses a page, the engine decrypts it in memory, meaning a DBA or any user with legitimate SELECT permissions receives the plaintext result. TDE is therefore a defense against stolen media or backups, not against authorized users who can query the database directly.
✓
Always Encrypted with deterministic encryption
Why this is correct
Always Encrypted encrypts sensitive columns in the client driver using a column encryption key, which is protected by a column master key held outside SQL Server in Azure Key Vault or Windows Certificate Store. The SQL Server Database Engine receives only ciphertext and never sees the plaintext, so DBAs cannot read the data. With deterministic encryption, the same plaintext always produces the same ciphertext, allowing the server to perform equality comparisons (e.g., WHERE clause lookups) without exposing the values.
✗
Dynamic Data Masking
Why it's wrong here
Dynamic Data Masking applies mask functions such as email or partial mask to hide column values from non-privileged application users, but the original plaintext is still stored in the database. Users with the db_owner role or the UNMASK permission can execute SELECT statements and see the unmasked data. Masking is purely a presentation-layer filter on query results; it does not prevent privileged users or attackers with direct access from reading the underlying values.
✗
Row-Level Security
Why it's wrong here
Row-Level Security restricts which rows are returned to a given user or logon by attaching security predicates to a table, typically using an inline table-valued function. The filter predicate is enforced by the query engine, but the data itself is stored unencrypted and the engine sees all intermediate values. A user with db_owner privilege can alter or drop the security policy to view every row, so RLS does not protect data from DBAs.

Go deeper

AZ-500 Secure compute, storage, and databases Practice Question

Why each option matters

Option-by-option breakdown

Related to this question