Protect SQL Servers Running On-Premises with Azure Security Center

During Microsoft Ignite in November 2021, Azure Security Center and Azure Defender are now called Microsoft Defender for Cloud. They’ve also renamed Azure Defender plans to Microsoft Defender plans. For example, Azure Defender for Storage is now Microsoft Defender for Storage.

In this article, we will show you how to protect and leverage advanced data security for SQL servers running on-premises with Azure Security Center, and then simulate a SQL Injection (SQLi) attack on SQL database, and finally investigate the alert in Security Center as well as in Azure Sentinel.

Introduction

Microsoft Defender for Cloud 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 main value propositions:

1) Cloud Security Posture Management (CSPM) – Helps 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 other clouds such as Amazon AWS or Google GCP, in addition 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 machines whether they are running on Azure, on-premises, or different cloud environments such as Amazon AWS or Google GCP.

Last year, Microsoft announced advanced data security for SQL servers running on Azure virtual machines. Advanced data security (ADS) 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 that could indicate threats to your database.

The good news is, Microsoft just announced ADS for SQL servers running on-premises and on other cloud environments as well. This (preview) feature brings the same level of protection that was introduced for SQL servers on Azure VMs which includes SQL injection vulnerabilities and attacks, access anomalies by location, principal, or application, brute-force against SQL credentials, and much more.

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 with Azure Defender enabled. Please note that you can use Azure Defender free for 30 days to evaluate threat protection for SQL servers on machines.

3) Log Analytics Workspace – To create a new workspace, follow the instructions in Create a Log Analytics workspace.

4) SQL Server(s) running on-premises or on other cloud providers.

Onboard SQL server on-premises

For SQL servers that are hosted on Azure VMs, you can leverage the automatic provisioning of the Log Analytics (MMA) agent. The installation of the agent can be done automatically (recommended) for Azure VMs or you can install the agent manually. However, for Windows and SQL servers running on-premises, you have two options as follows:

1) Connect any Windows machine to Azure Security Center using Azure Arc.

2) Connect Windows machines to Azure Security Center without Azure Arc.

The steps below illustrate the process of installing and connecting Windows machines to Security Center without Azure Arc:

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) On the Azure Security Center navigation left pane, under General, click the Getting Started option, and click the Get Started tab.

4) Under Add non-Azure computers, click the Configure button as shown in the figure below.

5) On the Onboard servers to the Security Center page, you can create a new workspace or you may use one of the workspaces that you already have. Select the desired workspace, and then click on +Add Servers as shown in the figure below.

6) On the Direct Agent page, download the appropriate Windows Agent (64 or 32-bit version). Make sure to copy the Workspace ID, and Primary Key values to the clipboard; you will need those values when installing the agent on the SQL Server system. Then copy the agent installation file to a shared network location where the SQL machine can access it.

7) For this example, the agent installation will be done on a Windows Server 2019 computer with SQL Server 2019 Standard Edition located on-premises. However, the same set of procedures applies to a non-Azure SQL VM located in a different cloud provider. Log on to the target system, and run the command line below to automate the installation of the MMA agent. Make sure to replace the Workspace_ID and the Workspace_Key with your own values: MMASetup-AMD64.exe /Q:A /R:N /C:”setup.exe /qn ADD_OPINSIGHTS_WORKSPACE=1 OPINSIGHTS_WORKSPACE_AZURE_CLOUD_TYPE=0 OPINSIGHTS_WORKSPACE_ID=<yourworkspaceID> OPINSIGHTS_WORKSPACE_KEY=<yourworkspaceprimarykey> AcceptEndUserLicenseAgreement=1”

8) The OPINSIGHTS_WORKSPACE_AZURE_CLOUD_TYPE parameter in the command above is the cloud environment specification. The default is 0, which represents Azure commercial cloud; only use 1 if you are installing the agent in an Azure Government cloud.

9) To validate the connectivity between the SQL Server on-premises and the workspace, you can use the TestCloudConnection tool. On 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.

10) To activate advanced data security for SQL servers on machines to Azure Security Center, you must first restart your SQL server on-premises.

Set up protection for SQL server on-premises

To enable Advanced Data Security for SQL Servers on-premises or on other cloud environments, 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 servers on machine.

5) Toggle the option for SQL servers on machine (Preview) to ‘Enabled‘ as shown in the figure below and then click Save on the left-hand side.

6) 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.

The next step is to verify SQL server(s) on-premises connectivity to Azure Security Center.

Verify SQL Server on-premises connectivity

