Troubleshoot machine protection in Defender for SQL on Machines

If you enable Defender for SQL Server on Machines and some SQL instances aren't in a protected state, use this article to troubleshoot deployment issues.

Before starting the troubleshooting steps, ensure you have:

• Followed the steps to enable Defender for SQL on Machines.
• Reviewed the protection status of databases running on protected machines.

Step 1: Understand how resources are created

Defender for SQL Servers on Machines automatically creates resources as shown in the diagram.

Resources are summarized in the table:

Resource type	Level created
Resource group - Created in East US Azure region	Subscription level
Managed identity - A user-assigned managed identity is created in each Azure region	Subscription level
Log Analytics workspace - Use the default or a custom workspace.	Subscription level
Data collection rule (DCR) - Created for each workspace	Subscription level
Data collection rule association (DCRA)	Defined on each SQL Server instance
Azure Monitoring Agent (AMA)	The extension is installed on each SQL Server instance
Defender for SQL extension	The extension is installed on each SQL Server instance

Step 2: Make sure extensions are allowed

To ensure protection works as expected, make sure your organizational deny policy doesn't block these extensions:

• Defender for SQL (IaaS and Arc)
  • Publisher: Microsoft.Azure.AzureDefenderForSQL
  • Type: AdvancedThreatProtection.Windows
• SQL IaaS Extension (IaaS)
  • Publisher: Microsoft.SqlServer.Management
  • Type: SqlIaaSAgent
• SQL IaaS Extension (Arc)
  • Publisher: Microsoft.AzureData
  • Type: WindowsAgent.SqlServer
• AMA extension (IaaS and Arc)
  • Publisher: Microsoft.Azure.Monitor
  • Type: AzureMonitorWindowsAgent

Step 3: Ensure East US region is allowed

When you enable the plan, Azure creates a resource group in the East US region. Ensure no deny policies block this region.

Step 4: Verify resource naming conventions

Defender for SQL Server on Machines uses specific naming conventions for resources. Ensure your organization doesn't block these naming conventions and don't modify any automatically created resources.

• DCR: MicrosoftDefenderForSQL--dcr
• DCRA: /Microsoft.Insights/MicrosoftDefenderForSQL-RulesAssociation
• Resource group: DefaultResourceGroup-
• Log Analytics workspace: D4SQL--

Defender for SQL uses MicrosoftDefenderForSQL as a createdBy database tag.

Step 5: Identify misconfigurations at subscription level

To identify which subscriptions have misconfigurations, use the SQL Servers on Machines AMA Helper workbook.

1. Open the SQL Servers on Machines AMA Helper workbook.

   

2. In Subscriptions Overview review misconfigurations at subscription level.

   • SQL Servers on Azure Virtual Machines - shows subscriptions that contain Azure VMs.
   • Arc-Enabled SQL Servers - shows subscriptions that contain Azure Arc-enabled VMs.

   Subscriptions appear on these tabs in accordance with your specific environment.

   

3. Review component configurations for each subscription.

   • The number of SQL Server instances in the subscription.
   • Instances with the Defender for SQL extension installed.
   • Instances with the AMA extension installed.
   • DCRs created for each workspace in the subscription (across all regions).
   • DCRAs created for each SQL instance.
   • Managed identity created for each region at the subscription level.
   • Log Analytics workspace created for each region at the subscription level.
   • AMA autoprovisioning enabled for the subscription.
   • Defender for SQL enabled for the subscription.

4. For each subscription, check which component 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.

   

After locating a subscription with misconfigurations, resolve the misconfigurations first at the subscription level, then at the resource level and extension installation level.

Step 6: Resolve misconfigurations at the subscription level

After identifying misconfigurations, start by fixing DCR issues, then workspace issues, and finally identity issues at the subscription level.

Fix misconfigurations in the correct order. DCR resolution relies on workspace resolution, and workspace resolution relies on identity resolution. If you try to resolve these misconfigurations out of order, they aren't resolved.

1. Navigate to Policy > Compliance.

2. Select Scope.

   

3. In Scope, select the relevant subscription.

4. On the Compliance page, select the policy in accordance with your workspace configuration:

   • Default workspace: Defender for SQL on SQL VMs and Arc-enabled SQL Servers.
   • Custom workspace: Defender for SQL on SQL VMs and Arc-enabled SQL Servers-custom.

   

5. Search for and resolve each noncompliant issue in this order Identity > Workspace > DCR.

6. 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 workspace or Configure Arc-enabled SQL Servers to automatically install Microsoft Defender for SQL and DCR with a Log Analytics workspace.

7. For each policy that is noncompliant, review the compliance reason and select Create remediation task to resolve it.

   

8. Fill in the relevant information.

9. Select Remediate.

10. Repeat these steps for each noncompliant policy and subscription.

Input custom values with PowerShell deployment script

If you couldn't resolve subscription issues with the workbook, Defender for SQL Servers on Machines provides a PowerShell deployment script that enables you to input your own values for workspace, DCR, and user Identity. To use the PowerShell script, follow the instructions on this page.

Step 7: Resolve misconfigurations at the resource level

After resolving misconfigurations at the subscription level, resolve misconfigurations at the resource level, including DCRA misconfigurations and incomplete AMA or Defender for SQL extension deployment.

Troubleshoot extension misconfigurations

1. In the Azure portal, navigate to Policy > Compliance.

2. Select Scope.

3. In the dropdown, select the subscription with misconfigurations.

4. Search for and select Defender for SQL on SQL VMs and Arc-enabled SQL Servers initiative.

5. Select the noncompliant policy name.

   • Defender for SQL extension - Create and assign a built-in user-assigned managed identity.
   • AMA extension: - Configure SQL Virtual Machines to automatically install Azure Monitor Agent or Configure Arc-enabled SQL Servers to automatically install Azure Monitor Agent.

6. For each policy that is noncompliant, review the compliance reason and select Create remediation task to resolve it.

Troubleshoot DCRA misconfigurations

1. In the Azure portal, Search for and select Data collection rules.

2. Select Subscription equals > select the relevant subscription.

   

3. Select Apply.

4. Locate and select the relevant DCR. The DCR naming convention follows this format: MicrosoftDefenderForSQL-region-dcr.

5. Select Configuration > Resources.

   

6. Select + Add.

   

7. In the Resource types dropdown table, select Machines - Azure Arc and Virtual machines in accordance with your deployment.

   

8. Expand each resource group and select each machine.

   

9. Select Apply.

Step 8: Reverify protection status

After completing all the steps on this page, reverify the protection status of each SQL Server instance.