Azure

How to harden security of Azure SQL Database

SQL Database Is the Crown Jewel — Defend It

Azure SQL Database stores your transactional data — customer records, financial transactions, and business-critical information. A data breach from SQL Database can result in regulatory fines, customer trust loss, and business disruption. This guide covers the complete hardening path from network isolation to data classification and threat detection.

Threat Landscape and Attack Surface

Hardening Azure SQL Database requires understanding the threat landscape specific to this service. Azure services are attractive targets because they often store, process, or transmit sensitive data and provide control-plane access to cloud infrastructure. Attackers probe for misconfigured services using automated scanners that continuously sweep Azure IP ranges for exposed endpoints, weak authentication, and default configurations.

The attack surface for Azure SQL Database includes several dimensions. The network perimeter determines who can reach the service endpoints. The identity and access layer controls what authenticated principals can do. The data plane governs how data is protected at rest and in transit. The management plane controls who can modify the service configuration itself. A comprehensive hardening strategy addresses all four dimensions because a weakness in any single layer can be exploited to bypass the controls in other layers.

Microsoft’s shared responsibility model means that while Azure secures the physical infrastructure, network fabric, and hypervisor, you are responsible for configuring the service securely. Default configurations prioritize ease of setup over security. Every Azure service ships with settings that must be tightened for production use, and this guide walks through the critical configurations that should be changed from their defaults.

The MITRE ATT&CK framework for cloud environments provides a structured taxonomy of attack techniques that adversaries use against Azure services. Common techniques relevant to Azure SQL Database include initial access through exposed credentials or misconfigured endpoints, lateral movement through overly permissive RBAC assignments, and data exfiltration through unmonitored data plane operations. Each hardening control in this guide maps to one or more of these attack techniques.

Compliance and Regulatory Context

Security hardening is not just a technical exercise. It is a compliance requirement for virtually every regulatory framework that applies to cloud workloads. SOC 2 Type II requires evidence of security controls for cloud services. PCI DSS mandates network segmentation and encryption for payment data. HIPAA requires access controls and audit logging for health information. ISO 27001 demands a systematic approach to information security management. FedRAMP requires specific configurations for government workloads.

Azure Policy and Microsoft Defender for Cloud provide built-in compliance assessments against these frameworks. After applying the hardening configurations in this guide, run a compliance scan to verify your security posture against your applicable regulatory standards. Address any remaining findings to achieve and maintain compliance. Export compliance reports on a scheduled basis to satisfy audit requirements and demonstrate continuous adherence.

The Microsoft cloud security benchmark provides a comprehensive set of security controls mapped to common regulatory frameworks. Use this benchmark as a checklist to verify that your hardening effort covers all required areas. Each control includes Azure-specific implementation guidance and links to the relevant Azure service documentation.

Step 1: Enable Azure AD-Only Authentication

# Set Azure AD admin
az sql server ad-admin create --server sql-prod --resource-group rg-data \
  --display-name "SQL Admins" --object-id "aad-group-id"

# Enable Azure AD-only authentication
az sql server ad-only-auth enable --server sql-prod --resource-group rg-data

Step 2: Configure Private Endpoints

# Create private endpoint
az network private-endpoint create \
  --name pe-sql --resource-group rg-network \
  --vnet-name vnet-prod --subnet snet-pe \
  --private-connection-resource-id $(az sql server show --name sql-prod --resource-group rg-data --query id -o tsv) \
  --group-id sqlServer --connection-name sql-conn

# Disable public network access
az sql server update --name sql-prod --resource-group rg-data \
  --enable-public-network false

Step 3: Enable Advanced Threat Protection

# Enable Defender for SQL
az security pricing create --name SqlServers --tier Standard

# Enable Advanced Threat Protection on the server
az sql server threat-policy update --server sql-prod --resource-group rg-data \
  --state Enabled --email-addresses soc@contoso.com \
  --email-account-admins true

Defender for SQL detects: SQL injection, anomalous access patterns, brute-force login attempts, and data exfiltration.

Step 4: Enable Auditing

