Introduction
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:
- Vulnerability Assessment is a service that helps you identify and remediate vulnerabilities in your database environments to improve your security posture
- Advanced Threat Protection detects suspicious activities related to your databases and alerts you with details and recommended actions.
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
Preparation
You need at least the Security Admin role to enable Azure Defender for SQL. For more information about roles and privileges, visit this article.
Execution
Method 1: Automatically create a SQL VM through the Azure Marketplace (recommended)
Creating the VM in Azure & setting up SQL server on it
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.
Enable Microsoft Defender for SQL servers on machines
Follow the guidance here.
- Step 1. Install the agent extension
- Step 2. Provision the Log Analytics agent on your SQL server's host:
- Step 3. Enable the optional plan in Defender for Cloud's environment settings page:
Step 1. Install the SQL IaaS agent extension
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:
Step 2. Provision the Log Analytics agent on your SQL server's host
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:
- In the Azure Portal, find your virtual machine and select the virtual machine resource (not the SQL virtual machine resource, which has also been created). Ensure that the VM is started by selecting Start on the VM’s Overview (ensuring that the VM is started is a prerequisite for the log analytics agent to be able to be installed on it).
- Then go to the Microsoft Defender for Cloud blade.
- Go to Recommendations, and in the search box type Log Analytics agent should be installed on your virtual machine, and click on the associated recommendation.
- Then under the Affected resources, in the Unhealthy resources tab, select the VM that you just created.
- Then click Fix.
- In the pop-up that appears, select Create a new workspace
- In the new screen, select a resource group and a name for the workspace.
- Then click Review + Create, and once the validation has passed, click Create. Now you have a customer-created Log Analytics workspace.
- Then go back to the previous tab where the following recommendation is: Log Analytics agent should be installed on virtual machines
- In the Fixing resources blade, under the Workspace ID, find and select the newly created workspace, and click Fix 1 resource.
- After approx. 24 hours, the VM will appear under the Healthy Resources tab in the Log Analytics agent should be installed on virtual machines recommendation.
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 the Azure portal, go to the Microsoft Defender for Cloud service.
- Under Management, select Environment settings. Then click in the management groups shown until you find your subscription and click it
- Make sure that Enable all Microsoft Defender for Cloud plans is selected.
- Then ensure that the SQL servers on machines Defender plan is ON (as shown below) and click Save at the top of the page to commit the change.
Enable the optional plan in Defender for Cloud's environment settings page on your workspace
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:
- In the Azure portal, go to the Microsoft Defender for Cloud service.
- Under Management, select Environment settings. Then click in the management groups shown until you find your subscription, and then underneath the subscription, select the workspace you created, and click it
- Making sure that Enable all Microsoft Defender for Cloud plans is selected, then ensure that the SQL servers on machines Defender plan is ON (as shown below) and click Save at the top of the page to commit the change.
Validate the alert
- Connect to the virtual machine you created using RDP.
- Open PowerShell and paste the query below on one line:
Import-Module (Get-ChildItem -Path "$Env:ProgramFiles\Microsoft Monitoring Agent\Agent\Health Service State\Resources\" -File SqlAdvancedThreatProtectionShell.psm1 -Recurse).FullName ; Get-Command -Module SqlAdvancedThreatProtectionShell
- Then, from the options that appear, select Test-BruteForce. This will simulate a brute-force attack on the SQL VM.
- To validate this alert in the Azure Portal, go to Microsoft Defender for Cloud and go to Alerts.
- Add a new filter, and filter for Affected resource equals the name of the VM you created, and press enter.
- You will then see the following alert:
Method 2- Register an existing SQL Virtual Machine manually
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.
- Creating the VM & Setting up SQL server on it
- Installing IaaS agent extension (3 parts)
Consideration 1 for Register an existing SQL Virtual Machine manually: Creating the VM in Azure & setting up SQL server on it
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:
- Create a virtual machine in Azure that has Windows Server 2019 Datacentre Virtual Machine. Follow the Quickstart instructions here.
- Connect to your VM. To do this, you can follow the instructions here.
- Once you are connected from within the VM, you need to go to a browser and to download the SQL server on the VM (you can download from https://www.microsoft.com/en-us/sql-server/sql-server-downloads). You need to enable that website link on the browser. Download the Developer edition as shown in the image below and select the basic tier.
- You also need to download and install SSMS (SQL Server Management Studio), by following the instructions here. This link also provides the guidance to connect to a SQL server instance.
Update 2 for Register an existing SQL Virtual Machine manually: Installing IaaS agent extension
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.
- Go to the Azure portal and search for Subscriptions and select the subscription you want. Under Settings on the left navigation pane, click Resource Providers. Select Register or Re-register for Microsoft.SqlVirtualMachine, depending on its current status.
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:
- Open a cloud shell terminal from the top navigation bar as shown the example below:
- Select Bash (Cloud Shell) from the left drop-down arrow selected, if it’s not already as shown the example below:
- If you don’t already have a storage account selected where to save the script, then choose a storage account
- Register in lightweight mode, using: # Register Enterprise or Standard self-installed VM in Lightweight mode, but make sure to paste in your VM’s details instead of <vm_name>, <resource_group_name>, <vm_location>, <license_type>. For license type, use PAYG.
az sql vm create --name <vm_name> --resource-group <resource_group_name> --location <vm_location> --license-type <license_type>
- Then upgrade to full mode, but using your VM’s details instead of <vm_name> and <resource_group_name>.
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:
- Sign in to the Azure portal.
- Go to your SQL Server VMs.
- Select your SQL Server VM from the list. If your SQL Server VM is not listed here, it likely hasn't been registered with the SQL IaaS Agent extension.
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.
Conclusion
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.
Reviewers
Special thanks to YuriDiogenes , Tomer Rotstein and David Trigano for reviewing this article.
Microsoft
