Manage vulnerability findings in your Azure SQL databases

Artikkeli
08/07/2024

Microsoft Defender for Cloud provides vulnerability assessment for your Azure SQL databases. Vulnerability assessment scans your databases for software vulnerabilities and provides a list of findings. You can use the findings to remediate software vulnerabilities and disable findings.

Prerequisites

Make sure that you know whether you're using the express or classic configurations before you continue.

To see which configuration you're using:

In the Azure portal, open the specific resource in Azure SQL Database, SQL Managed Instance Database, or Azure Synapse.
Under the Security heading, select Defender for Cloud.
In the Enablement Status, select Configure to open the Microsoft Defender for SQL settings pane for either the entire server or managed instance.

If the vulnerability settings show the option to configure a storage account, you're using the classic configuration. If not, you're using the express configuration.

Express configuration
Classic configuration

Express configuration

View scan history

Select Scan History in the vulnerability assessment pane to view a history of all scans previously run on this database.

Express configuration doesn't store scan results if they're identical to previous scans. The scan time shown in the scan history is the time of the last scan where the scan results changed.

Disable specific findings from Microsoft Defender for Cloud (preview)

If you have an organizational need to ignore a finding rather than remediate it, you can disable the finding. Disabled findings don't impact your secure score or generate unwanted noise. You can see the disabled finding in the "Not applicable" section of the scan results.

When a finding matches the criteria you've defined in your disable rules, it won't appear in the list of findings. Typical scenarios might include:

Disable findings with medium or lower severity
Disable findings that are non-patchable
Disable findings from benchmarks that aren't of interest for a defined scope

Important

To disable specific findings, you need permissions to edit a policy in Azure Policy. Learn more in Azure RBAC permissions in Azure Policy.

To create a rule:

From the recommendations detail page for Vulnerability assessment findings on your SQL servers on machines should be remediated, select Disable rule.
Select the relevant scope.
Define your criteria. You can use any of the following criteria:
- Finding ID
- Severity
- Benchmarks
Create a disable rule for VA findings on SQL servers on machines
Select Apply rule. Changes might take up to 24 hours to take effect.
To view, override, or delete a rule:
1. Select Disable rule.
2. From the scope list, subscriptions with active rules show as Rule applied.
3. To view or delete the rule, select the ellipsis menu ("...").

Configure email notifications using Azure Logic Apps

To receive regular updates of the vulnerability assessment status for your database, you can use the customizable Azure Logic Apps template.

Using the template will allow you to:

Choose the timing of the email reports.
Have a consistent view of your vulnerability assessment status that includes disabled rules.
Send reports for Azure SQL Servers and SQL VMs.
Customize report structure and look-and-feel to match your organizational standards.

Manage vulnerability assessments programmatically

The express configuration is supported in the latest REST API version with the following functionality:

Description	Scope	API
Baseline bulk operations	System Database	Sql Vulnerability Assessment Baselines Sql Vulnerability Assessment Baseline
Baseline bulk operations	User Database	Database Sql Vulnerability Assessment Baselines
Single rule baseline operations	User Database	Database Sql Vulnerability Assessment Rule Baselines
Single rule baseline operations	System Database	Sql Vulnerability Assessment Rule Baselines Sql Vulnerability Assessment Rule Baseline
Single scan results	User Database	Database Sql Vulnerability Assessment Scan Result
Single scan results	System Database	Sql Vulnerability Assessment Scan Result
Scan details (summary)	User Database	Database Sql Vulnerability Assessment Scans
Scan details (summary)	System Database	Sql Vulnerability Assessment Scans
Execute manual scan	User Database	Database Sql Vulnerability Assessment Execute Scan
Execute manual scan	System Database	Sql Vulnerability Assessment Execute Scan
VA settings (GET only is supported for Express Configuration)	User Database	Database Sql Vulnerability Assessments Settings
VA Settings operations	Server	Sql Vulnerability Assessments Settings Sql Vulnerability Assessments

Using Azure Resource Manager templates

Use the following ARM template to create a new Azure SQL Logical Server with express configuration for SQL vulnerability assessment.

To configure vulnerability assessment baselines by using Azure Resource Manager templates, use the Microsoft.Sql/servers/databases/sqlVulnerabilityAssessments/baselines type. Make sure that vulnerabilityAssessments is enabled before you add baselines.

Here are several examples to how you can set up baselines using ARM templates:

Setup batch baseline based on latest scan results:

{
    "type": "Microsoft.Sql/servers/databases/sqlVulnerabilityAssessments/baselines",
    "apiVersion": "2022-02-01-preview",
    "name": "[concat(parameters('serverName'),'/', parameters('databaseName') , '/default/default')]",
    "properties": {
        "latestScan": true
        }
}

Setup batch baseline based on specific results:

{
    "type": "Microsoft.Sql/servers/databases/sqlVulnerabilityAssessments/baselines",
    "apiVersion": "2022-02-01-preview",
    "name": "[concat(parameters('serverName'),'/', parameters('databaseName') , '/default/default')]",
    "properties": {
        "latestScan": false,
        "results": {
        "VA2065": [
            [
                 "FirewallRuleName3",
                     "62.92.15.67",
                     "62.92.15.67"
            ],
            [
                 "FirewallRuleName4",
                     "62.92.15.68",
                     "62.92.15.68"
            ]
        ],
        "VA2130": [
            [
                 "dbo"
            ]
        ]
     }
  }
}

Set up baseline for a specific rule:

{
    "type": "Microsoft.Sql/servers/databases/sqlVulnerabilityAssessments/baselines/rules",
    "apiVersion": "2022-02-01-preview",
    "name": "[concat(parameters('serverName'),'/', parameters('databaseName') , '/default/default/VA1143')]",
    "properties": {
    "latestScan": false,
    "results": [
        [ "True" ]
        ]
    }
}

Set up batch baselines on the master database based on latest scan results:

{
    "type": "Microsoft.Sql/servers/databases/sqlVulnerabilityAssessments/baselines",
    "apiVersion": "2022-02-01-preview",
    "name": "[concat(parameters('serverName'),'/master/default/default')]",
    "properties": {
        "latestScan": true
        }
}

Using PowerShell

Express configuration isn't supported in PowerShell cmdlets but you can use PowerShell to invoke the latest vulnerability assessment capabilities using REST API, for example:

Enable express configuration on an Azure SQL Server
Setup baselines based on latest scan results for all databases in an Azure SQL Server
Express configuration PowerShell commands reference

Using Azure CLI

Invoke express configuration using Azure CLI.

Troubleshooting

Revert back to the classic configuration

To change an Azure SQL database from the express vulnerability assessment configuration to the classic configuration:

Disable the Defender for Azure SQL plan from the Azure portal.

Use PowerShell to reconfigure using the classic experience:

Update-AzSqlServerAdvancedThreatProtectionSetting `
            -ResourceGroupName "demo-rg" `
            -ServerName "dbsrv1" `
            -Enable 1
Update-AzSqlServerVulnerabilityAssessmentSetting `
            -ResourceGroupName "demo-rg" `
            -ServerName "dbsrv1" `
            -StorageAccountName "mystorage" `
            -RecurringScansInterval Weekly `
            -ScanResultsContainerName "vulnerability-assessment"

You might have to tweak Update-AzSqlServerVulnerabilityAssessmentSetting according to Store Vulnerability Assessment scan results in a storage account accessible behind firewalls and VNets.

Errors

“Vulnerability Assessment is enabled on this server or one of its underlying databases with an incompatible version”

Possible causes:

Switching to express configuration failed due to a server policy error.

Solution: Try again to enable the express configuration. If the issue persists, try to disable the Microsoft Defender for SQL in the Azure SQL resource, select Save, enable Microsoft Defender for SQL again, and select Save.
Switching to express configuration failed due to a database policy error. Database policies aren't visible in the Azure portal for Defender for SQL vulnerability assessment, so we check for them during the validation stage of switching to express configuration.

Solution: Disable all database policies for the relevant server and then try to switch to express configuration again.
Consider using the provided PowerShell script for assistance.

Classic configuration

View scan history

Select Scan History in the vulnerability assessment pane to view a history of all scans previously run on this database.

Disable specific findings from Microsoft Defender for Cloud (preview)

If you have an organizational need to ignore a finding, rather than remediate it, you can optionally disable it. Disabled findings don't impact your secure score or generate unwanted noise.

When a finding matches the criteria you've defined in your disable rules, it won't appear in the list of findings. Typical scenarios might include:

Disable findings with medium or lower severity
Disable findings that are non-patchable
Disable findings from benchmarks that aren't of interest for a defined scope

Important

To disable specific findings, you need permissions to edit a policy in Azure Policy. Learn more in Azure RBAC permissions in Azure Policy.
Disabled findings will still be included in the weekly SQL vulnerability assessment email report.
Disabled rules are shown in the "Not applicable" section of the scan results.