In this section, we will verify if the SQL server on-premises is connected and report to Azure Security Center. At the time of this writing, the current Security Center‘s pricing and settings page does not count SQL servers on machine yet. Only Azure SQL virtual machines resources are reflected in the SQL servers on machine resource count below. I expect this page will be updated very soon.

To make sure that your on-premises SQL server is connected to the relevant Log Analytics Workspace is to check the “Advanced settings” tab in the Log Analytics workspace resource page > Connected Sources > Windows Servers as shown in the screenshot below.

To verify that SQL Vulnerability Assessment and SQL Advanced Threat Protection solutions are active for SQL Servers on-premises, you need to click on Windows Computers Connected and then run the query below:

Heartbeat
| where ComputerEnvironment == "Non-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 Non-Azure. The results should be similar to the image below:

To view security recommendations for your SQL servers on-premises in the Security Center dashboard, please go to the Azure Portal and click on the “Security Center” → “Compute & apps” → “VMs and Servers” tab → Filter on “Resource type: Server” as shown in the figure below.

Perform SQL Injection attack

A quick overview of SQL Injection (SQLi) attack. SQLi is a type of injection attack that makes it possible to execute malicious SQL statements. These statements control a database server behind a web application. Attackers can use SQL Injection vulnerabilities to bypass application security measures. They can go around authentication and authorization of a web page or web application and retrieve the content of the entire SQL database. They can also use SQL Injection to add, modify, and delete records in the database.

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

Open PowerShell session with administrative privileges on any machine within the same environment and run the following PowerShell function. Make sure to replace the server name, SQL instance name, username, and password with your values:

Function Sqlinjection {
        Param(
        [Parameter(Mandatory=$false)]
        [string]$ServerName,
        [Parameter(Mandatory=$false)]
        [string]$InstanceName,
        [Parameter(Mandatory=$true)]
        [string]$UserName,
        [Parameter(Mandatory=$true)]
        [string]$Password
    )
     
    if (![String]::IsNullOrEmpty($ServerName))
    {
        $server = $ServerName
    }
    else
    {
        $server = $env:COMPUTERNAME
    }
     
    if (![String]::IsNullOrEmpty($InstanceName))
    {
        $server += "\$InstanceName"
    }
      
    if (![String]::IsNullOrEmpty($UserName))
    {
          Write-Host "Starting SQL injection attack $server"

                $SqlConnection = New-Object System.Data.SqlClient.SqlConnection "Server = $server; User ID=$UserName; Password=$Password;Application Name=$env:computername-testsqli;"
                $SqlConnection.Open();
                $SqlCommand = New-Object System.Data.SqlClient.SqlCommand("select * from sys.databases where database_id like 'l%' --Charbel1", $SqlConnection);
                $SqlCommand.ExecuteNonQuery();
                $SqlCommand = New-Object System.Data.SqlClient.SqlCommand("select * from sys.databases where database_id like ''' --Charbel2", $SqlConnection);
                $SqlCommand.ExecuteNonQuery();
                $SqlCommand = New-Object System.Data.SqlClient.SqlCommand("select * from sys.databases where database_id like '' or 1=1;select '' --Charbel3", $SqlConnection);
                $SqlCommand.ExecuteNonQuery();
     }
}

Sqlinjection -ServerName <'SQLServerName'> -InstanceName <'SQLInstanceName'> -UserName <'UserName'> -Password <'Password'> -Verbose

The script is simulating very basic SQL injection scenarios. So you can imagine there is an input box on some front-end Web App UI and the content of that box is concatenate to a query in the background.

For a SQL injection attack to make sense, you need to be in the context of some user-facing input channel, but this script is simulating common known parentheses escaping patterns so you could check Advanced Threat Protection (ATP) for SQL easily without setting up a front-end website.

Please note that Advanced-Data Security (ADS) SQL Injection detector can catch things more sophisticated than that, it’s just a basic SQL injection attack. This detection applies to SQL Servers whether they are running on Azure, on-premises, or different cloud providers.

Investigate SQL alert in MDC

After running the script above, you need to wait around 10 minutes for the alert to kick in.

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.

If you’ve already integrated Azure Security Center with Azure Monitor, then you will also receive a notification based on the action group that you specified. In my example, I am using email notifications. 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:

That’s it there you have it!

Summary

In this article, we showed you how to protect SQL servers running on-premises by leveraging Azure Security Center (Advanced Data Security), and then we simulated a SQL Injection (SQLi) 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, and for SQL Servers running on-premises or on other cloud providers.

• 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 the suspicious activity details and recommended actions to investigate and mitigate the threat.

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-