Tag: Database Auditing

azure, Databases, Microsoft Certification, SC-500, security October 10, 2026

Configure database auditing for Azure SQL Database and Azure SQL Managed Instance (SC-500 Exam Prep)

This post is a part of the "SC-500: Implementing End-to-End Security Controls for Cloud and AI Workloads" Exam Prep Hub.
This topic falls under these sections:
Secure storage, databases, and networking (25–30%)
   --> Implement security for databases
      --> Configure database auditing for Azure SQL Database and Azure SQL Managed Instance

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.

Overview

Database auditing records database activity so that organizations can investigate security incidents, identify unauthorized access, support compliance requirements, and understand how data is being used.

For the SC-500 exam, database auditing primarily involves configuring and managing auditing for:

Azure SQL Database
Azure SQL Managed Instance
SQL databases hosted in Azure
Microsoft Entra authentication and database activity
Audit destinations and retention
Audit logs and monitoring

Auditing is different from authentication and authorization:

Authentication determines who or what is connecting.
Authorization determines what the principal is allowed to do.
Auditing records what happened, who performed the action, when it occurred, and other relevant details.

A user might be correctly authenticated and authorized to read a table, but auditing can record that the user actually performed the read operation.

Why Database Auditing Is Important

Database auditing supports several security and governance objectives.

Detecting suspicious activity

Audit records can help identify:

Repeated failed login attempts
Access to sensitive tables
Unexpected changes to database objects
Changes to permissions or roles
Unusual administrative activity
Attempts to access data outside normal business patterns

Supporting compliance

Many regulatory and organizational standards require organizations to maintain evidence of access to sensitive data. Audit logs can help demonstrate:

Who accessed data
Which operations were performed
When the operations occurred
Whether privileged users changed security settings
Whether sensitive data was accessed or modified

Investigating security incidents

When an incident occurs, audit logs can help security teams reconstruct events and determine:

Which account was used
Which database was accessed
Which commands were executed
Whether data was read, changed, or deleted
Whether permissions were modified
The approximate time sequence of activity

Establishing accountability

Auditing helps associate database activity with a user, application, service principal, or managed identity. This is especially important when multiple applications or administrators access the same database.

Azure SQL Auditing

Azure SQL auditing tracks database events and writes audit records to a configured destination.

Auditing can be configured at different scopes, depending on the service:

Azure SQL logical server
Individual Azure SQL Database
Azure SQL Managed Instance
SQL databases hosted by the managed instance

The exact configuration experience and available settings can vary between Azure SQL Database and Azure SQL Managed Instance.

For Azure SQL Database, auditing can generally be configured at the server or database level. A database-level configuration can provide more specific control for an individual database.

For Azure SQL Managed Instance, auditing is configured for the managed instance and can capture activity across the databases hosted by that instance.

Azure SQL Database Auditing

Azure SQL Database auditing records database events for databases hosted on an Azure SQL logical server.

Auditing can be enabled through the Azure portal, Azure PowerShell, Azure CLI, REST APIs, or infrastructure-as-code tools.

At a high level, configuring auditing involves:

Selecting the SQL server or database.
Opening the auditing configuration.
Enabling auditing.
Selecting an audit destination.
Configuring retention and related settings.
Saving the configuration.
Reviewing the generated audit records.

Auditing can be configured at the server level so that databases inherit the server’s auditing configuration. A database-level configuration can be used when a particular database requires different auditing behavior.

Azure SQL Managed Instance Auditing

Azure SQL Managed Instance provides auditing for database activity across the managed instance.

Because a managed instance can host multiple databases, auditing at the managed-instance level is useful when an organization wants consistent auditing across its database environment.

Auditing can help record activity such as:

Database connections
Queries and stored procedure execution
Data access
Data changes
Permission changes
Schema changes
Security-related operations

The audit configuration should be reviewed carefully to ensure that the selected events meet the organization’s security and compliance requirements without generating unnecessary volumes of data.

Audit Destinations

Azure SQL auditing supports several destinations. The appropriate destination depends on the organization’s retention, analysis, and monitoring requirements.

Azure Storage

Audit logs can be written to an Azure Storage account.

Azure Storage is useful when an organization needs:

