Study guide

7.1 Data Classification and Server/Database Auditing

DP-300: Administering Microsoft Azure SQL Solutions Exam

Track your progress as you read

Finished sections get a check mark.

1 Introduction & Exam Overview

1.1 Current DP-300 Exam Facts 1.2 Skills Outline and Domain Weights 1.3 Study Strategy and Official Resources

2 Planning and Deploying Azure SQL Solutions

2.1 Choosing and Deploying Azure SQL Database Offerings 2.2 Automated Deployment, Azure Arc, and SQL Database in Microsoft Fabric 2.3 Table Partitioning and Sharding Strategy 2.4 Hybrid SQL Server and IaaS Patching

3 Configuring Resources for Scale and Performance

3.1 Configuring Azure SQL Database for Scale and Performance 3.2 Configuring Azure SQL Managed Instance for Scale and Performance 3.3 Configuring SQL Server on Azure VMs for Scale and Performance 3.4 Configuring Table Partitioning and Data Compression

4 Planning and Implementing a Migration Strategy

4.1 Evaluating Migration Requirements and Strategies 4.2 Implementing Online, Offline, and Azure Migrations 4.3 Migrating Between Azure SQL Services and Troubleshooting

5 Configuring Database Authentication and Authorization

5.1 Microsoft Entra ID Authentication for Azure SQL 5.2 Authentication for SQL on Azure VMs and Managed Instance; Troubleshooting 5.3 Security Principals and Entra-Backed Users 5.4 Permissions, Least Privilege, and T-SQL Management

6 Implementing Security for Data at Rest and in Transit

6.1 Transparent Data Encryption and Object-Level Encryption 6.2 Always Encrypted and VBS Enclaves 6.3 Firewall Rules, Private Links, and Service Endpoints

7 Implementing Compliance Controls for Sensitive Data

7.1 Data Classification and Server/Database Auditing 7.2 Change Data Tracking and Dynamic Data Masking 7.3 Row-Level Security and Ledger in Azure SQL

8 Monitoring Resource Activity and Performance

8.1 Operational Baselines and Performance Metric Sources 8.2 Interpreting Metrics and Configuring Activity Monitoring 8.3 Monitoring with Database Watcher (Preview) 8.4 Monitoring with Extended Events

9 Monitoring and Optimizing Query Performance

9.1 Configuring and Monitoring with Query Store 9.2 Session Blocking and Dynamic Management Views 9.3 Index Changes and Execution Plan Review 9.4 Query Construct Modifications and Intelligent Insights

10 Configuring Database Solutions for Optimal Performance

10.1 Index, Statistics, and Integrity Maintenance 10.2 Automatic Tuning and Database-Scoped Configuration 10.3 Server Settings and Resource Governor 10.4 Compute/Storage Scaling and Intelligent Query Processing

11 Creating and Managing SQL Server Agent Jobs

11.1 Schedules for Regular Maintenance Jobs 11.2 Job Alerts, Notifications, and Agent Troubleshooting 11.3 Automating Deployment with ARM and Bicep Templates

12 Automating Deployments and Database Tasks in Azure

12.1 Azure PowerShell, Azure CLI, and Deployment Monitoring 12.2 Elastic Jobs for Cross-Database Automation 12.3 Azure Automation Runbooks, Alerts, and Troubleshooting Automated Tasks

13 Planning an HA/DR Strategy and Backup and Restore

13.1 Recommending HA/DR Strategy from RPO and RTO 13.2 Hybrid and Azure-Specific HA/DR Solutions and Testing 13.3 Backup and Restore Strategy with Native Tools 13.4 Point-in-Time Restore and Recovery Operations

14 Configuring HA/DR: Long-Term Retention, Geo-Replication, and Failover Groups

14.1 Long-Term Retention, T-SQL Backup/Restore, and Cloud Storage 14.2 Active Geo-Replication 14.3 Failover Groups

15 Advanced HA/DR: Availability Groups, FCIs, Log Shipping, and Troubleshooting

15.1 Always On Availability Groups on SQL Managed Instance and Azure VMs 15.2 Always On Failover Cluster Instances and Log Shipping 15.3 Monitoring and Troubleshooting HA/DR Solutions

7.1 Data Classification and Server/Database Auditing

Key Takeaways Explain This Topic AI

