DP-900 Describe core data concepts Practice Question
Your organization stores sensitive financial data in Azure SQL Database. You need to audit all SELECT operations on the 'Transactions' table and alert the security team when a user outside the finance department queries the table. Which feature should you use?
⚠ Common exam trap
Watch out — candidates often confuse auditing (logging who did what) with security features that protect data at rest or in transit, such as TDE or Dynamic Data Masking, which do not provide any logging or alerting capabilities.
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
✓
SQL Server Auditing
SQL Server Auditing is the correct choice because it tracks database events, including SELECT operations, and writes them to an audit log. You can configure an audit policy to capture all SELECT statements on the 'Transactions' table and then set up an alert (e.g., via Azure Monitor or Logic Apps) that triggers when a user from outside the finance department executes such a query. This directly meets the requirement to both audit and alert on specific user actions.
Answer analysis
Option-by-option breakdown
For each option: why learners choose it and why it is or isn't the right answer here.
- ✗
Microsoft Defender for SQL
Why it's wrong here
Microsoft Defender for SQL is a cloud-native security solution that offers vulnerability assessments, anomaly detection, and advanced threat protection for Azure SQL databases. It continuously monitors database activity for suspicious patterns, such as SQL injection attempts, but it does not produce a table-specific audit trail of successful SELECT operations. Its findings are derived from telemetry and audit logs, not from an independent per-table access log. Therefore, while it can alert on threats, it cannot answer 'who read this specific financial table and when' with the granularity supplied by SQL Server Auditing.
- ✗
Dynamic Data Masking
Why it's wrong here
Dynamic Data Masking is a presentation-layer feature that obscures sensitive columns in query results for non-privileged users, for example hiding credit card numbers as 'XXXX-XXXX-XXXX-1234'. It does not log, record, or track which users accessed which masked or unmasked values, nor does it capture the SQL statements executed against a table. Because masking is applied at query time and leaves no audit artifacts, it cannot be used for forensic or compliance investigations of data access. It is purely a preventive control to limit data exposure, not a detective control.
- ✓
SQL Server Auditing
Why this is correct
SQL Server Auditing is the correct choice because it tracks database events by writing audit entries to a designated destination such as Azure Blob Storage or Azure Log Analytics. You can define database-level audit specifications that include SELECT actions on specific financial tables, capturing the user principal, the exact T-SQL statement, and the timestamp of each access. These audit records can then be integrated with alerting rules to notify administrators about unusual queries against sensitive tables. This provides both a granular access log and a trigger mechanism, fulfilling the requirement in the scenario.
- ✗
Transparent Data Encryption
Why it's wrong here
Transparent Data Encryption (TDE) performs real-time encryption and decryption of the underlying database files, backups, and transaction logs at rest, using a database encryption key. While TDE protects the physical data files from being copied and read without the key, it does not provide any visibility into queries or user activity. Because encryption is transparent to the application and requires no code changes, there is no built-in audit log of who accessed the data. TDE addresses confidentiality at the storage layer but does not support the post-incident access analysis required by this scenario.
Go deeper
Related to this question
Learn chapter
Data Roles and Core Concepts
Key term
Azure SQL Database
Azure SQL Database is a fully managed relational database-as-a-service (DBaaS) in Microsoft Azure, based on the SQL Server engine, that handles scaling, backups, patching, and high availability automatically.
Key term
Data
Data is raw, unprocessed information, like numbers, words, or measurements, that can be stored, processed, and analyzed by computers.
About these practice questions
One of 820 original DP-900 practice questions on Courseiva, each with a full explanation and wrong-answer analysis — not exam dumps or protected exam content. Learn why practice questions differ from exam dumps →
JA
Written by Johnson Ajibi, MSc IT Security
Senior Network & Security Engineer · founder of Courseiva
This DP-900 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 DP-900 exam.