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 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.
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:
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.
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.
SQL vulnerability assessment scans can also be run on-demand:
From the resource's Defender for Cloud page, select View additional findings in Vulnerability Assessment to access the scan results from previous scans.
To run an on-demand scan to scan your database for vulnerabilities, select Scan from the toolbar:
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
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.
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.
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.
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 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 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:
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 24hrs to take effect.
To view, override, or delete a rule:
Select Disable rule.
From the scope list, subscriptions with active rules show as Rule applied.
To view or delete the rule, select the ellipsis menu ("...").
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
- 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.
Feedback
Submit and view feedback for