Protect SQL Servers Running On Azure VMs With Azure Security Center

7 min read

Introduction

Azure Security Center gives you complete visibility and control over the security of hybrid cloud workloads, including compute, network, storage, identity, and application workloads. Azure Security Center (ASC) has two mains value proposition:

  1. Cloud Security Posture Management (CSPM) – Help you prevent misconfiguration to strengthen your security posture for all different types of cloud workloads and resources in Azure (IaaS, PaaS, and SaaS).
  2. Cloud Workload Protection Platform (CWPP) – Protect against threats for servers whether they are running in Azure, on-premises or in different clouds such as Amazon AWS or Google GCP, in additional to cloud-native workloads such as Web Apps, Kubernetes, Key Vaults, as well as for SQL databases (PaaS/VM) and storage accounts.

Advanced Data Security (ADS) is one of many features that is included in Azure Security Center that falls under the Cloud Workload Protection Platform (CWPP) which is something you must consider for your SQL Servers running on Azure virtual machines.

Last year, Microsoft announced advanced data security for SQL servers running on Azure virtual machines. Advanced data security for SQL Servers on Azure Virtual Machines is a unified package for advanced SQL security capabilities which is in public (preview) at the time of this writing. This feature includes functionality for identifying and mitigating potential database vulnerabilities and detecting anomalous activities such as SQL injection, SQL brute force attack that could indicate threats to your database.

In this blog post, I will show you how to protect and leverage Advanced Data Security (ADS) for SQL servers running on Azure VMs with Azure Security Center, and then simulate a SQL brute force attack and finally investigate the alert in Security Center as well as in Azure Sentinel.

Prerequisites

To follow this article, you need to have the following:

  1. Microsoft Azure subscription. If you don’t have an Azure subscription, you can create a free one here.
  2. Azure Security Center – Standard Tier enabled.
  3. Log Analytics Workspace – To create a new workspace, follow the instructions in Create a Log Analytics workspace.
  4. SQL Server(s) running on Azure VM. In this example, I am using the Free SQL Server License: SQL 2019 Developer on Windows Server 2019.

Set up protection for SQL Server

To enable Advanced Data Security for SQL servers on Azure Virtual Machines, this should be done at the subscription/workspace level:

  1. Open Azure Portal and sign in with a user who has Security Admin privileges.
  2. On the left navigation pane, click Security Center.
  3. From Security Center’s sidebar, open the Pricing & settings page.
  4. Select the desired subscription or workspace for which you want to enable Advanced Data Security for SQL Server on Azure VMs.
  5. Toggle the option for SQL servers on machine (Preview) to enabled as shown in the screenshot below and then click Save on the left-hand side. Please note that SQL server protection on IaaS VM is free during the (Preview) period as shown in the screenshot below.
  6. Behind the scene, Azure Security Center will go ahead and provision/install the Microsoft Monitoring Agent (MMA) on the Azure SQL VM. To activate advanced data security solutions and report to Azure Security Center, you must first restart your SQL Server IaaS VM on Azure.

Now Advanced Data Security for SQL Servers will be enabled on all SQL Servers connected to the selected workspace or the default workspace of the selected subscription.

Verify Advanced data security for SQL Server on Azure VM

In this section, we need to verify the SQL Server on Azure VM is connected and reporting to Azure Security Center. The Azure Security Center dashboard will show SQL servers count running on Azure VMs. In this example, I have only one SQL server as IaaS VM. Please note that only Azure SQL virtual machines resources are reflected in the SQL servers resource count below. However, this is not the case for SQL servers running on-premises.

To validate the connectivity between the SQL server running on Azure VM and the workspace, you can use the TestCloudConnection tool. Login to the SQL server computer, open the command prompt (cmd) and navigate to the folder C:\Program Files\Microsoft Monitoring Agent\Agent. From there, execute the command TestCloudConnection.exe, and if the connectivity is working properly, you should see all tests succeeded as shown in the output below.

To verify that SQL Vulnerability Assessment and SQL Advanced Threat Protection solutions are active for SQL servers on Azure VMs, you need to go to the Log Analytics workspace where Azure Security Center is reporting to.

In the Log Analytics workspace under General | Logs run the query below:

Heartbeat
| where ComputerEnvironment == "Azure"
| where Solutions has " \"sqlVulnerabilityAssessment\", \"sqlAdvancedThreatProtection\"" 
| summarize arg_max(TimeGenerated, *) by SourceComputerId | top 500000 by Computer asc | render table

If you click and open the results for that particular SQL IaaS VM, you will see the full details next to the Solutions sqlVulnerabilityAssessment” and “sqlAdvancedThreatProtection“. The Computer Environment is also Azure. The results should be similar to the image below:

To view security recommendations for your SQL servers on Azure VM in the Azure Security Center dashboard, please go to the Azure Portal and click on “Security Center” → “Data & storage” → “SQL” tab → Filter on “Resource type: SQL virtual machine” as shown in the screenshot below.

Please make sure to address and review all security recommendations for your SQL databases. In this example, I have the following Unhealthy resources. The Severity is grouped by High, Medium and Low.

Perform SQL brute force attack

A quick overview of MS SQL brute force attack flow:

  1. The attacker uses port scanning techniques to identify the open ports on the target SQL system.
  2. Once the attacker found port 1433/1434 in an open state, it starts brute-forcing the ‘SA‘ login which is a default administrator account. The attacker will also use other accounts and not necessarily the ‘SA‘ login.
  3. The attacker usually holds a dictionary with the most common passwords used by database administrators, thus making the attack faster and successful in most cases.
  4. Once the attacker has access, he gets complete access to the database. The attacker may further exploit the system if Microsoft SQL server has vulnerabilities allowing the attacker to gain complete access of the operating system