Long-term retention
Centralized storage
Low-cost archival
Integration with other data-processing tools
Storage-based compliance evidence

When using Azure Storage, consider:

Storage account security
Access control
Network restrictions
Encryption
Retention policies
Immutability requirements
Lifecycle management

Audit logs should not be stored in a location where unauthorized users can modify or delete them.

For stronger protection, organizations can use storage security features such as restricted access, role-based access control, and immutable storage where appropriate.

Log Analytics Workspace

Audit logs can be sent to a Log Analytics workspace.

This destination is useful when security teams need to:

Query audit records
Correlate database events with other Azure activity
Build dashboards
Create alerts
Investigate incidents
Use Microsoft Sentinel for security monitoring

Log Analytics is often the most useful destination for operational security monitoring because audit data can be queried using Kusto Query Language.

For example, security teams might use audit data to investigate:

Access to sensitive databases
Changes to database permissions
Unusual administrative activity
Repeated failed connections
Unexpected data modification

Event Hubs

Audit logs can also be sent to Azure Event Hubs.

Event Hubs is useful when audit data must be streamed to another system, such as:

A security information and event management platform
A security analytics platform
A custom monitoring application
A third-party compliance or monitoring solution

Event Hubs is designed for high-throughput event ingestion and streaming rather than long-term log storage by itself.

Choosing a Destination

Requirement	Suitable destination
Long-term archival	Azure Storage
Interactive investigation and queries	Log Analytics workspace
Streaming audit data to another system	Event Hubs
Security analytics and alerting	Log Analytics and Microsoft Sentinel
Compliance retention	Azure Storage, often with additional retention controls

An organization may use more than one destination when it needs both operational monitoring and long-term retention.

Types of Activity That Can Be Audited

The exact audit events available depend on the Azure SQL service and configuration, but auditing can capture several important categories of activity.

Authentication and connection activity

Examples include:

Successful database connections
Failed connection attempts
Authentication-related events
Connection information

These events can help identify brute-force attempts, misconfigured applications, or unexpected access.

Data access

Examples include:

Reading data
Selecting data from sensitive tables
Executing stored procedures
Accessing specific database objects

Data-access auditing is particularly important for databases containing:

Personally identifiable information
Financial information
Healthcare information
Customer records
Confidential business data

Data changes

Examples include:

Insert operations
Update operations
Delete operations
Bulk data changes

Auditing data changes can help determine whether records were modified or removed.

Schema changes

Examples include:

Creating tables
Altering tables
Dropping tables
Creating or modifying stored procedures
Changing database objects

Schema auditing is useful because unauthorized schema changes can create security vulnerabilities or affect application behavior.

Permission and role changes

Examples include:

Granting permissions
Revoking permissions
Adding users to database roles
Removing users from database roles
Changing ownership or security-related settings

These events are important for detecting privilege escalation.

Administrative activity

Examples include:

Changes to auditing configuration
Changes to database settings
Changes to security configuration
Administrative commands

Administrative auditing helps establish accountability for privileged operations.

Auditing Versus Microsoft Defender for SQL

Azure SQL auditing and Microsoft Defender for SQL serve related but different purposes.

Azure SQL auditing

Auditing primarily records database activity for:

Investigation
Compliance
Accountability
Historical analysis
Security monitoring

It answers questions such as:

What activity occurred in the database?

Microsoft Defender for SQL

Microsoft Defender for SQL provides additional security capabilities, such as:

Threat detection
Security alerts
Vulnerability assessment
Security recommendations
Identification of suspicious database activity

It answers questions such as:

Does this activity appear suspicious or represent a security risk?

Auditing and Defender for SQL can be used together. Auditing provides detailed activity records, while Defender for SQL can identify and alert on potentially malicious behavior.

Auditing and Microsoft Sentinel

Audit logs can be integrated with Microsoft Sentinel to support centralized security monitoring.

A typical workflow is:

Enable auditing on Azure SQL Database or Azure SQL Managed Instance.
Send audit logs to a Log Analytics workspace.
Connect the workspace to Microsoft Sentinel.
Create queries and analytics rules.
Configure alerts and incidents.
Investigate related activity across Azure and other environments.

For example, Microsoft Sentinel could correlate:

