On-prem SQL Security with Azure Arc, Defender & Sentinel (part 1)

When organizations protect their assets, it's still mostly servers (AV, EDR, Vulnerability management), but the actual application running on the server doesn't get much attention.

One of the essential applications almost every organization run is SQL Server. Organizations store various data within SQL, many times crown jewels are in SQL databases, but yet, there's not much security involved.

Azure Arc and Arc enabled SQL Server enables us to take that security to whole new level. I'm going to introduce those means we have in the toolbox today.

1. Azure Arc enabled SQL Server (this article)
2. Defender for Cloud (especially Defender for SQL)
3. Sentinel

Azure Arc enabled SQL Server

I'm not going into details what Azure Arc is, but concentrate on capabilities of Azure Arc enabled SQL server. To cut story short, "Azure Arc is?a bridge that extends the Azure platform to help you build applications and services with the flexibility to run across datacenters, at the edge, and in multicloud environments." - Plan and deploy Azure Arc-enabled servers

While, Azure Arc enabled SQL "enables you to manage all of your SQL Servers from a single point of control: Azure. As you connect your SQL Servers to Azure, you get a single place to view the detailed inventory of your SQL Servers and databases." Read about pre-requisites.

Since April 11th 2023, Azure has been automatically looking at your Arc Servers if they have SQL Server installed. If found, it's been automatically onboarding them as SQL Server too (docs). (SQL Server onboarding is done by installing SQL extension via Arc extension manager into Azure Arc Server).

So, you may already have some Arc enable SQL Servers in your environment.

Arc enabled SQL have a "License Type" configuration setting; Pay-as-you-go, Paid and LicenseOnly. Above mentioned automation uses "LicenseOnly" by default. (More about license types here)

However, many customers have purchased SQL servers with Software Assurance and they should use "Paid" license type (also referred as "License with Software Assurance") to enable features we're going to explore in next chapter.

To change license type:

• For few servers, it's easy to use Azure portal to modify license type.
• For many servers, it makes sense doing it at scale with this PowerShell. (personal suggestion: run the script from PC)

For new SQL Servers to automatically use correct license type, it's recommended to add this Tag into your subscription or resource group.

Another suggestion to automate & monitor SQL Server onboarding, is to use Azure Policy, more info about that here: Using Azure Policy to onboard multiple SQL Servers at scale to Azure Arc-enabled SQL Server

SQL Server & Database inventory

It's hard to protect something if you don't know about it - this leads us to asset inventory.

Now, as you've Arc'd your SQL Servers, of course you have inventory of SQL Servers:

Also details of a particular SQL server:

Once you're with "Paid" license type you'll get also inventory of your SQL Databases within SQL Server:

And details of a particular database as well:

This information is also accessible in Azure Resource Graph (ARG) which we can browse with Resource Graph Explorer:

type?==?"microsoft.azurearcdata/sqlserverinstances"
type?==?"microsoft.azurearcdata/sqlserverinstances/databases"        

(scroll the results right to catch more details)

Here are some example scenarios showing How to query data that is available with Azure Arc-enabled SQL Server.

SQL Server Dashboard

Because the data is available in ARG, you can visualize it with Azure Dashboard (or Azure Monitor Workbook).

Here's Microsoft's "SQL Server Instances" dashboard:

sql-server-samples/samples/features/azure-arc/dashboard at master · microsoft/sql-server-samples · GitHub

(includes instructions to add new Azure Dashboard)

The dashboard successfully creates the visual inventory of you Arc enabled SQL Servers, Azure SQL Servers and Azure SQL Managed Instances with

• number of servers
• sql server type distribution
• number of databases
• databases per type
• dbs per server
• sql server editions
• cores per edition
• compatibility mode
• defender for sql status
• encrypted
• sql server version
• sql server backup invervals
• sql server recovery mode

(The dashboard can be customized and queries can be altered to suit your needs)

SQL Best Practice Assessment

One of the essential "free" benefits you get with software assurance (Paid license type) is SQL Best Practice assessment which "provides a mechanism to evaluate the configuration of your SQL Server".

Assessments vary across the board; from performance, optimization, backup to security (eg. weak passwords).

"The?Results?page reports all the issues categorized based on their severity for all the SQL Server instances running on the machine. You can switch the results view between the SQL Server instances running on the machine and assessment execution times using the top-down menus "Instance name" and "Collected at" respectively. The recommendations are organized into?All,?New,?and?Resolved?tabs. The tabs can be used to view all the recommendations from the currently selected run, the newer recommendations compared to the previous run, and the resolved recommendations from the previous runs respectively. The tabs help to keep track of the progress between the runs. The?Insights?tab identifies the most recurring issues and the databases with the maximum number of issues."

Documentation is here: Configure best practices assessment on an Azure Arc-enabled SQL Server instance

You can manually configure the assessment by pointing it to appropriate Log Analytics workspace. Assessment will run periodically.

Configuring the assessment also deploys Azure Monitor Agent to SQL Server (in case it's not already deployed) and creates Data Collection Rule (DCR) to collect the log files from the server.

Main pre-requisites here is:

• SQL Server built-in login?NT AUTHORITY\SYSTEM?must be the member of SQL Server?sysadmin?server role for all the SQL Server instances running on the machine

With many SQL Servers you may want to enable the assessment at scale using Azure Policy, docs are here.

Arc enabled SQL Server also supports scenarios to automated patching (preview) and automated backups (preview). These are still in preview and not covered in this article.

That's all for part 1.

Continue reading:

Part 2 - On-prem SQL Security with Azure Arc, Defender & Sentinel (part 2)

Part 3 - On-prem SQL Security with Azure Arc, Defender & Sentinel (part 3)