SQL vulnerability assessment helps you identify database vulnerabilities

APPLIES TO: Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics

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 Azure'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:

  1. In the Azure portal, open the specific resource in Azure SQL Database, SQL Managed Instance Database, or Azure Synapse.

  2. Under the Security heading, select Defender for Cloud.

  3. Select Advanced Threat Protection settings on the link to open the Microsoft Defender for SQL settings pane for either the entire server or managed instance.

    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.

  4. In the Server settings page, define the Microsoft Defender for SQL settings:

    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.

  5. SQL vulnerability assessment scans can also be run on-demand:

    To run an on-demand scan to scan your database for vulnerabilities, select Scan from the toolbar:

    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, and
  • A summary by severity of the risks
  • A list of the findings for further investigations

Sampl scan report from the SQL vulnerability assessment scanner

To remediate the vulnerabilities discovered:

  1. Review your results and determine which of the report's findings are true security issues for your environment.

  2. 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.

    Examining the findings from a vulnerability scan

  3. 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.

    Approving a finding as a baseline for future scans

  4. 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.

    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

Export an assessment report

Select Export Scan Results to create a downloadable Excel report of your scan result. This report contains a summary tab that displays a summary of the assessment. The report includes all failed checks. It also includes a Results tab that contains the full set of results from the scan. The results include all checks that were run and the result details for each.

View scan history

Select Scan History in the vulnerability assessment pane to view a history of all scans previously run on this database. Select a particular scan in the list to view the detailed results of that scan.

Manage vulnerability assessments programmatically

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.
Convert-AzSqlInstanceDatabaseVulnerabilityAssessmentScan Converts vulnerability assessment scan results of a managed database to an Excel file.
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.

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