# Enable server-level auditing to Log Analytics
az sql server audit-policy update --server sql-prod --resource-group rg-data \
  --state Enabled \
  --log-analytics-target-state Enabled \
  --log-analytics-workspace-resource-id "/subscriptions/{sub}/resourceGroups/rg-monitor/providers/Microsoft.OperationalInsights/workspaces/law-prod"

Step 5: Implement Data Classification and Masking

-- Classify sensitive columns
ADD SENSITIVITY CLASSIFICATION TO dbo.Customers.Email
WITH (LABEL = 'Confidential', INFORMATION_TYPE = 'Contact Info');

ADD SENSITIVITY CLASSIFICATION TO dbo.Customers.SSN
WITH (LABEL = 'Highly Confidential', INFORMATION_TYPE = 'National ID');

-- Apply dynamic data masking
ALTER TABLE dbo.Customers ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()');
ALTER TABLE dbo.Customers ALTER COLUMN SSN ADD MASKED WITH (FUNCTION = 'partial(0,"XXX-XX-",4)');
ALTER TABLE dbo.Customers ALTER COLUMN Phone ADD MASKED WITH (FUNCTION = 'default()');

Identity and Access Management Deep Dive

Identity is the primary security perimeter in cloud environments. For Azure SQL Database, implement a robust identity and access management strategy that follows the principle of least privilege.

Managed Identities: Use system-assigned or user-assigned managed identities for service-to-service authentication. Managed identities eliminate the need for stored credentials (connection strings, API keys, or service principal secrets) that can be leaked, stolen, or forgotten in configuration files. Azure automatically rotates the underlying certificates, removing the operational burden of credential rotation.

Custom RBAC Roles: When built-in roles grant more permissions than required, create custom roles that include only the specific actions needed. For example, if a monitoring service only needs to read metrics and logs from Azure SQL Database, create a custom role with only the Microsoft.Insights/metrics/read and Microsoft.Insights/logs/read actions rather than assigning the broader Reader or Contributor roles.

Conditional Access: For human administrators accessing Azure SQL Database through the portal or CLI, enforce Conditional Access policies that require multi-factor authentication, compliant devices, and approved locations. Set session lifetime limits so that administrative sessions expire after a reasonable period, forcing re-authentication.

Just-In-Time Access: Use Azure AD Privileged Identity Management (PIM) to provide time-limited, approval-required elevation for administrative actions. Instead of permanently assigning Contributor or Owner roles, require administrators to activate their role assignment for a specific duration with a business justification. This reduces the window of exposure if an administrator’s account is compromised.

Service Principal Hygiene: If managed identities cannot be used (for example, for external services or CI/CD pipelines), use certificate-based authentication for service principals rather than client secrets. Certificates are harder to accidentally expose than text secrets, and Azure Key Vault can automate their rotation. Set short expiration periods for any client secrets and monitor for secrets that are approaching expiration.

Step 6: Enable Transparent Data Encryption with CMK

# TDE is enabled by default with service-managed keys
# Upgrade to customer-managed key
az sql server tde-key set --server sql-prod --resource-group rg-data \
  --kid "https://kv-prod.vault.azure.net/keys/sql-tde-key/version" \
  --server-key-type AzureKeyVault

Step 7: Configure Firewall and VNet Rules

# Deny Azure services access (if using private endpoints)
az sql server firewall-rule delete --server sql-prod --resource-group rg-data \
  --name AllowAllWindowsAzureIps

# Add specific IP rules only if needed
az sql server firewall-rule create --server sql-prod --resource-group rg-data \
  --name AllowOffice --start-ip-address 203.0.113.10 --end-ip-address 203.0.113.10

Step 8: Implement Row-Level Security

-- Create security predicate function
CREATE FUNCTION dbo.fn_TenantFilter(@TenantId int)
RETURNS TABLE WITH SCHEMABINDING
AS RETURN SELECT 1 AS Result 
WHERE @TenantId = CAST(SESSION_CONTEXT(N'TenantId') AS int);

