Abilitare le valutazioni delle vulnerabilità nei database Azure SQL con Configurazione rapida

Questo articolo fornisce script di PowerShell per l'abilitazione della valutazione della vulnerabilità SQL con configurazione rapida in Microsoft Defender per il cloud. Valutazione della vulnerabilità di SQL consente di identificare e correggere le vulnerabilità di sicurezza nei database. Configurazione rapida semplifica il processo di installazione usando l'archiviazione gestita da Microsoft anziché richiedere la configurazione di un account di archiviazione gestito dal cliente. Entrambe le versioni di script supportano la migrazione automatica dalla configurazione classica, dall'estrazione e dalla riapplicazione della baseline e dall'analisi del database per riflettere la nuova configurazione.

Tipi di versione

Sono disponibili due versioni di script a seconda del tipo di risorsa:

• Unified API version: usa l'API REST unificata (2026-04-01-preview) e supporta database SQL di Azure, Istanza gestita di SQL di Azure e Azure Synapse Analytics. Questa versione è l'approccio consigliato per le nuove distribuzioni e supporta tutti e tre i tipi di risorse in un singolo script.
• database SQL di Azure solo versione: supporta solo database SQL di Azure, usando il modulo Azure PowerShell (Az.Sql). Documentato per garantire la retrocompatibilità.

Entrambi gli script esegrano dalla configurazione classica (archiviazione gestita dal cliente) alla configurazione rapida (archiviazione gestita da Microsoft), inclusa la migrazione di base. La cronologia delle analisi non viene migrata in nessuna delle due versioni.

Versione unificata dell'API

API unificata per database SQL di Azure, Istanza gestita di SQL di Azure e Azure Synapse Analytics

Informazioni generali

Questo script automatizza la migrazione da Configurazione classica (archiviazione gestita dal cliente per le baseline per la valutazione delle vulnerabilità e i risultati delle analisi) a Configurazione rapida (archiviazione gestita da Microsoft) per la valutazione della vulnerabilità di SQL in Microsoft Defender per il cloud. Con la configurazione rapida non è necessario un account di archiviazione. Microsoft archivia le baseline e analizza i risultati.

Tipi di risorse supportati

Tipo di risorsa	Provider di Azure Resource Manager
Database SQL di Microsoft Azure	Microsoft.Sql/servers
Istanza gestita di SQL	Microsoft.Sql/managedInstances
Azure Synapse Analytics	Microsoft.Synapse/workspaces

Operazioni dello script

1. Legge le baseline correnti della configurazione classica dall'archiviazione BLOB
2. Rimuove Configurazione classica
3. Consente Configurazione rapida tramite l'API unificata (2026-04-01-preview)
4. Analizza tutti i database (analisi prebaselinea)
5. Riapplica le baseline alla Configurazione rapida
6. Analizza nuovamente tutti i database (analisi post-baseline) per riflettere lo stato di base aggiornato

Note

La cronologia delle scansioni non viene migrata. I risultati dell'analisi precedente rimangono nell'account di archiviazione originale.

• Se l'abilitazione non riesce, lo script offre automaticamente il ripristino delle impostazioni di configurazione classica.
• Se la migrazione di base riesce solo in parte, è possibile scegliere come procedere: riprovare, esaminare singolarmente, ripristinare o ignorare.
• L'esecuzione dello script in una risorsa che dispone già di Configurazione rapida è sicura. Esce senza modifiche.

Prerequisiti

PowerShell

Requisito	Versione minima
PowerShell	7.0
Modulo Az.Accounts	2.9.1
Modulo Az.Storage	4.8.0

Installare o aggiornare i moduli:

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

Autenticazione di Azure

Accedere prima di eseguire lo script:

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

Autorizzazioni

L'identità che esegue lo script richiede le autorizzazioni seguenti:

Autorizzazione	Scope	Perché
Microsoft.Security/* (o Collaboratore)	Server/MI/Workspace	Abilitare la configurazione rapida e gestire le linee di base
Microsoft. Sql/* o Microsoft. Synapse/*	Server/MI/Workspace	Leggere/eliminare le impostazioni di configurazione classica, elencare i database, avviare le analisi
Lettore dei dati del BLOB di archiviazione	Account di archiviazione usati dalla configurazione classica	Leggere le baseline esistenti dall'archivio BLOB

Importante

Istanza gestita di SQL: il Istanza gestita di SQL deve avere abilitata un'identità gestita assegnata dal sistema. Se SAMI è disabilitato, l'abilitazione della configurazione rapida ha esito negativo. Abilitare SAMI nel portale di Azure in la tua MI → Identità → Assegnata dal sistema → On.

Importante

Firewall di archiviazione: se l'account di archiviazione della Configurazione classica dispone di regole del firewall, consentire al computer che esegue lo script di accedervi. È possibile consentire l'elenco dell'indirizzo IP o usare un endpoint privato.

Parameters

Parametro	Obbligatorio	Default	Descrizione
-ServerResourceId	Yes	-	ID risorsa completo di Azure Resource Manager del server, dell'istanza gestita o dell'area di lavoro di Synapse
-Force	No	$false	Ignorare tutte le richieste di conferma (utile per l'automazione)
-ScanTimeoutSeconds	No	300	Numero massimo di secondi di attesa per ogni analisi del database
-ScanPollingIntervalSeconds	No	10	Secondi tra le interrogazioni dello stato della scansione

Come trovare l'ID della risorsa

Nel portale di Azure passare alla risorsa → Properties → Resource ID o usare:

# 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

Esempi di utilizzo

Server di database SQL

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

Istanza gestita di 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>"

Non interattivo (automazione/CI)

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

Con -Force, lo script:

• Ignora la richiesta di conferma prima di rimuovere la configurazione classica
• Ignora automaticamente le regole di base non riuscite (nessun menu di ripristino interattivo)

Script di esempio - 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

versione solo per database SQL di Azure

Versione con solo database SQL Azure (legacy)

Questo script di PowerShell consente la configurazione rapida delle valutazioni delle vulnerabilità in un'istanza di SQL Server di Azure.

Se si usa già la configurazione classica per la valutazione della vulnerabilità, questo script esegue la migrazione a Configurazione rapida e copia tutte le definizioni di base esistenti.

Importante

La cronologia di analisi non viene migrata a Configurazione rapida. I risultati dell'analisi cronologica rimangono nell'account di archiviazione usato in precedenza dalla configurazione classica.

Prerequisiti

Questo esempio richiede Azure PowerShell Az 1.0 o versioni successive. Eseguire Get-Module -ListAvailable Az per verificare le versioni installate. Se è necessario installarlo, vedere Installare il modulo Azure PowerShell.

Eseguire Connect-AzAccount per accedere ad Azure.

Se non si ha un account Azure, creare un account gratuito prima di iniziare.

• È necessario disporre del ruolo Storage Blob Data Reader nell'account di archiviazione.

• Configurare le impostazioni di rete di archiviazione per consentire al computer di accedere all'account di archiviazione.

• È necessario disporre dell'autorizzazione per creare cartelle nella directory di lavoro dello script.

Script di esempio - MigratingToExpressConfiguration.ps1

Note

È consigliabile usare il modulo Azure Az PowerShell per interagire con Azure. Per iniziare, vedere Installare Azure PowerShell. Per informazioni su come eseguire la migrazione al modulo Az PowerShell, vedere Eseguire la migrazione di Azure PowerShell da AzureRM ad 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"
}

Per esempio:

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

Altre informazioni su Azure PowerShell