Habilitación de evaluaciones de vulnerabilidades en bases de datos de Azure SQL con Express Configuration

En este artículo se proporcionan scripts de PowerShell para habilitar la evaluación de vulnerabilidades de SQL (VA) con express Configuration en Microsoft Defender para la nube. La evaluación de vulnerabilidades de SQL le ayuda a identificar y corregir las vulnerabilidades de seguridad en las bases de datos. Express Configuration simplifica el proceso de configuración mediante el uso de almacenamiento administrado por Microsoft, en lugar de exigirle que configure una cuenta de almacenamiento administrada por el cliente. Ambas versiones de script admiten la migración automatizada desde la configuración clásica, la extracción de línea de base y la reapplicación, y el examen de bases de datos para reflejar la nueva configuración.

Tipos de versión

Hay dos versiones de script disponibles en función del tipo de recurso:

Versión de la API unificada: usa la API REST unificada (2026-04-01-preview) y admite Azure SQL Database, Azure SQL Managed Instance y Azure Synapse Analytics. Esta versión es el enfoque recomendado para las nuevas implementaciones y admite los tres tipos de recursos de un solo script.
Versión exclusiva para Azure SQL Database: admite únicamente Azure SQL Database mediante el módulo Azure PowerShell (Az.Sql). Documentado para compatibilidad con versiones anteriores.

Ambos scripts migran de la configuración clásica (almacenamiento administrado por el cliente) a Express Configuration (almacenamiento administrado Microsoft), incluida la migración de línea base. El historial de escaneos no se migra en ninguna de las versiones.

Versión de LA API unificada
Versión exclusiva de Azure SQL Database

API unificada para Azure SQL Database, Azure SQL Managed Instance y Azure Synapse Analytics

Visión general

Este script automatiza la migración de Configuración clásica (almacenamiento administrado por el cliente para líneas base de evaluación de vulnerabilidades y resultados de los análisis) a Configuración exprés (almacenamiento administrado por Microsoft) para la evaluación de vulnerabilidades de SQL en Microsoft Defender para la nube. Con Express Configuration, no necesita una cuenta de almacenamiento. Microsoft almacena las líneas de base y los resultados de análisis.

Tipos de recurso compatibles

Tipo de recurso	proveedor de Azure Resource Manager
Azure SQL Database	`Microsoft.Sql/servers`
Instancia Administrada de SQL	`Microsoft.Sql/managedInstances`
Azure Synapse Analytics	`Microsoft.Synapse/workspaces`

Qué hace el script

Lee las líneas base actuales de la configuración clásica desde el almacenamiento de blobs.
Quita Configuración clásica
Habilita Configuración rápida a través de la API unificada (2026-04-01-preview)
Examina todas las bases de datos (examen previo a la base de datos)
Vuelve a aplicar sus líneas de base a Express Configuration
Examina de nuevo todas las bases de datos (examen posterior a la línea base) para reflejar el estado de línea base actualizado.

Note

El historial de análisis no se migra. Los resultados de escaneos anteriores siguen en su cuenta de almacenamiento original.

Si se produce un error en la habilitación, el script ofrece automáticamente la restauración de la configuración clásica.
Si se produce un error en la migración de línea base parcialmente, elija cómo continuar: reintentar, revisar individualmente, revertir o omitir.
Ejecutar el script en un recurso que ya tiene express Configuration es seguro. Sale sin cambios.

Prerequisites

PowerShell

Requirement	Versión mínima
PowerShell	7.0
Módulo Az.Accounts	2.9.1
Módulo Az.Storage	4.8.0

Instale o actualice los módulos:

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

Autenticación de Azure

Inicie sesión antes de ejecutar el script:

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

Permissions

La identidad que ejecuta el script necesita los permisos siguientes:

Permiso	Scope	Por qué
Microsoft.Security/* (o Colaborador)	Servidor,MI/Área de trabajo	Habilitación de la configuración rápida y administración de líneas base
Microsoft. Sql/* o Microsoft. Synapse/*	Servidor,MI/Área de trabajo	Leer o eliminar la configuración clásica, enumerar bases de datos, iniciar exámenes
Lector de datos de Storage Blob	Cuentas de almacenamiento usadas por la configuración clásica	Leer las líneas de base existentes desde Blob Storage.

Importante

SQL Managed Instance: la instancia administrada de SQL debe tener habilitada la identidad administrada asignada por el sistema (SAMI). Si SAMI está deshabilitado, se produce un error en la habilitación de Express Configuration. Habilite SAMI en Azure Portal, en MI → Identidad → Asignada por el sistema → Activado.

Importante

Cortafuegos de almacenamiento: Si la cuenta de almacenamiento de Configuración clásica tiene reglas de cortafuegos, permita que el equipo donde se ejecuta el script pueda acceder a ella. Puede permitir la lista de direcciones IP o usar un punto de conexión privado.

Parameters

Parámetro	Obligatorio	Default	Description
`-ServerResourceId`	Yes	-	Identificador completo del recurso de Azure Resource Manager del servidor, la instancia administrada o el área de trabajo de Synapse
`-Force`	No	`$false`	Omitir todas las solicitudes de confirmación (útiles para la automatización)
`-ScanTimeoutSeconds`	No	`300`	Número máximo de segundos de espera por examen de base de datos
`-ScanPollingIntervalSeconds`	No	`10`	Segundos entre consultas del estado del escaneo

Cómo encontrar tu identificador de recurso

En el portal de Azure, vaya al recurso → Properties → Resource ID o 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

Ejemplos de uso

Servidor de SQL Database

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

Instancia Administrada de SQL

.\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>"

No interactivo (automatización/CI)

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

Con -Force, la secuencia de comandos:

Omite el mensaje de confirmación antes de quitar la configuración clásica.
Omite automáticamente las reglas de línea base con error (sin menú de recuperación interactiva)

Script de ejemplo: 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

Solo versión Azure SQL Database (heredada)

Este script de PowerShell habilita la configuración rápida de las evaluaciones de vulnerabilidades en azure SQL Server.

Si ya usa la configuración clásica para la evaluación de vulnerabilidades, este script se migra a Express Configuration y copia todas las definiciones de línea de base existentes.

Importante

El historial de análisis no se migra a Express Configuration. Los resultados históricos del análisis siguen estando en la cuenta de almacenamiento que usaba anteriormente la Configuración clásica.

Prerequisites

Este ejemplo requiere Azure PowerShell Az 1.0, o cualquier versión posterior. Ejecute Get-Module -ListAvailable Az para ver qué versiones están instaladas. Si necesita instalarlo, consulte Instalación del módulo de Azure PowerShell.

Ejecute Connect-AzAccount para iniciar sesión en Azure.

Si no tiene una cuenta de Azure, cree una cuenta gratuita antes de comenzar.

Debe tener asignado el rol Storage Blob Data Reader en la cuenta de almacenamiento.
Configure las opciones de red de almacenamiento para permitir el acceso de la máquina a la cuenta de almacenamiento.
Debe tener permiso para crear carpetas en el directorio de trabajo del script.

Script de ejemplo: MigratingToExpressConfiguration.ps1

Note

Se recomienda usar el módulo de PowerShell de Azure Az para interactuar con Azure. Para comenzar, consulte Instalación de Azure PowerShell. Para obtener información sobre cómo migrar al módulo Az PowerShell, consulte Migración de Azure PowerShell de AzureRM a 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 -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"
}

Por ejemplo:

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 más sobre Azure PowerShell

Last updated on 2026-07-15

Habilitación de evaluaciones de vulnerabilidades en bases de datos de Azure SQL con Express Configuration

Tipos de versión

API unificada para Azure SQL Database, Azure SQL Managed Instance y Azure Synapse Analytics

Visión general

Tipos de recurso compatibles

Qué hace el script

Prerequisites

PowerShell

Autenticación de Azure

Permissions

Parameters

Cómo encontrar tu identificador de recurso

Ejemplos de uso

Servidor de SQL Database

Instancia Administrada de SQL

Azure Synapse Analytics

No interactivo (automatización/CI)

Script de ejemplo: MigrateToExpressConfiguration.ps1

Recursos adicionales