Skip to main content

Vulnerability Assessment Is Enabled On SQL Server By

More Info:​

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

Risk Level​

Medium

Address​

Security

Compliance Standards​

  • CIS AZURE
  • Cloudanix Best Practice

Triage and Remediation​

Remediation​

Using Console

To remediate the misconfiguration "Ensure That Vulnerability Assessment Is Enabled On SQL Server By Setting Storage Account" in Azure using Azure console, follow the below steps:

  1. Login to the Azure portal (https://portal.azure.com/) using your credentials.
  2. Navigate to the SQL server that you want to remediate.
  3. Click on the "Security + Compliance" option from the left-hand side menu.
  4. Under the "Security + Compliance" menu, click on the "Vulnerability assessment" option.
  5. In the "Vulnerability assessment" page, click on the "Storage settings" tab.
  6. Click on the "Configure storage settings" button.
  7. In the "Configure storage settings" page, select the "Use existing storage account" option.
  8. Select the storage account that you want to use for vulnerability assessment.
  9. Click on the "Save" button to save the changes.

By following the above steps, you have successfully remediated the misconfiguration "Ensure That Vulnerability Assessment Is Enabled On SQL Server By Setting Storage Account" in Azure using Azure console.

Using CLI

To remediate this misconfiguration in AZURE using AZURE CLI, follow these steps:

  1. Open the AZURE CLI terminal and log in to your AZURE account using the command az login.

  2. Once you are logged in, set the subscription in which the SQL Server is running using the command az account set --subscription <subscription_id>.

  3. Now, create a new storage account or use an existing one to store the vulnerability assessment scan results. You can create a new storage account using the command az storage account create --name <storage_account_name> --resource-group <resource_group_name> --location <location> --sku Standard_LRS, where <storage_account_name> is the name of the storage account, <resource_group_name> is the name of the resource group in which the storage account will be created, and <location> is the location where the storage account will be created.

  4. Once the storage account is created, enable vulnerability assessment on the SQL Server using the command az sql server va create --resource-group <resource_group_name> --server <sql_server_name> --storage-account <storage_account_name> --storage-key <storage_account_key> --scan-results-container-name <container_name>, where <resource_group_name> is the name of the resource group in which the SQL Server is running, <sql_server_name> is the name of the SQL Server, <storage_account_name> is the name of the storage account created in step 3, <storage_account_key> is the access key of the storage account, and <container_name> is the name of the container in which the vulnerability assessment scan results will be stored.

  5. Once the vulnerability assessment is enabled, you can run a scan on the SQL Server using the command az sql server va scan create --resource-group <resource_group_name> --server <sql_server_name> --scan-id <scan_id>, where <resource_group_name> is the name of the resource group in which the SQL Server is running, <sql_server_name> is the name of the SQL Server, and <scan_id> is the ID of the vulnerability assessment scan.

By following these steps, you can remediate the misconfiguration and ensure that vulnerability assessment is enabled on SQL Server by setting the storage account in AZURE using AZURE CLI.

Using Python

To remediate the misconfiguration "Ensure that Vulnerability Assessment is enabled on SQL Server by setting storage account" in Azure using Python, follow these steps:

  1. Import the necessary libraries:
from azure.identity import DefaultAzureCredential
from azure.mgmt.sql import SqlManagementClient
from azure.mgmt.storage import StorageManagementClient
from azure.mgmt.storage.models import StorageAccountCreateParameters
  1. Set up the Azure credentials:
credential = DefaultAzureCredential()
subscription_id = '<subscription_id>'
resource_group_name = '<resource_group_name>'
server_name = '<server_name>'
database_name = '<database_name>'
storage_account_name = '<storage_account_name>'
storage_account_key = '<storage_account_key>'
  1. Create a Storage Account:
storage_client = StorageManagementClient(credential, subscription_id)
storage_params = StorageAccountCreateParameters(
    sku={"name": "Standard_LRS"},
    kind="StorageV2",
    location="<location>"
)
storage_async_operation = storage_client.storage_accounts.create(
    resource_group_name,
    storage_account_name,
    storage_params
)
storage_account = storage_async_operation.result()
  1. Enable Vulnerability Assessment on SQL Server:
sql_client = SqlManagementClient(credential, subscription_id)
sql_client.vulnerability_assessments.create_or_update(
    resource_group_name,
    server_name,
    database_name,
    "default",
    {
        "storage_container_path": "<container_path>",
        "storage_account_access_key": storage_account_key,
        "recurring_scans": {
            "isEnabled": True,
            "emailSubscriptionAdmins": False,
            "emails": []
        }
    }
)
  1. Verify that Vulnerability Assessment is enabled:
vulnerability_assessment = sql_client.vulnerability_assessments.get(
    resource_group_name,
    server_name,
    database_name,
    "default"
)
print(vulnerability_assessment.state)

Note: Replace the placeholders <subscription_id>, <resource_group_name>, <server_name>, <database_name>, <storage_account_name>, <storage_account_key>, <location> and <container_path> with the appropriate values for your environment.

Using Terraform
# SQL Server (target of the finding)
resource "azurerm_mssql_server" "sql_server" {
  name                         = "SQL_SERVER_NAME"              # replace
  resource_group_name          = azurerm_resource_group.rg.name
  location                     = azurerm_resource_group.rg.location
  version                      = "12.0"
  administrator_login          = "SQL_ADMIN_USERNAME"           # replace
  administrator_login_password = "SQL_ADMIN_PASSWORD"           # replace
}

# Storage account used by Vulnerability Assessment
resource "azurerm_storage_account" "va_storage" {
  name                     = "VASTORAGEACCOUNTNAME"             # globally unique, 3–24 lower alphanumeric
  resource_group_name      = azurerm_resource_group.rg.name
  location                 = azurerm_resource_group.rg.location
  account_tier             = "Standard"
  account_replication_type = "LRS"
}

resource "azurerm_storage_container" "va_container" {
  name                  = "sql-va"
  storage_account_name  = azurerm_storage_account.va_storage.name
  container_access_type = "private"
}

# Required: server security alert policy (VA depends on this)
resource "azurerm_mssql_server_security_alert_policy" "sql_server_policy" {
  resource_group_name = azurerm_resource_group.rg.name
  server_name         = azurerm_mssql_server.sql_server.name
  state               = "Enabled"
}

# Enable Vulnerability Assessment on the SQL Server and set the storage account
resource "azurerm_mssql_server_vulnerability_assessment" "sql_server_va" {
  server_security_alert_policy_id = azurerm_mssql_server_security_alert_policy.sql_server_policy.id

  # Storage location for VA scan results
  storage_container_path = "${azurerm_storage_account.va_storage.primary_blob_endpoint}${azurerm_storage_container.va_container.name}"
  storage_account_access_key = azurerm_storage_account.va_storage.primary_access_key

  recurring_scans {
    enabled                   = true
    email_subscription_admins = true
    emails                    = ["SECURITY_TEAM_EMAIL@example.com"]  # replace or leave empty list
  }
}

This configuration enables SQL Vulnerability Assessment on the Azure SQL Server and stores scan results in the specified Storage Account container; it does not force a replacement of the existing SQL server, but it may create/modify the storage account and policy resources if they are new.

To verify, terraform plan should show creation (or update) of:

  • azurerm_storage_account.va_storage and azurerm_storage_container.va_container (if they’re new),
  • azurerm_mssql_server_security_alert_policy.sql_server_policy,
  • azurerm_mssql_server_vulnerability_assessment.sql_server_va, with no -/+ replacement of azurerm_mssql_server.sql_server.

Additional Reading:​