Skip to main content

lacework-global-623

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

Profile Applicability

• Level 2

Description

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

Rationale

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.

Impact

Enabling the Microsoft Defender for SQL features will incur additional costs for each SQL server.

Audit

From Azure Portal

  1. Go to SQL servers
  2. Select a server instance
  3. Click on Security Center
  4. Ensure that Microsoft Defender for SQL is set to Enabled
  5. Select Configure next to Enabled at subscription-level
  6. In Section Vulnerability Assessment Settings, Ensure Storage Accounts does not read Select Storage account with no storage accounts listed under the Storage account heading.

From Azure Powershell

Get the list of all SQL Servers

Get-AZSqlServer

For each Server

Get-AzSqlServerVulnerabilityAssessmentSetting -ResourceGroupName <resource group name> -ServerName <server name>

Ensure that value for parameter StorageAccountName is not empty (blank).

Sample Output:

ResourceGroupName : ResourceGroup01

ServerName : Server01

StorageAccountName : mystorage

ScanResultsContainerName : vulnerability-assessment

RecurringScansInterval : None

EmailSubscriptionAdmins : False

NotificationEmail : {}

Remediation

From Azure Portal

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

From Azure 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 and same Location" `
-ScanResultsContainerName "vulnerability-assessment" `
-RecurringScansInterval Weekly `
-EmailSubscriptionAdmins $true `
-NotificationEmail @("mail1@mail.com" , "mail2@mail.com")

References

https://docs.microsoft.com/en-us/azure/sql-database/sql-vulnerability-assessment
https://docs.microsoft.com/en-us/rest/api/sql/servervulnerabilityassessments/listbyserver
https://docs.microsoft.com/en-in/powershell/module/Az.Sql/Update-AzSqlServerVulnerabilityAssessmentSetting?view=azps-2.6.0
https://docs.microsoft.com/en-in/powershell/module/Az.Sql/Get-AzSqlServerVulnerabilityAssessmentSetting?view=azps-2.6.0
https://docs.microsoft.com/en-us/security/benchmark/azure/security-controls-v3-posture-vulnerability-management#pv-6-perform-software-vulnerability-assessments