SQL Data Discovery & Classification tags each column with an Information Type and a Sensitivity Label, plus an optional Sensitivity Rank (NONE/LOW/MEDIUM/HIGH/CRITICAL); metadata lives in sys.sensitivity_classifications and is read by Defender for SQL and Purview

ADD SENSITIVITY CLASSIFICATION ... WITH (LABEL = '...', INFORMATION_TYPE = '...', RANK = HIGH) is the T-SQL path; classifications can also be added in SSMS (Classification panel) or the Azure portal (SQL Database classification blade)

A server audit defines the destination; a server audit specification captures server-scoped action groups (logins, role changes), while a database audit specification captures database-scoped groups and individual actions (SELECT/INSERT/UPDATE/DELETE on objects)

Azure SQL Database and SQL MI audit destinations are Azure Storage (with retention), Log Analytics workspace (KQL queries), and Event Hub (stream to SIEM); SQL Server on VMs uses the engine's native SQL Server Audit to file or Windows logs

Microsoft Defender for SQL bundles Vulnerability Assessment, Data Discovery & Classification, and Advanced Threat Protection; the Vulnerability Assessment establishes a baseline of checked rules and surfaces remediation steps

Last updated: August 2026

Why Classification and Auditing Matter for DP-300

Compliance domains map almost one-to-one to DP-300's "Implement a secure environment" skill area. A scenario will describe a regulator (HIPAA, PCI DSS, SOX, GDPR) asking two questions: what sensitive data do you store? and who touched it and when? Classification answers the first; auditing answers the second. Microsoft Defender for SQL stitches them together and adds vulnerability assessment so you can prove the database itself is configured to a known-good baseline. Expect scenario questions that ask which blade, which T-SQL statement, or which destination a given control uses, and traps that conflate server-scoped and database-scoped audit actions.

SQL Data Discovery & Classification

SQL Data Discovery & Classification is built into SSMS, the Azure portal (for Azure SQL Database and Managed Instance), and surfaced by Microsoft Defender for Cloud / Microsoft Purview. It tags each column with two metadata dimensions plus an optional rank:

Dimension Purpose Examples

Information Type What kind of data the column holds National ID, Credit Card, Banking, Credentials, Health, Contact Info, Financial

  • Sensitivity Label Organizational classification policy Public, Internal, Confidential, Highly Confidential
  • Sensitivity Rank (optional) Weight within a label for prioritization NONE, LOW, MEDIUM, HIGH, CRITICAL

Labels and information types come from a default set you can extend, and the classification engine auto-discovers candidate columns using pattern matching, then recommends labels you can accept or override. The recommendations are persisted in the database as classifications surfaced through sys.sensitivity_classifications , and downstream services (Defender for SQL, Purview, advanced threat protection) read them to prioritize alerts.

Adding Classifications

Three entry points the exam tests:

SSMS : Object Explorer -> database -> Tasks -> Classify Data. The wizard recommends classifications, lets you accept/edit them, and generates a T-SQL script you can review and run.

