Protect SQL Servers Running On-Premises With Azure Security Center

9 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) – 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 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 machines whether they are running on Azure, on-premises or in 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 were 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.

In this article, I 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.

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 provider.

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. Protect SQL Servers Running On-Premises With Azure Security Center 1
  5. On the Onboard servers to 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. Protect SQL Servers Running On-Premises With Azure Security Center 2
  6. In 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. Protect SQL Servers Running On-Premises With Azure Security Center 3
  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 apply 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 Protect SQL Servers Running On-Premises With Azure Security Center 4
  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. Protect SQL Servers Running On-Premises With Azure Security Center 5
  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. Protect SQL Servers Running On-Premises With Azure Security Center 6
  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 SQL server on-premises is connected and reporting 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.

Protect SQL Servers Running On-Premises With Azure Security Center 7

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.

Protect SQL Servers Running On-Premises With Azure Security Center 8

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:

Protect SQL Servers Running On-Premises With Azure Security Center 9

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

Protect SQL Servers Running On-Premises With Azure Security Center 10

Perform SQL Injection attack

A quick overview about SQL Injection (SQLi) attack. SQLi is a type of an 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 own 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 a 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 actually 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 for SQL Servers whether they are running on Azure, on-premises, or in different cloud provider.

Investigate SQL alert in ASC

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. Protect SQL Servers Running On-Premises With Azure Security Center 11

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 notification. You can find more details on how to integrate Azure Security Center with Azure Monitor here.

Protect SQL Servers Running On-Premises With Azure Security Center 12

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:

Protect SQL Servers Running On-Premises With Azure Security Center 13

That’s it there you have it!

Summary

In this article, I 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 provider.

  • 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-

Related Posts

About Charbel Nemnom 577 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.