SC-500 Database Security: Azure SQL Hardening, Auditing and Defender for Databases

Clients reach Azure SQL Database through the logical server's public endpoint only when a rule lets their address in: a server-level IP firewall rule opens every database on the server, a database-level IP firewall rule opens one database, and a virtual network rule admits a whole Azure subnet that has the Microsoft.Sql service endpoint. Everything else is rejected before authentication is even attempted.

Evaluation runs from the narrow scope outward: a connection to a specific database is first checked against that database's rules, then against the server-level rules. So the way to give an external IP range access to just one database out of many is a database-level rule; any server-level rule, however it is created, opens all of them. Virtual network rules match Azure subnets, never internet IP ranges. The tooling differs too: the portal, CLI and PowerShell manage server-level rules, while database-level rules exist only in T-SQL.

Firewall changes are not always instant. Server-level rules are cached inside each database, and security changes can take up to about five minutes to show. To make a new server-level rule apply immediately for one database, connect to that database and run DBCC FLUSHAUTHCACHE. The command clears only the cache of the database you run it in, so it has to be run in each database that must see the change at once.

What 'Allow Azure services and resources to access this server' really permits

The Allow Azure services and resources to access this server setting creates a server-level rule named AllowAllWindowsAzureIps with start and end address 0.0.0.0, and it admits connections from any Azure resource with outbound connectivity, in any subscription and any tenant, including other customers'. It is not scoped to your subscription, resource group or Microsoft Entra tenant, so authentication becomes the only barrier.

Microsoft recommends narrower alternatives:

• A virtual network rule for the subnet the workload actually uses. An App Service app with regional virtual network integration, for example, sends its traffic from its integration subnet, so a rule for that subnet (with the Microsoft.Sql service endpoint) admits just that app.
• A private endpoint, which gives the server a private IP address in your virtual network (covered in the network-services lesson).

A virtual network rule only narrows access if the Azure services setting is then turned off; leaving it on keeps the whole of Azure allowed. IP rules for App Service outbound addresses are a weak substitute, because those addresses are shared with other apps on the same scale unit.

The logical server's Public network access setting decides whether the public endpoint exists at all. Selected networks keeps it and lets IP firewall rules and virtual network rules decide who gets in; Disable turns it off so that only private endpoint connections succeed, whatever rules exist.

Two consequences follow. First, Disable is a standing control, while rules are a current state: with Selected networks, removing every rule closes the endpoint only until a rule is added again, whereas Disable keeps it closed and blocks rule changes. Second, Disable has no exception for Azure-hosted clients: the AllowAllWindowsAzureIps rule is itself a public-endpoint rule, so a service such as an Azure Data Factory integration runtime that connected through it fails until it uses a private endpoint (for example a managed private endpoint) or public access is set back to Selected networks. Likewise, when an administrator must add a temporary IP rule and gets error 42101, the first step is to switch public network access to Selected networks; the private endpoint keeps working alongside it.

The connection policy (Default, Redirect or Proxy) on the same Connectivity page only controls how traffic is routed to the database node after the gateway. It does not grant or deny access and has nothing to do with TLS.

Minimum TLS version

The minimum TLS version is set once on the logical server (Networking > Connectivity) and applies to every database on it; there is no per-database setting. Clients that negotiate a lower version fail with an invalid TLS version login error. TLS 1.0 and 1.1 have been retired for Azure SQL, so in practice the choice is 1.2 or 1.3; setting 1.2 still accepts 1.3 clients. Once a minimum is enforced you cannot go back to the original "no minimum" default, only to another version.

Before raising the minimum, find out which clients would break. Microsoft documents two sources: the database's Metrics for successful connections, split by TLS version, and the client_tls_version_name field in the audit records. The Activity log only shows who changed the setting, not what clients negotiate.

Microsoft Entra-only authentication turns off SQL authentication on a logical server (or managed instance) so that only Microsoft Entra users, groups, service principals and managed identities can connect. It is a server setting on the Microsoft Entra ID page, labelled Support only Microsoft Entra authentication for this server.

• Prerequisite: the server must have a Microsoft Entra admin. Until one is set, the option is unavailable in the portal and API calls fail, whatever Azure role you hold. Set the Entra admin first, then enable the option.
• Effect on SQL logins: existing SQL logins and their database users stay where they are; they simply can no longer connect, over any path, private endpoint included. Removing them is a separate clean-up task.
• At scale: to make every new server comply, and block deployments that don't, assign the built-in Azure Policy definitions for Azure SQL Microsoft Entra-only authentication at a management group. Policy is the tool that evaluates resource configuration at deployment time; reporting and after-the-fact remediation tools leave non-compliant servers running in the meantime.

