This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Secure, optimize, and deploy database solutions (35–40%)
   --> Implement data security and compliance
      --> Implement auditing

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

In Parts 1 and 2, you learned how SQL Server auditing works, how Azure SQL auditing integrates with Azure services, and how auditing supports compliance, monitoring, and forensic investigations. This final section summarizes the topic, compares auditing with related security features, presents real-world scenarios, and concludes with 10 DP-800-style practice exam questions.

Auditing vs. Other SQL Security Features

Understanding the differences between SQL Server security features is critical for the DP-800 exam.

A common exam question is determining which technology satisfies a particular requirement:

• Need to record who accessed payroll data? → Auditing
• Need to hide Social Security numbers? → Dynamic Data Masking
• Need to encrypt credit card numbers? → Always Encrypted
• Need users to see only their own records? → Row-Level Security
• Need protection for database files at rest? → Transparent Data Encryption

SQL Server Audit Workflow

A simplified auditing workflow is shown below.

User Action
     │
     ▼
SQL Server
     │
     ▼
Audit Specification
(Server or Database)
     │
     ▼
SQL Server Audit
     │
     ▼
Audit Target
(File, Azure Storage,
Log Analytics, Event Hub)
     │
     ▼
Investigation /
Compliance Reporting

Common Audited Events

Organizations commonly audit:

Authentication

• Successful logins
• Failed logins
• Password changes
• Login creation
• Login deletion

Administrative Changes

• CREATE DATABASE
• DROP DATABASE
• ALTER DATABASE
• CREATE LOGIN
• ALTER LOGIN
• Server role changes

Security Changes

• GRANT
• DENY
• REVOKE
• Permission changes
• Role membership changes

Data Access

• SELECT
• INSERT
• UPDATE
• DELETE
• EXECUTE

Typically, organizations only audit access to sensitive tables rather than every table in the database.

Schema Changes

• CREATE TABLE
• ALTER TABLE
• DROP TABLE
• CREATE PROCEDURE
• ALTER PROCEDURE
• CREATE VIEW

Real-World Scenario 1

A healthcare provider stores patient records in Azure SQL Database.

Requirements:

• Record every UPDATE made to patient records.
• Retain logs for seven years.
• Alert security personnel when permission changes occur.

Recommended solution:

• Enable Azure SQL Auditing.
• Send logs to Azure Storage for long-term retention.
• Send logs to Log Analytics.
• Configure Azure Monitor alerts.
• Forward events to Microsoft Sentinel.

Real-World Scenario 2

A financial institution experiences unauthorized data modifications.

Requirements:

• Determine who modified account balances.
• Determine when modifications occurred.
• Review executed SQL statements.

Solution:

Query audit logs using:

• sys.fn_get_audit_file() (SQL Server)
• Log Analytics (Azure SQL)
• Azure Storage audit files

Review:

• Login name
• Timestamp
• Statement
• Database
• Object
• Session ID

Real-World Scenario 3

A company wants to monitor privileged users only.

Instead of auditing every database action:

Audit:

• Login events
• Role changes
• Permission changes
• ALTER statements
• DROP statements

This minimizes performance impact while providing meaningful security visibility.

Compliance Mapping

Remember:

Auditing provides evidence, not protection.

Performance Best Practices

For production environments:

✔ Audit only important events.

✔ Avoid auditing every SELECT statement unless required.

✔ Archive logs regularly.

✔ Protect audit files with appropriate permissions.

✔ Monitor storage consumption.

✔ Review audit logs routinely.

✔ Test audit configurations before production deployment.

✔ Separate audit storage from transaction log storage whenever practical.

DP-800 Exam Tips

Be comfortable answering questions about:

• Server Audit vs. Database Audit Specification
• Azure SQL auditing
• Audit destinations
• Log Analytics
• Azure Storage
• Event Hubs
• Microsoft Sentinel
• Azure Monitor
• Compliance scenarios
• Investigating suspicious activity
• Performance implications of auditing

Quick Review

Remember these key concepts:

Common DP-800 Pitfalls

Avoid these misconceptions:

• Auditing does not encrypt data.
• Auditing does not prevent unauthorized access.
• Auditing is not a replacement for backups.
• Auditing does not replace Microsoft Defender for SQL.
• Dynamic Data Masking does not record access.
• Always Encrypted does not log who viewed data.

Practice Exam Questions

Question 1

A company must determine who modified salary information in the Employees table. Which SQL Server feature should be implemented?

