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.

Introduction

In Part 1, you learned about the SQL Server Audit architecture, audit specifications, audit targets, audit action groups, and how to configure and manage audits in SQL Server. In this section, we’ll examine how auditing works in Azure SQL services, how audit data integrates with Azure monitoring solutions, and the performance and operational considerations that are especially relevant for the DP-800 exam.

Auditing in Azure SQL Database

Azure SQL Database includes built-in auditing capabilities that are designed for cloud-native environments. Unlike on-premises SQL Server, Azure SQL Database can automatically integrate with Azure services for centralized monitoring and compliance.

Azure SQL auditing records database events such as:

• Successful and failed logins
• Database schema changes
• Permission modifications
• Data access (SELECT)
• Data modifications (INSERT, UPDATE, DELETE)
• Stored procedure execution
• Security configuration changes
• Administrative operations

Auditing can be configured at two levels:

• Server level
• Individual database level

Server-level auditing provides a consistent policy across all databases on the logical SQL server, while database-level auditing allows different auditing configurations for specific databases.

Azure SQL Auditing Architecture

Azure SQL Database
        │
        ▼
    SQL Auditing
        │
 ┌──────┼────────┐
 ▼      ▼        ▼
Storage Log Analytics Event Hub
Account Workspace

One audit configuration can send events to one or more Azure services.

Audit Destinations in Azure

Unlike SQL Server, Azure SQL Database supports several cloud-based audit destinations.

Azure Storage Account

The most common destination.

Benefits include:

• Low-cost storage
• Long-term retention
• Backup
• Archive capabilities
• Easy export
• Compliance support

Organizations frequently retain audit logs in Storage Accounts for multiple years.

Log Analytics Workspace

Many organizations choose Log Analytics because it supports:

• Interactive searches
• Kusto Query Language (KQL)
• Dashboards
• Alerting
• Workbooks
• Azure Monitor integration

Example investigations include:

• Failed login trends
• Privileged user activity
• Permission changes
• Suspicious DELETE operations

Azure Event Hubs

Event Hubs allows organizations to stream audit events in near real time.

Typical integrations include:

• SIEM platforms
• Security monitoring solutions
• Custom monitoring applications
• Third-party security tools

Configuring Azure SQL Auditing

Auditing can be enabled through:

• Azure Portal
• Azure CLI
• PowerShell
• ARM templates
• Bicep
• Terraform
• Azure REST API

Within the Azure Portal, the configuration typically involves:

1. Select the SQL Server or database.
2. Open Auditing under the Security section.
3. Enable auditing.
4. Choose one or more destinations.
5. Configure retention settings.
6. Save the configuration.

Retention Policies

Azure Storage destinations support configurable retention periods.

Examples include:

• 90 days
• 180 days
• 1 year
• Multiple years

Retention should match organizational compliance requirements.

Examples:

Azure SQL Managed Instance Auditing

Azure SQL Managed Instance supports auditing capabilities similar to SQL Server while integrating with Azure services.

Supported destinations include:

• Azure Storage
• Log Analytics
• Event Hubs

Managed Instance also supports many SQL Server auditing features, making it easier to migrate on-premises workloads to Azure without redesigning security monitoring.

Microsoft Fabric SQL Auditing Considerations

Microsoft Fabric SQL databases and SQL analytics endpoints are integrated into the broader Microsoft Fabric governance ecosystem.

Rather than relying solely on traditional SQL Server Audit objects, Fabric environments also benefit from:

• Microsoft Purview governance
• Activity monitoring
• Workspace monitoring
• Capacity monitoring
• Microsoft Fabric Activity Log
• Azure Monitor integration
• Microsoft Defender integration

For the DP-800 exam, understand that auditing in Fabric emphasizes cloud-native monitoring and governance rather than traditional SQL Server Audit files.

Viewing Audit Logs

Azure Portal

Administrators can review:

• Audit status
• Destination
• Retention
• Recent activity

The portal provides quick access to Log Analytics and Storage Accounts where audit records reside.

Log Analytics

Audit records become searchable using Kusto Query Language (KQL).

Example:

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| where statement_s contains "DELETE"

This query returns DELETE statements captured by SQL auditing.

Storage Account

Audit files stored in Azure Storage can be:

• Downloaded
• Archived
• Imported
• Processed by external tools
• Loaded into Power BI
• Queried with Azure Data Explorer

Integrating Auditing with Azure Monitor