-- Apply security policy
CREATE SECURITY POLICY TenantPolicy
ADD FILTER PREDICATE dbo.fn_TenantFilter(TenantId) ON dbo.Orders,
ADD BLOCK PREDICATE dbo.fn_TenantFilter(TenantId) ON dbo.Orders;

-- Set tenant context in application
EXEC sp_set_session_context @key=N'TenantId', @value=42;

Step 9: Enable Vulnerability Assessment

# Enable vulnerability assessment
az sql va set --server sql-prod --resource-group rg-data \
  --storage-account staudits --storage-container-path vulnerability-assessments

Vulnerability assessment scans for misconfigurations, excessive permissions, unprotected sensitive data, and database vulnerabilities. Schedule recurring scans and review findings.

Step 10: Configure Backup and Geo-Redundancy

# Configure long-term backup retention
az sql db ltr-policy set --server sql-prod --resource-group rg-data \
  --database mydb \
  --weekly-retention P4W --monthly-retention P12M --yearly-retention P5Y \
  --week-of-year 1

Defense in Depth Strategy

No single security control is sufficient. Apply a defense-in-depth strategy that layers multiple controls so that the failure of any single layer does not expose the service to attack. For Azure SQL Database, this means combining network isolation, identity verification, encryption, monitoring, and incident response capabilities.

At the network layer, restrict access to only the networks that legitimately need to reach the service. Use Private Endpoints to eliminate public internet exposure entirely. Where public access is required, use IP allowlists, service tags, and Web Application Firewall (WAF) rules to limit the attack surface. Configure network security groups (NSGs) with deny-by-default rules and explicit allow rules only for required traffic flows.

At the identity layer, enforce least-privilege access using Azure RBAC with custom roles when built-in roles are too broad. Use Managed Identities for service-to-service authentication to eliminate stored credentials. Enable Conditional Access policies to require multi-factor authentication and compliant devices for administrative access.

At the data layer, enable encryption at rest using customer-managed keys (CMK) in Azure Key Vault when the default Microsoft-managed keys do not meet your compliance requirements. Enforce TLS 1.2 or higher for data in transit. Enable purge protection on any service that supports soft delete to prevent malicious or accidental data destruction.

At the monitoring layer, enable diagnostic logging and route logs to a centralized Log Analytics workspace. Configure Microsoft Sentinel analytics rules to detect suspicious access patterns, privilege escalation attempts, and data exfiltration indicators. Set up automated response playbooks that can isolate compromised resources without human intervention during off-hours.

Continuous Security Assessment

Security hardening is not a one-time activity. Azure services evolve continuously, introducing new features, deprecating old configurations, and changing default behaviors. Schedule quarterly security reviews to reassess your hardening posture against the latest Microsoft security baselines.

Use Microsoft Defender for Cloud’s Secure Score as a quantitative measure of your security posture. Track your score over time and investigate any score decreases, which may indicate configuration drift or new recommendations from updated security baselines. Set a target Secure Score and hold teams accountable for maintaining it.

Subscribe to Azure update announcements and security advisories to stay informed about changes that affect your security controls. When Microsoft introduces a new security feature or changes a default behavior, assess the impact on your environment and update your hardening configuration accordingly. Automate this assessment where possible using Azure Policy to continuously evaluate your resources against your security standards.

Conduct periodic penetration testing against your Azure environment. Azure’s penetration testing rules of engagement allow testing without prior notification to Microsoft for most services. Engage a qualified security testing firm to assess your Azure SQL Database deployment using the same techniques that real attackers would employ. The findings from these tests often reveal gaps that automated compliance scans miss.

Hardening Checklist

  1. Azure AD-only authentication
  2. Private endpoints with public access disabled
  3. Defender for SQL enabled
  4. Server-level auditing to Log Analytics
  5. Data classification and dynamic masking
  6. TDE with customer-managed keys
  7. Firewall rules tightly scoped
  8. Row-level security for multi-tenant
  9. Vulnerability assessment scheduled
  10. Long-term backup retention configured

For more details, refer to the official documentation: What is Azure SQL Database?, Azure SQL Database and Azure Synapse IP firewall rules, Troubleshoot connectivity issues and other errors.

Leave a Reply