Hybrid Management

Integrating a Server and a SQL Server instance with Azure Arc allows you to manage a resource as if it were natively in Azure. The server could actually be located on-premises, or even in another cloud provider. For example, the server that is used in the examples, within this chapter, is built in AWS.

It is possible to generate a script, from Azure, that can be run on a server in a different environment, which will onboard a SQL Server instance into Azure Arc. If you are using SQL Server 2022, however, then Arc integration is embedded into the instance setup. This is discussed in Chapter 2.

Once a Server is onboarded, then it will appear in your chosen resource group, as if it were a native resource. For example, Figure 21-1 shows the AWS server, within an Azure resource group. The resource name is the instance name of the EC2 instance in AWS. The computer name is the machine name of the server, at the operating system level.

The server will also become available in Log Analytics Workspace. A full discussion of this feature is beyond the scope of this book, mainly because the direction of travel is Azure Monitor. At the most basic level, however, to illustrate how Azure treats the AWS-based server as a native resource, consider Figure 21-2. You can see that Azure keeps a heartbeat with the server and treats it as an Azure resource.

Once onboarded, various Azure management options become available. Some of these options, such as Environmental Health and Defender for Cloud, are discussed in the following sections.

Environmental Health and SQL Best Practice Assessment

Note

At the time of writing, Environmental Health is in a period of transition. Azure SQL VMs have transitioned to SQL Best Practice Analyzer (SQL BPA), whereas VMs onboarded through Azure Arc still use the older Environment Health feature. The two features are very similar. They use the same agent and are configured in the same way. Because the focus of this chapter is on Azure Arc, I will discuss the setup process for Environment Health, as this will be transferable. The real difference between the features is that SQL BPA provides a much richer reporting experience. Therefore, when we look at using the report, I will switch to SQL BPA, as this is what you will most likely see at the time of reading.

Environmental Health/SQL BPA run hundreds of tests against a VM running SQL Server, the SQL Server instance, and the databases hosted within the instance to check for good practice configuration, throughout the stack.

Scrolling to the bottom of the resource will allow you to see compatible features, as shown in Figure 21-3. In this case, you can see that Microsoft Defender and Environmental Health are available.

Selecting the Environmental Health option will open the configuration page. To be able to continue, the Microsoft Monitoring Agent must be installed on the target server. This can be achieved by using the PowerShell script in Listing 21-1.

#Install the Azure PowerShell Module

Install-Module -Name Az -Repository PSGallery -Force

#Install the Connected Machine Module

Install-Module -Name Az.Connectedmachine

#Set Log Analytics Workspace Parameters

$Setting = @{ "workspaceId" = "8ca234da-dbf2-4ff4-bb3d-4b762f3248e1" }

$protectedSetting = @{ "workspaceKey" = "Cryr1AJbtzjIsW7i1LZx3lGX+15OXppi2Q5X1D9ioB==" }

#Set Machine Parameters

$genericSettings = @{

    Name = "MicrosoftMonitoringAgent"

    ResourceGroupName = "rg-prosqladmin"

    MachineName = "i-007aea894b5531f07"

    Location = "westeurope"

    Publisher = "Microsoft.EnterpriseCloud.Monitoring"

    ExtensionType = "MicrosoftMonitoringAgent"

}

#Configure Agent

New-AzConnectedMachineExtension @genericSettings -Settings $Setting -ProtectedSetting $protectedSetting

Listing 21-1

Install the Microsoft Monitoring Agent

The configuration page is illustrated in Figure 21-4. If the Microsoft Monitoring Agent hasn’t been installed, then there will be a warning stating that this must be completed. The Account Type can be configured as a Domain Account or a Managed Service Account, and the working directory that you specify should be prestaged.

Once this form is completed, you can use the button to download the configuration script, which must be run on the target server. When running this script, you will be prompted to enter the name of the account (and password, if it is a domain account). The script will then create a Windows Scheduled Task (Figure 21-5), which runs under the context of the specified account. The account needs to be a local administrator and needs to have the Logon as a batch job user rights assignment, and the Do not forcefully unload the user registry at user logoff setting must be enabled in group policy editor. It also needs to have sysadmin rights within the SQL Server instance.

Tip

