Troubleshoot Defender for SQL on Machines deployment

Article
02/13/2025

This article helps you to troubleshoot deployment of the Defender for SQL on Machines plan when using the Azure Monitoring Agent autoprovisioning process.

Defender for SQL Servers on Machines uses Azure Policy in its autoprovisioning process to create resources, configure and install extensions during the enablement process. To protect each SQL Server instance with Defender for Cloud, you must successfully create all resources, configurations, and installation extensions.

Prerequisites

Make sure you've enabled the Defender for SQL on Machines plan using the AMA-based autoprovisioning process and that you've followed all the prerequisite steps for enablement.

Automatic resource creation

Defender for SQL Servers on Machines automatically creates resources through the autoprovisioning process. Resources are created at the subscription level, and some resources are configured at the Azure SQL VM level, or at the Azure Arc-enabled SQL VM level.

The table summarizes the resource architecture described in the graphic.

Resource type	Level created
Resource group - created in East US Azure region. A user-assigned managed identity is created in each Azure region	Subscription level
Log Analytics workspace	Default/custom workspaces - Subscription level
Data Collection Rule (DCR) - Created for each workspace	Subscription level
Data Collection Rule Association (DCRA)	Defined for each SQL Server instance
Azure Monitoring Agent (AMA)	The extension is installed for each SQL Server instance
Defender for SQL extension	The extension is installed for each SQL Server instance

Identify deployment misconfigurations on a subscription

Defender for Cloud provides a workbook that allows you to locate which resources are misconfigured. The workbook lists which subscriptions have Defender for SQL on Machines enabled with misconfigurations.

Sign in to the Azure portal.
Open the SQL Servers on Machines AMA Helper workbook.
Select Subscription Overview > SQL Servers on Azure Virtual Machines.

Depending on your deployment and environment, you might not have SQL Servers on Azure Virtual Machines available. If that is the case, select Arc-Enabled SQL Servers instead.
To determine which subscriptions have misconfigurations, check which section of the screen doesn't match the expected configuration, such as 0/1, 10/15, or No. In our example screenshot, the Demo subscription has misconfigurations in DCRA 0/1.

Once you locate a subscription with misconfigurations, you should resolve the misconfigurations first on the subscription level and then on the resource level and extension installation level.

Resolve misconfigurations at the subscription level

You must Enable Defender for SQL using the AMA-based autoprovisioning process before attempting this section of the guide.

Sign in to the Azure portal.
Navigate to Azure Policy Compliance.
Select Scope.
In the dropdown, select the subscription with misconfigurations > Select.
on the Compliance page, select Defender for SQL on SQL VMs and Arc-enabled SQL Servers.
Search for and resolve each noncompliant issue in this order Identity > Workspace > DCR.

Warning

DCR resolution relies on Workspace resolution, and Workspace resolution relies on Identity resolution. If you try to resolve these misconfigurations out of order, the misconfigurations won't be resolved.
Fix each issue as follows:
- Identity - Create and assign a built-in user-assigned managed identity.
- Workspace - Configure the Microsoft Defender for SQL Log Analytics workspace.
- DCR - Configure SQL Virtual Machines to automatically install Microsoft Defender for SQL and DCR with a Log Analytics worskpace or Configure Arc-enabled SQL Servers to automatically install Microsoft Defender for SQL and DCR with a Log Analytics workspace.
For each policy that is noncompliant, review the compliance reason and select Create remediation task to resolve it.
Fill in the relevant information.
Select Remediate.
Repeat these steps for each noncompliant policy and subscription.
If you have a custom workspace, repeat these steps but in step 3 search for Defender for SQL on SQL VMs and Arc-enabled SQL Servers-custom for custom workspace.

Depending on your environment, Deny policies might be in place that prevent the creation of resources. If you encounter this issue, you can manually create and configure the plan at scale with PowerShell.

Resolve misconfigurations at the resource level

Resolve misconfigurations at the resource level only after resolving misconfiguration issues at the subscription level. These steps troubleshoot issues for each misconfigured DCRA configuration and incomplete AMA or Defender for SQL extensions.

AMA and Defender for SQL extension misconfigurations

Sign in to the Azure portal.
Navigate to Azure policy compliance.
Search for Defender for SQL on SQL VMs and Arc-enabled SQL Servers initiative.
Select Scope.
In the dropdown, select the subscription with misconfigurations.
Select Select.
Select Defender for SQL on SQL VMs and Arc-enabled SQL Servers initiative.
Select the noncompliant policy name.
- Defender for SQL extension policy name - Create and assign a built-in user-assigned managed identity.
- AMA extension policy name: - Configure SQL Virtual Machines to automatically install Azure Monitor Agent or Configure Arc-enabled SQL Servers to automatically install Azure Monitor Agent.
For each policy that is noncompliant, review the compliance reason and select Create remediation task to resolve it.

DCRA misconfigurations

Sign in to the Azure portal.
Search for and select Data collection rules.
Select Subscription equals.
Enter the relevant subscription.
Select Apply.
Locate and select the relevant DCR.

Note

The DCR naming convention follows this format: MicrosoftDefenderForSQL-region-dcr.
Select Configuration > Resources.
Select + Add.
In the Resource type dropdown table, select Machines - Azure Arc and Virtual machines.
Expand each resource group and select each machine.
Select Apply.