title	SQL vulnerability assessment
titleSuffix	Azure SQL Database & SQL Managed Instance & Azure Synapse Analytics
description	Learn how to configure SQL vulnerability assessment and interpret the assessment reports on Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.
author	cesanu
ms.author	cesanu
ms.reviewer	wiassaf, vanto, mathoma
ms.date	05/22/2022
ms.service	sql-db-mi
ms.subservice	security
ms.topic	how-to
ms.custom	sqldbrb=3
tags	azure-synapse
monikerRange	= azuresql \|\| = azuresql-db \|\| = azuresql-mi

SQL vulnerability assessment helps you identify database vulnerabilities

[!INCLUDEappliesto-sqldb-sqlmi-asa]

SQL vulnerability assessment is an easy-to-configure service that can discover, track, and help you remediate potential database vulnerabilities. Use it to proactively improve your database security.

Vulnerability assessment is part of the Microsoft Defender for SQL offering, which is a unified package for advanced SQL security capabilities. Vulnerability assessment can be accessed and managed via the central Microsoft Defender for SQL portal.

Note

Vulnerability assessment is supported for Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics. Databases in Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics are referred to collectively in the remainder of this article as databases, and the server is referring to the server that hosts databases for Azure SQL Database and Azure Synapse.

What is SQL vulnerability assessment?

SQL vulnerability assessment is a service that provides visibility into your security state. Vulnerability assessment includes actionable steps to resolve security issues and enhance your database security. It can help you to monitor a dynamic database environment where changes are difficult to track and improve your SQL security posture.

Vulnerability assessment is a scanning service built into Azure SQL Database. The service employs a knowledge base of rules that flag security vulnerabilities. It highlights deviations from best practices, such as misconfigurations, excessive permissions, and unprotected sensitive data.

The rules are based on Microsoft's best practices and focus on the security issues that present the biggest risks to your database and its valuable data. They cover database-level issues and server-level security issues, like server firewall settings and server-level permissions.

Results of the scan include actionable steps to resolve each issue and provide customized remediation scripts where applicable. You can customize an assessment report for your environment by setting an acceptable baseline for:

Permission configurations
Feature configurations
Database settings

Configure vulnerability assessment

Take the following steps to configure the vulnerability assessment:

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.
Select Configure on the link to open the Microsoft Defender for SQL settings pane for either the entire server or managed instance.

:::image type="content" source="media/sql-vulnerability-assessment/opening-sql-configuration.png" alt-text="Opening the Defender for SQL configuration":::

[!NOTE] SQL vulnerability assessment requires Microsoft Defender for SQL plan to be able to run scans. For more information about how to enable Microsoft Defender for SQL, see Microsoft Defender for SQL.
In the Server settings page, define the Microsoft Defender for SQL settings:

:::image type="content" source="media/sql-vulnerability-assessment/sql-vulnerability-scan-settings.png" alt-text="Configuring the SQL vulnerability assessment scans":::
1. Configure a storage account where your scan results for all databases on the server or managed instance will be stored. For information about storage accounts, see About Azure storage accounts.
  
  [!TIP] For more information about storing vulnerability assessment scans behind firewalls and VNets, see Store vulnerability assessment scan results in a storage account accessible behind firewalls and VNets.
2. To configure vulnerability assessments to automatically run weekly scans to detect security misconfigurations, set Periodic recurring scans to On. The results are sent to the email addresses you provide in Send scan reports to. You can also send email notification to admins and subscription owners by enabling Also send email notification to admins and subscription owners.
  
  [!NOTE] Each database is randomly assigned a scan time on a set day of the week. Email notifications are scheduled randomly per server on a set day of the week. The email notification report includes data from all recurring database scans that were executed during the preceding week (does not include on-demand scans).
SQL vulnerability assessment scans can also be run on-demand:
1. From the resource's Defender for Cloud page, select View additional findings in Vulnerability Assessment to access the scan results from previous scans.
  
  :::image type="content" source="media/sql-vulnerability-assessment/view-additional-findings-link.png" alt-text="Opening the scan results and manual scan options.":::
2. To run an on-demand scan to scan your database for vulnerabilities, select Scan from the toolbar:
  
  :::image type="content" source="media/sql-vulnerability-assessment/on-demand-vulnerability-scan.png" alt-text="Select scan to run an on-demand vulnerability assessment scan of your SQL resource":::

Note

The scan is lightweight and safe. It takes a few seconds to run and is entirely read-only. It doesn't make any changes to your database.

Remediate vulnerabilities