In this step, I will simulate a basic SQL brute force attack by running a PowerShell script targeting my SQL database where the MMA agent is installed and connected to Azure Security Center.

Open a PowerShell session with administrative privileges on any machine that has access to the SQL server and run the following PowerShell function. Make sure to replace the server name, SQL instance name, and username with your own values:

Function Sqlbruteforce {
[CmdletBinding()]
Param(
    [Parameter(Mandatory=$false)]
    [string]$ServerName,
    [Parameter(Mandatory=$false)]
    [string]$InstanceName,
    [Parameter(Mandatory=$false)]
    [string]$UserName,
    [Parameter(Mandatory=$false)]
    [int]$AttemptCount=30
) 

if (![String]::IsNullOrEmpty($ServerName))
{
    $server = $ServerName
}
else
{
    $server = $env:COMPUTERNAME
} 

if (![String]::IsNullOrEmpty($InstanceName))
{
    $server += "\$InstanceName"
}

if ([String]::IsNullOrEmpty($UserName))
{
    for ($i = 0; $i -lt $AttemptCount; $i++)
    {
        Write-Progress -Activity "Simulating username bruteforce attempts" -PercentComplete (($i/$AttemptCount) * 100) -ID 1 -CurrentOperation "Failed Login on different users"
        try
        {
            $SqlConnection = New-Object System.Data.SqlClient.SqlConnection "Server = $server; User ID=user$i; Password=password"
            $SqlConnection.Open();
        }
        catch 
        {
        }
    }
}
else
{
    for ($i = 0; $i -lt $AttemptCount; $i++)
    {
        Write-Progress -Activity "Simulating password bruteforce attempts" -PercentComplete (($i/$AttemptCount) * 100) -ID 1 -CurrentOperation "Failed Login on different passwords"
        try
        {
            $SqlConnection = New-Object System.Data.SqlClient.SqlConnection "Server = $server; User ID=$UserName; Password=password$i"
            $SqlConnection.Open();
        }
        catch 
        {
        }
    }
}

}

Sqlbruteforce -ServerName "vm-sql-2019-asc" -InstanceName "SQLINSTANCE" -UserName "sa" -Verbose

Please note that Advanced Data Security (ADS) for brute force attack and SQL Injection detector can catch things more sophisticated than that, it’s just a basic SQL brute force attack.

Investigate SQL alert

To view security alerts for your SQL servers on-premises in the Azure Security Center Portal, please go to the Azure Portal and click on “Security Center” → “Security alerts” → Click on “Filter” and in “Environment”, make sure only “Non-Azure” is selected” as shown in the screenshot below. Please note that the alert will take around 15 minutes to show up in Security Center | Security alerts after the attack.

If you’ve already integrated Azure Security Center with Azure Monitor, then you will receive a notification based on the action group that you specified. In my example, I am using email notification. You can find more details on how to integrate Azure Security Center with Azure Monitor here.

And if you’ve connected Azure Security Center to Azure Sentinel which I highly recommend as described in this article, then you can investigate this alert further by running the following Query from Azure Sentinel | Logs:

SecurityAlert
| where ProviderName == "SQLThreatDetection" 

If you click and open any of the results and then expand the ExtendedProperties, you will see the full details where you can investigate further and understand the behavior of this suspicious security alert. The results should be similar to the image below:

Summary

In this article, I showed you how to protect SQL servers running on Azure IaaS VMs by leveraging Azure Security Center (Advanced Data Security), and then we simulated a brute force attack and finally we investigated the alert in Security Center as well as in Azure Sentinel.

Advanced data security in Azure Security Center provides a set of advanced SQL security capabilities, consisting of Vulnerability assessment and Advanced Threat Protection for SQL Servers running on Azure VMs.

  • Vulnerability assessment is an easy to configure service that can discover, track, and help you remediate potential database vulnerabilities. It provides visibility into your security state and includes the steps to resolve security issues and enhance your database fortifications.
  • Advanced Threat Protection detects anomalous activities indicating unusual and potentially harmful attempts to access or exploit your SQL server. It continuously monitors your database for suspicious activities and provides action-oriented security alerts on anomalous database access patterns. These alerts provide suspicious activity details and recommended actions to investigate and mitigate the threat.

To learn more on how to protect SQL servers running on-premises with Azure Security Center, please check the following article.

To learn more about Azure Security Center, check the official documentation from Microsoft.

To learn more about Azure Sentinel, check the official documentation from Microsoft.

__
Thank you for reading my blog.

If you have any questions or feedback, please leave a comment.

-Charbel Nemnom-

About Charbel Nemnom 570 Articles
Charbel Nemnom is a Cloud Architect, Swiss Certified ICT Security Expert, Microsoft Most Valuable Professional (MVP), and Microsoft Certified Trainer (MCT), totally fan of the latest's IT platform solutions, accomplished hands-on technical professional with over 17 years of broad IT Infrastructure experience serving on and guiding technical teams to optimize the performance of mission-critical enterprise systems. Excellent communicator is adept at identifying business needs and bridging the gap between functional groups and technology to foster targeted and innovative IT project development. Well respected by peers through demonstrating passion for technology and performance improvement. Extensive practical knowledge of complex systems builds, network design, business continuity, and cloud security.

Be the first to comment

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.