To create a rule:

From the recommendations detail page for Vulnerability assessment findings on your SQL servers on machines should be remediated, select Disable rule.
Select the relevant scope.
Define your criteria. You can use any of the following criteria:
- Finding ID
- Severity
- Benchmarks
Select Apply rule. Changes might take up to 24 hours to take effect.
To view, override, or delete a rule:
1. Select Disable rule.
2. From the scope list, subscriptions with active rules show as Rule applied.
3. To view or delete the rule, select the ellipsis menu ("...").

Manage vulnerability assessments programmatically

Azure PowerShell

Note

This article uses the Azure Az PowerShell module, which is the recommended PowerShell module for interacting with Azure. To get started with the Az PowerShell module, see Install Azure PowerShell. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.

Important

The PowerShell Azure Resource Manager module is still supported, but all future development is for the Az.Sql module. For these cmdlets, see AzureRM.Sql. The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.

You can use Azure PowerShell cmdlets to programmatically manage your vulnerability assessments. The supported cmdlets are:

Cmdlet name as a link	Description
Clear-AzSqlDatabaseVulnerabilityAssessmentRuleBaseline	Clears the vulnerability assessment rule baseline. First, set the baseline before you use this cmdlet to clear it.
Clear-AzSqlDatabaseVulnerabilityAssessmentSetting	Clears the vulnerability assessment settings of a database.
Clear-AzSqlInstanceDatabaseVulnerabilityAssessmentRuleBaseline	Clears the vulnerability assessment rule baseline of a managed database. First, set the baseline before you use this cmdlet to clear it.
Clear-AzSqlInstanceDatabaseVulnerabilityAssessmentSetting	Clears the vulnerability assessment settings of a managed database.
Clear-AzSqlInstanceVulnerabilityAssessmentSetting	Clears the vulnerability assessment settings of a managed instance.
Convert-AzSqlDatabaseVulnerabilityAssessmentScan	Converts vulnerability assessment scan results of a database to an Excel file (export).
Convert-AzSqlInstanceDatabaseVulnerabilityAssessmentScan	Converts vulnerability assessment scan results of a managed database to an Excel file (export).
Get-AzSqlDatabaseVulnerabilityAssessmentRuleBaseline	Gets the vulnerability assessment rule baseline of a database for a given rule.
Get-AzSqlInstanceDatabaseVulnerabilityAssessmentRuleBaseline	Gets the vulnerability assessment rule baseline of a managed database for a given rule.
Get-AzSqlDatabaseVulnerabilityAssessmentScanRecord	Gets all vulnerability assessment scan records associated with a given database.
Get-AzSqlInstanceDatabaseVulnerabilityAssessmentScanRecord	Gets all vulnerability assessment scan records associated with a given managed database.
Get-AzSqlDatabaseVulnerabilityAssessmentSetting	Returns the vulnerability assessment settings of a database.
Get-AzSqlInstanceDatabaseVulnerabilityAssessmentSetting	Returns the vulnerability assessment settings of a managed database.
Set-AzSqlDatabaseVulnerabilityAssessmentRuleBaseline	Sets the vulnerability assessment rule baseline.
Set-AzSqlInstanceDatabaseVulnerabilityAssessmentRuleBaseline	Sets the vulnerability assessment rule baseline for a managed database.
Start-AzSqlDatabaseVulnerabilityAssessmentScan	Triggers the start of a vulnerability assessment scan on a database.
Start-AzSqlInstanceDatabaseVulnerabilityAssessmentScan	Triggers the start of a vulnerability assessment scan on a managed database.
Update-AzSqlDatabaseVulnerabilityAssessmentSetting	Updates the vulnerability assessment settings of a database.
Update-AzSqlInstanceDatabaseVulnerabilityAssessmentSetting	Updates the vulnerability assessment settings of a managed database.
Update-AzSqlInstanceVulnerabilityAssessmentSetting	Updates the vulnerability assessment settings of a managed instance.

For a script example, see Azure SQL vulnerability assessment PowerShell support.

Azure CLI

Important

The following Azure CLI commands are for SQL databases hosted on VMs or on-premises machines. For vulnerability assessments regarding Azure SQL Databases, refer to the Azure portal or PowerShell section.

You can use Azure CLI commands to programmatically manage your vulnerability assessments. The supported commands are:

Command name as a link	Description
`az security va sql baseline delete`	Delete SQL vulnerability assessment rule baseline.
`az security va sql baseline list`	View SQL vulnerability assessment baseline for all rules.
`az security va sql baseline set`	Sets SQL vulnerability assessment baseline. Replaces the current baseline.
`az security va sql baseline show`	View SQL vulnerability assessment rule baseline.
`az security va sql baseline update`	Update SQL vulnerability assessment rule baseline. Replaces the current rule baseline.
`az security va sql results list`	View all SQL vulnerability assessment scan results.
`az security va sql results show`	View SQL vulnerability assessment scan results.
`az security va sql scans list`	List all SQL vulnerability assessment scan summaries.
`az security va sql scans show`	View SQL vulnerability assessment scan summaries.

Resource Manager templates

To configure vulnerability assessment baselines by using Azure Resource Manager templates, use the Microsoft.Sql/servers/databases/vulnerabilityAssessments/rules/baselines type.

Ensure that you have enabled vulnerabilityAssessments before you add baselines.

Here's an example for defining Baseline Rule VA2065 to master database and VA1143 to user database as resources in a Resource Manager template:

   "resources": [
      {
         "type": "Microsoft.Sql/servers/databases/vulnerabilityAapiVersion": "2018-06-01",
         "name": "[concat(parameters('server_name'),'/', parameters('database_name') , '/default/VA2065/master')]",
         "properties": {
            "baselineResults": [
               {
                  "result": [
                     "FirewallRuleName3",
                     "StartIpAddress",
                     "EndIpAddress"
                  ]
               },
               {
                  "result": [
                     "FirewallRuleName4",
                     "62.92.15.68",
                     "62.92.15.68"
                  ]
               }
            ]
         },
         "type": "Microsoft.Sql/servers/databases/vulnerabilityAapiVersion": "2018-06-01",
         "name": "[concat(parameters('server_name'),'/', parameters('database_name'), '/default/VA2130/Default')]",
         "dependsOn": [
            "[resourceId('Microsoft.Sql/servers/vulnerabilityAssessments', parameters('server_name'), 'Default')]"
         ],
         "properties": {
            "baselineResults": [
               {
                  "result": [
                     "dbo"
                  ]
               }
            ]
         }
      }
   ]

For master database and user database, the resource names are defined differently:

Master database - "name": "[concat(parameters('server_name'),'/', parameters('database_name'), '/default/VA2065/master')]",
User database - "name": "[concat(parameters('server_name'),'/', parameters('database_name'), '/default/VA2065/default')]",

To handle Boolean types as true/false, set the baseline result with binary input like "1"/"0".

   {
      "type": "Microsoft.Sql/servers/databases/vulnerabilityapiVersion": "2018-06-01",
      "name": "[concat(parameters('server_name'),'/', parameters('database_name'), '/default/VA1143/Default')]",

      "dependsOn": [
         "[resourceId('Microsoft.Sql/servers/vulnerabilityAssessments', parameters('server_name'), 'Default')]"
      ],

      "properties": {
         "baselineResults": [
            {
               "result": [
                  "1"
               ]
            }
         ]
      }

   }

Learn more about Microsoft Defender for Azure SQL.
Learn more about data discovery and classification.
Learn more about storing vulnerability assessment scan results in a storage account accessible behind firewalls and VNets.
Check out common questions about Azure SQL databases.

Jaa

Manage vulnerability findings in your Azure SQL databases

Prerequisites

Express configuration

View scan history

Disable specific findings from Microsoft Defender for Cloud (preview)

Configure email notifications using Azure Logic Apps

Manage vulnerability assessments programmatically

Using Azure Resource Manager templates

Using PowerShell

Using Azure CLI

Troubleshooting

Revert back to the classic configuration

Errors

Classic configuration

View scan history

Disable specific findings from Microsoft Defender for Cloud (preview)

Manage vulnerability assessments programmatically

Azure PowerShell

Azure CLI

Resource Manager templates

Palaute

Lisäresursseja

Jaa

Manage vulnerability findings in your Azure SQL databases

Prerequisites

Express configuration

View scan history

Disable specific findings from Microsoft Defender for Cloud (preview)

Configure email notifications using Azure Logic Apps

Manage vulnerability assessments programmatically

Using Azure Resource Manager templates

Using PowerShell

Using Azure CLI

Troubleshooting

Revert back to the classic configuration

Errors

Classic configuration

View scan history

Disable specific findings from Microsoft Defender for Cloud (preview)

Manage vulnerability assessments programmatically

Azure PowerShell

Azure CLI

Resource Manager templates

Related content

Palaute

Lisäresursseja