Enable vulnerability assessments on Azure SQL databases with Express Configuration

Important

Attention: All Microsoft Defender for Cloud features will be officially retired in Azure in China region on August 18, 2026 per the announcement posted by 21Vianet.

This article provides PowerShell scripts for enabling SQL Vulnerability Assessment (VA) with Express Configuration in Microsoft Defender for Cloud. SQL Vulnerability Assessment helps you identify and remediate security vulnerabilities in your databases. Express Configuration simplifies the setup process by using Azure-managed storage instead of requiring you to configure a customer-managed storage account. Both script versions support automated migration from Classic Configuration, baseline extraction and reapplication, and database scanning to reflect the new configuration.

Version types

Two script versions are available depending on your resource type:

Preview version: Uses the unified REST API (2026-04-01-preview) and supports Azure SQL Database, Azure SQL Managed Instance (Preview), and Azure Synapse Analytics (Preview). This version is the recommended approach for new deployments and supports all three resource types in a single script.
Generally available version: Supports Azure SQL Database only, using the Azure PowerShell (Az.Sql) module.

Both scripts migrate from Classic Configuration (customer-managed storage) to Express Configuration (Azure-managed storage), including baseline migration. Scan history isn't migrated in either version.

Preview version
Generally available version

Public preview for Azure SQL Managed Instance and Azure Synapse Analytics Workspace (unified API)

Overview

This script automates the migration from Classic Configuration (customer-managed storage for vulnerability assessment baselines and scan results) to Express Configuration (Azure-managed storage) for SQL Vulnerability Assessment in Microsoft Defender for Cloud. With Express Configuration, you don't need a storage account. Microsoft stores baselines and scan results.

Supported resource types

Resource type	Azure Resource Manager provider
Azure SQL Database	`Microsoft.Sql/servers`
SQL Managed Instance	`Microsoft.Sql/managedInstances`
Azure Synapse Analytics	`Microsoft.Synapse/workspaces`

What the script does

Reads your current Classic Configuration baselines from blob storage
Removes Classic Configuration
Enables Express Configuration via the unified API (2026-04-01-preview)
Scans all databases (prebaseline scan)
Re-applies your baselines to Express Configuration
Scans all databases again (post-baseline scan) to reflect the updated baseline state

Note

Scan history isn't migrated. Past scan results remain in your original storage account.

If enablement fails, the script automatically offers to restore your Classic Configuration settings.
If baseline migration partially fails, you choose how to proceed: retry, review individually, revert, or skip.
Running the script on a resource that already has Express Configuration is safe. It exits with no changes.

Prerequisites

PowerShell

Requirement	Minimum version
PowerShell	7.0
Az.Accounts module	2.9.1
Az.Storage module	4.8.0

Install or update the modules:

Install-Module Az.Accounts -MinimumVersion 2.9.1 -Scope CurrentUser
Install-Module Az.Storage  -MinimumVersion 4.8.0  -Scope CurrentUser

Azure authentication

Connect-AzAccount -Environment AzureChinaCloud
Set-AzContext -SubscriptionId "<your-subscription-id>"

Permissions

The identity running the script needs the following permissions:

Permission	Scope	Why
Microsoft.Security/* (or Contributor)	Server/MI/Workspace	Enable Express Configuration and manage baselines
Microsoft.Sql/* or Microsoft.Synapse/*	Server/MI/Workspace	Read/delete Classic Configuration settings, list databases, initiate scans
Storage Blob Data Reader	Storage accounts used by Classic Configuration	Read existing baselines from blob storage

Important

SQL Managed Instance: Your SQL Managed Instance must have a System-Assigned Managed Identity (SAMI) enabled. If SAMI is disabled, Express Configuration enablement fails. Enable SAMI in the Azure portal under your MI → Identity → System Assigned → On.

Important

Storage firewall: If your Classic Configuration storage account has firewall rules, allow the script's machine to reach it. You can allow list your IP or use a Private Endpoint.

Parameters

Parameter	Required	Default	Description
`-ServerResourceId`	Yes	-	Full Azure Resource Manager resource ID of the server, managed instance, or Synapse workspace
`-Force`	No	`$false`	Skip all confirmation prompts (useful for automation)
`-ScanTimeoutSeconds`	No	`300`	Max seconds to wait per database scan
`-ScanPollingIntervalSeconds`	No	`10`	Seconds between scan status polls

How to find your resource ID

In the Azure portal, go to your resource → Properties → Resource ID, or use:

# SQL Database server
(Get-AzSqlServer -ResourceGroupName "myRG" -ServerName "myServer").ResourceId

# SQL Managed Instance
(Get-AzSqlInstance -ResourceGroupName "myRG" -InstanceName "myMI").Id

# Synapse workspace
(Get-AzSynapseWorkspace -ResourceGroupName "myRG" -Name "myWorkspace").Id

Usage examples

SQL Database server

.\MigrateToExpressConfiguration.ps1 `
  -ServerResourceId "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Sql/servers/<server-name>"

SQL Managed Instance

.\MigrateToExpressConfiguration.ps1 `
  -ServerResourceId "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Sql/managedInstances/<mi-name>"

Azure Synapse Analytics

.\MigrateToExpressConfiguration.ps1 `
  -ServerResourceId "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Synapse/workspaces/<workspace-name>"

Non-interactive (automation / CI)

.\MigrateToExpressConfiguration.ps1 `
  -ServerResourceId "<resource-id>" `
  -Force

With -Force, the script:

Skips the confirmation prompt before removing Classic Configuration
Automatically skips any failed baseline rules (no interactive recovery menu)

Sample script - MigrateToExpressConfiguration.ps1

#Requires -Modules @{ ModuleName="Az.Accounts"; ModuleVersion="2.9.1" }
#Requires -Modules @{ ModuleName="Az.Storage"; ModuleVersion="4.8.0" }
#Requires -Version 7.0

<#
.SYNOPSIS
    Migrates Azure SQL resources from Classic VA configuration to Express Configuration
    using the unified v2026-04-01-preview API.

.DESCRIPTION
    Supports: Azure SQL Database, SQL Managed Instance, and Azure Synapse Analytics.

    The script:
    1. Detects the resource type from the ARM resource ID
    2. Checks if Express Configuration is already enabled
    3. Extracts existing baselines from Classic Configuration storage (if any)
    4. Removes Classic VA settings (blocking - aborts and restores on failure)
    5. Enables Express Configuration via the new unified API
       - On failure: automatically offers to restore Classic Configuration
    6. Discovers and scans all databases
    7. Applies migrated baselines
       - On failure: offers interactive recovery (retry, per-rule review, or revert)
    8. Reports a detailed migration summary

    Scan history is NOT migrated - it remains in the original storage account.

    To revert manually, see:
    https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration

.PARAMETER ServerResourceId
    Server-level ARM resource ID. Examples:
      /subscriptions/{sub}/resourceGroups/{rg}/providers/Microsoft.Sql/servers/{server}
      /subscriptions/{sub}/resourceGroups/{rg}/providers/Microsoft.Sql/managedInstances/{mi}
      /subscriptions/{sub}/resourceGroups/{rg}/providers/Microsoft.Synapse/workspaces/{ws}

.PARAMETER Force
    Skip confirmation prompts (removal of Classic VA, baseline recovery choices).

.PARAMETER ScanTimeoutSeconds
    Maximum time in seconds to wait for each database scan to complete. Default: 300.

.PARAMETER ScanPollingIntervalSeconds
    Interval in seconds between scan status polls. Default: 10.

.EXAMPLE
    .\MigrateToExpressConfiguration.ps1 -ServerResourceId "/subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.Sql/servers/<server>"

.EXAMPLE
    .\MigrateToExpressConfiguration.ps1 -ServerResourceId "/subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.Sql/managedInstances/<mi>" -Force

.EXAMPLE
    .\MigrateToExpressConfiguration.ps1 -ServerResourceId "/subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.Synapse/workspaces/<ws>"
#>

param(
    [Parameter(Mandatory = $true)]
    [string]$ServerResourceId,

    [switch]$Force,

    [int]$ScanTimeoutSeconds = 300,

    [int]$ScanPollingIntervalSeconds = 10
)

$ErrorActionPreference = "Stop"
$ExpressApiVersion = "2026-04-01-preview"

# Classic VA API versions per resource type
$ClassicApiVersions = @{
    SqlServer          = "2021-11-01"
    SqlManagedInstance = "2023-08-01"
    Synapse            = "2021-06-01"
}

# ARM API versions for listing databases
$DatabaseListApiVersions = @{
    SqlServer          = "2021-11-01"
    SqlManagedInstance = "2023-08-01"
    Synapse            = "2021-06-01-preview"
}

# ======================================================================
#region --- Logging helpers ---
# ======================================================================

function Write-Log {
    param([string]$Message)
    Write-Host ("{0} - {1}" -f (Get-Date -Format "yyyy-MM-dd HH:mm:ss"), $Message)
}

function Write-LogError {
    param([string]$Message)
    Write-Host ("{0} - ERROR: {1}" -f (Get-Date -Format "yyyy-MM-dd HH:mm:ss"), $Message) -ForegroundColor Red
}

function Write-LogWarn {
    param([string]$Message)
    Write-Host ("{0} - WARN: {1}" -f (Get-Date -Format "yyyy-MM-dd HH:mm:ss"), $Message) -ForegroundColor Yellow
}

function Write-LogSuccess {
    param([string]$Message)
    Write-Host ("{0} - {1}" -f (Get-Date -Format "yyyy-MM-dd HH:mm:ss"), $Message) -ForegroundColor Green
}

function Write-LogDetail {
    param([string]$Message)
    Write-Host ("{0}   {1}" -f (Get-Date -Format "yyyy-MM-dd HH:mm:ss"), $Message) -ForegroundColor DarkGray
}

function Write-Section {
    param([string]$Title)
    Write-Host ""
    Write-Host ([string]::new([char]0x2501, 60)) -ForegroundColor Cyan
    Write-Host "  $Title" -ForegroundColor Cyan
    Write-Host ([string]::new([char]0x2501, 60)) -ForegroundColor Cyan
}

function Write-SubSection {
    param([string]$Title)
    Write-Host ""
    Write-Host "  --- $Title ---" -ForegroundColor Yellow
}

function Write-Box {
    param([string[]]$Lines)
    Write-Host ""
    Write-Host "  $([char]0x250C)$([string]::new([char]0x2500, 56))" -ForegroundColor DarkCyan
    foreach ($line in $Lines) {
        Write-Host "  $([char]0x2502) $line" -ForegroundColor DarkCyan
    }
    Write-Host "  $([char]0x2514)$([string]::new([char]0x2500, 56))" -ForegroundColor DarkCyan
}

#endregion

# ======================================================================
#region --- Retry ---
# ======================================================================

function Invoke-WithRetry {
    param(
        [scriptblock]$Action,
        [int]$MaxAttempts = 3,
        [string]$Description = "operation"
    )

    for ($attempt = 1; $attempt -le $MaxAttempts; $attempt++) {
        try {
            return (& $Action)
        }
        catch {
            if ($attempt -eq $MaxAttempts) {
                Write-LogError "Failed '$Description' after $MaxAttempts attempts: $($_.Exception.Message)"
                throw
            }
            $delay = [math]::Pow(2, $attempt) - 1
            Write-LogDetail "Attempt $attempt/$MaxAttempts failed for '$Description'. Retrying in ${delay}s..."
            Start-Sleep -Seconds $delay
        }
    }
}

#endregion

# ======================================================================
#region --- REST helper ---
# ======================================================================

function Invoke-ArmRequest {
    param(
        [string]$Method,
        [string]$Path,
        [object]$Body = $null
    )

    $params = @{ Method = $Method; Path = $Path }
    if ($Body) {
        $params.Payload = ($Body | ConvertTo-Json -Depth 10)
    }

    Write-LogDetail "$Method $Path"
    $resp = Invoke-AzRestMethod @params
    Write-LogDetail "=> HTTP $($resp.StatusCode)"

    return $resp
}

function Get-ErrorMessage {
    param($Response)
    try {
        $parsed = $Response.Content | ConvertFrom-Json
        if ($parsed.error.message) { return $parsed.error.message }
        if ($parsed.error.code)    { return "$($parsed.error.code): $($parsed.error.message)" }
    }
    catch {}
    return $Response.Content
}

#endregion

# ======================================================================
#region --- Resource ID Parsing ---
# ======================================================================

function Parse-ServerResourceId {
    param([string]$Id)

    $Id = $Id.Trim("/")

    $result = @{
        FullId         = $Id
        SubscriptionId = $null
        ResourceGroup  = $null
        ResourceName   = $null
        ResourceType   = $null
    }

    if ($Id -match "subscriptions/([^/]+)/resourceGroups/([^/]+)/") {
        $result.SubscriptionId = $Matches[1]
        $result.ResourceGroup  = $Matches[2]
    }
    else {
        throw "Could not parse subscription and resource group from: $Id"
    }

    if ($Id -match "providers/Microsoft\.Sql/servers/([^/]+)$") {
        $result.ResourceType = "SqlServer"
        $result.ResourceName = $Matches[1]
    }
    elseif ($Id -match "providers/Microsoft\.Sql/managedInstances/([^/]+)$") {
        $result.ResourceType = "SqlManagedInstance"
        $result.ResourceName = $Matches[1]
    }
    elseif ($Id -match "providers/Microsoft\.Synapse/workspaces/([^/]+)$") {
        $result.ResourceType = "Synapse"
        $result.ResourceName = $Matches[1]
    }
    else {
        throw @"
Unsupported or non-server-level resource ID. Expected one of:
  .../Microsoft.Sql/servers/{name}
  .../Microsoft.Sql/managedInstances/{name}
  .../Microsoft.Synapse/workspaces/{name}
Got: $Id
"@
    }

    return $result
}

#endregion

# ======================================================================
#region --- Classic VA Functions ---
# ======================================================================

function Get-ClassicVAStorageFromContent {
    param([string]$ResponseContent)

    $content = $ResponseContent | ConvertFrom-Json
    $path = $content.properties.storageContainerPath
    if ([string]::IsNullOrEmpty($path)) { return $null }

    $parts = $path -split "/"
    return @{
        StorageAccount = $parts[2].Split(".")[0]
        ContainerName  = $parts[3]
    }
}

function Get-StorageKey {
    param([hashtable]$Storage)
    return "$($Storage.StorageAccount)/$($Storage.ContainerName)"
}

# Check if ADS/ATP is enabled at a given resource path
function Get-AdsEnabled {
    param([string]$AtpUri)
    try {
        $resp = Invoke-ArmRequest -Method GET -Path $AtpUri
        if ($resp.StatusCode -eq 200) {
            $content = $resp.Content | ConvertFrom-Json
            $state = $content.properties.state
            return ($state -eq "Enabled")
        }
    }
    catch {}
    return $false
}

# Determine effective storage for a database using the same logic as the service:
#   1. If DB VA defined (has storagePath):
#        - If server VA defined AND server ADS ON AND DB ADS OFF → server storage
#        - Else → DB storage
#   2. Else → server storage
function Get-EffectiveStorage {
    param(
        [hashtable]$ServerStorage,
        [bool]$IsServerVaDefined,
        [bool]$IsServerAdsEnabled,
        [hashtable]$DbStorage,          # $null if DB VA has no storagePath
        [bool]$IsDatabaseAdsEnabled
    )

    $isDatabaseVaDefined = $null -ne $DbStorage

    if ($isDatabaseVaDefined) {
        if ($IsServerVaDefined -and $IsServerAdsEnabled -and -not $IsDatabaseAdsEnabled) {
            return @{ Storage = $ServerStorage; Source = "Server (ADS override)" }
        }
        else {
            return @{ Storage = $DbStorage; Source = "Database" }
        }
    }
    else {
        return @{ Storage = $ServerStorage; Source = "Server" }
    }
}

function Get-ClassicVASettings {
    param([hashtable]$Resource, [string[]]$Databases)

    $classicApiVersion = $ClassicApiVersions[$Resource.ResourceType]
    $settings = @{
        HasClassicVA       = $false
        ServerStorage      = $null
        DatabaseStorage    = @{}   # dbName → storage (only when effective storage differs from server)
        # Saved response bodies for restore
        RestoreData        = @{
            ServerUri  = $null
            ServerBody = $null
            Databases  = @{}   # dbName → @{ Uri; Body }
        }
    }

    # --- ATP URI templates per resource type ---
    $atpPaths = @{
        SqlServer          = @{
            Server = "/$($Resource.FullId)/advancedThreatProtectionSettings/Default?api-version=$classicApiVersion"
            Db     = { param($db) "/$($Resource.FullId)/databases/$db/advancedThreatProtectionSettings/Default?api-version=$classicApiVersion" }
        }
        SqlManagedInstance = @{
            Server = "/$($Resource.FullId)/advancedThreatProtectionSettings/Default?api-version=$classicApiVersion"
            Db     = { param($db) "/$($Resource.FullId)/databases/$db/advancedThreatProtectionSettings/Default?api-version=$classicApiVersion" }
        }
        Synapse            = @{
            Server = "/$($Resource.FullId)/securityAlertPolicies/Default?api-version=$($ClassicApiVersions['Synapse'])"
            Db     = { param($pool) "/$($Resource.FullId)/sqlPools/$pool/securityAlertPolicies/Default?api-version=$($ClassicApiVersions['Synapse'])" }
        }
    }

    $atp = $atpPaths[$Resource.ResourceType]

    # Check server-level ADS/ATP
    $isServerAdsEnabled = Get-AdsEnabled -AtpUri $atp.Server
    Write-LogDetail "Server ADS enabled: $isServerAdsEnabled"

    switch ($Resource.ResourceType) {
        "SqlServer" {
            # Server-level VA
            $uri = "/$($Resource.FullId)/vulnerabilityAssessments/default?api-version=$classicApiVersion"
            $resp = Invoke-ArmRequest -Method GET -Path $uri
            $isServerVaDefined = $false
            if ($resp.StatusCode -eq 200) {
                $storage = Get-ClassicVAStorageFromContent $resp.Content
                if ($storage) {
                    $settings.HasClassicVA = $true
                    $settings.ServerStorage = $storage
                    $settings.RestoreData.ServerUri = $uri
                    $settings.RestoreData.ServerBody = $resp.Content
                    $isServerVaDefined = $true
                    Write-Log "  Server-level Classic VA: $(Get-StorageKey $storage)"
                }
            }

            # Master database
            $masterUri = "/$($Resource.FullId)/databases/master/vulnerabilityAssessments/default?api-version=$classicApiVersion"
            $resp = Invoke-ArmRequest -Method GET -Path $masterUri
            if ($resp.StatusCode -eq 200) {
                $dbStorage = Get-ClassicVAStorageFromContent $resp.Content
                if ($dbStorage) {
                    $settings.HasClassicVA = $true
                    if (-not $settings.ServerStorage) { $settings.ServerStorage = $dbStorage }
                    $settings.RestoreData.Databases["master"] = @{ Uri = $masterUri; Body = $resp.Content }
                    Write-Log "  master Classic VA: $(Get-StorageKey $dbStorage)"

                    $isMasterAdsEnabled = Get-AdsEnabled -AtpUri (& $atp.Db "master")
                    $effective = Get-EffectiveStorage -ServerStorage $settings.ServerStorage -IsServerVaDefined $isServerVaDefined `
                        -IsServerAdsEnabled $isServerAdsEnabled -DbStorage $dbStorage -IsDatabaseAdsEnabled $isMasterAdsEnabled
                    if ($effective.Storage -and (Get-StorageKey $effective.Storage) -ne (Get-StorageKey $settings.ServerStorage)) {
                        $settings.DatabaseStorage["master"] = $effective.Storage
                        Write-LogDetail "  master effective: $(Get-StorageKey $effective.Storage) ($($effective.Source))"
                    }
                }
            }

            # User databases
            foreach ($db in $Databases) {
                if ($db -eq "master") { continue }
                $dbUri = "/$($Resource.FullId)/databases/$db/vulnerabilityAssessments/default?api-version=$classicApiVersion"
                $resp = Invoke-ArmRequest -Method GET -Path $dbUri
                if ($resp.StatusCode -eq 200) {
                    $dbStorage = Get-ClassicVAStorageFromContent $resp.Content
                    if ($dbStorage) {
                        if (-not $settings.ServerStorage) {
                            $settings.HasClassicVA = $true
                            $settings.ServerStorage = $dbStorage
                        }
                        $settings.RestoreData.Databases[$db] = @{ Uri = $dbUri; Body = $resp.Content }
                        Write-Log "  '$db' Classic VA: $(Get-StorageKey $dbStorage)"

                        $isDbAdsEnabled = Get-AdsEnabled -AtpUri (& $atp.Db $db)
                        $effective = Get-EffectiveStorage -ServerStorage $settings.ServerStorage -IsServerVaDefined $isServerVaDefined `
                            -IsServerAdsEnabled $isServerAdsEnabled -DbStorage $dbStorage -IsDatabaseAdsEnabled $isDbAdsEnabled
                        if ($effective.Storage -and (Get-StorageKey $effective.Storage) -ne (Get-StorageKey $settings.ServerStorage)) {
                            $settings.DatabaseStorage[$db] = $effective.Storage
                            Write-LogDetail "  '$db' effective: $(Get-StorageKey $effective.Storage) ($($effective.Source))"
                        }
                    }
                }
            }
        }

        "SqlManagedInstance" {
            $uri = "/$($Resource.FullId)/vulnerabilityAssessments/default?api-version=$classicApiVersion"
            $resp = Invoke-ArmRequest -Method GET -Path $uri
            $isServerVaDefined = $false
            if ($resp.StatusCode -eq 200) {
                $storage = Get-ClassicVAStorageFromContent $resp.Content
                if ($storage) {
                    $settings.HasClassicVA = $true
                    $settings.ServerStorage = $storage
                    $settings.RestoreData.ServerUri = $uri
                    $settings.RestoreData.ServerBody = $resp.Content
                    $isServerVaDefined = $true
                    Write-Log "  Server-level Classic VA: $(Get-StorageKey $storage)"
                }
            }

            foreach ($db in $Databases) {
                if ($db -eq "master") { continue }
                $dbUri = "/$($Resource.FullId)/databases/$db/vulnerabilityAssessments/default?api-version=$classicApiVersion"
                $resp = Invoke-ArmRequest -Method GET -Path $dbUri
                if ($resp.StatusCode -eq 200) {
                    $dbStorage = Get-ClassicVAStorageFromContent $resp.Content
                    if ($dbStorage) {
                        if (-not $settings.ServerStorage) {
                            $settings.HasClassicVA = $true
                            $settings.ServerStorage = $dbStorage
                        }
                        $settings.RestoreData.Databases[$db] = @{ Uri = $dbUri; Body = $resp.Content }
                        Write-Log "  '$db' Classic VA: $(Get-StorageKey $dbStorage)"

                        $isDbAdsEnabled = Get-AdsEnabled -AtpUri (& $atp.Db $db)
                        $effective = Get-EffectiveStorage -ServerStorage $settings.ServerStorage -IsServerVaDefined $isServerVaDefined `
                            -IsServerAdsEnabled $isServerAdsEnabled -DbStorage $dbStorage -IsDatabaseAdsEnabled $isDbAdsEnabled
                        if ($effective.Storage -and (Get-StorageKey $effective.Storage) -ne (Get-StorageKey $settings.ServerStorage)) {
                            $settings.DatabaseStorage[$db] = $effective.Storage
                            Write-LogDetail "  '$db' effective: $(Get-StorageKey $effective.Storage) ($($effective.Source))"
                        }
                    }
                }
            }
        }

        "Synapse" {
            $synapseApiVer = $ClassicApiVersions["Synapse"]
            $uri = "/$($Resource.FullId)/vulnerabilityAssessments/default?api-version=$synapseApiVer"
            $resp = Invoke-ArmRequest -Method GET -Path $uri
            $isServerVaDefined = $false
            if ($resp.StatusCode -eq 200) {
                $storage = Get-ClassicVAStorageFromContent $resp.Content
                if ($storage) {
                    $settings.HasClassicVA = $true
                    $settings.ServerStorage = $storage
                    $settings.RestoreData.ServerUri = $uri
                    $settings.RestoreData.ServerBody = $resp.Content
                    $isServerVaDefined = $true
                    Write-Log "  Workspace-level Classic VA: $(Get-StorageKey $storage)"
                }
            }

            foreach ($pool in $Databases) {
                if ($pool -eq "master") { continue }
                $poolUri = "/$($Resource.FullId)/sqlPools/$pool/vulnerabilityAssessments/default?api-version=$synapseApiVer"
                $resp = Invoke-ArmRequest -Method GET -Path $poolUri
                if ($resp.StatusCode -eq 200) {
                    $dbStorage = Get-ClassicVAStorageFromContent $resp.Content
                    if ($dbStorage) {
                        if (-not $settings.ServerStorage) {
                            $settings.HasClassicVA = $true
                            $settings.ServerStorage = $dbStorage
                        }
                        $settings.RestoreData.Databases[$pool] = @{ Uri = $poolUri; Body = $resp.Content }
                        Write-Log "  SQL pool '$pool' Classic VA: $(Get-StorageKey $dbStorage)"

                        $isPoolAdsEnabled = Get-AdsEnabled -AtpUri (& $atp.Db $pool)
                        $effective = Get-EffectiveStorage -ServerStorage $settings.ServerStorage -IsServerVaDefined $isServerVaDefined `
                            -IsServerAdsEnabled $isServerAdsEnabled -DbStorage $dbStorage -IsDatabaseAdsEnabled $isPoolAdsEnabled
                        if ($effective.Storage -and (Get-StorageKey $effective.Storage) -ne (Get-StorageKey $settings.ServerStorage)) {
                            $settings.DatabaseStorage[$pool] = $effective.Storage
                            Write-LogDetail "  '$pool' effective: $(Get-StorageKey $effective.Storage) ($($effective.Source))"
                        }
                    }
                }
            }
        }
    }

    return $settings
}

function Get-DatabaseList {
    param([hashtable]$Resource)

    $databases = @()
    $listApiVersion = $DatabaseListApiVersions[$Resource.ResourceType]

    switch ($Resource.ResourceType) {
        "SqlServer" {
            $uri = "/$($Resource.FullId)/databases?api-version=$listApiVersion"
            while ($uri) {
                $resp = Invoke-ArmRequest -Method GET -Path $uri
                if ($resp.StatusCode -eq 200) {
                    $content = $resp.Content | ConvertFrom-Json
                    foreach ($db in $content.value) {
                        if ($db.name -ne "master") { $databases += $db.name }
                    }
                    $uri = $content.nextLink
                }
                else { break }
            }
        }

        "SqlManagedInstance" {
            $uri = "/$($Resource.FullId)/databases?api-version=$listApiVersion"
            while ($uri) {
                $resp = Invoke-ArmRequest -Method GET -Path $uri
                if ($resp.StatusCode -eq 200) {
                    $content = $resp.Content | ConvertFrom-Json
                    foreach ($db in $content.value) { $databases += $db.name }
                    $uri = $content.nextLink
                }
                else { break }
            }
        }

        "Synapse" {
            $uri = "/$($Resource.FullId)/sqlPools?api-version=$listApiVersion"
            while ($uri) {
                $resp = Invoke-ArmRequest -Method GET -Path $uri
                if ($resp.StatusCode -eq 200) {
                    $content = $resp.Content | ConvertFrom-Json
                    foreach ($pool in $content.value) { $databases += $pool.name }
                    $uri = $content.nextLink
                }
                else { break }
            }
        }
    }

    return $databases
}

function Get-BaselineFromStorage {
    param(
        [string]$StorageAccountName,
        [string]$ContainerName,
        [string]$ResourceName,
        [string]$DatabaseName
    )

    try {
        $ctx = New-AzStorageContext -StorageAccountName $StorageAccountName
        $prefix = "scans/$ResourceName/$DatabaseName/baseline"
        $blobs = Get-AzStorageBlob -Container $ContainerName -Context $ctx -Prefix $prefix -ErrorAction Stop
    }
    catch {
        Write-LogError "Cannot access storage '$StorageAccountName/$ContainerName'."
        Write-LogDetail "Ensure you have Storage Blob Data Reader role on the storage account."
        Write-LogDetail "$($_.Exception.Message)"
        return $null
    }

    if ($blobs.Count -eq 0) { return $null }

    $latestBlob = $blobs | Sort-Object LastModified -Descending | Select-Object -First 1
    Write-LogDetail "Blob: $($latestBlob.Name) (modified: $($latestBlob.LastModified))"

    try {
        $tempFile = [System.IO.Path]::GetTempFileName()
        $null = Get-AzStorageBlobContent -Blob $latestBlob.Name -Container $ContainerName -Context $ctx -Destination $tempFile -Force
        $content = Get-Content -Path $tempFile -Raw
        Remove-Item -Path $tempFile -Force -ErrorAction SilentlyContinue
        return $content
    }
    catch {
        Write-LogError "Failed to download baseline blob: $($_.Exception.Message)"
        return $null
    }
}

# Returns $null if storage is inaccessible - caller must abort
function Get-AllBaselines {
    param(
        [hashtable]$Resource,
        [hashtable]$ClassicSettings,
        [string[]]$AllDatabases
    )

    $baselines = @{}

    if (-not $ClassicSettings.ServerStorage) {
        Write-Log "No Classic VA storage found - no baselines to extract."
        return $baselines
    }

    $serverStorage = $ClassicSettings.ServerStorage

    # Pre-flight: verify we can access ALL distinct storage targets
    $allStorageTargets = @{}
    $allStorageTargets[(Get-StorageKey $serverStorage)] = $serverStorage
    foreach ($entry in $ClassicSettings.DatabaseStorage.GetEnumerator()) {
        $key = Get-StorageKey $entry.Value
        if (-not $allStorageTargets.ContainsKey($key)) {
            $allStorageTargets[$key] = $entry.Value
        }
    }

    Write-Log "Verifying access to $($allStorageTargets.Count) storage target(s)..."
    foreach ($target in $allStorageTargets.Values) {
        $targetKey = "$(($target).StorageAccount)/$(($target).ContainerName)"
        Write-Log "  Checking '$targetKey'..."
        try {
            $ctx = New-AzStorageContext -StorageAccountName $target.StorageAccount
            $null = Get-AzStorageBlob -Container $target.ContainerName -Context $ctx -MaxCount 1 -ErrorAction Stop
            Write-LogSuccess "  '$targetKey' is accessible."
        }
        catch {
            Write-LogError "Cannot access storage '$targetKey'."
            Write-LogDetail "$($_.Exception.Message)"
            Write-Host ""
            Write-Host "  ┌────────────────────────────────────────────────────────┐" -ForegroundColor Red
            Write-Host "  │  STORAGE ACCESS FAILED - MIGRATION ABORTED             │" -ForegroundColor Red
            Write-Host "  │                                                        │" -ForegroundColor Red
            Write-Host "  │  Cannot read baselines from storage '$targetKey'." -ForegroundColor Red
            Write-Host "  │  Migration cannot proceed because baselines             │" -ForegroundColor Red
            Write-Host "  │  would be permanently lost.                            │" -ForegroundColor Red
            Write-Host "  │                                                        │" -ForegroundColor Red
            Write-Host "  │  Fix one of the following and re-run:                  │" -ForegroundColor Red
            Write-Host "  │  - Grant 'Storage Blob Data Reader' role on the        │" -ForegroundColor Red
            Write-Host "  │    storage account to your current identity             │" -ForegroundColor Red
            Write-Host "  │  - Allowlist your IP in the storage firewall            │" -ForegroundColor Red
            Write-Host "  │  - Verify the storage account still exists              │" -ForegroundColor Red
            Write-Host "  └────────────────────────────────────────────────────────┘" -ForegroundColor Red
            return $null
        }
    }

    foreach ($db in $AllDatabases) {
        Write-Log "  Extracting baseline for '$db'..."

        $storage = if ($ClassicSettings.DatabaseStorage.ContainsKey($db)) {
            $ClassicSettings.DatabaseStorage[$db]
        }
        else {
            $serverStorage
        }

        $baseline = Get-BaselineFromStorage `
            -StorageAccountName $storage.StorageAccount `
            -ContainerName $storage.ContainerName `
            -ResourceName $Resource.ResourceName `
            -DatabaseName $db

        # Fallback: if DB-specific storage yielded nothing, try server storage
        if (-not $baseline -and $ClassicSettings.DatabaseStorage.ContainsKey($db)) {
            Write-LogDetail "No baseline in DB-specific storage, trying server storage..."
            $baseline = Get-BaselineFromStorage `
                -StorageAccountName $serverStorage.StorageAccount `
                -ContainerName $serverStorage.ContainerName `
                -ResourceName $Resource.ResourceName `
                -DatabaseName $db
        }

        if ($baseline) {
            $parsed = $baseline | ConvertFrom-Json
            $ruleCount = if ($parsed.RuleBaselines) { $parsed.RuleBaselines.Count } else { 0 }
            $baselines[$db] = $baseline
            Write-LogSuccess "  Baseline found for '$db' ($ruleCount rule(s))."
        }
        else {
            Write-LogDetail "No baseline found for '$db'."
        }
    }

    return $baselines
}

function Remove-ClassicVASettings {
    param(
        [hashtable]$Resource,
        [string[]]$Databases,
        [hashtable]$RestoreData
    )

    Write-Log "Removing Classic VA settings..."
    $classicApiVersion = $ClassicApiVersions[$Resource.ResourceType]
    $errors = @()

    switch ($Resource.ResourceType) {
        "SqlServer" {
            foreach ($db in $Databases) {
                if ($db -eq "master") { continue }
                Write-Log "  Clearing VA for database '$db'..."
                try {
                    $uri = "/$($Resource.FullId)/databases/$db/vulnerabilityAssessments/default?api-version=$classicApiVersion"
                    $resp = Invoke-ArmRequest -Method DELETE -Path $uri
                    if ($resp.StatusCode -notin @(200, 204, 404)) {
                        $errors += "Database '$db': HTTP $($resp.StatusCode) - $(Get-ErrorMessage $resp)"
                    }
                }
                catch { $errors += "Database '$db': $($_.Exception.Message)" }
            }

            if ($errors.Count -eq 0) {
                Write-Log "  Clearing VA for 'master'..."
                try {
                    $uri = "/$($Resource.FullId)/databases/master/vulnerabilityAssessments/default?api-version=$classicApiVersion"
                    $resp = Invoke-ArmRequest -Method DELETE -Path $uri
                    if ($resp.StatusCode -notin @(200, 204, 404)) {
                        $errors += "master: HTTP $($resp.StatusCode) - $(Get-ErrorMessage $resp)"
                    }
                }
                catch { $errors += "master: $($_.Exception.Message)" }
            }

            if ($errors.Count -eq 0) {
                Write-Log "  Clearing server-level VA..."
                try {
                    $uri = "/$($Resource.FullId)/vulnerabilityAssessments/default?api-version=$classicApiVersion"
                    $resp = Invoke-ArmRequest -Method DELETE -Path $uri
                    if ($resp.StatusCode -notin @(200, 204, 404)) {
                        $errors += "Server: HTTP $($resp.StatusCode) - $(Get-ErrorMessage $resp)"
                    }
                }
                catch { $errors += "Server: $($_.Exception.Message)" }
            }
        }

        "SqlManagedInstance" {
            foreach ($db in $Databases) {
                if ($db -eq "master") { continue }
                Write-Log "  Clearing VA for database '$db'..."
                try {
                    $uri = "/$($Resource.FullId)/databases/$db/vulnerabilityAssessments/default?api-version=$classicApiVersion"
                    $resp = Invoke-ArmRequest -Method DELETE -Path $uri
                    if ($resp.StatusCode -notin @(200, 204, 404)) {
                        $errors += "Database '$db': HTTP $($resp.StatusCode) - $(Get-ErrorMessage $resp)"
                    }
                }
                catch { $errors += "Database '$db': $($_.Exception.Message)" }
            }

            if ($errors.Count -eq 0) {
                Write-Log "  Clearing server-level VA..."
                try {
                    $uri = "/$($Resource.FullId)/vulnerabilityAssessments/default?api-version=$classicApiVersion"
                    $resp = Invoke-ArmRequest -Method DELETE -Path $uri
                    if ($resp.StatusCode -notin @(200, 204, 404)) {
                        $errors += "Server: HTTP $($resp.StatusCode) - $(Get-ErrorMessage $resp)"
                    }
                }
                catch { $errors += "Server: $($_.Exception.Message)" }
            }
        }

        "Synapse" {
            $synapseApiVer = $ClassicApiVersions["Synapse"]
            foreach ($pool in $Databases) {
                if ($pool -eq "master") { continue }
                Write-Log "  Clearing VA for SQL pool '$pool'..."
                try {
                    $uri = "/$($Resource.FullId)/sqlPools/$pool/vulnerabilityAssessments/default?api-version=$synapseApiVer"
                    $resp = Invoke-ArmRequest -Method DELETE -Path $uri
                    if ($resp.StatusCode -notin @(200, 204, 404)) {
                        $errors += "SQL pool '$pool': HTTP $($resp.StatusCode) - $(Get-ErrorMessage $resp)"
                    }
                }
                catch { $errors += "SQL pool '$pool': $($_.Exception.Message)" }
            }

            if ($errors.Count -eq 0) {
                Write-Log "  Clearing workspace-level VA..."
                try {
                    $uri = "/$($Resource.FullId)/vulnerabilityAssessments/default?api-version=$synapseApiVer"
                    $resp = Invoke-ArmRequest -Method DELETE -Path $uri
                    if ($resp.StatusCode -notin @(200, 204, 404)) {
                        $errors += "Workspace: HTTP $($resp.StatusCode) - $(Get-ErrorMessage $resp)"
                    }
                }
                catch { $errors += "Workspace: $($_.Exception.Message)" }
            }
        }
    }

    if ($errors.Count -gt 0) {
        Write-LogError "Failed to remove some Classic VA settings:"
        foreach ($e in $errors) { Write-LogError "  - $e" }

        # Auto-rollback: restore already-deleted Classic policies
        Write-LogWarn "Attempting to restore already-deleted Classic VA policies..."
        Restore-ClassicVASettings -Resource $Resource -RestoreData $RestoreData | Out-Null

        return $false
    }

    Write-LogSuccess "Classic VA settings removed."
    return $true
}

#endregion

# ======================================================================
#region --- Restore Classic VA ---
# ======================================================================

function Restore-ClassicVASettings {
    param(
        [hashtable]$Resource,
        [hashtable]$RestoreData
    )

    Write-Section "Restoring Classic VA Configuration"
    $anyFailure = $false

    # Step 1: Delete Express Configuration if it was enabled
    Write-Log "Deleting Express Configuration (if active)..."
    $uri = "/$($Resource.FullId)/providers/Microsoft.Security/sqlVulnerabilityAssessments/default?api-version=$ExpressApiVersion"
    try {
        $resp = Invoke-ArmRequest -Method DELETE -Path $uri
        if ($resp.StatusCode -in @(200, 204, 404)) {
            Write-LogSuccess "Express Configuration removed."
        }
        else {
            Write-LogWarn "Express Configuration DELETE returned HTTP $($resp.StatusCode). Continuing with restore..."
        }
    }
    catch {
        Write-LogWarn "Could not delete Express Configuration: $($_.Exception.Message). Continuing..."
    }

    # Step 2: Restore server-level Classic VA policy
    if ($RestoreData.ServerUri -and $RestoreData.ServerBody) {
        Write-Log "Restoring server-level Classic VA policy..."
        try {
            $resp = Invoke-AzRestMethod -Method PUT -Path $RestoreData.ServerUri -Payload $RestoreData.ServerBody
            if ($resp.StatusCode -in @(200, 201)) {
                Write-LogSuccess "Server-level Classic VA restored."
            }
            else {
                Write-LogError "Server-level restore failed (HTTP $($resp.StatusCode)): $(Get-ErrorMessage $resp)"
                $anyFailure = $true
            }
        }
        catch {
            Write-LogError "Server-level restore error: $($_.Exception.Message)"
            $anyFailure = $true
        }
    }

    # Step 3: Restore database-level Classic VA policies
    foreach ($entry in $RestoreData.Databases.GetEnumerator()) {
        $dbName = $entry.Key
        $dbRestore = $entry.Value
        Write-Log "Restoring Classic VA for '$dbName'..."
        try {
            $resp = Invoke-AzRestMethod -Method PUT -Path $dbRestore.Uri -Payload $dbRestore.Body
            if ($resp.StatusCode -in @(200, 201)) {
                Write-LogSuccess "Classic VA restored for '$dbName'."
            }
            else {
                Write-LogError "Restore failed for '$dbName' (HTTP $($resp.StatusCode)): $(Get-ErrorMessage $resp)"
                $anyFailure = $true
            }
        }
        catch {
            Write-LogError "Restore error for '$dbName': $($_.Exception.Message)"
            $anyFailure = $true
        }
    }

    if ($anyFailure) {
        Write-LogError "Some Classic VA settings could not be restored."
        Write-Host ""
        Write-Host "  Manual restore instructions:" -ForegroundColor Yellow
        Write-Host "  https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration" -ForegroundColor Yellow
        return $false
    }

    Write-LogSuccess "Classic VA configuration restored successfully."
    return $true
}

#endregion

# ======================================================================
#region --- Express Configuration Functions (v2026-04-01-preview) ---
# ======================================================================

function Get-ExpressConfigStatus {
    param([hashtable]$Resource)

    $uri = "/$($Resource.FullId)/providers/Microsoft.Security/sqlVulnerabilityAssessments/default?api-version=$ExpressApiVersion"
    $resp = Invoke-ArmRequest -Method GET -Path $uri

    if ($resp.StatusCode -eq 200) {
        $content = $resp.Content | ConvertFrom-Json
        return $content.properties.state
    }

    return "Unknown"
}

function Enable-ExpressConfig {
    param([hashtable]$Resource)

    $uri = "/$($Resource.FullId)/providers/Microsoft.Security/sqlVulnerabilityAssessments/default?api-version=$ExpressApiVersion"
    $body = @{ properties = @{ state = "Enabled" } }

    $resp = Invoke-ArmRequest -Method PUT -Path $uri -Body $body

    if ($resp.StatusCode -in @(200, 201)) {
        return @{ Success = $true; Error = $null }
    }

    $errorMsg = Get-ErrorMessage $resp
    Write-LogError "Failed to enable Express Configuration (HTTP $($resp.StatusCode)): $errorMsg"
    return @{ Success = $false; Error = $errorMsg }
}

function Invoke-DatabaseScan {
    param(
        [hashtable]$Resource,
        [string]$DatabaseName,
        [int]$TimeoutSeconds,
        [int]$PollingIntervalSeconds
    )

    $encodedDb = [System.Uri]::EscapeDataString($DatabaseName)

    $initUri = "/$($Resource.FullId)/providers/Microsoft.Security/sqlVulnerabilityAssessments/default/scans/initiateScan?api-version=$ExpressApiVersion&databaseName=$encodedDb"
    $resp = Invoke-ArmRequest -Method POST -Path $initUri

    if ($resp.StatusCode -notin @(200, 202)) {
        $errorMsg = Get-ErrorMessage $resp
        Write-LogError "InitiateScan failed for '$DatabaseName' (HTTP $($resp.StatusCode)): $errorMsg"
        return @{ Status = "InitiateFailed"; Error = $errorMsg }
    }

    if ($resp.StatusCode -eq 200) {
        Write-Log "  Scan for '$DatabaseName' completed synchronously."
        $content = $resp.Content | ConvertFrom-Json
        return @{ Status = $content.properties.state; Error = $null }
    }

    # 202 - extract operation ID from Location header
    $operationId = $null
    try {
        $location = $null
        # HttpResponseHeaders uses TryGetValues (not ContainsKey)
        $locationValues = $null
        if ($resp.Headers -and $resp.Headers.TryGetValues("Location", [ref]$locationValues)) {
            $location = $locationValues | Select-Object -First 1
        }
        if ($location -and $location -match "scanOperationResults/([^?/&]+)") {
            $operationId = $Matches[1]
        }
    }
    catch {}

    if (-not $operationId) {
        try {
            $content = $resp.Content | ConvertFrom-Json
            $operationId = $content.properties.operationId
        }
        catch {}
    }

    if (-not $operationId) {
        Write-LogError "Could not extract operation ID from InitiateScan response for '$DatabaseName'."
        return @{ Status = "InitiateFailed"; Error = "No operation ID" }
    }

    Write-Log "  Scan initiated for '$DatabaseName' (operation: $operationId). Polling..."

    $sw = [System.Diagnostics.Stopwatch]::StartNew()
    while ($sw.Elapsed.TotalSeconds -lt $TimeoutSeconds) {
        Start-Sleep -Seconds $PollingIntervalSeconds

        $pollUri = "/$($Resource.FullId)/providers/Microsoft.Security/sqlVulnerabilityAssessments/default/scans/scanOperationResults/${operationId}?api-version=$ExpressApiVersion&databaseName=$encodedDb"
        $pollResp = Invoke-ArmRequest -Method GET -Path $pollUri

        if ($pollResp.StatusCode -eq 200) {
            $pollContent = $pollResp.Content | ConvertFrom-Json
            $scanStatus = $pollContent.properties.scanStatus
            Write-Log "  '$DatabaseName' [$([int]$sw.Elapsed.TotalSeconds)s]: $scanStatus"

            if ($scanStatus -in @("Passed", "Failed", "FailedToRun")) {
                return @{ Status = $scanStatus; Error = $null }
            }
        }
    }

    Write-LogError "Scan timed out for '$DatabaseName' after ${TimeoutSeconds}s"
    return @{ Status = "Timeout"; Error = "Exceeded ${TimeoutSeconds}s" }
}

# Converts 0/1 values to False/True in baseline results.
# Returns @{ WasConverted=$true/$false; Results=<converted array> }
function Convert-BinaryResults {
    param([array]$Results)

    $wasConverted = $false
    $converted = @()

    foreach ($row in $Results) {
        $newRow = @($row)
        for ($i = 0; $i -lt $newRow.Count; $i++) {
            if ($newRow[$i] -eq "1") { $newRow[$i] = "True"; $wasConverted = $true }
            elseif ($newRow[$i] -eq "0") { $newRow[$i] = "False"; $wasConverted = $true }
        }
        $converted += , $newRow
    }

    return @{ WasConverted = $wasConverted; Results = $converted }
}

# Returns: @{ Applied=N; Failed=N; FailedRules=@( @{ RuleId; Error; Results }, ... ) }
function Set-BaselineRulesForDatabase {
    param(
        [hashtable]$Resource,
        [string]$DatabaseName,
        [string]$BaselineJson
    )

    $baseline = $BaselineJson | ConvertFrom-Json

    if (-not $baseline.RuleBaselines -or $baseline.RuleBaselines.Count -eq 0) {
        Write-Log "  No baseline rules to apply for '$DatabaseName'."
        return @{ Applied = 0; Failed = 0; FailedRules = @() }
    }

    $encodedDb = [System.Uri]::EscapeDataString($DatabaseName)

    $applied = 0
    $failed = 0
    $failedRules = @()

    $total = $baseline.RuleBaselines.Count
    $idx = 0

    foreach ($rule in $baseline.RuleBaselines) {
        $idx++
        $ruleId = $rule.RuleId
        $expectedResults = @($rule.Properties.ExpectedResults)

        $body = @{
            latestScan = $false
            results    = $expectedResults
        }

        $uri = "/$($Resource.FullId)/providers/Microsoft.Security/sqlVulnerabilityAssessments/default/baselineRules/${ruleId}?api-version=$ExpressApiVersion&databaseName=$encodedDb"

        try {
            $resp = Invoke-ArmRequest -Method PUT -Path $uri -Body $body
            if ($resp.StatusCode -in @(200, 201)) {
                $applied++
                Write-LogDetail "[$idx/$total] Rule $ruleId - applied"
            }
            elseif ($resp.StatusCode -eq 400) {
                # Try converting 0/1 → False/True (binary rules use different format in Express)
                $converted = Convert-BinaryResults $expectedResults
                if ($converted.WasConverted) {
                    Write-LogDetail "[$idx/$total] Rule $ruleId - retrying with True/False conversion..."
                    $body.results = $converted.Results
                    $retryResp = Invoke-ArmRequest -Method PUT -Path $uri -Body $body
                    if ($retryResp.StatusCode -in @(200, 201)) {
                        $applied++
                        Write-LogDetail "[$idx/$total] Rule $ruleId - applied (after binary conversion)"
                    }
                    else {
                        $errMsg = Get-ErrorMessage $retryResp
                        Write-LogError "  [$idx/$total] Rule $ruleId - failed after conversion (HTTP $($retryResp.StatusCode)): $errMsg"
                        $failed++
                        $failedRules += @{ RuleId = $ruleId; Error = "HTTP $($retryResp.StatusCode): $errMsg"; Results = $converted.Results }
                    }
                }
                else {
                    $errMsg = Get-ErrorMessage $resp
                    Write-LogError "  [$idx/$total] Rule $ruleId - failed (HTTP 400): $errMsg"
                    $failed++
                    $failedRules += @{ RuleId = $ruleId; Error = "HTTP 400: $errMsg"; Results = $expectedResults }
                }
            }
            else {
                $errMsg = Get-ErrorMessage $resp
                Write-LogError "  [$idx/$total] Rule $ruleId - failed (HTTP $($resp.StatusCode)): $errMsg"
                $failed++
                $failedRules += @{ RuleId = $ruleId; Error = "HTTP $($resp.StatusCode): $errMsg"; Results = $expectedResults }
            }
        }
        catch {
            Write-LogError "  [$idx/$total] Rule $ruleId - error: $($_.Exception.Message)"
            $failed++
            $failedRules += @{ RuleId = $ruleId; Error = $_.Exception.Message; Results = $expectedResults }
        }
    }

    return @{ Applied = $applied; Failed = $failed; FailedRules = $failedRules }
}

# Apply a single rule baseline by ID
function Set-SingleBaselineRule {
    param(
        [hashtable]$Resource,
        [string]$DatabaseName,
        [string]$RuleId,
        [array]$Results
    )

    $encodedDb = [System.Uri]::EscapeDataString($DatabaseName)
    $body = @{ latestScan = $false; results = $Results }
    $uri = "/$($Resource.FullId)/providers/Microsoft.Security/sqlVulnerabilityAssessments/default/baselineRules/${RuleId}?api-version=$ExpressApiVersion&databaseName=$encodedDb"

    $resp = Invoke-ArmRequest -Method PUT -Path $uri -Body $body

    if ($resp.StatusCode -in @(200, 201)) { return $true }

    # Try binary conversion on 400 (0/1 → False/True)
    if ($resp.StatusCode -eq 400) {
        $converted = Convert-BinaryResults $Results
        if ($converted.WasConverted) {
            Write-LogDetail "  Rule $RuleId - retrying with True/False conversion..."
            $body.results = $converted.Results
            $retryResp = Invoke-ArmRequest -Method PUT -Path $uri -Body $body
            if ($retryResp.StatusCode -in @(200, 201)) { return $true }
            Write-LogError "  Rule $RuleId failed after conversion (HTTP $($retryResp.StatusCode)): $(Get-ErrorMessage $retryResp)"
            return $false
        }
    }

    Write-LogError "  Rule $RuleId failed (HTTP $($resp.StatusCode)): $(Get-ErrorMessage $resp)"
    return $false
}

#endregion

# ======================================================================
#region --- Interactive Baseline Recovery ---
# ======================================================================

function Invoke-BaselineRecovery {
    param(
        [hashtable]$Resource,
        [hashtable]$AllBaselineFailures,    # dbName → @( @{ RuleId; Error; Results }, ... )
        [hashtable]$ClassicRestoreData,
        [bool]$HadClassicVA
    )

    $totalFailed = ($AllBaselineFailures.Values | ForEach-Object { $_.Count } | Measure-Object -Sum).Sum

    Write-Section "Baseline Recovery"
    Write-Host ""
    Write-Host "  $totalFailed baseline rule(s) failed across $($AllBaselineFailures.Count) database(s):" -ForegroundColor Yellow
    foreach ($entry in $AllBaselineFailures.GetEnumerator()) {
        $ruleIds = $entry.Value | ForEach-Object { $_.RuleId }
        Write-Host "    $($entry.Key): $($ruleIds -join ', ')" -ForegroundColor Yellow
    }

    Write-Host ""
    Write-Host "  How would you like to proceed?" -ForegroundColor White
    Write-Host "    [R] Retry all failed rules" -ForegroundColor White
    Write-Host "    [I] Interactive - review and retry each rule individually" -ForegroundColor White
    if ($HadClassicVA) {
        Write-Host "    [V] Revert - undo migration and restore Classic Configuration" -ForegroundColor White
    }
    Write-Host "    [S] Skip - keep Express Configuration, accept missing baselines" -ForegroundColor White
    Write-Host ""

    $validChoices = if ($HadClassicVA) { @("R", "I", "V", "S") } else { @("R", "I", "S") }
    do {
        $choice = (Read-Host "  Enter choice ($($validChoices -join '/'))").Trim().ToUpper()
    } while ($choice -notin $validChoices)

    switch ($choice) {
        "R" { return Invoke-RetryFailedBaselines -Resource $Resource -AllBaselineFailures $AllBaselineFailures }
        "I" { return Invoke-InteractiveBaselineReview -Resource $Resource -AllBaselineFailures $AllBaselineFailures }
        "V" {
            $restored = Restore-ClassicVASettings -Resource $Resource -RestoreData $ClassicRestoreData
            return @{ Action = "Reverted"; Restored = $restored }
        }
        "S" {
            Write-Log "Skipping failed baselines. Express Configuration remains active."
            return @{ Action = "Skipped" }
        }
    }
}

function Invoke-RetryFailedBaselines {
    param(
        [hashtable]$Resource,
        [hashtable]$AllBaselineFailures
    )

    Write-SubSection "Retrying Failed Baseline Rules"

    $retrySucceeded = 0
    $retryFailed = 0
    $stillFailed = @{}

    foreach ($entry in $AllBaselineFailures.GetEnumerator()) {
        $dbName = $entry.Key
        $failedRules = $entry.Value

        Write-Log "Retrying $($failedRules.Count) rule(s) for '$dbName'..."

        $dbStillFailed = @()
        foreach ($rule in $failedRules) {
            $ok = Set-SingleBaselineRule -Resource $Resource -DatabaseName $dbName `
                -RuleId $rule.RuleId -Results $rule.Results
            if ($ok) {
                Write-LogSuccess "  Rule $($rule.RuleId) - applied"
                $retrySucceeded++
            }
            else {
                $retryFailed++
                $dbStillFailed += $rule
            }
        }

        if ($dbStillFailed.Count -gt 0) { $stillFailed[$dbName] = $dbStillFailed }
    }

    Write-Log "Retry complete: $retrySucceeded succeeded, $retryFailed still failing."
    return @{ Action = "Retried"; Succeeded = $retrySucceeded; StillFailed = $stillFailed }
}

function Invoke-InteractiveBaselineReview {
    param(
        [hashtable]$Resource,
        [hashtable]$AllBaselineFailures
    )

    Write-SubSection "Interactive Baseline Review"

    $reviewed = 0
    $applied = 0
    $skipped = 0
    $stillFailed = @{}
    $quit = $false

    foreach ($entry in $AllBaselineFailures.GetEnumerator()) {
        if ($quit) { break }
        $dbName = $entry.Key
        $failedRules = $entry.Value

        Write-Host ""
        Write-Host "  Database: $dbName ($($failedRules.Count) failed rule(s))" -ForegroundColor Cyan

        $dbStillFailed = @()
        foreach ($rule in $failedRules) {
            if ($quit) {
                $dbStillFailed += $rule
                continue
            }

            $resultPreview = try {
                ($rule.Results | ForEach-Object { "[$($_ -join ', ')]" }) -join ", "
            }
            catch { "(could not format)" }

            Write-Box @(
                "Database : $dbName",
                "Rule     : $($rule.RuleId)",
                "Error    : $($rule.Error)",
                "Results  : $resultPreview"
            )

            Write-Host ""
            $action = ""
            do {
                $action = (Read-Host "    Retry this rule? (Y=retry / N=skip / Q=quit review)").Trim().ToUpper()
            } while ($action -notin @("Y", "N", "Q"))

            $reviewed++

            switch ($action) {
                "Y" {
                    $ok = Set-SingleBaselineRule -Resource $Resource -DatabaseName $dbName `
                        -RuleId $rule.RuleId -Results $rule.Results
                    if ($ok) {
                        Write-LogSuccess "  Rule $($rule.RuleId) - applied"
                        $applied++
                    }
                    else {
                        $dbStillFailed += $rule
                    }
                }
                "N" {
                    Write-Log "  Skipped rule $($rule.RuleId)."
                    $skipped++
                    $dbStillFailed += $rule
                }
                "Q" {
                    Write-Log "  Quit interactive review."
                    $quit = $true
                    $dbStillFailed += $rule
                }
            }
        }

        if ($dbStillFailed.Count -gt 0) { $stillFailed[$dbName] = $dbStillFailed }
    }

    Write-Log "Interactive review: $reviewed reviewed, $applied applied, $skipped skipped."
    return @{ Action = "Interactive"; Applied = $applied; Skipped = $skipped; StillFailed = $stillFailed }
}

#endregion

# ======================================================================
#region --- Summary ---
# ======================================================================

function Write-MigrationSummary {
    param(
        [hashtable]$Resource,
        $PreBaselineScanResults,
        $PostBaselineScanResults,
        [hashtable]$BaselineResults,       # dbName → @{ Applied; Failed; FailedRules }
        [hashtable]$Baselines,
        [string[]]$AllDatabases,
        [string]$FinalState                # "Completed", "Reverted", "CompletedWithErrors"
    )

    Write-Section "Migration Summary"

    # Header
    Write-Host ""
    Write-Host "  Resource      : $($Resource.ResourceName)" -ForegroundColor White
    Write-Host "  Resource Type : $($Resource.ResourceType)" -ForegroundColor White
    Write-Host "  API Version   : $ExpressApiVersion" -ForegroundColor White

    $stateColor = switch ($FinalState) {
        "Completed"           { "Green" }
        "Reverted"            { "Yellow" }
        "CompletedWithErrors" { "Red" }
        default               { "White" }
    }
    Write-Host "  Final State   : $FinalState" -ForegroundColor $stateColor
    Write-Host ""

    # Pre-baseline scan results table
    if ($PreBaselineScanResults -and $PreBaselineScanResults.Count -gt 0) {
        Write-Host "  Pre-Baseline Scan Results:" -ForegroundColor White
        Write-Host ("  {0,-20} {1,-15}" -f "Database", "Status") -ForegroundColor DarkGray
        Write-Host ("  {0,-20} {1,-15}" -f ("─" * 20), ("─" * 15)) -ForegroundColor DarkGray

        foreach ($entry in $PreBaselineScanResults.GetEnumerator()) {
            $color = if ($entry.Value -in @("Passed", "Failed")) { "Green" } else { "Red" }
            Write-Host ("  {0,-20} {1,-15}" -f $entry.Key, $entry.Value) -ForegroundColor $color
        }
        Write-Host ""
    }

    # Baseline results table
    if ($Baselines -and $Baselines.Count -gt 0) {
        Write-Host "  Baseline Migration:" -ForegroundColor White
        Write-Host ("  {0,-20} {1,-12} {2,-12}" -f "Database", "Applied", "Failed") -ForegroundColor DarkGray
        Write-Host ("  {0,-20} {1,-12} {2,-12}" -f ("─" * 20), ("─" * 12), ("─" * 12)) -ForegroundColor DarkGray

        foreach ($db in $AllDatabases) {
            if ($BaselineResults.ContainsKey($db)) {
                $r = $BaselineResults[$db]
                $fColor = if ($r.Failed -gt 0) { "Red" } else { "Green" }
                Write-Host ("  {0,-20} " -f $db) -NoNewline -ForegroundColor White
                Write-Host ("{0,-12} " -f $r.Applied) -NoNewline -ForegroundColor Green
                Write-Host ("{0,-12}" -f $r.Failed) -ForegroundColor $fColor
            }
            elseif (-not $Baselines.ContainsKey($db)) {
                Write-Host ("  {0,-20} {1,-12} {2,-12}" -f $db, "-", "-") -ForegroundColor Gray
            }
        }
        Write-Host ""
    }

    # Post-baseline scan results table
    if ($PostBaselineScanResults -and $PostBaselineScanResults.Count -gt 0) {
        Write-Host "  Post-Baseline Scan Results:" -ForegroundColor White
        Write-Host ("  {0,-20} {1,-15}" -f "Database", "Status") -ForegroundColor DarkGray
        Write-Host ("  {0,-20} {1,-15}" -f ("─" * 20), ("─" * 15)) -ForegroundColor DarkGray

        foreach ($entry in $PostBaselineScanResults.GetEnumerator()) {
            $color = if ($entry.Value -in @("Passed", "Failed")) { "Green" } else { "Red" }
            Write-Host ("  {0,-20} {1,-15}" -f $entry.Key, $entry.Value) -ForegroundColor $color
        }
        Write-Host ""
    }

    # Footer
    switch ($FinalState) {
        "Completed" {
            Write-LogSuccess "Migration completed successfully!"
        }
        "CompletedWithErrors" {
            Write-LogWarn "Migration completed with some baseline failures. See details above."
            Write-Host "  To revert: https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration" -ForegroundColor Yellow
        }
        "Reverted" {
            Write-LogWarn "Migration was reverted. Classic VA configuration has been restored."
        }
    }
}

#endregion

# ======================================================================
# ======================================================================
#  MAIN FLOW
# ======================================================================
# ======================================================================

Write-Host ""
Write-Host "  SQL Vulnerability Assessment" -ForegroundColor Magenta
Write-Host "  Migration to Express Configuration" -ForegroundColor Magenta
Write-Host "  API Version: $ExpressApiVersion" -ForegroundColor Magenta
Write-Host ""

# --- Verify Azure auth ---
try {
    $context = Get-AzContext
    if (-not $context) { throw "No context" }
    Write-Log "Azure context: $($context.Account.Id) (Subscription: $($context.Subscription.Name))"
}
catch {
    Write-LogError "Not authenticated. Run Connect-AzAccount first."
    return
}

# --- Parse resource ID ---
$resource = Parse-ServerResourceId $ServerResourceId
Write-Log "Resource Type : $($resource.ResourceType)"
Write-Log "Resource Name : $($resource.ResourceName)"
Write-Log "Subscription  : $($resource.SubscriptionId)"
Write-Log "Resource Group: $($resource.ResourceGroup)"

# Ensure correct subscription context
$currentSubId = $context.Subscription.Id
if ($currentSubId -ne $resource.SubscriptionId) {
    Write-Log "Switching subscription context to $($resource.SubscriptionId)..."
    $null = Set-AzContext -SubscriptionId $resource.SubscriptionId
}

# ======================================================================
#  Step 1: Check Express Configuration
# ======================================================================
Write-Section "Step 1: Check Express Configuration Status"
$expressState = Get-ExpressConfigStatus -Resource $resource
Write-Log "Express Configuration state: $expressState"

if ($expressState -eq "Enabled") {
    Write-LogSuccess "Express Configuration is already enabled on this resource. No migration needed."
    return
}

# ======================================================================
#  Step 2: Discover Databases
# ======================================================================
Write-Section "Step 2: Discover Databases"
$databases = Get-DatabaseList -Resource $resource

# System databases per resource type
$systemDatabases = switch ($resource.ResourceType) {
    "SqlServer"          { @("master") }
    "SqlManagedInstance" { @("master", "msdb", "model") }
    "Synapse"            { @("master") }
}

# Combine and de-duplicate (ARM listing may include system DBs)
$allDatabases = @($systemDatabases) + @($databases) | Select-Object -Unique
Write-Log "Found $($databases.Count) user database(s) + $($systemDatabases.Count) system database(s): $($allDatabases -join ', ')"

if ($databases.Count -eq 0) {
    Write-LogWarn "No user databases found. Proceeding with system database(s) only."
}

# ======================================================================
#  Step 3: Check Classic VA Configuration
# ======================================================================
Write-Section "Step 3: Check Classic VA Configuration"
$classicSettings = Get-ClassicVASettings -Resource $resource -Databases $allDatabases

$baselines = @{}
$hadClassicVA = $classicSettings.HasClassicVA

if ($hadClassicVA) {
    Write-Log "Classic VA configuration detected."
    if ($classicSettings.ServerStorage) {
        Write-Log "  Server storage : $($classicSettings.ServerStorage.StorageAccount)/$($classicSettings.ServerStorage.ContainerName)"
    }
    if ($classicSettings.DatabaseStorage.Count -gt 0) {
        Write-Log "  Storage overrides: $($classicSettings.DatabaseStorage.Keys -join ', ')"
    }

    # ==================================================================
    #  Step 4: Extract Baselines
    # ==================================================================
    Write-Section "Step 4: Extract Baselines from Classic Storage"
    $baselines = Get-AllBaselines -Resource $resource -ClassicSettings $classicSettings -AllDatabases $allDatabases

    if ($null -eq $baselines) {
        Write-LogError "Migration aborted - cannot proceed without storage access."
        return
    }

    $baselinesFound = ($baselines.Keys | Measure-Object).Count
    $baselineDbNames = ($baselines.Keys | Sort-Object) -join ", "
    Write-Log "Baselines extracted for $baselinesFound of $($allDatabases.Count) database(s): $baselineDbNames"

    # ==================================================================
    #  Step 5: Confirm & Remove Classic VA
    # ==================================================================
    if (-not $Force) {
        Write-Host ""
        Write-Host "  ┌────────────────────────────────────────────────────────┐" -ForegroundColor Yellow
        Write-Host "  │  WARNING                                              │" -ForegroundColor Yellow
        Write-Host "  │                                                        │" -ForegroundColor Yellow
        Write-Host "  │  This will remove the current Classic VA settings for  │" -ForegroundColor Yellow
        Write-Host "  │  this resource and all databases.                      │" -ForegroundColor Yellow
        Write-Host "  │                                                        │" -ForegroundColor Yellow
        Write-Host "  │  • Baselines have been extracted and will be           │" -ForegroundColor Yellow
        Write-Host "  │    re-applied after enabling Express Configuration.    │" -ForegroundColor Yellow
        Write-Host "  │  • Scan history will NOT be migrated.                  │" -ForegroundColor Yellow
        Write-Host "  │  • If enablement fails, the script will offer to       │" -ForegroundColor Yellow
        Write-Host "  │    automatically restore Classic Configuration.        │" -ForegroundColor Yellow
        Write-Host "  └────────────────────────────────────────────────────────┘" -ForegroundColor Yellow
        Write-Host ""
        $confirmation = Read-Host "  Do you want to proceed? (y/n)"
        if ($confirmation -ne "y") {
            Write-Log "Migration cancelled by user."
            return
        }
    }

    Write-Section "Step 5: Remove Classic VA Settings"
    $removeSuccess = Remove-ClassicVASettings -Resource $resource -Databases $allDatabases -RestoreData $classicSettings.RestoreData

    if (-not $removeSuccess) {
        Write-LogError "Failed to fully remove Classic VA settings. Migration aborted."
        Write-Host ""
        Write-Host "  Express Configuration cannot be enabled while Classic VA policies remain." -ForegroundColor Yellow
        Write-Host "  Fix the errors above and re-run this script." -ForegroundColor Yellow
        return
    }
}
else {
    Write-Log "No Classic VA configuration found. Proceeding directly to enable Express Configuration."
}

# ======================================================================
#  Step 6: Enable Express Configuration
# ======================================================================
$stepNum = if ($hadClassicVA) { 6 } else { 4 }
Write-Section "Step $stepNum`: Enable Express Configuration"

$enableResult = Enable-ExpressConfig -Resource $resource

if (-not $enableResult.Success) {
    Write-LogError "Failed to enable Express Configuration."

    # Detect identity-related errors (SQL MI specific)
    $isIdentityError = $false
    if ($resource.ResourceType -eq "SqlManagedInstance" -and $enableResult.Error) {
        $errLower = $enableResult.Error.ToLower()
        if ($errLower -match "identity" -or $errLower -match "managed.?identity" -or
            $errLower -match "systemassigned" -or $errLower -match "authentication" -or
            $errLower -match "principal") {
            $isIdentityError = $true
        }
    }

    if ($isIdentityError) {
        Write-Host ""
        Write-Host "  ┌────────────────────────────────────────────────────────┐" -ForegroundColor Red
        Write-Host "  │  SQL MANAGED INSTANCE - IDENTITY ERROR                 │" -ForegroundColor Red
        Write-Host "  │                                                        │" -ForegroundColor Red
        Write-Host "  │  Express Configuration requires a System-Assigned      │" -ForegroundColor Red
        Write-Host "  │  Managed Identity (SAMI) on the Managed Instance.      │" -ForegroundColor Red
        Write-Host "  │                                                        │" -ForegroundColor Red
        Write-Host "  │  To fix:                                               │" -ForegroundColor Red
        Write-Host "  │  1. Go to Azure Portal > your MI > Identity            │" -ForegroundColor Red
        Write-Host "  │  2. Enable System-Assigned Managed Identity            │" -ForegroundColor Red
        Write-Host "  │  3. Re-run this script                                 │" -ForegroundColor Red
        Write-Host "  │                                                        │" -ForegroundColor Red
        Write-Host "  │  Or via CLI:                                           │" -ForegroundColor Red
        Write-Host "  │  az sql mi update --name <mi> --resource-group <rg> \  │" -ForegroundColor Red
        Write-Host "  │    --assign-identity                                   │" -ForegroundColor Red
        Write-Host "  │                                                        │" -ForegroundColor Red
        Write-Host "  │  NOTE: If both UAMI and SAMI are enabled, enablement   │" -ForegroundColor Red
        Write-Host "  │  may also fail. Try temporarily removing the UAMI.     │" -ForegroundColor Red
        Write-Host "  └────────────────────────────────────────────────────────┘" -ForegroundColor Red
    }

    # Offer to restore Classic VA if we had one (server-level or database-level)
    $hasRestoreData = $classicSettings.RestoreData.ServerBody -or ($classicSettings.RestoreData.Databases.Count -gt 0)
    if ($hadClassicVA -and $hasRestoreData) {
        if (-not $isIdentityError) {
            Write-Host ""
            Write-Host "  Express Configuration could not be enabled after removing Classic VA." -ForegroundColor Red
            Write-Host "  Common causes:" -ForegroundColor Yellow
            Write-Host "    - Classic VA policy removal is still propagating (wait a few minutes)" -ForegroundColor Yellow
            if ($resource.ResourceType -eq "SqlManagedInstance") {
                Write-Host "    - System-Assigned Managed Identity (SAMI) is not enabled on the MI" -ForegroundColor Yellow
            }
        }
        Write-Host ""

        $restoreChoice = "R"
        if (-not $Force) {
            Write-Host "  Would you like to restore Classic Configuration?" -ForegroundColor White
            Write-Host "    [R] Restore Classic Configuration now (recommended)" -ForegroundColor White
            if (-not $isIdentityError) {
                # Wait & Retry only makes sense for propagation delays, not identity issues
                Write-Host "    [W] Wait 60 seconds and retry enablement" -ForegroundColor White
            }
            Write-Host "    [Q] Quit (leaves resource without VA - manual action needed)" -ForegroundColor White
            Write-Host ""

            $validChoices = if ($isIdentityError) { @("R", "Q") } else { @("R", "W", "Q") }
            do {
                $restoreChoice = (Read-Host "  Enter choice ($($validChoices -join '/'))").Trim().ToUpper()
            } while ($restoreChoice -notin $validChoices)
        }

        switch ($restoreChoice) {
            "R" {
                $restored = Restore-ClassicVASettings -Resource $resource -RestoreData $classicSettings.RestoreData
                if ($restored) {
                    Write-MigrationSummary -Resource $resource -PreBaselineScanResults $null `
                        -PostBaselineScanResults $null -BaselineResults @{} -Baselines @{} `
                        -AllDatabases $allDatabases -FinalState "Reverted"
                }
                else {
                    Write-LogError "Classic VA restore encountered errors. Check messages above."
                    Write-Host "  Manual restore: https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration" -ForegroundColor Yellow
                }
                return
            }
            "W" {
                Write-Log "Waiting 60 seconds for propagation..."
                Start-Sleep -Seconds 60
                $retryResult = Enable-ExpressConfig -Resource $resource
                if (-not $retryResult.Success) {
                    Write-LogError "Retry failed. Restoring Classic Configuration..."
                    $restored = Restore-ClassicVASettings -Resource $resource -RestoreData $classicSettings.RestoreData
                    if (-not $restored) {
                        Write-LogError "Classic VA restore encountered errors. Check messages above."
                        Write-Host "  Manual restore: https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration" -ForegroundColor Yellow
                    }
                    Write-MigrationSummary -Resource $resource -PreBaselineScanResults $null `
                        -PostBaselineScanResults $null -BaselineResults @{} -Baselines @{} `
                        -AllDatabases $allDatabases -FinalState "Reverted"
                    return
                }
                Write-LogSuccess "Express Configuration enabled on retry."
            }
            "Q" {
                Write-LogError "Migration aborted. Resource may be without VA configuration."
                Write-Host "  Manual restore: https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration" -ForegroundColor Yellow
                return
            }
        }
    }
    else {
        if (-not $isIdentityError) {
            Write-Host ""
            Write-Host "  Common causes:" -ForegroundColor Yellow
            if ($resource.ResourceType -eq "SqlManagedInstance") {
                Write-Host "    - System-Assigned Managed Identity (SAMI) is not enabled on the MI" -ForegroundColor Yellow
            }
            Write-Host "    - Classic VA policy on a child resource was not fully removed" -ForegroundColor Yellow
        }
        Write-Host ""
        Write-Host "  Fix the issue above and re-run this script." -ForegroundColor Yellow
        return
    }
}
else {
    Write-LogSuccess "Express Configuration enabled successfully."
}

# ======================================================================
#  Step 7: Pre-Baseline Scan
# ======================================================================
$stepNum++
Write-Section "Step $stepNum`: Pre-Baseline Scan"

$preBaselineScanResults = [ordered]@{}
$i = 0
foreach ($db in $allDatabases) {
    $i++
    $pct = [int](($i / $allDatabases.Count) * 100)
    Write-Progress -Activity "Pre-baseline scan" -Status "$db ($i/$($allDatabases.Count))" -PercentComplete $pct

    Write-Log "Scanning '$db'..."
    try {
        $result = Invoke-WithRetry -Description "scan '$db'" -Action {
            Invoke-DatabaseScan -Resource $resource -DatabaseName $db `
                -TimeoutSeconds $ScanTimeoutSeconds -PollingIntervalSeconds $ScanPollingIntervalSeconds
        }
        $preBaselineScanResults[$db] = $result.Status
    }
    catch {
        $preBaselineScanResults[$db] = "Error"
        Write-LogError "Scan error for '$db': $($_.Exception.Message)"
    }

    $statusColor = if ($preBaselineScanResults[$db] -in @("Passed", "Failed")) { "Green" } else { "Red" }
    Write-Host ("  {0}: {1}" -f $db, $preBaselineScanResults[$db]) -ForegroundColor $statusColor
}
Write-Progress -Activity "Pre-baseline scan" -Completed

# Report scan issues
$scanFailures = $preBaselineScanResults.GetEnumerator() | Where-Object { $_.Value -in @("FailedToRun", "InitiateFailed", "Timeout", "Error") }
if ($scanFailures) {
    Write-Host ""
    Write-LogWarn "Some scans could not complete:"
    foreach ($f in $scanFailures) { Write-LogWarn "  $($f.Key): $($f.Value)" }
    Write-Host "  Baselines will still be applied where possible." -ForegroundColor Yellow
}

# ======================================================================
#  Step 8: Apply Baselines
# ======================================================================
$stepNum++
$baselineResults = @{}     # dbName → @{ Applied; Failed; FailedRules }
$allBaselineFailures = @{} # dbName → @( @{ RuleId; Error; Results }, ... )

if ($baselines.Count -gt 0) {
    Write-Section "Step $stepNum`: Apply Migrated Baselines"

    $i = 0
    foreach ($db in $allDatabases) {
        $i++
        $pct = [int](($i / $allDatabases.Count) * 100)
        Write-Progress -Activity "Applying baselines" -Status "$db ($i/$($allDatabases.Count))" -PercentComplete $pct

        if ($baselines.ContainsKey($db) -and -not [string]::IsNullOrEmpty($baselines[$db])) {
            Write-Log "Applying baseline for '$db'..."
            try {
                $result = Invoke-WithRetry -Description "baseline '$db'" -MaxAttempts 1 -Action {
                    Set-BaselineRulesForDatabase -Resource $resource -DatabaseName $db -BaselineJson $baselines[$db]
                }
                $baselineResults[$db] = $result
                Write-Log "  '$db': $($result.Applied) applied, $($result.Failed) failed"

                if ($result.FailedRules.Count -gt 0) {
                    $allBaselineFailures[$db] = $result.FailedRules
                }
            }
            catch {
                Write-LogError "Baseline error for '$db': $($_.Exception.Message)"
                $baselineResults[$db] = @{ Applied = 0; Failed = -1; FailedRules = @() }
                $allBaselineFailures[$db] = @( @{ RuleId = "(all)"; Error = $_.Exception.Message; Results = @() } )
            }
        }
    }
    Write-Progress -Activity "Applying baselines" -Completed

    # ===== Baseline failure recovery =====
    if ($allBaselineFailures.Count -gt 0 -and -not $Force) {
        $recovery = Invoke-BaselineRecovery `
            -Resource $resource `
            -AllBaselineFailures $allBaselineFailures `
            -ClassicRestoreData $classicSettings.RestoreData `
            -HadClassicVA $hadClassicVA

        if ($recovery.Action -eq "Reverted") {
            Write-MigrationSummary -Resource $resource -PreBaselineScanResults $preBaselineScanResults `
                -PostBaselineScanResults $null -BaselineResults $baselineResults -Baselines $baselines `
                -AllDatabases $allDatabases -FinalState "Reverted"
            return
        }

        # Update baseline results with recovery outcomes
        if ($recovery.StillFailed) {
            foreach ($entry in $recovery.StillFailed.GetEnumerator()) {
                $db = $entry.Key
                if ($baselineResults.ContainsKey($db)) {
                    $orig = $baselineResults[$db]
                    $recovered = $allBaselineFailures[$db].Count - $entry.Value.Count
                    $baselineResults[$db] = @{
                        Applied     = $orig.Applied + $recovered
                        Failed      = $entry.Value.Count
                        FailedRules = $entry.Value
                    }
                }
            }
            # Also clear databases that were fully recovered
            foreach ($db in @($allBaselineFailures.Keys)) {
                if (-not $recovery.StillFailed.ContainsKey($db)) {
                    if ($baselineResults.ContainsKey($db)) {
                        $orig = $baselineResults[$db]
                        $baselineResults[$db] = @{
                            Applied     = $orig.Applied + $allBaselineFailures[$db].Count
                            Failed      = 0
                            FailedRules = @()
                        }
                    }
                }
            }
            $allBaselineFailures = $recovery.StillFailed
        }
        elseif ($recovery.Action -in @("Retried", "Interactive")) {
            # All were resolved
            foreach ($db in @($allBaselineFailures.Keys)) {
                if ($baselineResults.ContainsKey($db)) {
                    $orig = $baselineResults[$db]
                    $baselineResults[$db] = @{
                        Applied     = $orig.Applied + $allBaselineFailures[$db].Count
                        Failed      = 0
                        FailedRules = @()
                    }
                }
            }
            $allBaselineFailures = @{}
        }
    }
}
else {
    Write-Log "No baselines to migrate."
}

# ======================================================================
#  Step 9: Post-Baseline Scan
# ======================================================================
$stepNum++
Write-Section "Step $stepNum`: Post-Baseline Scan"

$postBaselineScanResults = [ordered]@{}
$i = 0
foreach ($db in $allDatabases) {
    $i++
    $pct = [int](($i / $allDatabases.Count) * 100)
    Write-Progress -Activity "Post-baseline scan" -Status "$db ($i/$($allDatabases.Count))" -PercentComplete $pct

    Write-Log "Scanning '$db'..."
    try {
        $result = Invoke-WithRetry -Description "post-baseline scan '$db'" -Action {
            Invoke-DatabaseScan -Resource $resource -DatabaseName $db `
                -TimeoutSeconds $ScanTimeoutSeconds -PollingIntervalSeconds $ScanPollingIntervalSeconds
        }
        $postBaselineScanResults[$db] = $result.Status
    }
    catch {
        $postBaselineScanResults[$db] = "Error"
        Write-LogError "Scan error for '$db': $($_.Exception.Message)"
    }

    $statusColor = if ($postBaselineScanResults[$db] -in @("Passed", "Failed")) { "Green" } else { "Red" }
    Write-Host ("  {0}: {1}" -f $db, $postBaselineScanResults[$db]) -ForegroundColor $statusColor
}
Write-Progress -Activity "Post-baseline scan" -Completed

# ======================================================================
#  Final Summary
# ======================================================================

$finalState = if ($allBaselineFailures.Count -gt 0) { "CompletedWithErrors" } else { "Completed" }

Write-MigrationSummary -Resource $resource -PreBaselineScanResults $preBaselineScanResults `
    -PostBaselineScanResults $postBaselineScanResults -BaselineResults $baselineResults -Baselines $baselines `
    -AllDatabases $allDatabases -FinalState $finalState

Generally available version for Azure SQL databases

This PowerShell script enables the express configuration of vulnerability assessments on an Azure SQL Server.

If you already use Classic Configuration for vulnerability assessment, this script migrates to Express Configuration and copies all existing baseline definitions.

Important

Scan history isn't migrated to Express Configuration. Your historical scan results remain on the storage account previously used by the Classic Configuration.

Prerequisites

This sample requires Azure PowerShell Az 1.0 or later. Run Get-Module -ListAvailable Az to see which versions are installed. If you need to install, see Install Azure PowerShell module.

Run Connect-AzAccount -Environment AzureChinaCloud to sign in to Azure.

If you don't have an Azure trail subscription, create a trial subscription before you begin.

You must have the Storage Blob Data Reader role on the storage account.
Configure storage networking settings to allow your machine access to the storage account.
You must have permission to create folders in the script's working directory.

Sample script - MigratingToExpressConfiguration.ps1

Note

We recommend that you use the Azure Az PowerShell module to interact with Azure. To get started, see Install Azure PowerShell. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.

#Requires -Modules @{ ModuleName="Az.Sql"; ModuleVersion="3.11.0" }
#Requires -Modules @{ ModuleName="Az.Storage"; ModuleVersion="4.8.0" }
#Requires -Modules @{ ModuleName="Az.Accounts"; ModuleVersion="2.9.1" }
#Requires -Version 5.1

<#
.SYNOPSIS
    This script configured an Azure SQL Server to the express configuration Vulnerability Assessment feature, scans all databases that belong to the selected server and set baselines (if defined in classic configuration) from the storage that is configures in the policy.

.DESCRIPTION
This script migrates Azure SQL Server to express configuration Vulnerability Assessment feature by executing the following steps:
- It deletes the current Vulnerability Assessment settings (if exists).
  This step will reset all the Vulnerability Assessment scans and baselines for all databases.
- It copies the current baselines (if exist) from the customer's storage.

In order to revert this script follow the instructions as mentioned here: https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration.

.PARAMETER ServerSubscriptionId
    The Subscription id that the server belongs to

.PARAMETER ServerResourceGroupName
    The Resource Group that the server belongs to

.PARAMETER ServerName
    The SQL server name that we want to apply the new SQL Vulnerability Assessment policy to.

.PARAMETER Force
    Will remove the old Vulnerability Assessment setting without asking for confirmation.

.EXAMPLE
    .\MigratingToExpressConfiguration.ps1 -SubscriptionId "25b642fc-05c3-11ed-b939-0242ac120002" -ResourceGroupName "ResourceGroup01" -ServerName "Server01" -Force
    .\MigratingToExpressConfiguration.ps1 -SubscriptionId "25b642fc-05c3-11ed-b939-0242ac120002" -ResourceGroupName "ResourceGroup01" -ServerName "Server01"

#>

param
(
    [Parameter(Mandatory = $True)]
    [string]$SubscriptionId,

    [Parameter(Mandatory = $True)]
    [string]$ResourceGroupName,

    [Parameter(Mandatory = $True)]
    [string]$ServerName,

    [Parameter(Mandatory = $False)]
    [switch]$Force
)


###### New SQL Vulnerability Assessment Commands ######
#######################################################
function ClearSqlVulnerabilityAssessmentMasterSetting($SubscriptionId, $ResourceGroupName, $ServerName) {
    $Uri = "/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.Sql/servers/$ServerName/databases/master/VulnerabilityAssessments/default?api-version=2021-11-01"
    return SendRestRequest -Method "Delete" -Uri $Uri
}

function GetSqlVulnerabilityAssessmentMasterSetting($SubscriptionId, $ResourceGroupName, $ServerName) {
    $Uri = "/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.Sql/servers/$ServerName/databases/master/VulnerabilityAssessments/default?api-version=2021-11-01"
    return SendRestRequest -Method "Get" -Uri $Uri
}

function GetSqlVulnerabilityAssessmentServerSetting($SubscriptionId, $ResourceGroupName, $ServerName) {
    $Uri = "/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.Sql/servers/$ServerName/sqlVulnerabilityAssessments/default?api-version=2022-02-01-preview"
    return SendRestRequest -Method "Get" -Uri $Uri
}

function SetSqlVulnerabilityAssessmentServerSetting($SubscriptionId, $ResourceGroupName, $ServerName) {
    $Uri = "/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.Sql/servers/$ServerName/sqlVulnerabilityAssessments/default?api-version=2022-02-01-preview"
    $Body = @{
        properties = @{
            state = "Enabled"
        }
    }

    $Body = $Body | ConvertTo-Json
    return SendRestRequest -Method "Put" -Uri $Uri -Body $Body
}

function SetSqlVulnerabilityAssessmentBaselineOnUserDatabase($SubscriptionId, $ResourceGroupName, $ServerName, $DatabaseName, $Baseline) {
    $Uri = "/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.Sql/servers/$ServerName/databases/$DatabaseName/sqlVulnerabilityAssessments/default/baselines/default?api-version=2022-02-01-preview"
    $convertedBaseline = $Baseline | ConvertFrom-Json
    $properties = @{
        properties = @{
            latestScan = $false
            results    = @{}
        }
    }

    if ($convertedBaseline.RuleBaselines.Count -eq 0) {
        # baseline is null/empty. No need to send the API call.
        return
    }

    foreach ($rule in $convertedBaseline.RuleBaselines) {
        $ruleId = $rule.RuleId
        $expectedResults = $rule.Properties.ExpectedResults

        if ($ruleId -in $VABinaryRules) {
            if ($expectedResults[0][0] -eq "1") {
                $expectedResults[0][0] = "True"
            }
            else {
                $expectedResults[0][0] = "False"
            }
        }

        $properties.properties.results[$ruleId] = $expectedResults
    }


    $Body = $properties | ConvertTo-Json -Depth 5
    return SendRestRequest -Method "Put" -Uri $Uri -Body $Body
}

function SetSqlVulnerabilityAssessmentBaselineOnSystemDatabase($SubscriptionId, $ResourceGroupName, $ServerName, $DatabaseName, $Baseline) {
    $Uri = "/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.Sql/servers/$ServerName/sqlVulnerabilityAssessments/default/baselines/default?api-version=2022-02-01-preview&systemDatabaseName=master"
    $convertedBaseline = $Baseline | ConvertFrom-Json
    $properties = @{
        properties = @{
            latestScan = $false
            results    = @{}
        }
    }

    if ($convertedBaseline.RuleBaselines.Count -eq 0) {
        # baseline is null/empty. No need to send the API call.
        return
    }

    foreach ($rule in $convertedBaseline.RuleBaselines) {
        $ruleId = $rule.RuleId
        $expectedResults = $rule.Properties.ExpectedResults

        if ($ruleId -in $VABinaryRules) {
            if ($expectedResults[0][0] -eq "1") {
                $expectedResults[0][0] = "True"
            }
            else {
                $expectedResults[0][0] = "False"
            }
        }

        $properties.properties.results[$ruleId] = $expectedResults
    }


    $Body = $properties | ConvertTo-Json -Depth 5
    return SendRestRequest -Method "Put" -Uri $Uri -Body $Body
}

function RunSqlVulnerabilityAssessmentScanOnUserDatabase($SubscriptionId, $ResourceGroupName, $ServerName, $DatabaseName) {
    $Uri = "/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.Sql/servers/$ServerName/databases/$DatabaseName/sqlVulnerabilityAssessments/default/initiateScan?api-version=2022-02-01-preview"
    SendRestRequest -Method "Post" -Uri $Uri
}

function RunSqlVulnerabilityAssessmentScanOnSystemDatabase($SubscriptionId, $ResourceGroupName, $ServerName, $DatabaseName) {
    $Uri = "/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.Sql/servers/$ServerName/sqlVulnerabilityAssessments/default/initiateScan?api-version=2022-02-01-preview&systemDatabaseName=$DatabaseName"
    SendRestRequest -Method "Post" -Uri $Uri
}

function GetSqlVulnerabilityAssessmentScanOnUserDatabase($SubscriptionId, $ResourceGroupName, $ServerName, $DatabaseName) {
    $Uri = "/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.Sql/servers/$ServerName/databases/$DatabaseName/sqlVulnerabilityAssessments/default/scans/latest?api-version=2022-02-01-preview"
    return SendRestRequest -Method "Get" -Uri $Uri
}

function GetSqlVulnerabilityAssessmentScanOnSystemDatabase($SubscriptionId, $ResourceGroupName, $ServerName, $DatabaseName) {
    $Uri = "/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.Sql/servers/$ServerName/sqlVulnerabilityAssessments/default/scans/latest?api-version=2022-02-01-preview&systemDatabaseName=$DatabaseName"
    return SendRestRequest -Method "Get" -Uri $Uri
}


function SendRestRequest(
    [Parameter(Mandatory = $True)]
    [string] $Method,
    [Parameter(Mandatory = $True)]
    [string] $Uri,
    [parameter( Mandatory = $false )]
    [string] $Body = "DEFAULT") {

    $Params = @{
        Method       = $Method
        Path         = $Uri
    }

    if (!($Body -eq "DEFAULT")) {
        $Params = @{
            Method       = $Method
            Path         = $Uri
            Payload      = $Body
        }
    }

    Invoke-AzRestMethod @Params
}

#######################################################

function LogMessage {
    [CmdletBinding()]
    Param
    (
        [Parameter(Mandatory=$true, Position=0)]
        [string]$LogMessage
    )

    Write-Host ("{0} - {1}" -f (Get-Date), $LogMessage)
}

function LogError {
    [CmdletBinding()]
    Param
    (
        [Parameter(Mandatory=$true, Position=0)]
        [string]$LogMessage
    )

    Write-Error ("{0} - {1}" -f (Get-Date), $LogMessage)
}

#######################################################

function Retry() {
    param(
        [Parameter(Mandatory = $true)][Action]$action,
        [Parameter(Mandatory = $false)][int]$maxAttempts = 3
    )

    $attempts = 1
    do {
        try {
            $result = $action.Invoke();
            return $result
        }
        catch [Exception] {
            LogMessage -LogMessage $_.Exception.Message
        }

        # exponential backoff delay
        $attempts++
        if ($attempts -le $maxAttempts) {
            $retryDelaySeconds = [math]::Pow(2, $attempts)
            $retryDelaySeconds = $retryDelaySeconds - 1  # Exponential Backoff Max == (2^n)-1
            LogMessage -LogMessage ("Action failed. Waiting " + $retryDelaySeconds + " seconds before attempt " + $attempts + " of " + $maxAttempts + ".")
            Start-Sleep $retryDelaySeconds
        }
        else {
            LogError $_.Exception.Message
            $ex = New-Object System.Exception($_.Exception.Message)
            throw $ex
        }
    } while ($attempts -le $maxAttempts)
}


function HaveVulnerabilityAssessmentSetting($ResourceGroupName, $ServerName, $Databases) {
    # Check if we have a server setting.
    LogMessage -LogMessage "Check Vulnerability Assessment setting for '$($ServerName)' server"
    $vaServerSetting = Get-AzSqlServerVulnerabilityAssessmentSetting -ResourceGroupName $ResourceGroupName -ServerName $ServerName
    if (![string]::IsNullOrEmpty($vaServerSetting.StorageAccountName)) {
        return $true
    }

    # Check if we have a database setting for server
    foreach ($database in $Databases) {
        LogMessage -LogMessage "Check VA settings for '$($database.DatabaseName)' database"
        $vaDatabaseSetting = Get-AzSqlDatabaseVulnerabilityAssessmentSetting -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $database.DatabaseName
        if (![string]::IsNullOrEmpty($vaDatabaseSetting.StorageAccountName)) {
            return $true
        }
    }

    # Check if we have a master setting for server
    $vaSettingMaster =  GetSqlVulnerabilityAssessmentMasterSetting -SubscriptionId $SubscriptionId -ResourceGroupName $ResourceGroupName -ServerName $ServerName
    $object = ConvertFrom-Json $vaSettingMaster.Content
    if (![string]::IsNullOrEmpty($object.properties.storageContainerPath)) {
        return $true
    }

    return $false
}

function HaveExpressConfigurationVulnerabilityAssessmentSetting($SubscriptionId, $ResourceGroupName, $ServerName) {
    # Check if we have a server setting.
    LogMessage -LogMessage "Check express configuration Vulnerability Assessment setting for '$($ServerName)' server"
    $Response = GetSqlVulnerabilityAssessmentServerSetting -SubscriptionId $SubscriptionId -ResourceGroupName $ResourceGroupName -ServerName $ServerName
    if ($Response.Content.Contains("Enabled")) {
        return $true
    }

    return $false
}

function GetBlobsFromStorage($ContainerName, $Context) {

    # Use Get-AzStorageBlob to retrieve a list of blobs from the container
    $blobs = Get-AzStorageBlob -Container $ContainerName -Context $Context

    if ([string]::IsNullOrEmpty($blobs)) {
        return
    }

    return $blobs
}


function ExtractBaselineBlobName($ServerName, $DatabaseName) {
    $prefix = "scans/$ServerName/$DatabaseName/baseline"

    # Filter the list to include only blobs with names starting with "baseline"
    $baselineBlobs = $blobs | Where-Object { $_.Name.StartsWith($prefix) }

    if ($baselineBlobs.Count -eq 0) {
        return
    }
    else {
        # Sort the list by LastModified descending and get the first item
        $mostRecentBlob = $baselineBlobs | Sort-Object LastModified -Descending | Select-Object -First 1

        # Get the name of the most recent blob
        $mostRecentBlobName = $mostRecentBlob.Name

        LogMessage -LogMessage "The most recent blob with the 'baseline' prefix is: $mostRecentBlobName"

        return $mostRecentBlobName
    }
}

function ReadDatabaseBaselineFromStorage($ServerName, $BlobName, $ContainerName, $Context) {
    # Use Get-AzStorageBlobContent to retrieve the blob content as a string
    $blobContent = Get-AzStorageBlobContent -Blob $BlobName -Container $ContainerName -Context $Context
    $blobContent = $blobContent.ICloudBlob.DownloadText()

    if ([string]::IsNullOrEmpty($blobContent)) {
        return
    }

    return $blobContent
}

function GetBaselineConfigurationForDatabase($ServerName, $DatabaseName, $VaDatabasePolicyStorage, $VaDatabasePolicyContainer) {

    # Get storage account context
    $ctx = New-AzStorageContext -StorageAccountName $VaDatabasePolicyStorage

    # Extract baseline blob name
    $blobs = GetBlobsFromStorage -ContainerName $VaDatabasePolicyContainer -Context $ctx
    if ([string]::IsNullOrEmpty($blobs)) {
        $ex = New-Object System.Exception("Failed to get blobs from $($VaDatabasePolicyStorage) storage. Verify that you have Storage Blob Data Reader role assignment on the storage.")
        throw $ex
    }

    # Extract baseline blob name
    $blobName = ExtractBaselineBlobName -ServerName $ServerName -DatabaseName $DatabaseName
    if ([string]::IsNullOrEmpty($blobName)) {
        LogMessage -LogMessage "No baseline blob was found for $($DatabaseName) database."
        return
    }

    # Extract the baseline
    $baseline = ReadDatabaseBaselineFromStorage -ServerName $ServerName -BlobName $blobName -ContainerName $VaDatabasePolicyContainer -Context $ctx
    if ([string]::IsNullOrEmpty($baseline)) {
        $ex = New-Object System.Exception("Failed to get blobs from $($VaDatabasePolicyStorage) storage. Verify that you have Storage Blob Data Reader role assignment on the storage.")
        throw $ex
    }

    LogMessage -LogMessage "Found baseline for $($DatabaseName) database."
    return $baseline
}

function ClearBaselineFolder() {
    # Clean baseline folder
    $scriptPath = Get-Location
    $folderPath = Join-Path -Path $scriptPath.Path -ChildPath "scans"

    if (Test-Path $folderPath) {
        # Remove folder from previous runs
        Remove-Item $folderPath -Recurse
    }
}

#######################################################

$VABinaryRules = @("VA1018", "VA1022", "VA1023", "VA1024", "VA1043", "VA1044", "VA1045", "VA1051", "VA1052", "VA1053", "VA1058", "VA1059", "VA1067", "VA1071", "VA1072", "VA1091", "VA1092", "VA1093", "VA1102", "VA1143", "VA1219", "VA1230", "VA1235", "VA1245", "VA1264", "VA1265", "VA1277", "VA1279", "VA1283", "VA2060", "VA2061", "VA2121", "VA2122", "VA2128")

#######################################################


# Connect
$subscription = Connect-AzAccount -Environment AzureChinaCloud -Subscription $SubscriptionId
if ([string]::IsNullOrEmpty($subscription))
{
    LogError "Failed to get the subscription. Migration cancelled. Fix errors and try again later."
    return
}

$srv = Get-AzSqlServer -ResourceGroupName $ResourceGroupName -ServerName $ServerName
if ([string]::IsNullOrEmpty($srv))
{
    LogError "The server was not found. Migration cancelled. Fix errors and try again later."
    return
}

ClearBaselineFolder

$baselines = @{}
$databases = Get-AzSqlDatabase -ResourceGroupName $ResourceGroupName -ServerName $ServerName | Where-Object { $_.DatabaseName -ne "master" }
$haveVaSetting = HaveVulnerabilityAssessmentSetting -ResourceGroupName $ResourceGroupName -ServerName $ServerName -Databases $databases
$haveExpressConfigurationVA = HaveExpressConfigurationVulnerabilityAssessmentSetting -SubscriptionId $SubscriptionId -ResourceGroupName $ResourceGroupName -ServerName $ServerName

if ($haveExpressConfigurationVA) {
    LogMessage -LogMessage "Express configuration vulnerability assessment setting is already exist on this server. Cancelling script."
    return
}

if ($haveVaSetting) {
    # Get server policy container path
    $vaServerSetting = Get-AzSqlServerVulnerabilityAssessmentSetting -ResourceGroupName $ResourceGroupName -ServerName $ServerName
    $vaServerPolicyStorage = $vaServerSetting.StorageAccountName
    $vaServerPolicyContainer = $vaServerSetting.ScanResultsContainerName

    $canRemoveVa = $true

    # Go over each database and get the baseline (is exist).
    $i = 0
    foreach ($database in $Databases) {
        $i += 1
        $completed = ($i/$Databases.count) * 100
        Write-Progress -Activity "Processing" -Status "Progress:" -PercentComplete $completed
        LogMessage -LogMessage "Starting to fetch baseline for $($database.DatabaseName) database."
        $vaDatabaseSetting = Get-AzSqlDatabaseVulnerabilityAssessmentSetting -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $database.DatabaseName
        $adsDatabasePolicy = Get-AzSqlDatabaseAdvancedThreatProtectionSetting -ResourceGroupName $ResourceGroupName  -ServerName $ServerName -DatabaseName $database.DatabaseName
        $adsDatabasePolicyEnabled = ($null -ne $adsDatabasePolicy.ThreatDetectionState -and $adsDatabasePolicy.ThreatDetectionState -eq "Enabled") -or ($null -ne $adsDatabasePolicy.AdvancedThreatProtectionState -and $adsDatabasePolicy.AdvancedThreatProtectionState -eq "Enabled") # Handle breaking changes in the command.
        $containsDatabasePolicy = $adsDatabasePolicyEnabled -and ![string]::IsNullOrEmpty($vaDatabaseSetting.StorageAccountName)

        if ($containsDatabasePolicy) {
            # The database has database policy. Using the database policy storage.
            $vaDatabasePolicyStorage = $vaDatabaseSetting.StorageAccountName
            $vaDatabasePolicyContainer = $vaDatabaseSetting.ScanResultsContainerName
        }
        else {
            $vaDatabasePolicyStorage = $vaServerPolicyStorage
            $vaDatabasePolicyContainer = $vaServerPolicyContainer
        }

        try {
            $baseline = GetBaselineConfigurationForDatabase -ServerName $ServerName -DatabaseName $database.DatabaseName -VaDatabasePolicyStorage $vaDatabasePolicyStorage -VaDatabasePolicyContainer $vaDatabasePolicyContainer
            $baselines[$database.DatabaseName] = $baseline
            LogMessage -LogMessage "Finished to fetch baseline for $($database.DatabaseName) database."
        }
        catch {
            LogError "An error occurred: $($_.Exception.Message) while handling $($database.DatabaseName) database."
            $canRemoveVa = $false
        }
    }
    Write-Progress -Activity "Processing" -Status "Ready" -Completed

    LogMessage -LogMessage "Starting to fetch baseline for master database."
    $vaSettingMaster =  GetSqlVulnerabilityAssessmentMasterSetting -SubscriptionId $SubscriptionId -ResourceGroupName $ResourceGroupName -ServerName $ServerName
    $vaSettingMaster = ConvertFrom-Json $vaSettingMaster.Content

    $adsDatabasePolicy = Get-AzSqlDatabaseAdvancedThreatProtectionSetting -ResourceGroupName $ResourceGroupName  -ServerName $ServerName -DatabaseName "master"
    $adsDatabasePolicyEnabled = ($null -ne $adsDatabasePolicy.ThreatDetectionState -and $adsDatabasePolicy.ThreatDetectionState -eq "Enabled") -or ($null -ne $adsDatabasePolicy.AdvancedThreatProtectionState -and $adsDatabasePolicy.AdvancedThreatProtectionState -eq "Enabled") # Handle breaking changes in the command.
    $containsDatabasePolicy = $adsDatabasePolicyEnabled -and ![string]::IsNullOrEmpty($vaSettingMaster.properties.storageContainerPath)

    if ($containsDatabasePolicy -and ![string]::IsNullOrEmpty($vaServerPolicyStorage)) {
        # The database has database policy. Using the database policy storage.
        $vaDatabasePolicyStorage = $vaSettingMaster.properties.storageContainerPath.Split("/")[2].Split(".")[0]
        $vaDatabasePolicyContainer = $vaSettingMaster.properties.storageContainerPath.Split("/")[3]
    }
    else {
        $vaDatabasePolicyStorage = $vaServerPolicyStorage
        $vaDatabasePolicyContainer = $vaServerPolicyContainer
    }

    try {
        $baseline = GetBaselineConfigurationForDatabase -ServerName $ServerName -DatabaseName "master" -VaDatabasePolicyStorage $vaDatabasePolicyStorage -VaDatabasePolicyContainer $vaDatabasePolicyContainer
        $baselines["master"] = $baseline
        LogMessage -LogMessage "Finished to fetch baseline for master database."
    }
    catch {
        LogError "An error occurred: $($_.Exception.Message) while handling master database."
        $canRemoveVa = $false
    }

    ClearBaselineFolder

    if ($canRemoveVa) {
        if (!$Force) {
            LogMessage -LogMessage "We are going to remove the current Vulnerability Assessment settings for this server and underlying databases."
            $Confirmation = Read-Host -Prompt "Do you approve (y/n)?"
            if ($Confirmation -ne "y") {
                LogMessage -LogMessage "You chose to stop the migration process. Existing VA settings will not be changed."
                return
            }
        }

        # Clear all server and databases policies
        $i = 0
        foreach ($database in $Databases) {
            $i += 1
            $completed = ($i/$Databases.count) * 100
            Write-Progress -Activity "Processing" -Status "Progress:" -PercentComplete $completed
            LogMessage -LogMessage "Clear Vulnerability Assessment setting for '$($database.DatabaseName)' database."
            Clear-AzSqlDatabaseVulnerabilityAssessmentSetting -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $database.DatabaseName
        }
        Write-Progress -Activity "Processing" -Status "Ready" -Completed

        # Removing Vulnerability Assessment settings from master db
        Retry -action { ClearSqlVulnerabilityAssessmentMasterSetting -SubscriptionId $SubscriptionId -ResourceGroupName $ResourceGroupName -ServerName $ServerName }

        # Removing old server Vulnerability Assessment setting
        LogMessage -LogMessage "Clear Vulnerability Assessment setting for '$($ServerName)' server."
        Clear-AzSqlServerVulnerabilityAssessmentSetting -ResourceGroupName $ResourceGroupName -ServerName $ServerName
    }
    else {
        LogError "Migration cancelled. Fix errors and try again later."
        return
    }
}

# Set new SQL Vulnerability Assessment Setting
LogMessage -LogMessage "Add express configuration Vulnerability Assessment feature setting for '$($ServerName)' server."
$Response = SetSqlVulnerabilityAssessmentServerSetting -SubscriptionId $SubscriptionId -ResourceGroupName $ResourceGroupName -ServerName $ServerName
$successStatusCodes = @(200, 201, 202)
if ($Response.StatusCode -in $successStatusCodes) {
    LogMessage -LogMessage "Congratulations, your server '$($ServerName)' server is set up with express configuration Vulnerability Assessment feature"
}
else {
    LogMessage -LogMessage "There was a problem to enable express configuration Vulnerability Assessment feature on the '$($ServerName)' server. Error '$($Response.StatusCode)': '$($Response.Content)'"
    return
}

# Run a new scan on all the databases
$i = 0
foreach ($database in $Databases) {
    $i += 1
    $completed = ($i/$Databases.count) * 100
    Write-Progress -Activity "Processing" -Status "Progress:" -PercentComplete $completed
    LogMessage -LogMessage "Run scan on '$($database.DatabaseName)' database."
    Retry -action { RunSqlVulnerabilityAssessmentScanOnUserDatabase -SubscriptionId $SubscriptionId -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $database.DatabaseName }
}
Write-Progress -Activity "Processing" -Status "Ready" -Completed

LogMessage -LogMessage "Run scan on 'master' database."
Retry -action { RunSqlVulnerabilityAssessmentScanOnSystemDatabase -SubscriptionId $SubscriptionId -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName "master" }

LogMessage -LogMessage "Wait for scan results.."
Start-Sleep 60

# Wait for scan results
$i = 0
foreach ($database in $Databases) {
    $i += 1
    $completed = ($i/$Databases.count) * 100
    Write-Progress -Activity "Processing" -Status "Progress:" -PercentComplete $completed
    try {
        LogMessage -LogMessage "Waiting for results for $($database.DatabaseName) database."
        Retry -action { GetSqlVulnerabilityAssessmentScanOnUserDatabase -SubscriptionId $SubscriptionId -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $database.DatabaseName }
        LogMessage -LogMessage "Received results for $($database.DatabaseName) database."
    }
    catch {
        LogMessage -LogMessage "Failed to get latest scan results for $($database.DatabaseName). Stopping the migration."
        LogMessage -LogMessage "You can revert back to classic configuration. For more information: https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration"
        return
    }
}
Write-Progress -Activity "Processing" -Status "Ready" -Completed

try {
    LogMessage -LogMessage "Waiting for results for master database"
    Retry -action { GetSqlVulnerabilityAssessmentScanOnSystemDatabase -SubscriptionId $SubscriptionId -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName "master" }
}
catch {
    LogMessage -LogMessage "Failed to get latest scan results for master. Stopping the migration"
    LogMessage -LogMessage "You can revert back to classic configuration. For more information: https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration"
    return
}

# Apply baselines from each database
$successMigration = @()
$failedMigration = @()
if (!$haveVaSetting) {
    # no need to migrate baseline as there is no baseline to extract.
    $successMigration = $databases
}
else {
    $i = 0
    foreach ($database in $Databases) {
        $i += 1
        $completed = ($i/$Databases.count) * 100
        Write-Progress -Activity "Processing" -Status "Progress:" -PercentComplete $completed
        try {
            if (![string]::IsNullOrEmpty($baselines[$database.DatabaseName])) {
                LogMessage -LogMessage "Applying baseline for '$($database.DatabaseName)' database."
                Retry -action { SetSqlVulnerabilityAssessmentBaselineOnUserDatabase -SubscriptionId $SubscriptionId -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName $database.DatabaseName -Baseline $baselines[$database.DatabaseName] }
            }
            LogMessage -LogMessage "Baseline was successfully applied for '$($database.DatabaseName)' database."
            $successMigration += $database.DatabaseName
        }
        catch {
            LogError "Failed to set baseline for $($database.DatabaseName) database."
            $failedMigration += $database.DatabaseName
        }
    }
    Write-Progress -Activity "Processing" -Status "Ready" -Completed

    try {
        if (![string]::IsNullOrEmpty($baselines["master"])) {
            LogMessage -LogMessage "Applying baseline for 'master' database."
            Retry -action { SetSqlVulnerabilityAssessmentBaselineOnSystemDatabase -SubscriptionId $SubscriptionId -ResourceGroupName $ResourceGroupName -ServerName $ServerName -DatabaseName "master" -Baseline $baselines["master"] }
            $successMigration += "master"
        }
    }
    catch {
        LogError "Failed to set baseline for master database."
        $failedMigration += "master"
    }
}

if ($successMigration.Count -eq 0) {
    LogError "Failed to set baseline for all the databases."
    LogMessage -LogMessage "You can revert back to classic configuration. For more information: https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration"
}
elseif ($failedMigration.Count -eq 0) {
    LogMessage -LogMessage  "The migration process completed successfully."
}
else {
    LogMessage -LogMessage  "The migration process completed. The migration was successful for $($successMigration -join ',') and unsuccessful for $($failedMigration -join ',')"
    LogMessage -LogMessage "You can revert back to classic configuration. For more information: https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration"
}

For example:

Write-Host "----------- Migrating To express configuration example -----------"

$ServerName = "<Your server name>"  # The server name.
$SubscriptionId = "<Your subscription>" # The subscription id that the servers belong to.
$ResourceGroupName = "<Your resource group name>" # The resource group name that the servers belong to.

.\MigratingToExpressConfiguration.ps1 -SubscriptionId $SubscriptionId -ResourceGroupName $ResourceGroupName -ServerName $ServerName

Learn more about Azure PowerShell

Last updated on 2026-05-29

Enable vulnerability assessments on Azure SQL databases with Express Configuration

Version types

Public preview for Azure SQL Managed Instance and Azure Synapse Analytics Workspace (unified API)

Overview

Supported resource types

What the script does

Prerequisites

PowerShell

Azure authentication

Permissions

Parameters

How to find your resource ID

Usage examples

SQL Database server

SQL Managed Instance

Azure Synapse Analytics

Non-interactive (automation / CI)

Sample script - MigrateToExpressConfiguration.ps1

Additional resources