A suspicious Microsoft Entra sign-in
A database permission change
Access to a sensitive table
Activity from an unusual IP address
A subsequent data export

This correlation provides more context than reviewing database logs alone.

Retention and Log Management

Audit logs should be retained according to:

Regulatory requirements
Organizational policies
Incident-response requirements
Legal and contractual obligations
Storage costs
Data sensitivity

Retention should be long enough to support investigations and compliance audits.

Important considerations include:

How long logs are retained
Whether logs can be deleted by ordinary administrators
Whether logs are protected from modification
Whether access to logs is itself audited
Whether archived logs can be searched or restored
Whether retention policies apply consistently across databases

Audit logs may contain sensitive information, so they should be protected using appropriate access controls and encryption.

Securing Audit Logs

Audit logs are security evidence and should be protected as carefully as the database itself.

Restrict access

Only authorized personnel should be able to read, export, or delete audit logs.

Use least-privilege access through Microsoft Entra ID and Azure RBAC where supported.

Protect against deletion or modification

Consider:

Storage immutability
Resource locks where appropriate
Restricted administrative access
Separate security or compliance ownership
Monitoring of changes to audit configuration

A log that can be easily deleted by the person being investigated provides limited forensic value.

Encrypt audit data

Audit data should be protected using encryption at rest and secure transport.

Azure services generally provide encryption at rest, but organizations must still configure access and key-management controls appropriately.

Monitor auditing configuration

Security teams should monitor changes to:

Whether auditing is enabled
Audit destinations
Retention settings
Audit policies
Database-level overrides
Permissions to audit destinations

An attacker who disables auditing may be attempting to conceal activity.

Configuring Auditing in the Azure Portal

The following is a conceptual configuration process. The exact portal labels may vary as Azure services evolve.

Azure SQL Database

Open the Azure portal.
Navigate to the Azure SQL logical server or database.
Select Auditing under the security-related settings.
Enable auditing.
Choose one or more supported destinations.
Configure the destination details.
Configure retention or related settings.
Save the configuration.
Generate or perform test activity.
Verify that audit records are being delivered.

When configuring auditing at the server level, review whether individual databases inherit the configuration or override it.

Azure SQL Managed Instance

Open the Azure portal.
Navigate to the managed instance.
Select the auditing configuration.
Enable auditing.
Select the destination.
Configure retention and related settings.
Save the configuration.
Verify that activity from the managed instance’s databases is being recorded.

Common Exam Considerations

Server-level versus database-level configuration

A server-level auditing configuration can provide centralized coverage, while a database-level configuration can provide more specific control.

When troubleshooting, determine whether:

Auditing is enabled at the server level
The database has its own auditing configuration
A database-level setting overrides the inherited configuration
The selected destination is correctly configured

Auditing does not grant access

Enabling auditing does not allow a user to connect to a database or read data.

Authentication and authorization must still be configured separately.

Auditing does not block activity

Auditing records activity. It does not, by itself, prevent a user from executing a query or changing data.

To prevent activity, use controls such as:

Microsoft Entra authentication
Azure RBAC
Database roles and permissions
Network access controls
Microsoft Defender for SQL
Azure Policy
Microsoft Purview or other data-governance controls

Auditing is not the same as diagnostic logging

Diagnostic settings are used to route platform logs and metrics to destinations such as Log Analytics, Storage, or Event Hubs.

Azure SQL auditing is a database-specific auditing capability. Diagnostic settings may be involved in routing or collecting related logs, but they do not replace the need to configure database auditing appropriately.

Do not collect more data than necessary

Auditing should be designed to meet security and compliance objectives while controlling:

Storage costs
Query volume
Log noise
Sensitive information exposure
Operational overhead

A useful audit policy focuses on meaningful events and protects the resulting records.

Best Practices

Enable auditing for production databases.
Use Log Analytics when interactive investigation and alerting are required.
Use Azure Storage for long-term retention and archival.
Send relevant audit data to Microsoft Sentinel for centralized security monitoring.
Protect audit destinations with least-privilege access.
Use retention policies that meet regulatory and organizational requirements.
Protect logs against unauthorized deletion or modification.
Monitor changes to auditing configuration.
Review audit records regularly.
Correlate audit activity with identity, network, and application logs.
Use Microsoft Defender for SQL for threat detection in addition to auditing.
Test auditing after configuration changes.
Document which events are audited and why.
Avoid relying on auditing as a substitute for authorization.
Ensure that audit logs themselves are treated as sensitive data.

