Troubleshoot Defender for SQL on Machines configuration

Before starting the troubleshooting steps, you must enable Defender for SQL server on Machines at the subscription or SQL resource level..

Step 1: Required resources and enablement process

Defender for SQL Server on Machines automatically creates the following resources on your machines:

When you enable Defender for SQL Server on a subscription or specified SQL Server, it performs the following actions to protect each SQL Server instance:

• Creates a system-managed identity if there's no user-managed identity in the subscription.
• Installs the Defender for SQL extension on the virtual machine/Arc-enabled server hosting the SQL Server.
• Impersonates the Windows user running the SQL Server service (default sysadmin role) to access the SQL Server instance.

Step 2: Ensure that you fulfilled the prerequisites

• Subscription permissions: To deploy the plan on a subscription, including Azure Policy, you need Subscription Owner permissions.

• SQL Server instance permissions: SQL Server service accounts must have the sysadmin fixed server role on each SQL Server instance, which is the default setting. Learn more about the SQL Server service account requirement.

• Supported Resources:

  • SQL virtual machines, and Azure Arc SQL Server instances are supported.
  • On-premises machines must be onboarded to Arc and registered as Azure Arc SQL Server instances.

Communication: Allow outbound HTTPS traffic over Transmission Control Protocol (TCP) port 443 using Transport Layer Security (TLS) to *.<region>.arcdataservices.com URL. Learn more about URL requirements.

• Extensions: Ensure these extensions aren't blocked in your environment. Learn more about restricting extensions installation on Windows VMs.

  • 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

• Supported SQL Server versions - SQL Server 2012 R2 (11.x) and later versions.

• Supported operating systems- SQL Server 2012 R2 and later versions.

Step 3: Identify and resolve protection misconfigurations at the SQL Server instance Level

Follow the verification process to identify protection misconfigurations on SQL Server instances.

The recommendation The status of Microsoft SQL Servers on Machines should be protected can be used to verify the protection status of SQL Servers, but the recommendation should be remediated at the resource level. Any SQL server that is unprotected is identified in the unhealthy resource section of the recommendation with a protection status listed and a reason.

Use the corresponding unhealthy reason and recommended actions to resolve the misconfiguration:

Multiple SQL Server instances on the same virtual machine

If you have multiple SQL Server instances installed on the same virtual machine, the recommendation The status of Microsoft SQL Servers on Machines should be protected can't differentiate between instances. To correlate the error message with the corresponding SQL Server instance, check the error message under the Defender for SQL extension. The Defender for SQL extension can display the following reasons for each instance:

• Restart the SQL Server
• Check permissions
• Ensure the SQL Server instance is active

1. In the Azure portal, search for and select SQL virtual machine.

2. Select the relevant virtual machine.

3. Navigate to Settings > Extensions + applications.

   

4. Select the relevant extension to view its protection status.

   

Based on the unhealthy reason listed, take the appropriate action to remediate the issue.

Step 4: Reverify protection status

After completing the remediation of all errors for each SQL Server instance, reverify the protection status of each SQL Server instance.