Microsoft Defender for Cloud - 'SQL servers on machines should have vulnerability findings resolved'

[Dick_Lake]

Databases contain some of your most sensitive data, which makes them an obvious target for attackers.  Most attackers are usually looking for data, whether it is to acquire sensitive data for their own use (to sell), to encrypt it (to sell back to you), or to destroy it (to cause you reputational and operational harm).  Databases have an extended attack surface and are often misconfigured which can lead to an attacker gaining access, elevating permissions, and wreaking havoc. 

This recommendation is generated by Defender for SQL on machines Vulnerability Assessment.  The rules that we check for are a set of possible misconfigurations that should be addressed.  When you have findings for this recommendation, you have four options on how to handle it.  We’ll go into depth on each of them in this blog.

Prerequisites:

• You must have already opted into the Defender for SQL on Machines workload protection plan.  If you do see this recommendation in the Microsoft Defender for Cloud recommendation blade, that means you have not enabled the plan.  If you want to implement Defender for SQL on Machines, check out this documentation.

Exempt a Resource:

You can exempt a recommendation from any scope so that it doesn’t affect your secure score.  We will still conduct the VA scan and report the results, however; the resources’ status will change to ‘Not Applicable’ in the recommendation.  Exemptions can be made via the portal, or programmatically. 

IMPORTANT NOTE: This recommendation is included in multiple policy initiatives, creating an exemption in the portal will create an exemption for each initiative.

Exempting the recommendation in the portal:

Open the Microsoft Defender for Cloud Recommendations blade and navigate to ‘SQL servers on machines should have vulnerability findings resolved’.

1. This recommendation will show all the affected resources and the passed, failed, and not applicable checks that were run across all the databases. 
2. Select the ‘Exempt’ button located in the top left corner.
3. When creating an exemption, you must determine what the scope will be.  If you select Management Group (MG), all resources under will be impacted, the same is true for the subscription’s selection.  If you opt for the scope to be at the resource level, you can pick and choose which resources will be in the exemption scope.
4. Optionally, you can set an expiration date for the recommendation exemption.  I strongly recommend utilizing this capability so that you are continually re-evaluating whether that justification remains true.
5. The last set of decisions is related to ‘why’ this can be exempted.  If you have another solution that mitigates the need for the recommendation, select ‘Mitigated’.  If you have made the conscious decision to accept the risk, select ‘Waiver’.  The exemption description text box does require input and I strongly recommend that you use that space to tell a full story and provide links to any other pertinent information.  Remember, somewhere down the line, someone is going to see this exemption and want to be able to understand it. 

Exempting the recommendation via PowerShell:

Because all the Azure recommendations are powered by Azure Policy, if we want to programmatically create an exception on a recommendation, the easiest way to do so is through PowerShell.

$Subscription = Get-AzSubscription

$ASCDefault = Get-AzPolicyAssignment -Id /subscriptions/$($Subscription.Id)/providers/Microsoft.Authorization/policyAssigments/SecurityCenterBuiltIn

New-AzPolicyExemption -Name '<Detailed-Name>' -ExemptionCategory Waiver -PolicyAssignment $ASCDefault -Scope "/subcriptions/$($Subscription.Id)" -PolicyDefinitionReferenceId serverSqlDbVulnerabilityAssessmentMonitoring

1. Get the Azure subscription we want to utilize.
2. Get the Azure Policy Assignment that the recommendation is included in.
3. Create a new Azure Policy Exemption.  Be sure to include the ‘PolicyDefinitionReferenceId’ parameter.

When you go the programmatic route, Azure will not assume that you want to exempt this recommendation from every policy initiative.  You will need to repeat steps two and three for each initiative this definition is associated with. 

Disabling a Specific Rule:

Disabling a rule is similar to exempting a resource in that it will cease to impact your secure score, however; when you disable a rule, you disable that rule for all resources underneath that scope (either Management Group or Subscription). 

1. Open the Microsoft Defender for Cloud Recommendations blade and navigate to ‘SQL servers on machines should have vulnerability findings resolved’.
2. Select ‘Disable rule’.
3. Select the scope you want this rule to exist at.
4. There are three potential criteria for which you can choose from when creating this rule:
1. By ID’s. Ex: ‘VA2201’ (exclude singular) or ‘VA2201;VA1072’ (it is important to use a semicolon as the field separator).
2. By Severity level.
3. By benchmark.
5. Again, a justification field where it is best practice to include as much information as possible about the reasoning this rule was created.

Accept a Baseline:

As you review your assessment results, you can mark specific results as being an acceptable baseline in your environment.  Each baseline is essentially a customization of how the results are reported.  In subsequent scans, results that match the baseline are considered as ‘Passed’.  After you’ve established your baseline security state, vulnerability assessments only report on deviations from the baseline, freeing you up to focus your attention on other relevant issues. 

To accept findings in a baseline, we’ll walk through an example, starting again on the recommendation ‘SQL servers on machines should have vulnerability findings resolved’. 