Practice Exam Questions

Question 1

An organization needs to record activity performed against an Azure SQL Database so that security analysts can investigate suspicious queries and create alerts. Which destination is the most appropriate?

A. Azure Key Vault
B. Azure Storage only
C. Log Analytics workspace
D. Azure Resource Graph

Correct answer: C

Explanation: A Log Analytics workspace is designed for querying and analyzing log data. It can also be used with Microsoft Sentinel to create alerts and investigate security incidents. Azure Storage is better suited to archival and long-term retention.

Question 2

A company must retain Azure SQL audit records for several years at a relatively low cost. The records must also be protected from unauthorized modification. Which approach is most appropriate?

A. Store the records only in the SQL database being audited
B. Send the records to Azure Storage and configure appropriate retention and immutability controls
C. Send the records only to Azure Event Hubs without any downstream storage
D. Disable auditing after exporting the records once per year

Correct answer: B

Explanation: Azure Storage is appropriate for long-term retention. Additional controls, such as retention policies and immutable storage, can help protect audit records from deletion or modification.

Question 3

Which statement best describes the purpose of Azure SQL auditing?

A. It records database activity for investigation, accountability, and compliance
B. It automatically grants users permission to access database objects
C. It replaces Microsoft Entra authentication
D. It prevents all unauthorized queries from executing

Correct answer: A

Explanation: Auditing records activity that occurs in the database. It does not grant permissions, replace authentication, or automatically block queries.

Question 4

An administrator enables auditing for an Azure SQL Database but users still cannot connect to the database. What is the most likely explanation?

A. Auditing can only be enabled after all users are assigned the Owner role
B. Auditing automatically blocks connections until Microsoft Sentinel is configured
C. Auditing records activity but does not provide authentication or authorization
D. Auditing requires Azure Storage to be configured before any user can connect

Correct answer: C

Explanation: Authentication and authorization are separate from auditing. A user must still have a valid authentication method and sufficient database permissions.

Question 5

A security team wants to correlate Azure SQL activity with Microsoft Entra sign-ins, virtual machine alerts, and other cloud security events. Which solution is most appropriate?

A. Azure Files
B. Microsoft Sentinel connected to a Log Analytics workspace
C. Azure DNS
D. Azure Resource Manager locks only

Correct answer: B

Explanation: Microsoft Sentinel can use Log Analytics data to correlate database audit events with identity, infrastructure, and other security events.

Question 6

An organization wants to investigate whether a privileged administrator changed database permissions. Which type of audit activity is most relevant?

A. Permission and role changes
B. Storage account replication events
C. Virtual network route changes only
D. Azure billing events only

Correct answer: A

Explanation: Permission and role changes can reveal privilege escalation or unauthorized changes to database access.

Question 7

A company configures auditing at the Azure SQL logical server level. One database has different auditing requirements and must use a separate configuration. What should the administrator investigate?

A. Whether the database can override or use a database-level auditing configuration
B. Whether auditing can only be configured at the subscription level
C. Whether the database must be moved to Azure Cosmos DB
D. Whether auditing requires a dedicated virtual machine

Correct answer: A

Explanation: Azure SQL Database auditing can be configured at the server or database level. The administrator should determine whether the database-level configuration provides the required override or separate behavior.

Question 8

Which statement correctly compares Azure SQL auditing and Microsoft Defender for SQL?

A. Auditing blocks threats, while Defender for SQL only stores logs
B. Auditing and Defender for SQL are identical features
C. Auditing records database activity, while Defender for SQL provides additional threat detection and security recommendations
D. Defender for SQL is required before auditing can be enabled

Correct answer: C

Explanation: Auditing provides activity records for investigation and compliance. Microsoft Defender for SQL adds security capabilities such as threat detection, alerts, and vulnerability-related recommendations.

Question 9

An organization sends Azure SQL audit records to Event Hubs. What is the primary reason for selecting Event Hubs?

