11.2 Database Auditing & Vulnerability Assessment
Key Takeaways
- Azure SQL Auditing tracks database events, writing telemetry logs to facilitate regulatory compliance (such as PCI DSS, HIPAA, and SOC 2) and investigate anomalous security activities.
- Audit logs can be streamed to three primary target destinations: Azure Storage accounts (cost-effective long-term archival), Log Analytics workspaces (KQL querying and Defender for Cloud integration), and Azure Event Hubs (real-time SIEM ingestion into third-party tools like Splunk or QRadar).
- Dynamic Data Masking (DDM) restricts sensitive data exposure by masking query results for non-privileged database users on the fly, offering Default, Email, Random, and Custom String masking functions without altering stored database data.
- Microsoft Defender for SQL provides advanced threat protection and vulnerability management through automated scans, security baseline configurations, and actionable remediation steps.
- Security baseline management in SQL Vulnerability Assessment allows security administrators to mark acceptable findings as approved baselines, suppressing recurring false positives in compliance reports.
7.2 Database Auditing & Vulnerability Assessment
Maintaining database security compliance requires continuous operational visibility into database activities, proactive identification of security misconfigurations, and controlled presentation of sensitive data. Azure SQL provides built-in enterprise capabilities—Azure SQL Auditing, Dynamic Data Masking (DDM), and Microsoft Defender for SQL—to track database transactions, obfuscate sensitive output, and remediate platform vulnerabilities before they are exploited.
Azure SQL Auditing Architecture
Azure SQL Auditing tracks database events and records them in an audit log. Auditing establishes an immutable audit trail necessary for regulatory compliance frameworks (such as PCI DSS, HIPAA, SOC 2, and ISO 27001) and enables security operations teams to perform post-incident forensic investigations.
+-----------------------------------------------------------------------------------+
| AZURE SQL AUDITING ARCHITECTURE |
+-----------------------------------------------------------------------------------+
| Azure SQL Database / Server Audit Engine |
| Captures: Statements, Schema Changes, Logons, Permissions, Data Sensitivity |
| | |
| +-----------------------+-----------------------+ |
| | | | |
| v v v |
| +-----------------+ +-----------------+ +-----------------+ |
| | STORAGE ACCOUNT | | LOG ANALYTICS | | EVENT HUBS | |
| | (Append Blobs / | | (KQL Queries & | | (Real-time SIEM | |
| | WORM Archival) | | Sentinel SIEM) | | Ingestion) | |
| +-----------------+ +-----------------+ +-----------------+ |
+-----------------------------------------------------------------------------------+
Server-Level vs. Database-Level Audit Policies
- Server-Level Audit Policy: Configured on the Azure SQL Logical Server. Automatically applies audit collection rules to all existing databases and any newly created databases on that server.
- Database-Level Audit Policy: Configured on an individual database. Allows tailored auditing rules for specific high-compliance databases without generating excessive logging noise for lower-tier databases.
Audit Event Categories
Azure SQL Auditing logs a comprehensive array of event categories, including:
- Executed SQL queries and batch statements (including parameter values when configured)
- Successful and failed database logon attempts
- Schema changes (
CREATE,ALTER,DROPstatements) - Security permission grants, revokes, and role membership modifications
- Access to sensitive columns tagged with Data Discovery & Classification labels
Audit Target Destinations
Azure SQL Auditing streams audit logs to one or more of three primary target destinations, depending on operational and retention requirements:
| Target Destination | Primary Use Case | Key Features & Advantages |
|---|---|---|
| Azure Storage Account | Long-term compliance archival | Low-cost storage. Logs saved as JSON append blobs. Supports Immutable Storage policies (WORM / Write-Once-Read-Many) to satisfy regulatory retention mandates (e.g., 7-year retention). |
| Log Analytics Workspace | Operational reporting & KQL analytics | Ingests data into AzureDiagnostics or SQLSecurityAuditEvents tables. Enables Kusto Query Language (KQL) searching, automated alert rules, and native Microsoft Sentinel SIEM integration. |
| Azure Event Hubs | Real-time external SIEM integration | Streams events to Event Hub namespaces for real-time ingestion by third-party SIEM platforms (Splunk, Datadog, IBM QRadar, ArcSight). |
Dynamic Data Masking (DDM)
Dynamic Data Masking (DDM) limits sensitive data exposure by masking query results returned to non-privileged database users on the fly. DDM is designed to prevent unauthorized operational staff (such as call center representatives or support engineers) from viewing sensitive data while allowing application queries to function normally.
Critical Architecture Rule
DDM is a query presentation control, NOT an encryption mechanism or security boundary. The underlying data in database storage pages remains completely unmasked and unmodified. Users with direct table read privileges and query execution capabilities can potentially infer original values through brute-force T-SQL WHERE clause probes (e.g., SELECT * FROM Employees WHERE Salary > 100000).
+-----------------------------------------------------------------------------------+
| DYNAMIC DATA MASKING (DDM) PRESENTATION |
+-----------------------------------------------------------------------------------+
| Database Storage (Unmodified): [ Credit Card: "4111-2222-3333-4444" ] |
| | |
| +------------------------------------+ |
| | | |
| v v |
| Privileged User (db_owner / UNMASK) Non-Privileged User (Customer Rep) |
| Output: "4111-2222-3333-4444" Output: "XXXX-XXXX-XXXX-4444" |
+-----------------------------------------------------------------------------------+
Masking Functions
Azure SQL provides four built-in DDM masking functions:
- Default Masking Function: Fully masks values based on column data type:
- String data types: Replaces values with
XXXXor fewerxs if string length is under 4 characters. - Numeric data types: Replaces values with a zero (
0). - Date/Time data types: Replaces values with
01.01.1900 00:00:00.000.
- String data types: Replaces values with
- Email Masking Function: Exposes the first letter of an email address, replaces middle characters with
XXX, and appends the domain suffix (e.g.,aXXX@domain.com). - Random Number Masking Function: Replaces numeric values with a random integer generated within a specified boundary
[start, end](e.g.,random(1, 100)). - Custom String Masking Function: Exposes designated prefix and suffix characters and pads the middle with a custom string using syntax
CustomString(prefix_length, "padding", suffix_length)(e.g.,CustomString(2, "XX-XXXX-", 2)masks12345678as12XX-XXXX-78).
Privileged Access & UNMASK Permission
Database owners (db_owner) and administrative accounts bypass DDM by default. To grant specific non-admin users or application roles the ability to view unmasked data, administrators grant the database-level UNMASK permission (GRANT UNMASK TO [ServiceRole]).
Microsoft Defender for SQL
Microsoft Defender for SQL (a component of Microsoft Defender for Cloud under the Defender for Databases plan) provides continuous security posture management and advanced threat detection for Azure SQL Database, Managed Instances, and SQL Servers hosted on Azure Virtual Machines.
1. Vulnerability Assessment (VA)
SQL Vulnerability Assessment is an automated scanning service that discovers, tracks, and helps remediate database security misconfigurations.
- Scan Triggers: Executes automated weekly recurring background scans or can be triggered manually on-demand via the Azure Portal, REST API, or PowerShell (
Start-AzSqlDatabaseVulnerabilityAssessmentScan). - Rule Categories: Scans against over 100 security rules covering excessive database permissions, missing audit policies, weak firewall configurations, disabled encryption features, and exposed administrative flags.
- Remediation Guidance: Each flagged finding includes a severity score (High, Medium, Low), detailed description, impact analysis, and ready-to-run T-SQL or ARM remediation scripts.
2. Baseline Management
In enterprise environments, certain flags raised by Vulnerability Assessment scans may represent intentional, approved architecture choices (e.g., a specific legacy service account requiring elevated read access). Repeatedly reporting these approved configurations creates alert fatigue.
- Setting Baselines: Administrators can mark specific scan results as an Approved Baseline at the database or server level.
- Compliant Baseline Status: In subsequent automated scans, configurations matching the approved baseline pass checks, allowing security teams to focus on new drift or unapproved modifications.
3. Advanced Threat Protection (ATP)
Advanced Threat Protection continuously analyzes database telemetry in real time to detect anomalous, suspicious, or harmful activities:
- SQL Injection Detection: Identifies malicious T-SQL queries attempting to exploit application parameters.
- Anomalous Location / Access: Alerts when logins occur from unfamiliar IP ranges, unexpected geographic locations, or dormant accounts.
- Brute-Force Attacks: Detects high-frequency failed password attempts targeting SQL logins.
- Data Exfiltration Patterns: Triggers high-priority security alerts when an account executes query patterns extracting unusually high data volumes.
A compliance officer requires database audit logs from an Azure SQL Database to be processed in real time by an external third-party Security Information and Event Management (SIEM) system such as Splunk. Which audit log target destination should be configured?
An organization needs to prevent customer service representatives from viewing full credit card numbers in query outputs, while ensuring that the underlying database values remain unencrypted and fully intact for payment processing applications. Which feature should be implemented?
During a routine Microsoft Defender for SQL Vulnerability Assessment scan, a security administrator identifies a flagged finding for a dedicated administrative account that is required and approved by enterprise policy. How should the administrator prevent this finding from generating recurring false-positive alerts in future scans?