7.1 Data Classification and Server/Database Auditing

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:

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:

1. 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.
2. 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.
3. 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:

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.

Audit Differences Across the Three Platforms

DP-300 questions often hinge on which audit mechanism applies to which PaaS/IaaS offering. The table consolidates the differences:

For SQL MI, prefer the Azure auditing destination for cloud-native compliance workflows; the engine SQL Server Audit exists for parity with on-prem scripts. For Azure SQL Database there is no Windows log option - it is purely a managed audit. For SQL Server on VMs, there is no built-in Log Analytics destination from the engine; you collect audit files and forward them with the Azure Monitor agent or ship them to a SIEM yourself.

Operational Exam Traps

• The audit destination is configured on the server audit object, not on the specification. Creating a database audit specification without a server audit enabled does nothing.
• A failed login audit (FAILED_LOGIN_GROUP) is server-scoped; you cannot capture it in a database audit specification.
• Enabling auditing on the logical server in Azure SQL Database propagates to all databases; enabling on a database overrides the server setting. The override behavior is a common scenario: "server-level auditing is on; database X must audit only SELECT on table Y" - the database audit is additive, not exclusive, unless you explicitly disable inherited groups.
• TRACK_COLUMN_UPDATED is not an audit setting - it belongs to Change Tracking (covered next). Do not confuse the two compliance-adjacent features.
• Audit failures (e.g., storage unreachable) can fail open or fail closed depending on configuration; in Azure SQL Database, the audit defaults to continuing on failure so the database stays available, which is itself a compliance tradeoff to know.

Takeaways

Classification is metadata; auditing is enforcement evidence; Defender for SQL is the scan and alert bundle. Choose the audit specification that matches the event scope (server = groups only, database = groups + individual actions), route to the destination that matches the consumer (Storage for retention, Log Analytics for queries, Event Hub for SIEM), and use Vulnerability Assessment baselines to make "is the database still hardened?" an auditable, drift-only signal.