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 2022 on Windows Server 2022 Datacenters Virtual Machine that is hosted in Azure. You will do this through the Azure Marketplace.
Enable Microsoft Defender for SQL servers on machines
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.
- Look for Databases and under Pricing* click on Select types >. Ensure that Azure SQL Databases toggle is On.
- Then under Monitoring coverage click on Settings >, under Configuration click on Edit configuration, add a Custom Workspace or leave it on Default Workspace. Ensure that Register Azure SQL Server instances by enabling SQL IaaS extension automatic registration is checked and then click on Apply.
5. Click on Continue and then Save at the top of the page to commit the change.
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 , hspinto, Tomer Rotstein and David Trigano for reviewing this article.