A. To stream audit events to another monitoring or security system
B. To replace database authentication
C. To provide database table-level permissions
D. To encrypt database columns automatically

Correct answer: A

Explanation: Event Hubs is designed for high-throughput event ingestion and streaming. It can forward audit events to downstream monitoring or security systems.

Question 10

A security team notices that audit records are missing after an administrator changed the auditing configuration. Which action should be performed first?

A. Delete the database and recreate it
B. Disable Microsoft Entra authentication
C. Confirm that auditing is still enabled and verify the configured destination and delivery settings
D. Assign the Security Reader role to every database user

Correct answer: C

Explanation: The first troubleshooting step is to verify the auditing configuration, including whether auditing remains enabled and whether the destination is correctly configured. The team should also verify that the destination is receiving records and that no configuration change disabled or redirected auditing.

Final Exam Point

The key exam distinction is that auditing records database activity, while authentication, authorization, network controls, and threat-detection services determine whether activity should be allowed or considered suspicious.

Go to the SC-500 Exam Prep Hub main page

Data Governance, Data Security, Database Administration, Databases, DP-800, Microsoft Certification, SQL August 1, 2026

Implement auditing – Part 3 (DP-800 Exam Prep)

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.

Feature	Purpose	Protects Data?	Records Activity?
SQL Server Audit	Records security events	No	Yes
Dynamic Data Masking	Obscures sensitive data	Yes	No
Row-Level Security	Restricts row access	Yes	No
Always Encrypted	Encrypts sensitive columns	Yes	No
Transparent Data Encryption (TDE)	Encrypts database files	Yes	No
SQL Server Permissions	Controls access	Yes	No
Microsoft Defender for SQL	Detects suspicious activity	Indirectly	Partially

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.

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

Requirement	SQL Auditing Helps?
Determine who accessed sensitive data	Yes
Record failed logins	Yes
Detect unauthorized permission changes	Yes
Track schema modifications	Yes
Recover deleted data	No
Encrypt stored data	No
Prevent unauthorized access	No (permissions control access)

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:

Topic	Key Point
SQL Server Audit	Defines where audit data is stored
Server Audit Specification	Audits server-level events
Database Audit Specification	Audits database-level events
Azure Storage	Long-term audit storage
Log Analytics	Search and analyze audit events
Event Hubs	Stream audit events
Azure Monitor	Alerting and dashboards
Microsoft Sentinel	SIEM and threat investigation
Defender for SQL	Threat detection
`sys.fn_get_audit_file()`	Reads SQL Server audit files

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

azure, Data Governance, Data Security, DP-800, Microsoft Certification, SQL August 1, 2026

Implement auditing – Part 2 (DP-800 Exam Prep)

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:

Select the SQL Server or database.
Open Auditing under the Security section.
Enable auditing.
Choose one or more destinations.
Configure retention settings.
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:

Regulation	Typical Retention
PCI DSS	At least one year
HIPAA	Several years (organization-specific)
SOX	Often seven years
Internal security policies	Varies

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:

Repeated failed logins
Successful privileged login
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.

Auditing	Defender for SQL
Records activity	Detects threats
Supports compliance	Uses behavioral analytics
Captures events	Generates security alerts
Used during investigations	Identifies suspicious behavior

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.

Feature	Purpose
Auditing	Records activity
Dynamic Data Masking	Hides data
Row-Level Security	Filters rows
Always Encrypted	Encrypts data
Transparent Data Encryption	Encrypts database files
Microsoft Defender for SQL	Detects threats

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

Data Governance, Data Security, Databases, DP-800, Microsoft Certification August 1, 2026

Implement auditing – Part 1 (DP-800 Exam Prep)

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

Auditing is a critical security and compliance capability in Microsoft SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Microsoft Fabric SQL databases. An audit records database and server activities so administrators can determine who performed an action, when it occurred, what object was affected, and whether the action succeeded or failed.

Auditing plays an important role in:

Security monitoring
Regulatory compliance
Incident investigations
Forensics
Insider threat detection
Change tracking
Governance

For the DP-800 exam, you should understand:

SQL Server Audit architecture
Server and database audit specifications
Audit targets
Audited action groups
Creating and managing audits
Azure SQL auditing
Performance considerations
Best practices

