Microsoft Defender for SQL

Microsoft Defender for SQL is a unified security package for Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse SQL pools. It integrates three security capabilities: Vulnerability Assessment (VA), Advanced Threat Protection (ATP), and Data Discovery & Classification. The service is available in two tiers: the free basic tier (limited to vulnerability assessment) and the paid standard tier (full suite). For AZ-500, you need to know the standard tier, which is enabled at the server level for Azure SQL Database and Managed Instance, and at the machine level for SQL Server on Azure VMs.

Defender for SQL operates through a combination of agentless scanning and log analysis. For Azure SQL Database and Managed Instance, vulnerability assessment uses a built-in scanning engine that runs against the database catalog views and system tables. It checks for misconfigurations, missing patches, and security best practices. The scan results are stored in a user-specified Azure Storage account as a JSON file. Threat detection relies on SQL audit logs and telemetry from the Azure SQL backend. It uses Microsoft's threat intelligence and machine learning models to detect anomalous patterns such as SQL injection, brute force attacks, and unusual data exfiltration. Data discovery uses a classifier that scans the database schema and sample data to identify sensitive columns (e.g., credit card numbers, social security numbers).

To enable Defender for SQL on an Azure SQL Database server using Azure CLI:

az sql server update --name <server-name> --resource-group <rg> --enable-advanced-data-security true

To enable vulnerability assessment scanning with a storage account:

az sql server va set --name <server-name> --resource-group <rg> --storage-account <storage-account> --enable-va true

To view vulnerability assessment results:

az sql server va list --name <server-name> --resource-group <rg>

To check threat detection alerts in PowerShell:

Get-AzSqlServerAdvancedThreatProtectionSetting -ServerName <server> -ResourceGroupName <rg>

Defender for SQL integrates tightly with Microsoft Defender for Cloud. Alerts from ATP appear in the Security Alerts blade in Defender for Cloud. Vulnerability assessment findings appear under Recommendations. Data classification integrates with Azure Purview (now Microsoft Purview) for broader data governance. Defender for SQL also supports Azure Policy: you can use built-in policies to enforce enabling Defender for SQL on all servers in a subscription. For SQL Server on Azure VMs, Defender for SQL requires the Log Analytics agent and is managed through the Azure Arc-enabled SQL Server or directly via the SQL IaaS Agent extension.

When a SQL injection attempt occurs, the following happens: 1. The user sends a query that contains suspicious patterns (e.g., ' OR 1=1). 2. The SQL engine parses the query and logs it to the SQL audit log. 3. The Defender for SQL backend ingests the audit log within minutes (typically 5-10 minutes latency). 4. Machine learning models analyze the query pattern and compare it to known SQL injection signatures. 5. If a match is found, an alert is generated with severity (e.g., High), description, and recommended remediation. 6. The alert is pushed to Defender for Cloud and optionally to a configured SIEM. 7. An email notification can be sent to the security team if configured.

The entire process from query execution to alert generation is typically under 15 minutes.

A fintech company processes credit card payments and must comply with PCI DSS. They use Azure SQL Database for transactional data. They enable Defender for SQL on all production servers. Vulnerability assessment scans weekly and identifies missing firewall rules and weak passwords. They set baselines to suppress known acceptable configurations. Threat detection alerts them to a potential SQL injection attack from a compromised web application. They investigate and block the offending IP using Azure SQL Database firewall. Data discovery identifies columns storing credit card numbers and they apply the 'Confidential' label. This helps them demonstrate compliance during audits. A common issue is that the vulnerability assessment storage account is in a different region, causing latency in scan results. They move the storage account to the same region as the SQL server to reduce latency.

A healthcare organization uses Azure SQL Managed Instance for patient records. They enable Defender for SQL and configure threat detection alerts to be sent to Azure Sentinel for SOC analysis. They use vulnerability assessment to check for missing patches and excessive permissions. One day, an alert fires for 'Anomalous client login' from a foreign IP address. The SOC investigates and finds that a developer accidentally exposed a connection string in a public GitHub repository. They rotate credentials and implement Azure Key Vault for secret management. They also classify columns containing patient health information (PHI) using data discovery. A challenge they face is that the weekly scan schedule is insufficient for their compliance requirements; they implement on-demand scans after every schema change.

An e-commerce company uses Azure SQL Database with a high volume of connections. They enable Defender for SQL but initially experience performance overhead due to audit logging. They optimize by enabling audit only on critical databases and setting appropriate retention periods. Threat detection alerts them to a brute force attack from a botnet. They use the alert's data to identify the attacking IP ranges and add firewall rules to block them. They also use data classification to identify columns storing customer PII and apply 'Confidential' labels. A common misconfiguration is not setting up email notifications, so the security team misses critical alerts until they check the portal. They configure email notifications for high-severity alerts and integrate with their incident management system.