1. First, we begin by selecting a SQL virtual machine that shows in the ‘Affected resources’ field.
2. Next, select which database we want to work with.
3. Then we identify and select which check we want to accept
   
4. Here we show you the exact query Defender for SQL on machines used to evaluate the specific check. 
5. Results from the above query are checked to see if they are already in the baseline.
6. If you see the results are as expected (in this case that XP_CMDSHELL is enabled [which is super risky and I never recommend it]), you can click the button to add all the results to the baseline.  The next time the vulnerability assessment scan runs and evaluates it against the baseline, this will show up as a 'Passed' check.  

Any deviation from the defined baseline will result in a mismatch and the check will become a finding again. 

Accept a Baseline Programmatically:

Working in the portal is great when you are focused on either a small set of subscriptions or database servers.  In order to do things at scale, it is best if we do these actions programmatically.  Below are some examples of how to do this.  You will need to tailor these commands to suite your needs. 

Accept all:

Once you have reviewed all the findings for a specific database you can choose to add them to the baseline.  The easiest way to do this programmatically is through PowerShell. 

$workspaceId = '<YourWorkspaceId>' # This is the Log Analytics workspace that is used by the Defender plan. 

$resourceId = '/subscriptions/<SubscriptionId>/resourceGroups/<ResourceGroupName>/providers/Microsoft.Compute/virtualMachines/<SQLServerName>'

$Server = '<SQLServerInstanceName>'

$Database = '<DatabaseName>' # You will have multiple databases, if you have a similar finding across the databases, you will need to change the variables and re run the command below.

Set-AzSecuritySqlVulnerabilityAssessmentBaseline -ResourceId $ResourceId `
                                                 -WorkspaceId $WorkspaceId `
                                                 -Server $Server `
                                                 -Database $Database

For more information about the command used, please check out the documentation here: Set-AzSecuritySqlVulnerabilityAssessmentBaseline (Az.Security) | Microsoft Learn

Copy a template and merge:

For some organizations, it is best to work on one databases configuration and use that as a baseline for others.  In these scenarios, we want to be able to copy a baseline from Database $A and apply it to Database $B. 

$workspaceId = '<YourWorkspaceId>'
$fromResourceId = '/subscriptions/<SubscriptionId>/resourceGroups/<ResourceGroupName>/providers/Microsoft.Compute/virtualMachines/<SourceSQLVirtualMachineName>'
$toResourceId = '/subscriptions/<SubscriptionId>/resourceGroups/<ResourceGroupName>/providers/Microsoft.Compute/virtualMachines/<TargetSQLVirtualMachineName>'
$Server = '<ServerInstanceName>'
$Database = '<DatabaseName>'

Get-AzSecuritySqlVulnerabilityAssessmentBaseline -WorkspaceId $workspaceId ` #Fetch the baseline from the source database we want to utilize
                                                 -ResourceId $fromResourceId `
                                                 -Server $Server `
                                                 -Database $Database | `
Set-AzSecuritySqlVulnerabilityAssessmentBaseline -WorkspaceId $workspaceId ` #We pipe the results to the cmdlet to set the baseline for our target database
                                                 -ResourceId $toResourceId `
                                                 -Server $Server `
                                                 -Database $Database

If your source database does not have a baseline already set, PowerShell will throw an error.  

Add a specific finding to the baseline:

Sometimes you’ll review your baseline and want to add certain findings into the baseline, but don’t want to add every result.  In this scenario, you must do this programmatically. 

$workspaceId = '<WorkspaceId>'
$resourceId = '/subscriptions/<SubscriptionId>/resourceGroups/<ResourceGroupName>/providers/Microsoft.Compute/virtualMachines/<SQLVMName>'
$Server = '<ServerInstanceName>'
$Database = '<DatabaseName>'

Set-AzSecuritySqlVulnerabilityAssessmentBaseline -WorkspaceId $workspaceId `
                                                 -ResourceId $ResourceId `
                                                 -Server $Server `
                                                 -Database $Database `
                                                 -BaselineSet @{VA1258 = @(, @("WideWorldImportersDW", "SQL2022CRM\datasecuritysa"))}

Here we specifically added one finding into the baseline for VA1258 - Database owners are as expected.  

Fix Items in a Baseline:

Each finding in a vulnerability assessment scan contains a remediation step(s) that your database administrators would need to act on.

While our recommendations are based on the settings required in the database, do exercise caution and evaluate whether or not this is the right choice for your organization. 

Summary:

Taking a good hard look at the configurations of your databases will help you reduce your overall risk.  While this article was written specifically with regards to managing vulnerability assessments for SQL Virtual Machines, the concepts are relevant to Azure SQL databases as well (although the programmatic references are different).

References:

Rules reference: SQL vulnerability assessment rules reference for Microsoft Defender for Cloud | Microsoft Learn

Rules changelog: SQL vulnerability assessment rules changelog for Microsoft Defender for Cloud | Microsoft Learn

Acknowledgements

• Many thanks to Catalin Esanu and Tomer Rotstein for supporting this blog.