By default, the Logon as a batch job user rights assignment is assigned to administrators anyway. If your organization has a strict security stance, however, you may need to specifically assign the user rights assignment, using the Local Security Policy console.

For reference, Listing 21-2 shows the script that can be downloaded from the Azure portal. If you are comfortable with PowerShell, then you can just use the commands manually, as opposed to downloading the script.

Tip

Both the Microsoft Management Agent and the sqlserver PowerShell module must be installed on the target server.

[CmdletBinding()]

Param(

      [Parameter(Mandatory=$false)]

      [string]$ManagedServiceAccountName

)

if ($ManagedServiceAccountName)

{

      Add-SQLAssessmentTask -SQLServerName "EC2AMAZ-U3R2RGM" -WorkingDirectory "C:sql_assessmentwork_dir" -RunWithManagedServiceAccount $True -ScheduledTaskUsername $ManagedServiceAccountName -ScheduledTaskPassword (new-object System.Security.SecureString)

}

else

{

      Add-SQLAssessmentTask -SQLServerName "EC2AMAZ-U3R2RGM" -WorkingDirectory "C:sql_assessmentwork_dir"

}

Listing 21-2

Script to Create Scheduled Task

Figure 21-5 shows the Scheduled Task that is created.

Tip

Microsoft advise that it can take up to 24 hours for the assessment to register in Azure, but in my experience, it usually appears within an hour.

Once the Server has registered, then you are able to view the findings of the best practice analysis. Figure 21-6 illustrates the front page of the SQL BPA, which can be found by clicking the SQL Best Practice Assessment page (or Environment Health page) within the resource.

Tip

Remember, we are now switching to SQL BPA, instead of Environmental Health, to discuss the reports.

The page shows the timestamp of each assessment and the current status of that assessment. You can change the scheduling of the assessment by using the configuration tab, which is shown in Figure 21-7.

Drilling through the timestamp will show the completed assessment. Every region of the report is interactive. For example, hovering over a slice of a pie chart (at the top of Figure 21-8) will display its category name and percentage. Clicking a section of the pie chart will filter the findings below, by that issue severity or category. The filters in blue, below, will allow you to filter the report further. The name filter allows you to filter by either SQL Server instance or by database, in the format sql server instance:database. For example, Figure 21-9 shows the report filtered by the Chapter21 database.

You can also filter the instances of a “finding” in the bottom-right table, by clicking the overall “finding” in the bottom-left table. For example, clicking the DbIntegrity finding on the left filters the table on the right, so that only instances of that finding are displayed. This is demonstrated in Figure 21-10.

The message column provides remediation advice and clicking the message will provide more verbose details. For example, clicking the link for the CHECKDB finding against the Chapter21 database shows the details in Figure 21-11.

The New Issues tab of the report provides a filtered list of the findings that have appeared since the previous assessment was run, and the Resolved Issues tab provides details of the issues that have been resolved since the last time the assessment was run. The Insights tab (Figure 21-12) is particularly useful, giving you details of the most commonly reoccurring issues followed by a list of the databases that have the most issues.

The Trends tab, shown in Figure 21-13, offers bar charts, tracking (at time of writing) the number of new issues, resolved issues, and issue severity over time, with each bar representing an assessment.

The Log button in the BPA report, shown in Figure 21-8 and Figure 21-9, will open the last query to be run in a Workspace Analytics Logs query window. This is shown in Figure 21-14. The query is written in the Kusto Query Language. You can modify the query, or write your own. A list of categorized functions, example queries, and data tables can be found in the left-hand pane, along with helpers, such as data preview for the tables. The results pane is split into Results and Chat tabs, allowing you to choose how you consume the query results.

Tip

Kusto Query Language, or KQL, is a variation of SQL that can be used to search logs.

For reference, the query shown in Figure 21-14 can be found in Listing 21-3.

let selectedCategories = dynamic([]);

let selectedTotSev = dynamic([]);

SqlAssessment_CL

| where _ResourceId =~ "/subscriptions/35974afc-0cbb-4623-a2f9-1368dd4cea6f/resourceGroups/rg-prosqladmin/providers/Microsoft.Compute/virtualMachines/vm-prosqladmin"

| extend asmt = parse_csv(RawData)

