Habilitar avaliações de vulnerabilidade em bancos de dados SQL do Azure com a Configuração Expressa

Este artigo fornece scripts do PowerShell para habilitar a VA (Avaliação de Vulnerabilidades do SQL) com a Configuração Expressa em Microsoft Defender para Nuvem. A Avaliação de Vulnerabilidades do SQL ajuda você a identificar e corrigir vulnerabilidades de segurança em seus bancos de dados. A Configuração Expressa simplifica o processo de instalação usando o armazenamento gerenciado por Microsoft em vez de exigir que você configure uma conta de armazenamento gerenciada pelo cliente. Ambas as versões de script dão suporte à migração automatizada da Configuração Clássica, à extração e à reaplicação de linha de base e à verificação de banco de dados para refletir a nova configuração.

Tipos de versão

Duas versões de script estão disponíveis dependendo do tipo de recurso:

Versão da APIUnificada: usa a API REST unificada (2026-04-01-preview) e dá suporte a Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure e Azure Synapse Analytics. Essa versão é a abordagem recomendada para novas implantações e dá suporte a todos os três tipos de recursos em um único script.
Versão somente para Banco de Dados SQL do Azure: Oferece suporte somente ao Banco de Dados SQL do Azure, usando o módulo Azure PowerShell (Az.Sql). Documentado para compatibilidade com versões anteriores.

Ambos os scripts migram da Configuração Clássica (armazenamento gerenciado pelo cliente) para a Configuração Expressa (armazenamento gerenciado por Microsoft), incluindo a migração de linha de base. O histórico de varredura não é migrado em nenhuma das versões.

Versão da API unificada
Somente a versão do Banco de Dados SQL do Azure

API unificada para Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure e Azure Synapse Analytics

Visão geral

Esse script automatiza a migração de Classic Configuration (armazenamento gerenciado pelo cliente para linhas de base de avaliação de vulnerabilidade e resultados de verificação) para Express Configuration (armazenamento gerenciado por Microsoft) para a Avaliação de Vulnerabilidade do SQL no Microsoft Defender para Nuvem. Com a Configuração Expressa, você não precisa de uma conta de armazenamento. A Microsoft armazena as linhas de base e os resultados de verificação.

Tipos de recursos suportados

Tipo de recurso	provedor de Azure Resource Manager
Banco de Dados SQL do Azure	`Microsoft.Sql/servers`
Instância Gerenciada de SQL	`Microsoft.Sql/managedInstances`
Azure Synapse Analytics	`Microsoft.Synapse/workspaces`

O que o script faz

Lê as suas configurações de referência atuais da Configuração Clássica do Armazenamento de Blobs
Remove Configuração Clássica
Permite Configuração expressa por meio da API unificada (2026-04-01-preview)
Verifica todos os bancos de dados (verificação pré-base)
Aplica novamente suas linhas de base à Configuração Expressa
Verifica todos os bancos de dados novamente (verificação pós-linha de base) para refletir o estado de linha de base atualizado

Note

O histórico de verificação não é migrado. Os resultados da verificação anterior permanecem em sua conta de armazenamento original.

Se a habilitação falhar, o script se oferecerá automaticamente para restaurar suas configurações de Configuração Clássica.
Se a migração de linha de base falhar parcialmente, você escolherá como continuar: repetir, revisar individualmente, reverter ou ignorar.
A execução do script em um recurso que já tem a Configuração expressa é segura. Ele sai sem alterações.

Pré-requisitos

PowerShell

Requirement	Versão mínima
PowerShell	7.0
Módulo Az.Accounts	2.9.1
Módulo Az.Storage	4.8.0

Instale ou atualize os módulos:

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

Autenticação do Azure

Entre antes de executar o script:

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

Permissões

A identidade que executa o script precisa das seguintes permissões:

Permissão	Scope	Por que
Microsoft.Security/* (ou Colaborador)	Servidor/MI/Espaço de trabalho	Habilitar a Configuração Expressa e gerenciar linhas de base
Microsoft. Sql/* ou Microsoft. Synapse/*	Servidor/MI/Espaço de trabalho	Ler/excluir configurações clássicas de configuração, listar bancos de dados, iniciar verificações
Leitor de Dados do Blob de Armazenamento	Contas de armazenamento usadas pela Configuração Clássica	Ler as linhas de base existentes do Armazenamento de Blobs

Importante

Instância Gerenciada de SQL: sua Instância Gerenciada de SQL deve ter uma identidade gerenciada atribuída pelo sistema (SAMI) habilitada. Se o SAMI estiver desabilitado, a habilitação da Configuração Expressa falhará. Habilite o SAMI no portal do Azure em seu MI → Identidade → Atribuído pelo sistema → Ligado.

Importante

Firewall de armazenamento: se sua conta de armazenamento de Configuração Clássica tiver regras de firewall, permita que o computador do script o alcance. Você pode permitir listar o seu IP ou usar um ponto de extremidade privado.

Parameters

Parâmetro	Obrigatório	Default	Description
`-ServerResourceId`	Yes	-	ID completa do recurso do Azure Resource Manager do servidor, da instância gerenciada ou do espaço de trabalho do Synapse
`-Force`	No	`$false`	Ignorar todos os prompts de confirmação (úteis para automação)
`-ScanTimeoutSeconds`	No	`300`	Número máximo de segundos para aguardar cada varredura do banco de dados
`-ScanPollingIntervalSeconds`	No	`10`	Segundos entre as pesquisas de status de verificação

Como localizar sua ID de recurso

No portal do Azure, acesse o recurso → Properties → Resource ID ou use:

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

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

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

Exemplos de uso

servidor do banco de dados SQL

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

Instância Gerenciada de SQL

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

Azure Synapse Analytics

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

Não interativo (automação/CI)

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

Com -Force, o script:

Ignora o prompt de confirmação antes de remover a Configuração Clássica
Ignora automaticamente todas as regras de linha de base com falha (sem menu de recuperação interativa)

Script de exemplo – 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

Versão exclusiva do Banco de Dados SQL do Azure (legado)

Esse script do PowerShell habilita a configuração expressa de avaliações de vulnerabilidade em um SQL Server do Azure.

Se você já usar a Configuração Clássica para avaliação de vulnerabilidade, esse script migrará para a Configuração Expressa e copiará todas as definições de linha de base existentes.

Importante

O histórico de varredura não é transferido para a Configuração Expressa. Os resultados da verificação histórica permanecem na conta de armazenamento usada anteriormente pela Configuração Clássica.

Pré-requisitos

Este exemplo exige o Azure PowerShell Az 1.0 ou posterior. Execute Get-Module -ListAvailable Az para ver quais versões estão instaladas. Se é preciso instalar, consulte Instalar o módulo do Azure PowerShell.

Execute Connect-AzAccount para entrar no Azure.

Se você ainda não tiver uma conta do Azure, crie uma conta gratuita antes de começar.

Você deve ter a função Storage Blob Data Reader na conta de armazenamento.
Defina as configurações de rede de armazenamento para permitir o acesso do computador à conta de armazenamento.
Você deve ter permissão para criar pastas no diretório de trabalho do script.

Script de exemplo – MigratingToExpressConfiguration.ps1

Note

Recomendamos que você use o módulo Azure Az PowerShell para interagir com Azure. Para começar, consulte Instalar Azure PowerShell. Para saber como migrar para o módulo Az PowerShell, confira Migrar o Azure PowerShell do AzureRM para o Az.

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

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

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

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

.PARAMETER ServerSubscriptionId
    The Subscription id that the server belongs to

.PARAMETER ServerResourceGroupName
    The Resource Group that the server belongs to

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

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

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

#>

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

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

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

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


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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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


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

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

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

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

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


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

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

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

    Invoke-AzRestMethod @Params
}

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

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

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

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

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

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

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

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

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


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

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

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

    return $false
}

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

    return $false
}

function GetBlobsFromStorage($ContainerName, $Context) {

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

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

    return $blobs
}


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

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

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

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

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

        return $mostRecentBlobName
    }
}

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

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

    return $blobContent
}

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

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

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

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

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

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

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

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

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

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

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


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

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

ClearBaselineFolder

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

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

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

    $canRemoveVa = $true

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

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

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

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

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

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

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

    ClearBaselineFolder

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

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

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

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

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

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

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

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

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

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

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

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

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

Por exemplo:

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

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

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

Learn mais sobre Azure PowerShell

Last updated on 2026-07-15

Habilitar avaliações de vulnerabilidade em bancos de dados SQL do Azure com a Configuração Expressa

Tipos de versão

API unificada para Banco de Dados SQL do Azure, Instância Gerenciada de SQL do Azure e Azure Synapse Analytics

Visão geral

Tipos de recursos suportados

O que o script faz

Pré-requisitos

PowerShell

Autenticação do Azure

Permissões

Parameters

Como localizar sua ID de recurso

Exemplos de uso

servidor do banco de dados SQL

Instância Gerenciada de SQL

Azure Synapse Analytics

Não interativo (automação/CI)

Script de exemplo – MigrateToExpressConfiguration.ps1

Recursos adicionais