Control: 4.2.2 Ensure that Vulnerability Assessment (VA) is enabled on a SQL server by setting a Storage Account

Description

Enable Vulnerability Assessment (VA) service scans for critical SQL servers and corresponding SQL databases.

Enabling Microsoft Defender for SQL server does not enables Vulnerability Assessment capability for individual SQL databases unless storage account is set to store the scanning data and reports.

The Vulnerability Assessment service scans databases for known security vulnerabilities and highlights deviations from best practices, such as misconfigurations, excessive permissions, and unprotected sensitive data. Results of the scan include actionable steps to resolve each issue and provide customized remediation scripts where applicable. Additionally, an assessment report can be customized by setting an acceptable baseline for permission configurations, feature configurations, and database settings.

Remediation

From Azure Console

Go to SQL servers.
Select a server instance.
Click on Security Center.
Select Configure next to Enabled at subscription-level.
In Section Vulnerability Assessment Settings, Click Select Storage account.
Choose Storage Account (Existing or Create New). Click Ok.
Click Save.

From Powershell

If not already, Enable Microsoft Defender for a SQL:

Set-AZSqlServerThreatDetectionPolicy -ResourceGroupName <resource group name> -ServerName <server name> -EmailAdmins $True

To enable ADS-VA service by setting Storage Account

Update-AzSqlServerVulnerabilityAssessmentSetting `
  -ResourceGroupName "<resource group name>"`
  -ServerName "<Server Name>"`
  -StorageAccountName "<Storage Name from same subscription andsame Location" `
  -ScanResultsContainerName "vulnerability-assessment" `
  -RecurringScansInterval Weekly `
  -EmailSubscriptionAdmins $true `
  -NotificationEmail @("mail1@mail.com" , "mail2@mail.com")

Default Value

By default, Microsoft Defender for SQL is not enabled for a SQL server. Enabling Microsoft Defender for SQL does not enable VA scanning by setting Storage Account automatically.

Usage

Run the control in your terminal:

powerpipe control run azure_compliance.control.cis_v200_4_2_2

Snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe control run azure_compliance.control.cis_v200_4_2_2 --share

SQL

This control uses a named query:

sql_server_and_databases_va_enabled