Defender for SQL uses MicrosoftDefenderForSQL as a createdBy database tag.
Steps to enable Defender for SQL on non-Azure machines
Connect SQL server to Azure Arc. For more information on the supported operating systems, connectivity configuration, and required permissions, see the following documentation:
Search for and select Microsoft Defender for Cloud.
In the Defender for Cloud menu, select Environment settings.
Select the relevant subscription.
On the Defender plans page, locate the Databases plan and select Select types.
In the Resource types selection window, toggle the SQL servers on machines plan to On.
Select Continue.
Select Save.
Once enabled we use one of the following policy initiatives:
Configure SQL VMs and Arc-enabled SQL servers to install Microsoft Defender for SQL and AMA with a Log analytics workspace (LAW) for a default LAW. This creates resources groups with data collection rules and a default Log analytics workspace. For more information about the Log analytics workspace, see Log Analytics workspace overview.
Configure SQL VMs and Arc-enabled SQL servers to install Microsoft Defender for SQL and AMA with a user-defined LAW. This creates a resource group with data collection rules and a custom Log analytics workspace in the predefined region. During this process, we install the Azure monitoring agent. For more information about the options to install the AMA agent, see Azure Monitor Agent prerequisites.
To complete the installation process, a restart of the SQL server (instance) is necessary for versions 2017 and older.
Enable Defender for SQL on Azure virtual machines using the AMA agent
Prerequisites for enabling Defender for SQL on Azure virtual machines
An active Azure subscription.
Subscription owner permissions on the subscription in which you wish to assign the policy.
SQL Server on machines prerequisites:
Permissions: the Windows user operating the SQL server must have the Sysadmin role on the database.
Extensions: The following extensions should be added to the allowlist:
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
Since we're creating a resource group in East US, as part of the autoprovisioning enablement process, this region needs to be allowed or Defender for SQL can't complete the installation process successfully.
Steps to enable Defender for SQL on Azure virtual machines
Search for and select Microsoft Defender for Cloud.
In the Defender for Cloud menu, select Environment settings.
Select the relevant subscription.
On the Defender plans page, locate the Databases plan and select Select types.
In the Resource types selection window, toggle the SQL servers on machines plan to On.
Select Continue.
Select Save.
Once enabled we use one of the following policy initiatives:
Configure SQL VMs and Arc-enabled SQL servers to install Microsoft Defender for SQL and AMA with a Log analytics workspace (LAW) for a default LAW. This creates a resources group in East US, and managed identity. For more information about the use of the managed identity, see Resource Manager template samples for agents in Azure Monitor. It also creates a resource group that includes a Data Collection Rules (DCR) and a default LAW. All resources are consolidated under this single resource group. The DCR and LAW are created to align with the region of the virtual machine (VM).
Configure SQL VMs and Arc-enabled SQL servers to install Microsoft Defender for SQL and AMA with a user-defined LAW. This creates a resources group in East US, and managed identity. For more information about the use of the managed identity, see Resource Manager template samples for agents in Azure Monitor. It also creates a resources group with a DCR and a custom LAW in the predefined region.
To complete the installation process, a restart of the SQL server (instance) is necessary for versions 2017 and older.
Common questions
Once the deployment is done, how long do we need to wait to see a successful deployment?
It takes approximately 30 minutes to update the protection status by the SQL IaaS Extension, assuming all the prerequisites are fulfilled.
How do I verify that my deployment ended successfully and that my database is now protected?
Locate the database on the upper search bar in the Azure portal.
Under the Security tab, select Defender for Cloud.
Check the Protection status. If the status is Protected, the deployment was successful.
What is the purpose of the managed identity created during the installation process on Azure SQL VMs?
The managed identity is part of the Azure Policy, which pushes out the AMA. It's used by the AMA to access the database to collect the data and send it via the Log Analytics Workspace (LAW) to Defender for Cloud. For more information about the use of the managed identity, see Resource Manager template samples for agents in Azure Monitor.
Can I use my own DCR or managed-identity instead of Defender for Cloud creating a new one?
How many resource groups and Log analytics workspaces are created through the auto-provisioning process?
By default, we create the resource group, workspace and DCR per region that has the SQL machine. If you choose the custom workspace option, only one resource group/DCR is created in the same location as the workspace.
How can I enable SQL servers on machines with AMA at scale?
See Enable Microsoft Defender for SQL servers on machines at scale for the process of how to enable Microsoft Defender for SQL’s autoprovisioning across multiple subscriptions simultaneously. It's applicable to SQL servers hosted on Azure Virtual Machines, on-premises environments, and Azure Arc-enabled SQL servers.
Which tables are used in LAW with AMA?
Defender for SQL on SQL VMs and Arc-enabled SQL servers uses the Log Analytics Workspace (LAW) to transfer data from the database to the Defender for Cloud portal. This means that no data is saved locally at the LAW. The tables in the LAW named SQLAtpStatus and the SqlVulnerabilityAssessmentScanStatus will be retired when MMA is deprecated. ATP and VA status can be viewed in the Defender for Cloud portal.
How does Defender for SQL collect logs from the SQL server?
Defender for SQL uses Xevent, beginning with SQL Server 2017. On previous versions of SQL Server, Defender for SQL collects the logs using the SQL server audit logs.
I see a parameter named enableCollectionOfSqlQueriesForSecurityResearch in the policy initiative. Does this mean that my data is collected for analysis?
This parameter isn't in use today. Its default value is false, meaning that unless you proactively change the value, it remains false. There's no effect from this parameter.
Learn how to connect your Azure subscriptions to Microsoft Defender for Cloud, enabling enhanced security monitoring, compliance management, and the implementation of best practices for threat protection.
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.