Azure Monitor provides centralized monitoring across Azure resources.

Audit logs can generate:

• Alerts
• Dashboards
• Metrics
• Workbooks
• Notifications

Example alert:

Notify the security team whenever more than ten failed login attempts occur within five minutes.

Microsoft Sentinel Integration

Microsoft Sentinel is Microsoft’s cloud-native Security Information and Event Management (SIEM) platform.

Audit logs can be streamed into Sentinel where security analysts can:

• Detect attacks
• Investigate incidents
• Correlate events
• Create analytics rules
• Build hunting queries
• Automate responses

Example scenario:

1. Repeated failed logins
2. Successful privileged login
3. Mass DELETE operations

Sentinel correlates these events into a potential security incident.

Microsoft Defender for SQL

Auditing and Microsoft Defender for SQL complement one another.

For example:

Auditing records that a user executed a large number of DELETE statements, while Defender for SQL may identify that behavior as anomalous and raise a security alert.

Performance Considerations

Auditing introduces some performance overhead because every audited event must be written to an audit target.

The impact depends on factors such as:

• Number of audited events
• Frequency of activity
• Storage performance
• Audit destination
• Network latency (Azure)

Fortunately, SQL Server auditing is highly optimized and generally has minimal impact when configured appropriately.

Reducing Performance Overhead

Microsoft recommends several strategies.

Audit Only Necessary Events

Avoid auditing every possible action.

Instead, focus on:

• Logins
• Permission changes
• Sensitive table access
• Administrative operations

Avoid Excessive SELECT Auditing

High-volume transactional systems may execute millions of SELECT statements daily.

Auditing every SELECT can:

• Increase storage consumption
• Generate enormous audit files
• Reduce performance

Instead, audit only access to sensitive tables.

Separate Audit Storage

Whenever possible:

• Store audit files on separate disks.
• Use dedicated Azure Storage Accounts.
• Avoid sharing storage with transaction logs.

Archive Older Logs

Large audit repositories become difficult to search.

Implement:

• Automatic archiving
• Lifecycle management
• Long-term storage
• Periodic cleanup

Monitoring Audit Health

Administrators should routinely verify that auditing is functioning correctly.

Check:

• Audit status
• Storage availability
• Remaining storage capacity
• Failed audit writes
• Log Analytics ingestion
• Event Hub connectivity
• Audit retention settings

Monitoring helps prevent gaps in audit coverage.

Common Auditing Scenarios

Scenario 1

A hospital must record every update to patient records.

Recommended approach:

• Database auditing
• Audit UPDATE operations
• Store logs in Azure Storage
• Retain logs according to healthcare regulations

Scenario 2

A bank wants immediate notification when administrators change permissions.

Recommended approach:

• Audit permission changes
• Send events to Log Analytics
• Create Azure Monitor alerts
• Forward alerts to Microsoft Sentinel

Scenario 3

A company wants to investigate suspicious DELETE statements after a potential insider attack.

Recommended approach:

• Query audit logs
• Identify user accounts
• Review timestamps
• Correlate activity with authentication logs

Common Mistakes

Candidates often confuse several related security technologies.

Remember:

• Auditing records activity.
• It does not prevent activity.
• It does not encrypt data.
• It does not mask data.

DP-800 Exam Tips

Expect scenario-based questions such as:

• Which audit destination should be selected?
• Which service enables security investigations?
• Which Azure service should receive audit logs?
• How should audits be configured for compliance?
• Which audit events should be enabled?
• How can auditing be integrated with Azure Monitor?

Also remember:

• Azure Storage is commonly used for long-term retention.
• Log Analytics is best for querying and analysis.
• Event Hubs is designed for real-time event streaming.
• Microsoft Sentinel builds on audit logs to provide advanced threat detection and incident response.
• Microsoft Defender for SQL complements auditing by detecting suspicious behavior rather than simply recording it.

Best Practices Summary

• Enable auditing for all production databases.
• Audit only security-relevant events to minimize overhead.
• Prefer centralized monitoring using Azure Monitor and Log Analytics.
• Protect audit logs from unauthorized modification or deletion.
• Configure retention policies that satisfy organizational and regulatory requirements.
• Integrate auditing with Microsoft Sentinel for security operations.
• Periodically review audit logs and validate that auditing remains enabled after deployments or configuration changes.
• Document audit policies and test recovery procedures for audit data.

Go to the DP-800 Exam Prep Hub main page