| extend AsmtId=tostring(asmt[1]), CheckId=tostring(asmt[2]), DisplayString=asmt[3], Description=tostring(asmt[4]), HelpLink=asmt[5], TargetType=case(asmt[6] == 1, "Server", asmt[6] == 2, "Database", ""), TargetName=tostring(asmt[7]),

    Severity=case(asmt[8] == 30, "High", asmt[8] == 20, "Medium", asmt[8] == 10, "Low", asmt[8] == 0, "Information", asmt[8] == 1, "Warning", asmt[8] == 2, "Critical", "Passed"), Message=tostring(asmt[9]), TagsArr=split(tostring(asmt[10]), ","), Sev = toint(asmt[8])

| where AsmtId == "8b0ec946-2ac0-4340-97ec-197a590eb3b8"

    and (set_has_element(dynamic(['*']), CheckId) or "'*'" == "'*'")

    and (set_has_element(dynamic(['*']), TargetName) or "'*'" == "'*'")

    and set_has_element(dynamic([30, 20, 10, 0]), Sev)

    and (array_length(set_intersect(TagsArr, dynamic(['*']))) > 0 or "'*'" == "'*'")

| extend Category = case(array_length(set_intersect(TagsArr, dynamic(["CPU", "IO", "Storage"]))) > 0, '0',

    array_length(set_intersect(TagsArr, dynamic(["TraceFlag", "Backup", "DBCC", "DBConfiguration", "SystemHealth", "Traces", "DBFileConfiguration", "Configuration", "Replication", "Agent", "Security", "DataIntegrity", "MaxDOP", "PageFile", "Memory", "Performance", "Statistics"]))) > 0, '1',

    array_length(set_intersect(TagsArr, dynamic(["UpdateIssues", "Index", "Naming", "Deprecated", "masterDB", "QueryOptimizer", "QueryStore", "Indexes"]))) > 0, '2',

    '3')

| where (Sev >= 0 and array_length(selectedTotSev) == 0 or Sev in (selectedTotSev))

    and (Category in (selectedCategories) or array_length(selectedCategories) == 0)

| project

    TargetType,

    TargetName,

    Severity,

    Tags=strcat_array(array_slice(TagsArr, 1, -1), ','),

    CheckId,

    SeverityCode = toint(Sev),

    Message

| distinct *

| summarize Cnt = count() by SeverityCode, Severity, Tags, CheckId

| project Severity, Tags, CheckId, Cnt, SeverityCode

| order by SeverityCode desc, Cnt desc

Listing 21-3

Kusto Query

Tip

A full discussion of how to use Kusto is beyond the scope of this book. A Microsoft tutorial, however, can be found at https://learn.microsoft.com/en-us/azure/data-explorer/kusto/query/tutorial.

Microsoft Defender

Microsoft Defender for Cloud is a rich, vulnerability assessment tool. A subset of this tool is Microsoft Defender for SQL, which can be used to discover and help remediate vulnerabilities in database settings, feature configuration, and permissions. It scans configuration against multiple benchmarks, including the CIS (Center for Information Security) benchmark.

It can not only be configured against native Azure components, such as SQL Server Managed Instances, Azure SQL Databases, and even CosmosDB, but it can also be configured to scan servers running SQL Server in a hybrid environment. The only requirement is that the SQL Server extension is installed and that there is a secure network path to Azure, such as a VPN or Express Route. This means that while Arc enabling a SQL Server instance is not a requirement in its own right, when you do this, the SQL extension is installed, meaning that you automatically have the ability to use Defender. Because this chapter focuses on the Arc use case, however, from this point, I will assume that the instance is Arc enabled.

Once a server has been registered in Azure Arc, you will be able to integrate the SQL Server instance with Azure Defender for Cloud. This gives you the ability to identify potential database vulnerabilities and also offers remediation advice. At the bottom of the resource, you will find a Microsoft Defender for Cloud option (see Figure 21-3). If you are not already consuming Azure Defender for Cloud, then the upgrade screen shown in Figure 21-15 will be displayed. This screen gives you a summary of costs and allows you to select the subscriptions that you want to protect.

After selecting the subscription(s) that you want to purchase Defender for, you will be able to specify the types of components that you want to protect with Defender. In this use case, we have selected Servers, Databases, and Storage, as shown in Figure 21-16.