Why Database Auditing Matters

Unlike backups or transaction logs, auditing focuses on security events rather than data recovery.

Auditing helps answer questions such as:

Who deleted a customer record?
Who changed employee salaries?
Who attempted unauthorized access?
Which administrator modified security settings?
When was sensitive information viewed?
Which login repeatedly failed?

Organizations frequently require auditing for compliance standards including:

HIPAA
PCI DSS
SOX
GDPR
ISO 27001
FedRAMP

SQL Server Audit Architecture

SQL Server auditing is built using three major components.

			
SQL Server Audit
        │
        ▼
Audit Target
(File, Windows Security Log,
Windows Application Log)
        │
        ▼
Audit Specification
(Server or Database)
        │
        ▼
Audited Actions

		

The architecture is intentionally modular.

Component 1 — SQL Server Audit

The Audit object defines:

Where audit information is written
How failures are handled
File size
Retention behavior
Queue delay
Whether auditing is enabled

Think of the Audit object as the destination.

Example:

			
CREATE SERVER AUDIT SecurityAudit
TO FILE
(
    FILEPATH = 'D:\AuditLogs\'
);
GO
ALTER SERVER AUDIT SecurityAudit
WITH (STATE = ON);

		

The audit itself records nothing until specifications are attached.

Component 2 — Audit Specifications

Audit specifications determine what activities should be captured.

Two specification types exist.

Server Audit Specification

Captures server-level events.

Examples include:

Login creation
Login failures
ALTER LOGIN
Server role changes
Backup operations
Database creation
Database deletion

Example:

			
CREATE SERVER AUDIT SPECIFICATION ServerAuditSpec
FOR SERVER AUDIT SecurityAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP);
ALTER SERVER AUDIT SPECIFICATION ServerAuditSpec
WITH (STATE = ON);

		

Database Audit Specification

Captures activity inside a database.

Examples:

SELECT
INSERT
UPDATE
DELETE
EXECUTE
Permission changes
Schema changes

Example:

			
USE SalesDB;
CREATE DATABASE AUDIT SPECIFICATION DatabaseAuditSpec
FOR SERVER AUDIT SecurityAudit
ADD (SELECT ON dbo.Customers BY PUBLIC),
ADD (UPDATE ON dbo.Customers BY PUBLIC);
ALTER DATABASE AUDIT SPECIFICATION DatabaseAuditSpec
WITH (STATE =ON);

		

Relationship Between Audit Objects

			
SQL Server Audit
      │
      ├──────────────┐
      │              │
      ▼              ▼
Server Audit     Database Audit
Specification    Specification
      │              │
      ▼              ▼
 Audited Actions  Database Actions
      │
      ▼
 Audit Log

		

One audit may support multiple specifications.

Audit Targets

The audit target specifies where audit events are stored.

SQL Server supports three primary targets.

1. File Target

Most common.

Advantages:

High performance
Large storage capacity
Easy backup
Easy archive
Supports filtering
Recommended by Microsoft

Example

			
TO FILE
(
FILEPATH='D:\AuditLogs\'
)

2. Windows Security Log

Suitable when:

Centralized Windows auditing exists
Security teams monitor Security logs
Compliance requires OS-level auditing

Advantages

Tamper resistant
Centrally managed

Requires elevated permissions.

3. Windows Application Log

Less secure than the Security Log.

Typically used when:

Security Log permissions are unavailable
Simpler deployments
Testing environments

Audit Actions

SQL Server audits individual actions or groups of actions.

Examples include:

SELECT
INSERT
UPDATE
DELETE
EXECUTE
CREATE TABLE
ALTER TABLE
DROP TABLE
LOGIN
LOGOUT

Audit Action Groups

Rather than auditing individual commands, SQL Server commonly audits predefined action groups.

Examples include:

Action Group	Description
FAILED_LOGIN_GROUP	Failed logins
SUCCESSFUL_LOGIN_GROUP	Successful logins
DATABASE_OBJECT_CHANGE_GROUP	Table and view changes
DATABASE_PERMISSION_CHANGE_GROUP	Permission modifications
SERVER_ROLE_MEMBER_CHANGE_GROUP	Changes to server roles
SCHEMA_OBJECT_CHANGE_GROUP	CREATE/ALTER/DROP objects
DATABASE_ROLE_MEMBER_CHANGE_GROUP	Changes to database roles
BACKUP_RESTORE_GROUP	Backup and restore events
SERVER_OBJECT_CHANGE_GROUP	Server object modifications

