Data Governance, Data Security, Databases, DP-800, Microsoft Certification

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 GroupDescription
FAILED_LOGIN_GROUPFailed logins
SUCCESSFUL_LOGIN_GROUPSuccessful logins
DATABASE_OBJECT_CHANGE_GROUPTable and view changes
DATABASE_PERMISSION_CHANGE_GROUPPermission modifications
SERVER_ROLE_MEMBER_CHANGE_GROUPChanges to server roles
SCHEMA_OBJECT_CHANGE_GROUPCREATE/ALTER/DROP objects
DATABASE_ROLE_MEMBER_CHANGE_GROUPChanges to database roles
BACKUP_RESTORE_GROUPBackup and restore events
SERVER_OBJECT_CHANGE_GROUPServer 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.

ViewPurpose
sys.server_auditsLists configured server audits
sys.server_audit_specificationsLists server audit specifications
sys.database_audit_specificationsLists database audit specifications
sys.server_audit_specification_detailsDisplays server audit actions
sys.database_audit_specification_detailsDisplays database audit actions
sys.dm_server_audit_statusShows 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

Leave a comment