When a vulnerability scan completes, the report is displayed in the Azure portal. The report presents:

An overview of your security state
The number of issues that were found
A summary by severity of the risks
A list of the findings for further investigations

:::image type="content" source="media/sql-vulnerability-assessment/sample-sql-vulnerabilities-report.png" alt-text="Sampl scan report from the SQL vulnerability assessment scanner":::

To remediate the vulnerabilities discovered:

Review your results and determine which of the report's findings are true security issues for your environment.
Select each failed result to understand its impact and why the security check failed.

[!TIP] The findings details page includes actionable remediation information explaining how to resolve the issue.

:::image type="content" source="media/sql-vulnerability-assessment/examining-vulnerability-findings.gif" alt-text="Examining the findings from a vulnerability scan":::
As you review your assessment results, you can mark specific results as being an acceptable baseline in your environment. A baseline is essentially a customization of how the results are reported. In subsequent scans, results that match the baseline are considered as passes. After you've established your baseline security state, vulnerability assessment only reports on deviations from the baseline. In this way, you can focus your attention on the relevant issues.

:::image type="content" source="media/sql-vulnerability-assessment/baseline-approval.png" alt-text="Approving a finding as a baseline for future scans":::
If you change the baselines, use the Scan button to run an on-demand scan and view the customized report. Any findings you've added to the baseline will now appear in Passed with an indication that they've passed because of the baseline changes.

:::image type="content" source="media/sql-vulnerability-assessment/passed-per-custom-baseline.png" alt-text="Passed assessments indicating they've passed per custom baseline":::

Your vulnerability assessment scans can now be used to ensure that your database maintains a high level of security, and that your organizational policies are met.

Advanced capabilities

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 may include:

Disable findings with severity below medium
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.

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
:::image type="content" source="media/sql-vulnerability-assessment/disable-rule-vulnerability-findings-sql.png" alt-text="Create a disable rule for VA findings on SQL servers on machines":::
Select Apply rule. Changes might take up to 24hrs 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.
  
  :::image type="content" source="media/sql-vulnerability-assessment/modify-rule.png" alt-text="Modify or delete an existing rule":::
3. To view or delete the rule, select the ellipsis menu ("...").

Manage vulnerability assessments programmatically

Azure PowerShell

[!INCLUDE updated-for-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.

Using 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/vulnerabilityAssessments/rules/baselines",
         "apiVersion": "2018-06-01-preview",
         "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/vulnerabilityAssessments/rules/baselines",
         "apiVersion": "2018-06-01-preview",
         "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/vulnerabilityAssessments/rules/baselines",
      "apiVersion": "2018-06-01-preview",
      "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"
               ]
            }
         ]
      }

   }

Permissions

One of the following permissions is required to see vulnerability assessment results in the Microsoft Defender for Cloud recommendation SQL databases should have vulnerability findings resolved:

Security Admin
Security Reader

The following permissions are required to changes vulnerability assessment settings:

SQL Security Manager
Storage Blob Data Reader
Owner role on the storage account

The following permissions are required to open links in email notifications about scan results or to view scan results at the resource-level:

SQL Security Manager
Storage Blob Data Reader

Data residency

SQL Vulnerability Assessment queries the SQL server using publicly available queries under Defender for Cloud recommendations for SQL Vulnerability Assessment, and stores the query results. The data is stored in the configured user-owned storage account.

SQL Vulnerability Assessment allows you to specify the region where your data will be stored by choosing the location of the storage account. The user is responsible for the security and data resiliency of the storage account.

Next steps

Learn more about Microsoft Defender for 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.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

SQL vulnerability assessment helps you identify database vulnerabilities

What is SQL vulnerability assessment?

Configure vulnerability assessment

Remediate vulnerabilities

Advanced capabilities

View scan history

Disable specific findings from Microsoft Defender for Cloud (preview)

Manage vulnerability assessments programmatically

Azure PowerShell

Azure CLI

Using Resource Manager templates

Permissions

Data residency

Next steps

FilesExpand file tree

sql-vulnerability-assessment.md

Latest commit

History

sql-vulnerability-assessment.md

File metadata and controls

SQL vulnerability assessment helps you identify database vulnerabilities

What is SQL vulnerability assessment?

Configure vulnerability assessment

Remediate vulnerabilities

Advanced capabilities

View scan history

Disable specific findings from Microsoft Defender for Cloud (preview)

Manage vulnerability assessments programmatically

Azure PowerShell

Azure CLI

Using Resource Manager templates

Permissions

Data residency

Next steps