These predefined groups simplify auditing and reduce administrative effort.

Creating a Basic Audit

Step 1

Create the audit.

			
CREATE SERVER AUDIT MyAudit
TO FILE
(
FILEPATH='D:\AuditLogs\'
);

		

Step 2

Enable the audit.

			
ALTER SERVER AUDIT MyAudit
WITH (STATE=ON);

Step 3

Create a database audit specification.

			
USE SalesDB;
CREATE DATABASE AUDIT SPECIFICATION SalesAudit
FOR SERVER AUDIT MyAudit
ADD
(
SELECT ON dbo.Customers BY PUBLIC
);

		

Step 4

Enable the specification.

			
ALTER DATABASE AUDIT SPECIFICATION SalesAudit
WITH (STATE=ON);

Now every SELECT against Customers is captured.

Viewing Audit Logs

Audit files can be queried using the built-in table-valued function:

			
SELECT *
FROM sys.fn_get_audit_file
(
'D:\AuditLogs\*',
DEFAULT,
DEFAULT
);

		

Returned information includes:

Event time
Login name
Database name
Server name
Object name
Statement executed
Action ID
Session ID
Success or failure

This function is commonly used for reporting and investigations.

Managing Audit State

Audits can be enabled or disabled without deleting them.

Disable:

			
ALTER SERVER AUDIT SecurityAudit
WITH (STATE = OFF);

Enable:

			
ALTER SERVER AUDIT SecurityAudit
WITH (STATE = ON);

Similarly, individual audit specifications can be enabled or disabled independently of the audit object.

Catalog Views for Auditing

Several system catalog views help administrators monitor audit configuration.

View	Purpose
`sys.server_audits`	Lists configured server audits
`sys.server_audit_specifications`	Lists server audit specifications
`sys.database_audit_specifications`	Lists database audit specifications
`sys.server_audit_specification_details`	Displays server audit actions
`sys.database_audit_specification_details`	Displays database audit actions
`sys.dm_server_audit_status`	Shows audit runtime status

Example:

			
SELECT *
FROM sys.server_audits;

Audit Failure Behavior

SQL Server allows administrators to specify what happens if an audit target becomes unavailable.

Options include:

Continue

Database operations continue even if auditing fails.

Suitable for:

Development environments
Non-critical systems

Fail Operation

Only the audited operation fails.

Example:

A user attempts to update a table.
The audit cannot write to disk.
The UPDATE is rejected.

This option helps ensure sensitive operations are never performed without being audited.

Shut Down Server

The SQL Server instance shuts down if auditing fails.

This provides the highest level of security but can impact availability. It is generally reserved for environments with strict regulatory requirements.

Best Practices

Microsoft recommends the following auditing practices:

Audit only important security events to reduce overhead.
Prefer file targets for performance and scalability.
Protect audit files with appropriate NTFS permissions.
Archive audit logs regularly.
Monitor available disk space to prevent audit interruptions.
Test audit configurations before deploying to production.
Use separate storage volumes for audit files when possible.
Review audit logs regularly rather than collecting them without analysis.
Combine auditing with least-privilege security and Microsoft Defender for SQL for comprehensive protection.
Document audit policies to satisfy compliance requirements and facilitate incident response.

DP-800 Exam Tips

Understand the distinction between a SQL Server Audit (defines the destination) and an Audit Specification (defines what is captured).
Know when to use Server Audit Specifications versus Database Audit Specifications.
Be familiar with common audit action groups, especially login, permission, object change, and backup-related groups.
Remember that sys.fn_get_audit_file is the primary method for reading audit files.
Recognize that file targets are generally Microsoft’s recommended choice for production deployments because they offer the best balance of performance, scalability, and manageability.
Be able to identify scenarios where auditing supports regulatory compliance, forensic investigations, and security monitoring.

Go to the DP-800 Exam Prep Hub main page