Welcome back to the third installment of our multi part blog series on enhancing the security and management of Azure Arc Enabled SQL Server. In our previous posts, we explored how to evaluate SQL Server configurations using Best Practices Assessment for Azure Arc Enabled SQL Server (Post 1) and learned how to efficiently onboard multiple SQL servers at scale using Azure Policy (Post 2). Now, in multi part series, we will take another crucial step towards fortifying your SQL Server environment by enabling Microsoft Defender for Cloud on Arc Enabled SQL Server Machines.
Microsoft Defender offers robust protection and detection capabilities for SQL servers, safeguarding your databases from potential threats and vulnerabilities. In this blog post, we will explore how Microsoft Defender for SQL servers can help detect and mitigate potential threats to your databases. We'll walk you through the process of enabling Microsoft Defender for Cloud on Azure Arc enabled SQL Server machines, generating incidents and alerts, and reviewing security recommendations to bolster your SQL Server security.
Understanding Microsoft Defender for SQL Servers
Microsoft Defender offers robust protection by detecting anomalous activities that indicate unusual and potentially harmful attempts to access or exploit SQL server databases.
Alerts for Suspicious Database Activities: Microsoft Defender provides real-time alerts for suspicious database activities, potential vulnerabilities, SQL injection attacks, and unusual database access and query patterns.
Protection for Hybrid Environments: Extend your Azure-native SQL server protections to safeguard SQL servers hosted in Azure, multicloud environments, and on-premises machines with Microsoft Defender. It fully supports hybrid environments and safeguards SQL servers hosted in Azure, multicloud environments, and even on-premises machines. Let's explore the supported configurations:
- SQL Server on Virtual Machines
- On-premises SQL servers
- Azure Arc-enabled SQL Server
- SQL Server running on Windows machines without Azure Arc
- Multicloud SQL servers:
- Connect your AWS accounts to Microsoft Defender for Cloud
- Connect your GCP project to Microsoft Defender for Cloud
Identifying Vulnerabilities and Mitigating Threats
The Microsoft Defender plan includes powerful functionality for identifying and mitigating potential database vulnerabilities, ensuring the robustness of your SQL server deployments. Through a comprehensive vulnerability assessment service, you can discover, track, and remediate potential database vulnerabilities. The assessment scans provide a comprehensive overview of your SQL machines' security state and detailed security findings.
Prerequisites:
- Have an Azure account with an active subscription. If needed, create a free Azure Account
- You can deploy this lab environment by cloning the repository from the Azure Arc Git repo (Link) using powershell or Azure Cloud Shell.
git clone -b lu_arc_sql https://github.com/microsoft/azure_arc.git - SQL Server machine ( JSLU-Win-SQL-02) is deployed and enabled with Azure Arc extension
- Your Windows-based SQL Server instance is connected to Azure. Follow the instructions to onboard your SQL Server instance to Azure Arc-enabled SQL Server.
- Verify Arc connected machine agent prerequisites. The Arc agent must be running in the typical 'full' mode.
- Verify Arc connected machine agent network requirements
- Register resource providers. Specifically:
- Microsoft.AzureArcData
- Microsoft.HybridCompute
- For instructions, see Register resource providers.
-
Your user account is assigned one of the Security Center Roles (RBAC)
- Enable VM insights for Azure Monitor Agent
Enabling Microsoft Defender for Cloud on Arc enabled SQL Server Machines
Step 1: Setting up Microsoft Monitoring Agent
1. Access Azure Arc by searching for it on the Azure home page and selecting Azure Arc.
2. Navigate to All Azure Arc Resources and choose your SQL Server instance (JSLU-Win-SQL-02) Server - Azure Arc resource.
3. Check if Microsoft Monitoring Agent is installed under the Extensions section. If it's in the status of Creating, wait for it to become Succeeded.
4. Return to the extensions page on your Arc-enabled server and refresh it until the status for MicrosoftMonitoringAgent shows "Succeeded.
5. If the extension doesn't exist, follow Enable VM insights for Azure Monitor Agent to configure Microsoft Monitoring Agent.
6. Once Microsoft Monitoring Agent is successfully installed, proceed with adding Log Analytics Agent - Azure Arc.
7. Open another Azure Portal window and navigate to your resource group (ArcSql-Levelup). Then, choose your arcsqllevelup Log Analytics Workspace.
8. In the left menu blade, click on Agents, and expand the Log Analytics agent instructions.
9. Copy the Workspace ID and Primary key (Workspace Key) from your Log Analytics Workspace and paste them into the add extension page. Click Review + create and then Create.
10. Upon completion, you will have the following extensions successfully installed.
Step 2: Enabling Defender for Cloud
1. Search for "defender" on the Azure home page and select Microsoft Defender for Cloud.
2. Click on "Inventory" from the left blade.
3.. Navigate to the Inventory page and filter Resource types to "servers - azure arc and sql server - azure arc."
4. Check if Defender is already turned on for your Arc-enabled servers and SQL Servers at the subscription level. If not, enable it at the Resource Group level.
5. Expand the Tenant Root Group, and subsequently, select your subscription.
6. Enable the plans for Servers and Databases, and proceed to click on "Select types" under Pricing for Databases.
7. On the Resource types selection page, make sure to enable only "SQL servers on machines" and then proceed by clicking "Continue." Next, Save the settings.
8. Return to the Environment settings page and click on "Refresh" to view the enabled plans at the subscription level.
9. Head over to the Inventory page, where you will notice that Defender for Cloud is now activated for both your Arc-enabled servers and SQL servers.
Step 3: Generating Defender for Cloud Incidents and Alerts
(Prerequisite)
- You can deploy this lab environment by cloning the repository from the Azure Arc Git repo (Link) using Powershell or Azure Cloud Shell
- For this demo, the PowerShell script from the provided GitHub repository link is utilized to simulate a brute force attack.
1. Log in to SQL Server machine ( JSLU-Win-SQL-02) that has been deployed for demo. Open PowerShell, change the directory to C:\ArcBoxLevelup, and run .\testDefenderForSQL.ps1, waiting for it to complete.
Step 4: Reviewing Alerts from Defender
1. Search for "Azure Arc" on the Azure home page and select Azure Arc.
2. Choose your Arc enabled SQL Server machine (JSLU-Win-SQL-02) SQL Server - Azure Arc resource.
3. Select Microsoft Defender for Cloud from the left menu blade and review the number of Security alerts.
4. Explore further into the Recommendations section by selecting "View additional recommendations" in Defender for Cloud.
5. Scroll further to find your alerts and click on the suspected brute-force attack attempt.
6. Click anywhere on the alert to view its description.
7. Click View full details for more information on the user, client IP address, and cause.
8. Take action to review logs and mitigate the attack, suppress alerts, or configure email notifications.
9. Repeat the process for the Potential SQL Injection alert.
10. Navigate to the Search panel, enter "Resource Graph," and then proceed to select "Resource Graph Explorer."
11. Create a new query, execute it, and then choose "Formatted results" to visualize the data. Finally, pin the results to your dashboard for easy access and reference.
securityresources
| where type == "microsoft.security/locations/alerts"
| project TimeGeneratedUtc =
properties.TimeGeneratedUtc, Id = tolower(tostring(properties.ResourceIdentifiers[0].AzureResourceId))
, AlertName = properties.AlertDisplayName, Severity =
properties.Severity, Intent = properties.Intent, Status = properties.Status
, AlertURL = properties.AlertUri
| join kind = inner ( resources
| where type startswith "microsoft.hybridcompute/machines"
| project Id = tolower(tostring(id))
) on Id
| project TimeGeneratedUtc, Id, AlertName, Severity, Intent, Status, AlertURL
Summary:
In this blog post, we delve into the powerful capabilities of Microsoft Defender for SQL servers, a robust security solution designed to protect your databases from potential threats and vulnerabilities. The post explores how the Defender plan detects and alerts on anomalous activities, potential exploits, and SQL injection attacks, providing enhanced protection for your SQL servers. The blog further discusses how Microsoft Defender extends its protective capabilities to Azure-native SQL servers and hybrid environments, enabling seamless security across various deployment scenarios, including on-premises and multicloud environments.
Thanks for reading this blog post. If you have any comments or questions, don’t hesitate to leave them in the comments section. To learn more about the services used in this post, check out the following resources:
- Using Azure Policy to onboard multiple SQL Servers at scale to Azure Arc-enabled SQL Server - Part 1
- Evaluate SQL Server configuration using Best practices assessment for Azure Arc Enabled SQL Server
- The Azure Arc-enabled SQL Server lab environment can be deployed either through the ARM template in Azure Portal or by cloning cloning the repository from the Azure Arc Git repo (Link)