Azure portal : Azure SQL Database or Managed Instance blade -> Security -> Data Discovery & Classification (also reached through Microsoft Defender for Cloud's regulatory dashboards).

  • T-SQL for repeatable/templated deployments: ADD SENSITIVITY CLASSIFICATION TO dbo.Customers.NationalID
  • WITH (LABEL = 'Confidential', INFORMATION_TYPE = 'National ID', RANK = HIGH);

DROP SENSITIVITY CLASSIFICATION FROM dbo.Customers.NationalID;

Valid RANK values are NONE , LOW , MEDIUM , HIGH , CRITICAL . Existing classifications are read with: SELECT schema_name, table_name, column_name, label, label_id,

  • information_type, information_type_id, rank, rank_desc
  • FROM sys.sensitivity_classifications;

A common trap: classification does not encrypt, mask, or restrict access by itself. It is metadata. Controls that act on it (alerting, masking suggestions, Purview lineage) are separate decisions. A scenario that says "classify then data is automatically protected" is wrong - classification feeds policy and reporting, not enforcement.

Server Audit vs Database Audit Specification

The SQL Server Audit architecture (used by SQL Server on VMs, SQL Managed Instance, and as the engine-level audit inside Azure SQL Database) splits auditing into three objects:

Server Audit - defines where audit output goes and the queue behavior. Created once per audit. In the on-prem/VM engine you can target a file, the Windows Application log, or the Windows Security log. In Azure SQL Database/MI this is the managed audit that ships to Storage, Log Analytics, or Event Hub.

Server Audit Specification - captures server-scoped events. It can use only audit action groups (no individual actions). Examples: SUCCESSFUL_LOGIN_GROUP , FAILED_LOGIN_GROUP , SERVER_ROLE_MEMBER_CHANGE_GROUP , BACKUP_RESTORE_GROUP , SERVER_PERMISSION_CHANGE_GROUP .

Database Audit Specification - captures database-scoped events. It supports both audit action groups and individual audit action classes scoped to specific objects, schemas, or the whole database. Examples: SELECT , INSERT , UPDATE , DELETE on dbo.Sales or on SCHEMA::Sales ; group SCHEMA_OBJECT_ACCESS_GROUP ; group DATABASE_OBJECT_PERMISSION_CHANGE_GROUP .

A key exam trap is the asymmetry: server audit specifications can only use action groups; database audit specifications can also list individual actions against objects. When a scenario asks "audit every SELECT against dbo.Patients by the NurseRole role," the answer is a database audit specification with SELECT ON OBJECT::dbo.Patients BY NurseRole , not a server audit specification (which cannot target objects).

  • On-prem SQL Server audit T-SQL mirrors the engine feature: USE master;
  • CREATE SERVER AUDIT Audit_PatientData TO FILE (FILEPATH = 'C:\Audit\');
  • ALTER SERVER AUDIT Audit_PatientData WITH (STATE = ON);
  • CREATE SERVER AUDIT SPECIFICATION ServerAudit_Spec
  • FOR SERVER AUDIT Audit_PatientData
  • ADD (SUCCESSFUL_LOGIN_GROUP);
  • USE Clinic;
  • CREATE DATABASE AUDIT SPECIFICATION DbAudit_PatientSelect
  • FOR SERVER AUDIT Audit_PatientData
  • ADD (SELECT ON OBJECT::dbo.Patients BY public)
  • WITH (STATE = ON);
  • Audit Destinations and Retention

For Azure SQL Database and SQL Managed Instance , the audit logs stream to one or more destinations, configured in the portal (Diagnostic settings), CLI, PowerShell, or REST:

Destination Best for Retention

Azure Storage account Long-term archival, low cost Container-level retention policy; default 0 (indefinite) or days you set

  • Log Analytics workspace KQL queries, alerting, workbooks 31 days free; up to 7 years with per-GB pricing
  • Azure Event Hub Streaming to third-party SIEM (Splunk, QRadar, Sentinel) Hub capture or downstream retention

Audit records land as JSON lines in the storage container ( <server>-<database>-audit/ ) and as the SQLSecurityAuditEvents table in Log Analytics. Retention is set independently per destination. When an exam scenario asks for long-term cheap archival plus real-time alerting, configure both Storage (for retention) and Log Analytics (for alerting).

SQL Server on Azure VMs uses the engine's native SQL Server Audit targeting a file, the Windows Application log, or the Windows Security log (the Security log requires SeAuditPrivilege ). From there, the Azure Monitor agent can forward to a Log Analytics workspace. The audit destinations above are not configured directly on a VM-hosted engine.

Microsoft Defender for SQL and Vulnerability Assessment

Microsoft Defender for SQL is the Azure-native bundle that protects Azure SQL Database, SQL Managed Instance, and (via the SQL IaaS Agent extension) SQL Server on VMs. It includes three capabilities the exam treats as one:

Data Discovery & Classification - the same labeling surfaced above, surfaced as a Defender finding when columns are unclassified.

Vulnerability Assessment (VA) - a scheduled and on-demand scan that runs a library of security checks against the database, compares to a baseline you set (so expected configuration drift does not fire), and returns a risk-ranked remediation list (high, medium, low). Baselines are the critical concept: VA does not alert on a check that has been marked as accepted in the baseline.

Advanced Threat Protection (ATP) - behavioral alerts for SQL injection, brute-force login attempts, anomalous access patterns, and potential data exfiltration, delivered to Microsoft Defender for Cloud and email/subscription admins.

A frequent scenario pattern: a compliance auditor wants evidence the database is hardened. Enabling Defender for SQL and running a Vulnerability Assessment scan produces a report you can export; marking reviewed rules as baseline lets subsequent scans flag only new drift. For SQL DB/MI you enable Defender per database or per server (with the server setting inheriting to new databases); for SQL Server on VMs, you configure it through the SQL VM resource's security blade.