The Databases category has an additional configuration menu in the pricing column, which allows you to choose the type of database(s) you wish to protect. For our use case, we will choose SQL Server on machines, as shown in Figure 21-17.

In the Settings column, there is a link that will display a menu to configure provisioning. Figure 21-18 shows the advanced options menu, where we have selected to provision the Log Analytics agent.

Once an instance has been scanned, there will be a scorecard of findings and recommendations at the top of the Microsoft Defender for Cloud page, within the resource, as shown in Figure 21-19.

Beneath the scorecard, you will find a paged list of recommendations and a paged list of findings from the vulnerability assessment. These lists are illustrated in Figure 21-20 and Figure 21-21, respectively.

Drilling through a recommendation will provide extra details, and in some cases, the ability to remediate. For example, drilling through the File integrity monitoring should be enabled on machines recommendation displays the full description and remediation steps, as shown in Figure 21-22.

You will note that this recommendation has a quick fix option. Clicking this button will display a confirmation window (Figure 21-23).

Tip

In order to use the quick fix options, you must have an automation account in Azure and you must have Change Tracking and Inventory enabled.

Other recommendations, such as SQL servers should have vulnerability findings resolved, are far more generic. Drilling through this finding will display a subreport for vulnerabilities that should be remediated. A filter that I find particularly useful is the benchmark filter, shown in Figure 21-24, which gives the ability to filter results by benchmark.

At the top of the report, you will see a list of databases, with a severity scorecard insight next to each. You can also filter the list of security checks, by clicking one of these databases. For example, the security checks in Figure 21-25 show the list filtered by the Chapter21 database.

You can drill through a vulnerability to view additional information, including a description of the issue, the potential impact of the current configuration, the benchmark(s) which require the configuration, remediation instructions (although this is often a link to Microsoft Docs), and the query that is used to determine the result of the security check. This is shown in Figure 21-26 for the TDE not enabled vulnerability, which violates the FedRAMP benchmark.

In addition to the vulnerability scan, Defender also uses anomalous behavior detection to alert you if there is suspicious activity on a server that could imply an attack, or preattack is in progress. Figure 21-27 uses sample alerts to demonstrate what these alerts may look like, if you are under attack.

If the machine learning used to generate alerts gets it wrong, then you can use the Suppression Rules screen to create suppression rules for the alerts. For example, Figure 21-28 shows a suppression rule which will suppress the Login from a suspicious IP alert, should the connection be made from the IP address 10.0.0.1, because we know this address to be safe.

Drilling through Microsoft Defender for Cloud ➤ Environmental Settings and choosing Email notifications allows you to configure e-mail notification for security alerts, as shown in Figure 21-29. Here, you can choose who should receive the notifications and what is the minimum level of severity, to which they should be notified.

Summary

Registering a SQL Server instance with Azure Arc is possible with supported versions of SQL Server, but doing so is a manual process, which involves running scripts and installing agents on your server. SQL Server 2022 makes the process far more simple, by building Arc registration into the SQL Server setup.

Once registered in Arc, the resource appears as a resource in your designated Azure resource group. This opens up many possibilities, including using Environmental Health/SQL Best Practice Assessment and Azure Defender for Cloud.

SQL BPA runs hundreds of checks against the full stack to ensure that your SQL Server environment is configured to best practices. SQL BPA provides a rich reporting interface, with drill-throughs that allow you to easily target the highest priority issues. It also provides trend analysis, so that you can keep track of the issues being addressed over time. This can feed into agile project methodology or BAU metrics.

Azure Defender for Cloud protects SQL Server workloads, both with vulnerability assessments and machine learning that alerts when anomalous activity is detected. The assessment provides drill-through reports, with the bottom level containing full details, including a description of the finding, the potential impact, and suggested remedial actions. In some instances, a quick fix feature is provided that allows you to resolve the issue directly from within Defender, although this does require addition agent installation and an Azure automation account.

The attack scanning provides alerts in situations such as brute-force attacks, SQL injection attacks, and access from unexpected IP addresses. These alerts appear in the Defender console, but can also be sent to members of Azure subscription roles, such as Owner or Contributor. Alerts can also be suppressed if the anomaly detection has made an error.