Every SQL Server needs Defender for Cloud: End-to-end solution deep dive
Most organizations operate instances of Microsoft SQL Server in their on-premises and cloud networks. As more IT shops are investing in Microsoft’s cloud-native cybersecurity stack based on Microsoft Defender for Cloud and Microsoft Sentinel, a great opportunity leveraging Microsoft Azure Arc technology to extend protection to SQL Servers in any cloud is available. This article will describe why this is so important and of such high value, how to extend the protection into all your environments, and what alerting products you can expect from the solution (as seen in Figure 1).
Figure 1 – Microsoft Defender for Cloud security alerts from two SQL servers, one running in Azure and one in a private cloud on-prem.
TABLE OF CONTENTS OF THIS ARTICLE
1 – SQL instances everywhere need protection
2 – Azure VMs running SQL Server
3 – Non-Azure VM servers running SQL Server
4 – Enable Defender for Cloud workload protection for SQL Server on machines
5 – Operating Defender for Cloud workload protection for SQL Server on machines
6 – In-line security status for all your SQL Servers
7 – Testing Advanced Data Security protection
8 – To learn more
1 – SQL instances everywhere need protection
The SQL Server database engine is ubiquitous in the contemporary datacenter. Whether used as part of another Microsoft product, like on-premises MDM in Configuration Manager (Intune), supporting a mission critical line of business application like SAP, or running a backend database for an infinite number of custom distributed applications, SQL Server has been present for decades in most networks.
These are referred to as “IaaS” (Infrastructure as a Service) instances of SQL Server—as opposed to “PaaS” instances of Azure SQL Server (Platform as a Service). SQL Servers have always been a favorite target of bad actors–in 2019 attacks against SQL server represented 77% of all application attacks and they continue to be a high risk source of hacking attempts.
Defender for Cloud workload protection for SQL Server mitigates the risk of the ‘soft underbelly’ attack surface presented by SQL IaaS servers in your estate. And it does so in a uniform fashion for SQL Server IaaS instances running both in Azure and instances running not in Azure. Figure 2 shows a high-level architecture diagram for Defender for Cloud workload protection for SQL Servers on machines.
Figure 2 – Defender for Cloud workload protection for SQL IaaS servers on Azure and non-Azure servers.
Figure 2 includes these solution components (this article walks through these step-by-step):
- (Lower left) The Microsoft.AzureArcData resource provider needs to be enabled on the Azure subscription.
- (Upper left) SQL Server on machines protection must be enabled in Defender for Cloud in the Azure subscription.
- (Upper center) An Azure VM needs to have the SQLIaaSExtention VM extension installed to be recognized by Azure Resource Manager (ARM) as a SQL virtual machine.
- (Center) An Azure Arc-enabled server needs to have the WindowsAgent.SQLServer VM extension installed to be recognized by ARM as an Azure Arc-enabled SQL Server.
- (Lower center) Optionally configure Arc-enabled machines running SQL Server to automatically have the SQL Server extension installed via Azure Policy. (Recommended)
- (Upper right) The SQLAdvancedThreatProtection solution needs to be installed in the Log Analytics workspace the server is connected to (a coincident but separate step when enabling SQL Server on machines protection in Defender for Cloud).
Protecting SQL servers from database-focused threats
Since SQL servers can implement their own ‘SQL security‘, separate from Active Directory domain security or even local server security, the security of a SQL server requires special care to monitor. Even a robust monitoring solution can miss SQL-specific threats unless you are explicitly looking for that data. That’s a reason Defender for Cloud workload protection for SQL Servers is of such high value.
Figure 1 above includes alerts fired for common SQL Server attacks–SQL injection and brute force–surfaced alongside alerts from other assets protected by Defender for Cloud. The Advanced Data Security features (previously called Advanced Threat Protection (ATP)), of Defender for SQL server can identify Potential SQL injection, Access from unusual location or data center, Access from unfamiliar principal or potentially harmful application, and Brute force SQL credentials. See more details in Advanced Threat Protection alerts.
Key take-aways for the whole solution:
- Defender for Cloud workload protection for SQL Server must be enabled at the Azure subscription
and at the Log Analytics workspace level. - In-Azure VMs must be discovered as SQL virtual machines, and non-Azure VMs must be discovered as SQL Server – Azure Arc resources. A VM extension is used in both cases to effect this.
- All servers must be connected to an Azure Log Analytics workspace with the SQLAdvancedThreatProtection solution installed.
2 – Azure VMs running SQL Server
In-Azure Windows servers running most IaaS instances of SQL server are immediately ready to enable for Defender for Cloud protection. Azure knows an Azure VM is running SQL server when the SQL Server IaaS Agent (SQLIaasExtention VM extension) is installed. When an Azure VM with the SQLIaasExtension is found (as seen in Figure 3), a second ARM resource is created in Azure—the Azure SQL Server—a resource you can deploy Defender for Cloud workload protection for SQL Server to.
Figure 3 – An Azure VM with the SqlIaasExtension VM extension means there is also a SQL virtual machine resource in Azure.
Getting the SqlIaasExtension installed on Azure VMs running SQL Server
Deploying a SQL Server VM Azure Marketplace image through the Azure portal automatically registers the SQL Server VM with the extension. However, if you choose to self-install SQL Server on an Azure virtual machine or provision an Azure VM from a custom VHD, then you must register your Azure VM with the SQL IaaS extension to create an Azure SQL Server.
Automatically register Azure VMs running SQL Server
The recommended method to register all SQL Server versions is to enable automatic registration. Navigate in the Azure portal to the SQL virtual machines blade and push the Automatic SQL Server VM registration button as seen in Figure 4. When automatic registration is enabled, a job runs monthly to detect whether or not SQL Server is installed on all the unregistered VMs in the subscription.
Figure 4 – Enable automatic registration of Azure VMs running SQL Server as SQL virtual machines.
By default, Azure VMs with SQL Server 2016 or later installed will be automatically registered with the SQL IaaS Agent extension when detected by the CEIP service. To install the extension on self-installed and image-sourced SQL Servers individually, consult this link for the procedures.
3 – Non-Azure VM servers running SQL Server
Windows servers not in Azure that are running IaaS instances of SQL server require some planning to be ready to enable for Defender for Cloud protection. These servers can exist as physical servers anywhere, in a private cloud (such as VMware, Azure Stack HCI, or Nutanix), or in another public cloud like Amazon AWS or Google GCP.
The key technology that must be used is Azure Arc. Azure Arc connects to all these clouds and the Windows and Linux servers running in them.
Prerequisites for protecting SQL Server on non-Azure servers
This article assumes that your on-premises and other cloud (non-Azure) Windows servers running SQL Server are already enrolled as Azure Arc-enabled servers. That is, you see them in the Azure Arc -> Servers view of the Azure Portal in Connected status as seen in Figure 5.
Figure 5 – Azure Arc servers are ready to proceed to the next step: Registration also as Azure Arc-enabled SQL Servers.
If your SQL servers are not seen in the Azure Portal as connected Azure Arc servers, then follow one of these two references to get up to speed on Azure Arc for servers in your organization:
Quick-enable one server to Azure Arc:
Deploying Azure Arc to multiple servers: (Recommended)
Alternatively, you can follow this link to install Azure Arc server and Azure Arc SQL server at the same time. If you are running one or more VMware private clouds, consider Azure Arc-enabled VMware to onboard servers (VMware hosts and guest VMs) to Azure Arc.
Supported Azure regions
Arc-enabled SQL Server is available in the following regions:
|
East US |
West US 2 |
South Central US |
France Central |
North Europe |
Korea Central |
Southeast Asia |
|
East US 2 |
Central US |
UK South |
West Europe |
Japan East |
East Asia |
Australia East |
If you Azure Arc server resources don’t exist in one of these regions, consider migrating the Azure Arc servers to one of these regions in order to deploy Azure Arc-enabled SQL Server.
As a best practice, always and broadly deploy Azure Arc for servers!
Enable the Microsoft.AzureArcData resource provider
A prerequisite to deploying Azure Arc-enabled SQL Server is registering the Microsoft.AzureArcData resource provider with the Azure subscription. Make sure this is done by navigating to Subscription -> Resource Providers, typing “AzureArc” in the search bar, and checking the status as seen in Figure 6.
Figure 6 – Register the Microsoft.AzureArcData resource provider with the Azure subscription.
Adding Azure Arc SQL Server to an Azure Arc server
Azure knows an Azure Arc-enabled server is running SQL server when the Azure extension for SQL Server (WindowsAgent.SqlServer VM extension) is installed. When an Azure Arc-enabled server with the WindowsAgent.SqlServer is found (as seen in Figure 7), a second ARM resource is created in Azure—a SQL Server – Azure Arc—a resource you can deploy Defender for Cloud workload protection for SQL Server to—identically as for in-Azure SQL IaaS instances.
Figure 7 – An Azure Arc server with the WindowsAgent.SqlServer VM extension means there is also an SQL server – Azure Arc resource in Azure.
Getting the WindowsAgent.SqlServer extension installed on non-Azure VMs running SQL Server
Azure Arc is the engine that installs, updates, and deletes VM extensions from Azure Arc-enabled servers. You can communicate with and instruct the Azure Connected Machine agent (or ‘Azure Arc agent’) to install the ‘Azure Arc SQL agent’ extension via several methods.
Automatically register Azure Arc-enabled servers running SQL Server
The recommended method to register all SQL Server versions later than SQL Server 2012 is to enable automatic registration. Navigate in the Azure portal to the Azure Arc -> SQL servers blade and push the Automatic Arc-enabled SQL Server Registration button as seen in Figure 8.
Figure 8 – Enable automatic registration of Azure Arc-enabled servers running SQL Server as Azure Arc-enabled SQL servers.
Azure Policy supporting automatic Azure Arc-enabled SQL Server registration
When you enable automatic Azure Arc-enabled SQL Server registration as seen in Figure 8, you are ‘in the background’ creating an Azure Policy assignment and an immediate automatic remediation task to Azure Arc-enabled servers in the selected subscription or resource group. The name of the policy is “Configure Arc-enabled machines running SQL Server to have SQL Server extension installed” as seen in Figure 9.
Figure 9 – Enabling automatic Azure Arc-enabled SQL Server registration assigns this policy and performs immediate remediation.
The user or remediation task service principal onboarding Arc-enabled SQL Server resources requires the following permissions:
- Microsoft.AzureArcData/sqlServerInstances/read
- Microsoft.AzureArcData/sqlServerInstances/write
Alternatively, that is without using automatic Azure Arc-enabled SQL Server registration or Azure Policy, you can connect SQL Server instances installed on multiple Windows machines to Azure Arc using methods at this link.
Notes on Azure Arc-enabled SQL Server
- Beginning with SQL Server 2022 (16.x) you can connect a new SQL Server instance to Azure Arc when you are installing it on Windows Operating System. See Install SQL Server 2022.
- SQL Server on Azure Arc-enabled servers does not support SQL Server Failover Cluster Instances.
- Although Azure Arc for SQL Server supports Linux servers running SQL Server, Defender for SQL advanced data security on IaaS servers is currently only available for Windows servers.
4 – Enable Defender for Cloud workload protection for SQL Server on machines
When your Azure VMs running SQL Server show up in the Azure portal as SQL Virtual Machines, and when your non-Azure VMs running SQL Server show up as Azure Arc SQL Servers, you are ready to enable Defender for Cloud’s database protection plans. There are two settings in the Defender for Cloud Environment settings that need to be configured as seen in Figure 10.
Figure 10 – Enable Defender for Cloud protection in two places: The subscription and the Log Analytics workspace.
Defender plans for Databases
The first place to turn on Defender for SQL servers on machines is shown in Figure 11. Navigate to Microsoft Defender for Cloud -> Environment settings -> Your Azure subscription name -> Select types and make sure SQL servers on machines is On.
Figure 11 – Enable Defender for Cloud protection for SQL Server on machines in the Azure subscription.
Defender plans for SQL servers on machines
The second place to turn on Defender for SQL servers on machines is shown in Figure 12. Navigate to Microsoft Defender for Cloud -> Environment settings -> Your Azure subscription name -> Your Log Analytics workspace. Make sure SQL servers on machines is On.
Figure 12 – Enable Defender for Cloud protection for SQL Server on machines in the Log Analytics workspace.
Turning on protection in the Log Analytics workspace installs the SQLAdvancedThreatProtection solution that can be verified at Log Analytics workspace -> Solutions as seen in Figure 13.
Figure 13 – Enabling Defender for Cloud protection for SQL Server on machines in the Log Analytics workspace installs the SQLAdvancedThreatProtection solution.
Verifying protection status
When servers are enrolled in the solution, they report their protection status to the Log Analytics workspace table “SqlAtpStatus”. Learn more about the KQL available to monitor server status at this link. Here are a few queries to get you started:
-
List status of servers enrolled in the solution:
SqlAtpStatus
| summarize arg_max(TimeGenerated, *) by Computer
-
List servers that are enrolled in the solution but not protected:
SqlAtpStatus
| where Status contains “Not”
| distinct Computer
(Servers reporting Not Protected status will also have a Last Error column to help with troubleshooting.)
Notes on the Log Analytics solution
- The SQL ATP solution is referred to in current documentation as Advanced Data Security.
- Installing the SQL ATP solution in the Log Analytics workspace will deploy the solution to all servers running IaaS instances of SQL Server that are connected to the same workspace—even those not enrolled in Defender for Cloud.
How does it work?
Microsoft has designed Defender for Cloud workload protection for SQL Server
on machines to ensure the minimal effect on SQL server performance. SQL server auditing is not enabled on SQL server instances in most cases, rather, the data necessary to provide SQL threat protection telemetry is gathered externally and using event traces.
The service has a split architecture to balance data uploading and speed with performance:
- Some detectors, including an extended events trace named SQLAdvancedThreatProtectionTraffic, run on the machine for real-time speed advantages.
- Other detectors run in the cloud to spare the machine from heavy computational loads.
Microsoft lab tests of the solution showed maximum CPU usage averaging 3% for peak slices, comparing it against benchmark loads.
SQL Server auditing
The modern Advanced Data Security solution platform does not need a SQL Server audit created, so don’t assume that by deploying the solution, you have enabled any level of SQL auditing.
The SQL ATP overview
does list enabling SQL Auditing as an optional next step: https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-overview.
While auditing isn’t required for SQL Advanced Data Security protection, Microsoft has these considerations for auditing:
- Helps you maintain regulatory compliance, understand database activity, and gain insight into discrepancies and anomalies that could indicate business concerns or suspected security violations.
- Enables and facilitates adherence to compliance standards, although it doesn’t guarantee compliance.
SqlThreatDetection_Audit in legacy situations
The creation of SQL Server audits may have been part of the SQL ATP product at some point in the past, or still exists (although unpublished) to support down-level SQL Server instances (such as SQL Server 2014) that may not be able to use the current/modern “split architecture”.
The creation of a SQL Server audit and audit spec named “SqlThreatDetection_Audit” may be created in the SQL Server configuration when the solution is installed by the Log Analytics agent, but this is not documented by Microsoft at this time.
Additional SQL Server configuration may be necessary if this audit is created. See this link for details on audit settings and SQL Server service account permissions that may be required when enabling audits.
5 – Operating Defender for Cloud workload protection for SQL Server on machines
After completing the steps described in this article, all your in-Azure and non-Azure IaaS instances of SQL Server will be protected by Defender for Cloud. You will view the security health of your SQL Servers on machines instances in the Defender for Cloud portal alongside your other enterprise security coverages as seen in Figure 14.
Figure 14 – SQL Servers on machines are first-class citizens in the Defender for Cloud coverage model.
Elevating the security protection of IaaS instances of SQL Server alongside the other significant protections of Defender for Cloud brings many cybersecurity benefits. Example features of Defender for Cloud that SQL databases benefit from include security posture (Secure Score), regulatory compliance, security recommendations, security alerts, inventory, and workbooks (visualizations and dashboards).
Defender for Cloud workload protection for SQL Server on machines with Microsoft Sentinel
Everyone using Defender for Cloud should also be using Microsoft Sentinel, at the least as a no-cost common alerting console for all the Microsoft Defender products, if not as their primary enterprise Security Information and Event Management (SIEM) platform. Surfacing alerts from SQL Server on machines–alongside other incidents occurring across the estate–is of high investigative value and just makes sense (the ‘single pane of glass’ concept).
Figure 15 shows an alert from Defender for Cloud workload protection for SQL Servers on machines along with other incidents from other security data sources in the same subscription, such as a Fortinet firewall. Having a SIEM with all relevant data sources connected is key to rapidly determining if attacks on assets have succeeded and if lateral movement has occurred.
Figure 15 – Alerts from Defender for Cloud workload protection for SQL Server on machines in the Microsoft Sentinel console.
Connecting Microsoft Defender for Cloud to Microsoft Sentinel enables a robust set of incident notification options so the security and database teams are kept informed of situations. Both Defender for Cloud and Microsoft Sentinel are capable of an infinite number of automated responses to threats, also known as Security Orchestration, Automation and Response (SOAR).
6 – In-line security status for all your SQL Servers
In addition to the top-level dashboards and portal views for Defender for Cloud and Microsoft Sentinel, the current state of protected SQL servers is also part of the resource page for Azure SQL Servers and Azure Arc – SQL Servers. Figures 16 and 17 illustrate very nicely the 100% parity between the protection management of in-Azure IaaS SQL Servers and non-Azure IaaS SQL Servers.
Figure 16 – Microsoft Defender for Cloud protection status for SQL virtual machine (in Azure).
Figure 17 – Microsoft Defender for Cloud protection status for SQL Server – Azure Arc (not in Azure).
In addition to verifying that Defender for SQL is in Protected status for the server, the current status of recommendations, security incidents and alerts, and vulnerability assessment findings are all in one place. This makes it easier for server and app owners to keep tabs on their specific servers and databases without requiring access to enterprise-wide console views.
7 – Testing Advanced Data Security protection
Microsoft has provided a built-in way to test that Defender for SQL (SQL ATP) alerting is live. (Details at this link.) To get starting with testing, run this PowerShell on a SQL server enrolled in the solution:
Import-Module (Get-ChildItem -Path “$Env:ProgramFiles\Microsoft Monitoring Agent\Agent\Health Service State\Resources\” -File SqlAdvancedThreatProtectionShell.psm1 -Recurse).FullName ; Get-Command -Module SqlAdvancedThreatProtectionShell
Figure 18 shows the various test commands built into the SqlAdvancedThreatProtectionShell PowerShell module:
Figure 18 – Test commands available for end-to-end solution validation.
Running a full-up SQL injection detection test
To perform an end-to-end solution validation test of detecting a SQL injection attack, using SQL security (the ‘sa’ account), run this PowerShell on a SQL server in Protected status:
$Secure = Read-Host -AsSecureString
$encrypted = ConvertFrom-SecureString -SecureString $Secure
$Secure2 = ConvertTo-SecureString -String $encrypted
test-SqlAtpInjection -UserName sa -Password $Secure2
Within a few minutes of executing the test command, alerts will surface in Defender for Cloud and Microsoft Sentinel, as seen in Figure 19. If you elected to receive them, an email alert will be directly sent by Defender for Cloud.
Figure 19 – Microsoft Defender for Cloud security alert of SQL injection attack detected on an on-prem SQL Server.
As seen in Figure 19, the details of the SQL injection detection alert include the exact attack code (see “Vulnerable statement”). Related entities links to investigate the account, host, IP address, network connection, and compute process name can be seen in the lower right—these are big helps to quickly understand and triage an incident.
After completing your end-to-end testing, you can rest assured that you have deployed a leading world-class defense of your databases, wherever in the world they might be running.
8 – To learn more
SQL Server on Azure VM documentation
https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines
Azure Arc-enabled SQL Server
https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/overview
Enable Microsoft Defender for SQL servers on machines
https://learn.microsoft.com/en-us/azure/defender-for-cloud/defender-for-sql-usage
Protect Azure Arc-enabled SQL Server with Microsoft Defender for Cloud
https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/configure-advanced-data-security
If you are a stakeholder in a hybrid IT estate, you owe it to yourself to learn about Azure Arc, get a copy of my book: (co-authored by Microsoft’s Steve Buchanan)
Azure Arc-Enabled Kubernetes and Servers: Extending Hyperscale Cloud Management to Your Datacenter
https://www.amazon.com/Azure-Arc-Enabled-Kubernetes-Servers-Hyperscale/dp/1484277678
Tags: #MVPBuzz #HybridCloud #CloudSecurity #DefenderForCloud #SQLServer #AzureArc #AzureHybrid #DefenderforSQL #SqlAtp #DatabaseSecurity