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 Azure Defender for SQL offering, which is a unified package for advanced SQL security capabilities. Vulnerability assessment can be accessed and managed via the central Azure 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:

  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 Security Center.

  3. Select Configure on the link to open the Azure Defender for SQL settings pane for either the entire server or managed instance.

    Opening the Defender for SQL configuration

    Note

    SQL vulnerability assessment requires Azure Defender for SQL plan to be able to run scans. For more information about how to enable Azure Defender for SQL, see Azure Defender for SQL.

  4. In the Server settings page, define the Azure 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:

    1. From the resource's Security Center page, select View additional findings in Vulnerability Assessment to access the scan results from previous scans.

      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:

      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.

Disable specific findings from Azure Security Center (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 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.

To create a rule:

  1. From the recommendations detail page for Vulnerability assessment findings on your SQL servers on machines should be remediated, select Disable rule.

  2. Select the relevant scope.

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

  4. Select Apply rule. Changes might take up to 24hrs to take effect.

  5. To view, override, or delete a rule:

    1. Select Disable rule.

    2. From the scope list, subscriptions with active rules show as Rule applied.

      Modify or delete an existing rule

    3. To view or delete the rule, select the ellipsis menu ("...").

Manage vulnerability assessments programmatically

Using Azure PowerShell

Note

This article has been updated to use the Azure Az PowerShell module. The Az PowerShell module 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"
               ]
            }
         ]
      }

   }

Data residency

SQL Vulnerability Assessment queries the SQL server using publicly available queries under Security Center 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