A. Transparent Data Encryption

B. SQL Server Audit

C. Dynamic Data Masking

D. Row-Level Security

Answer: B

Explanation:

SQL Server Audit records database activity, including UPDATE operations, allowing administrators to identify who modified data, when the modification occurred, and which statement was executed. The other options protect or restrict data but do not record user activity.

Question 2

Which SQL Server object specifies where audit records are written?

A. Database Audit Specification

B. Server Audit Specification

C. SQL Server Audit

D. Audit Action Group

Answer: C

Explanation:

The SQL Server Audit object defines the audit destination, such as a file, Windows Security Log, or Windows Application Log. Audit specifications determine which events are captured.

Question 3

An organization wants to search audit logs using Kusto Query Language (KQL). Which Azure service should store the audit data?

A. Azure Storage

B. Event Hubs

C. Log Analytics Workspace

D. Azure Key Vault

Answer: C

Explanation:

Log Analytics stores audit data in a format that supports KQL queries, dashboards, alerts, and Azure Monitor integration. Azure Storage is intended for long-term retention rather than interactive querying.

Question 4

Which audit specification captures database-level activities such as SELECT, UPDATE, and DELETE?

A. Server Audit

B. Database Audit Specification

C. Audit Target

D. Server Audit Specification

Answer: B

Explanation:

Database Audit Specifications capture actions performed within a database, including DML operations and permission changes. Server Audit Specifications capture server-level activities.

Question 5

Which Azure service is primarily intended for streaming audit events to external monitoring systems in near real time?

A. Azure Storage

B. Azure Files

C. Log Analytics

D. Azure Event Hubs

Answer: D

Explanation:

Azure Event Hubs provides scalable event streaming for integration with SIEM platforms, custom monitoring solutions, and security tools. It is optimized for real-time event ingestion.

Question 6

Which function is commonly used to read SQL Server audit files?

A. OPENROWSET()

B. sys.fn_get_audit_file()

C. sp_readaudit

D. sys.fn_audit_log()

Answer: B

Explanation:

sys.fn_get_audit_file() is the built-in table-valued function used to read SQL Server audit files and return audit events in a queryable format.

Question 7

A security administrator needs immediate notification whenever database permissions change. Which solution best meets this requirement?

A. Configure auditing with Log Analytics and Azure Monitor alerts.

B. Disable auditing and use transaction logs.

C. Store audit files only in Azure Storage.

D. Enable Transparent Data Encryption.

Answer: A

Explanation:

Auditing records permission changes, while Azure Monitor can generate alerts based on those audit events stored in Log Analytics. Azure Storage alone does not provide real-time alerting.

Question 8

Which statement correctly describes SQL Server auditing?

A. It encrypts sensitive columns.

B. It prevents unauthorized access to data.

C. It automatically restores deleted records.

D. It records security-related database and server activity.

Answer: D

Explanation:

Auditing records activities for monitoring, compliance, and investigation. It does not encrypt data, restore deleted records, or enforce permissions.

Question 9

Which audit target is generally recommended by Microsoft for most on-premises production SQL Server environments?

A. File

B. Windows Security Log

C. Windows Application Log

D. Azure Event Hubs

Answer: A

Explanation:

File targets provide excellent performance, scalability, and flexibility. They are the recommended destination for most production SQL Server deployments.

Question 10

Which Microsoft security service uses audit information to help detect suspicious database activity and investigate incidents?

A. Azure Backup

B. Microsoft Sentinel

C. SQL Server Agent

D. Azure Resource Manager

Answer: B

Explanation:

Microsoft Sentinel consumes audit logs from services such as Azure SQL Database to correlate events, detect threats, automate investigations, and assist security analysts. It complements auditing by providing advanced security analytics rather than simply recording events.

Final DP-800 Takeaways

For the DP-800 exam, remember these core principles:

• SQL Server Audit defines where audit records are stored.
• Server Audit Specifications capture server-level activities such as logins and server role changes.
• Database Audit Specifications capture database-level activities such as data access and schema changes.
• Azure Storage is ideal for long-term retention.
• Log Analytics enables interactive querying, dashboards, and Azure Monitor alerts.
• Azure Event Hubs supports real-time streaming to external systems.
• Microsoft Sentinel extends auditing with SIEM capabilities, threat detection, and incident response.
• Auditing provides accountability, supports compliance, and enables forensic investigations, but it does not replace encryption, access control, or threat protection technologies.

Go to the DP-800 Exam Prep Hub main page