Data Classification and Auditing | Guided by A Guide to Cloud

Compliance controls

Simple explanation

Think of compliance like running a hospital ward.

Data classification is labelling every medicine cabinet — “Controlled Substance,” “General Use,” “Patient Data.” You need to know what’s sensitive before you can protect it.

Auditing is the security camera — it records who accessed what, when, and from where. If something goes wrong, you review the footage.

Change data tracking is the medication log — it records every change to the prescription, not just the current state. You can trace exactly what changed and when.

Data classification

Azure SQL Database and MI include built-in data discovery and classification:

How it works

Automatic discovery — Azure scans column names and content to suggest classifications
Manual labelling — you confirm or add classifications
Labels and types — each column gets an information type (e.g., “Financial”) and sensitivity label (e.g., “Confidential”)

Built-in sensitivity labels

Label	Use For
Public	Non-sensitive data
General	Internal business data
Confidential	Sensitive business data
Highly Confidential	Regulated data (PII, PHI, financial)

Amara’s workflow at Harbour Health:

Navigate to the database in Azure Portal → Data Discovery & Classification
Review Azure’s recommendations (it detected SSN, email, and date of birth columns)
Accept recommendations and manually add any missed columns
Export the classification report for compliance auditors

-- Add classification via T-SQL
ADD SENSITIVITY CLASSIFICATION TO dbo.Patients.SSN
WITH (LABEL = 'Highly Confidential', LABEL_ID = '...',
      INFORMATION_TYPE = 'National ID', INFORMATION_TYPE_ID = '...');

-- View classifications
SELECT * FROM sys.sensitivity_classifications;

Classification and Microsoft Purview

For organisations using Microsoft Purview, sensitivity labels defined in Purview can be applied to Azure SQL columns. This creates a unified classification system across databases, files, and other data stores.

The exam focuses on Azure SQL’s built-in classification feature. Purview integration is a bonus topic.

SQL auditing

Auditing records database events to an audit log for security analysis and compliance.

Azure SQL Database auditing

Setting	Options
Where to store logs	Azure Blob Storage, Log Analytics workspace, or Event Hub
Server-level audit	Applies to all databases on the server
Database-level audit	Applies to a specific database only
What’s logged	Successful/failed logins, data access, schema changes, permission changes

Configuration via Azure Portal:

Navigate to SQL server → Auditing
Enable auditing
Choose destination: Storage, Log Analytics, or Event Hub
Select audit actions (BATCH_COMPLETED_GROUP, SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, etc.)

-- Create a server audit (SQL Server on VMs / MI)
CREATE SERVER AUDIT NorthStarAudit
TO FILE (FILEPATH = 'C:\AuditLogs\', MAXSIZE = 100 MB);

ALTER SERVER AUDIT NorthStarAudit WITH (STATE = ON);

-- Create a database audit specification
CREATE DATABASE AUDIT SPECIFICATION PatientDataAudit
FOR SERVER AUDIT NorthStarAudit
ADD (SELECT ON dbo.Patients BY PUBLIC)
WITH (STATE = ON);

Querying audit logs

In Log Analytics (KQL):

AzureDiagnostics
| where Category == "SQLSecurityAuditEvents"
| where action_name_s == "SELECT"
| where object_name_s == "Patients"
| project TimeGenerated, server_principal_name_s, client_ip_s, statement_s

Server Audit vs Database Audit
Aspect	Server-level Audit	Database-level Audit
Scope	All databases on the server	Specific database
Where configured	On the logical server	On the individual database
Actions	Server events + all DB events	Database-specific events
Both enabled?	Both run independently	Both run independently
Best for	Baseline compliance across all DBs	Extra auditing on sensitive databases

Exam tip: audit log destinations

Blob Storage — cheapest, best for long-term retention and compliance archives
Log Analytics — best for querying and analysis (KQL), alerting, dashboards
Event Hub — best for real-time streaming to SIEM systems (Sentinel, Splunk)

You can send audit logs to multiple destinations simultaneously. The exam may ask which destination to use for “real-time security alerting” (Event Hub + SIEM) vs “quarterly compliance review” (Blob Storage).

Change data tracking (CDC and CT)

Two mechanisms for tracking data changes:

Change Tracking (CT)

Lightweight — tracks WHICH rows changed, not WHAT changed
Returns current version of changed rows
Good for sync scenarios (mobile apps, distributed caches)

-- Enable change tracking on database
ALTER DATABASE NorthStarERP SET CHANGE_TRACKING = ON
(CHANGE_RETENTION = 7 DAYS, AUTO_CLEANUP = ON);

-- Enable on a table
ALTER TABLE dbo.Orders ENABLE CHANGE_TRACKING;

-- Query changes since version 5
SELECT * FROM CHANGETABLE(CHANGES dbo.Orders, 5) AS CT;

Change Data Capture (CDC)

Records the FULL before/after values of changed rows
Creates shadow tables with change history
Good for auditing, data warehousing, and regulatory compliance

-- Enable CDC on the database (MI and SQL on VMs)
EXEC sys.sp_cdc_enable_db;

-- Enable on a table
EXEC sys.sp_cdc_enable_table
  @source_schema = 'dbo',
  @source_name = 'Patients',
  @role_name = 'cdc_reader';

-- Query changes
SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Patients(@from_lsn, @to_lsn, 'all');

Change Tracking vs Change Data Capture
Feature	Change Tracking (CT)	Change Data Capture (CDC)
What it records	Which rows changed (IDs + version)	Full before/after values
Storage overhead	Low	Higher (shadow tables)
Requires SQL Agent	No	Yes (cleanup jobs)
Azure SQL DB support	Yes	Yes
MI support	Yes	Yes
Best for	Sync, cache invalidation	Auditing, data warehousing, compliance

Question

What is the difference between Change Tracking and Change Data Capture?

Click or press Enter to reveal answer

Answer

Change Tracking records WHICH rows changed (lightweight, for sync). CDC records the FULL before/after values of changes (heavier, for auditing and compliance).

Click to flip back

Question

Where can Azure SQL audit logs be stored?

Click or press Enter to reveal answer

Answer

Three destinations: Azure Blob Storage (long-term archive), Log Analytics workspace (KQL queries and alerts), Event Hub (real-time streaming to SIEM). You can use multiple simultaneously.

Click to flip back

Question

What does Azure SQL data classification do?

Click or press Enter to reveal answer

Answer

Discovers and labels sensitive columns in your database with information types and sensitivity labels. Helps identify where PII, financial data, and health records live for compliance.

Click to flip back

Knowledge Check

Amara needs to track every time patient records are read by any user, with details available for quarterly compliance audits. Where should she send audit logs?

Knowledge Check

Kenji needs to replicate order changes to a data warehouse, including the before and after values of each update. Which feature should he enable?

🎬 Video coming soon

Next up: Data Masking, Ledger, and Row-Level Security — control who sees what data with masking, verify data integrity with ledger, and enforce row-level access.