Microsoft Defender for SQL contains several plans: Microsoft Defender for Azure SQL database servers, Microsoft Defender for SQL servers on machines, and there is a third plan for open-source relational databases. This article is focused on validating alerts for SQL Server on Machines.
Once you enable the Defender for Azure SQL database servers or the Defender for SQL servers on machines plan, you get the following capabilities that together protect your SQL environments from cyberattacks. These capabilities are:
In this article, you will learn how to validate the alert that is triggered when a suspicious activity is detected on your SQL server on a virtual machine. You will also learn how to simulate this alert in a SQL VM that has SQL installed automatically through the Azure Marketplace, or manually on the VM.
Method 1: Automatically create a SQL VM through the Azure Marketplace (recommended)
Method 2 Additional Considerations: Register an existing SQL Virtual Machine manually
You need at least the Security Admin role to enable Azure Defender for SQL. For more information about roles and privileges, visit this article.
In this method of the article, you will set up SQL Server 2019 on a Windows Server 2019 Datacentres Virtual Machine that is hosted in Azure. You will do this through the Azure Marketplace.
Follow the guidance here.
Deploying a SQL Server VM Azure Marketplace image through the Azure portal automatically registers the SQL Server VM with the SQL IaaS agent extension, in lightweight mode (which is sufficient).
See more:
The step shown below requires you to wait for approximately 24 hours for the VM to appear in Microsoft Defender for Cloud.
If you prefer to provision the Log Analytics agent manually but straight away (instead of waiting for 24 hours), see the guidance here.
Otherwise, if you can wait up to 24 hours after creating the VM, then you can perform the following instructions as part of this step:
Note: If you have Auto-Provisioning configured as on for installing the log analytics agent on your resources
Step 3- Enable the optional plan in Defender for Cloud's environment settings page on your subscription
You need to enable Microsoft Defender for SQL servers on machines on your subscription:
In step 2, when creating the log analytics workspace, you will have created a log analytics workspace through portal. Then you connected SQL VM to the workspace (There are two ways to do this- either manually but instant, or wait for the recommendation to appear in ~24 hours, and then connect them through that recommendation). Here, I have chosen to wait for 24 hours before the next steps.
Now, you need to connect Microsoft Defender for Cloud to the workspace in the environment settings.
You need to enable Microsoft Defender for SQL servers on machines on your subscription:
Import-Module (Get-ChildItem -Path "$Env:ProgramFiles\Microsoft Monitoring Agent\Agent\Health Service State\Resources\" -File SqlAdvancedThreatProtectionShell.psm1 -Recurse).FullName ; Get-Command -Module SqlAdvancedThreatProtectionShell
The additional considerations that follow are if you’re installing SQL manually on a VM. Everything else, can be followed as per the steps in Method 1 of automatic VM.
In this scenario you will set up SQL Server 2019 on a Windows Server 2019 Datacentre Virtual Machine that is hosted in Azure. You can use the article below as your main reference:
Provision SQL Server on Azure VM (Azure portal) - SQL Server on Azure VMs | Microsoft Docs
The overall steps are in the following order:
Part 1 of Installing IaaS Agent Extension- Register your SQL Server VM with the SQL IaaS Agent extension
Register your SQL Server VM with the SQL IaaS Agent extension as explained here.
Part 2 of Installing IaaS Agent Extension - Upgrade SQL Server VMs to full
SQL Server VMs that have registered the extension in lightweight mode need upgrade to full using the Azure portal, the Azure CLI, or Azure PowerShell. SQL Server VMs in NoAgent mode can upgrade to full after the OS is upgraded to Windows 2008 R2 and above.
Keep in mind that it is not possible to downgrade, in this case you will need to unregister the SQL Server VM from the SQL IaaS Agent extension. Doing so will remove the SQL virtual machine resource, but will not delete the actual virtual machine.
To learn more about full mode, see management modes.
To register a SQL Server VM in full mode with the Azure CLI you should follow the steps below:
az sql vm create --name <vm_name> --resource-group <resource_group_name> --location <vm_location> --license-type <license_type>
az sql vm update --name <vm_name> --resource-group <resource_group_name> --sql-mgmt-type full
Part 3 of Installing IaaS Agent Extension - Verify registration status for the VM to be a SQL Server VM
You can verify if your SQL Server VM has already been registered with the SQL IaaS Agent extension by using the Azure portal, the Azure CLI, or Azure PowerShell.
Verify the registration status with the Azure portal using the following steps:
View the value under Status. If Status is Succeeded, then the SQL Server VM has been registered with the SQL IaaS Agent extension successfully.
Note: This article only goes through natively creating a SQL VM in Azure. If you wish to use a SQL server outside of Azure that you’d like to test, make sure to follow this article about SQL Server on Azure Arc-enabled servers | Microsoft Docs. Then, look at this article for getting it connected to Microsoft Defender for Cloud.
By the end of this article, you should be able to validate an alert coming from Microsoft Defender for SQL on machines and the importance of having this level of threat detection for your SQL on machine workloads.
P.S. Subscribe to our Microsoft Defender for Cloud Newsletter to stay up to date on helpful tips and new releases and join our Tech Community where you can be one of the first to hear the latest Microsoft Defender for Cloud news, announcements and get your questions answered by Azure Security experts.
Special thanks to @Yuri Diogenes , Tomer Rotstein and David Trigano for reviewing this article.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.