Microsoft built two roles for separation of duties around this setting:

Transparent data encryption (TDE) encrypts data files, logs and backups at rest with a database encryption key (DEK); the TDE protector encrypts that DEK. With a service-managed protector Microsoft owns the key. With a customer-managed key (bring your own key) the protector is an RSA key in Azure Key Vault or Managed HSM that you control, so you can make the data unreadable by withdrawing the server's access to it, with no application changes. Cell-level encryption needs code changes and dynamic data masking is not encryption at all.

Setting up the customer-managed protector requires:

• Soft-delete and purge protection on the key vault. Losing the protector makes every database it protects inaccessible, so Azure SQL refuses a vault without both and the save fails validation.
• A managed identity on the server (system- or user-assigned) with get, wrapKey and unwrapKey on the key. Both Key Vault permission models are supported: with vault access policies you grant those three key permissions; with Azure RBAC the least-privilege role is Key Vault Crypto Service Encryption User for the server's identity. The grant goes to the server's identity, because it is the server that wraps and unwraps the DEK, and the role should allow only cryptographic use of keys, not key management or access to secrets.
• If the vault has a firewall, the vault must let Azure SQL in (the trusted Microsoft services exception).

Revocation: if the server loses access to the protector (key deleted, disabled or permissions removed), its databases deny connections and become inaccessible within minutes. If access is restored within about 30 minutes the databases heal automatically; after that, recovery is not automatic and you must revalidate the key (the portal's Revalidate key option) to bring them back, which can take longer.

Rotation takes two halves. A rotation policy on the key in Key Vault creates new key versions on schedule, and automatic key rotation on the server's TDE settings (off by default) makes Azure SQL detect a new version and switch the protector to it, within about 24 hours. Rotating the protector re-wraps only the DEK, so no data is re-encrypted. Both halves are needed: a new key version that the server never switches to protects nothing new, and an expiry date on a key only marks when it should stop being used.

Azure SQL Database auditing records database events (by default successful and failed authentications and completed batches) and writes them to one or more destinations. You can define an audit policy at the server level, the database level, or both.

• A server-level policy applies to every existing and future database on the logical server. It is the low-effort choice when every database must be audited the same way.
• A database-level policy applies to one database and runs alongside a server policy rather than replacing it, so the database is then audited twice.

Microsoft recommends server-level auditing only, and adding database-level auditing just for a database that needs something different: another storage account, a different retention period, a separate workspace, or different event types. Adding a destination to the server policy instead would send every database's records there.

There is one performance-driven exception. Server-level records written to storage are consolidated in a single master folder, so retrieving one database's trail means sifting through all of them. For large, busy OLTP estates, Microsoft's guidance is to turn server auditing off and audit each database at database level, so each writes to its own folder. The fix is the auditing level, not the event set: the separate folders come only from database-level policies with server auditing off, and trimming default action groups changes what is recorded rather than where it is stored.

Audit destinations, retention and the diagnostic setting

Azure SQL auditing writes to three destination types, and you can select several at once; each receives the full record set.

These three are the only destination types the audit policy offers; anything else is reached by forwarding from one of them.

Retention changes are not retroactive. If retention was 0 (unlimited) and you change it to a number of days, the new period applies only to logs written after the change; older files stay until you remove them another way, for example with a storage lifecycle management policy where that is allowed.

Log Analytics and Event Hubs are wired through a diagnostic setting that sends the SQLSecurityAuditEvents category. The portal creates it for you, but if someone later deletes it, auditing fails silently: the Auditing page still lists the workspace while no records arrive. Microsoft's safeguard is an activity log alert rule with an action group on diagnostic-setting deletion for the server. A policy that checks whether auditing is enabled would still pass, because the audit setting itself never changed.

Auditing can write to a storage account that accepts only selected virtual networks, but only when four conditions hold:

1. The account is general-purpose v2. A GPv1 or Blob Storage account must be upgraded first.
2. The storage firewall has Allow trusted Microsoft services to access this storage account turned on. Adding the server's outbound IP addresses is not the documented mechanism.
3. The logical server authenticates with its managed identity, not storage access keys, which aren't supported in this setup (and can't work anyway when shared key access is disabled).
4. That identity holds Storage Blob Data Contributor on the account, because auditing writes append blobs. Storage Blob Data Reader cannot write.

Immutable audit storage stops anyone altering or deleting audit files for a fixed period with a time-based retention policy on the container. For SQL auditing to keep writing to it:

• Allow protected append writes must be Append blobs or Block and append blobs; None isn't supported.
• The storage policy's retention interval must be shorter than the SQL audit retention in days.
• An audit retention of 0 (unlimited) isn't supported together with a storage immutability policy.

Azure SQL Managed Instance auditing uses the SQL Server audit model in T-SQL, not the Auditing page that SQL Database has: you create a server audit (where records go), one or more audit specifications (what to record), and then enable both. CREATE SERVER AUDIT leaves the audit itself off, so a common fault is a specification that is on but an audit that never started; ALTER SERVER AUDIT ... WITH (STATE = ON) fixes it.

Choose the specification by scope. A server audit specification takes server-level action groups such as FAILED_LOGIN_GROUP. A database audit specification, created inside the database, takes database action groups or individual object actions; to record only reads of one table, add SELECT ON dbo.TableName BY public. Broad groups such as SCHEMA_OBJECT_ACCESS_GROUP record far more than one table, and DATABASE_OBJECT_ACCESS_GROUP covers database objects such as certificates and assemblies, not table reads. A specification always belongs to a server audit; it can't exist without one.

CREATE SERVER AUDIT FinanceAudit TO EXTERNAL_MONITOR;
ALTER SERVER AUDIT FinanceAudit WITH (STATE = ON);
-- in the Finance database
CREATE DATABASE AUDIT SPECIFICATION InvoiceReads
  FOR SERVER AUDIT FinanceAudit
  ADD (SELECT ON dbo.Invoices BY public)
  WITH (STATE = ON);

Auditing Microsoft support operations

Auditing of Microsoft support operations records what Microsoft support engineers do on your server when they access it during a support request you opened, which some regulators require. It is separate from your normal audit:

• Azure SQL Database: turn on Enable Auditing of Microsoft support operations on the server's Auditing page. It can use its own destination, so the existing server audit policy stays unchanged.
• Azure SQL Managed Instance: create a separate server audit with OPERATOR_AUDIT = ON (to EXTERNAL_MONITOR), and select the DevOps operations Audit Logs category in the instance's diagnostic setting. Turning support-operations auditing on for an existing audit overwrites it so that it logs only support operations, and the diagnostic category produces nothing without the audit behind it.

Don't confuse it with neighbours: Customer Lockbox for Microsoft Azure controls whether support engineers may access your data at all (approval), the Activity log records control-plane operations, and Microsoft Purview Audit covers Microsoft 365 activity, not database operations.

The Databases plan in Microsoft Defender for Cloud bundles four offerings that add threat detection (and, for SQL, vulnerability assessment) to database services. You can enable the whole plan on a subscription or use Select types to choose offerings.

Subscription level versus resource level

For Azure SQL, enabling the offering on the subscription protects every server in it, including servers created later. To protect only chosen servers, leave the subscription plan off and enable Microsoft Defender for SQL on each server (the server's Security > Microsoft Defender for Cloud page); billing is per protected server. The least-privilege role for a team that should switch on Defender for SQL and manage vulnerability assessment for its own servers only is SQL Security Manager scoped to those servers; Security Admin on the subscription reaches beyond them, and SQL Server Contributor lacks the security permissions by design.

Advanced Threat Protection, delivered by Defender for SQL, watches database activity with built-in detections and raises security alerts with investigation and mitigation guidance; you write no detection queries. It is different from auditing (raw events you must analyse) and vulnerability assessment (weaknesses, not attacks).

Alerts appear in Defender for Cloud's security alerts, on the database resource's Microsoft Defender for Cloud page, and in Microsoft Sentinel or Defender XDR when connected. To investigate, pair the alert with the audit trail. One gap matters: when Microsoft Entra authentication fails, the credential is rejected before the database is reached, so neither server nor database auditing records failed Entra sign-ins. Find those in the sign-in logs of the Microsoft Entra admin center; failed SQL logins do appear in the SQL audit. Entra audit logs record directory changes, and the Activity log is control-plane only.

Email notifications are configured in Defender for Cloud's environment settings. By default only subscription owners get email, and only for high-severity alerts. To notify someone without giving them a role, add their address as an additional recipient, and set the minimum severity (Medium sends medium and high). Email goes to the roles and addresses listed in those settings; an Azure role assignment on its own is not a notification.

SQL vulnerability assessment scans databases for misconfigurations, excessive permissions and unprotected sensitive data, and reports failed rules as Defender for Cloud recommendations. It comes in two configurations:

A baseline records that a failing result is intended (for example, permissions you granted on purpose) so the rule passes from then on. To view findings across a subscription, Microsoft lists Security Admin or Security Reader; only Security Reader is read-only, so it fits an auditor who must not change settings.