使用 Express Configuration 对Azure SQL数据库启用漏洞评估

重要

注意：根据世纪互联发布的公告，2026 年 8 月 18 日，中国地区的 Azure 中将正式停用所有 Microsoft Defender for Cloud 功能。

本文提供了 PowerShell 脚本，用于在 Microsoft Defender for Cloud 中使用 Express Configuration 启用 SQL 漏洞评估（VA）。 SQL 漏洞评估可帮助你识别和修正数据库中的安全漏洞。快速配置通过使用Azure托管存储来简化设置过程，而无需配置客户管理的存储帐户。这两个脚本版本都支持从经典配置、基线提取和重新应用以及数据库扫描自动迁移，以反映新配置。

版本类型

根据资源类型，有两个脚本版本可用：

统一 API 版本：使用统一的 REST API（2026-04-01-preview），并支持Azure SQL 数据库、Azure SQL 托管实例和Azure Synapse Analytics。 此版本是新部署的建议方法，支持单个脚本中的所有三种资源类型。
Azure SQL 数据库专用版本：仅支持 Azure SQL 数据库，使用 Azure PowerShell（Az.Sql）模块。文档中注明这是为了向后兼容。

这两个脚本都从经典配置（客户管理的存储）迁移到 Express Configuration（Azure托管存储），包括基线迁移。扫描历史记录未在任一版本中迁移。

统一 API 版本
仅限 Azure SQL 数据库的版本

适用于Azure SQL 数据库、Azure SQL 托管实例和Azure Synapse Analytics的统一 API

概述

此脚本自动从经典配置（客户管理的存储用于漏洞评估基线和扫描结果）迁移到 Express Configuration（Azure 托管存储），以便在 Microsoft Defender for Cloud 中执行 SQL 漏洞评估。使用 Express Configuration 时，不需要存储帐户。 Microsoft存储基线和扫描结果。

支持的资源类型

资源类型	Azure 资源管理器资源提供程序
Azure SQL 数据库	`Microsoft.Sql/servers`
SQL 托管实例	`Microsoft.Sql/managedInstances`
Azure Synapse Analytics	`Microsoft.Synapse/workspaces`

脚本的作用

从 Blob 存储中读取当前的经典配置基线
删除经典配置
使通过统一 API 进行快速配置（2026-04-01-preview）
扫描所有数据库（基线前扫描）
将您的基线重新应用于 Express Configuration
再次扫描所有数据库（基线后扫描）以反映更新的基线状态

注意

扫描历史记录未迁移。过去的扫描结果保留在原始存储帐户中。

如果启用失败，脚本 会自动提示恢复 您的经典配置设置。
如果基线迁移部分失败，您可以选择后续操作方式：重试、逐项检查、回退或跳过。
在已具有 Express Configuration 的资源上运行脚本是安全的。退出且未进行任何更改。

先决条件

PowerShell

Requirement	最低版本
PowerShell	7.0
Az.Accounts 模块	2.9.1
Az.Storage 模块	4.8.0

安装或更新模块：

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

Azure身份验证

在运行脚本之前登录：

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

权限

运行脚本的标识需要以下权限：

许可	Scope	为什么
Microsoft.Security/* (或参与者)	服务器/MI/工作区	启用 Express Configuration 并管理基线
Microsoft。Sql/* 或 Microsoft。Synapse/*	服务器/MI/工作区	读取/删除经典配置设置、列出数据库、启动扫描
存储 Blob 数据读者	经典配置使用的存储帐户	从 Blob 存储读取现有基线

重要

SQL 托管实例：你的 SQL 托管实例必须启用系统分配的托管标识（SAMI）。如果 SAMI 已禁用，Express Configuration 启用将失败。在 Azure 门户中的“MI”→“标识”→“系统分配的”下，将 SAMI 设置为启用。

重要

存储防火墙：如果经典配置存储帐户具有防火墙规则，则允许脚本的计算机访问它。您可以将您的 IP 加入允许列表，或使用专用终结点。

参数

参数	必选	默认	Description
`-ServerResourceId`	Yes	-	服务器、托管实例或 Synapse 工作区的完整 Azure 资源管理器资源 ID
`-Force`	No	`$false`	跳过所有确认提示（对自动化很有用）
`-ScanTimeoutSeconds`	No	`300`	每次数据库扫描的最长等待秒数
`-ScanPollingIntervalSeconds`	No	`10`	扫描状态轮询间隔（秒）

如何查找资源 ID

在 Azure 门户中，转到资源 → Properties → Resource ID 或使用：

# 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

用法示例

SQL Database 服务器

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

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

非交互式（自动化/CI）

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

使用 -Force 时，脚本如下：

在删除经典配置之前跳过确认提示
自动跳过任何失败的基线规则（无交互式恢复菜单）

示例脚本 - 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

仅限 Azure SQL 数据库版本（旧版）

此 PowerShell 脚本支持在 Azure SQL 服务器上快速配置漏洞评估。

如果已使用经典配置进行漏洞评估，此脚本将迁移到 Express Configuration 并复制所有现有基线定义。

重要

扫描历史记录不会迁移到 Express Configuration。历史扫描结果仍保留在经典配置以前使用的存储帐户上。

先决条件

本示例需要 Azure PowerShell Az 1.0 或更高版本。运行 Get-Module -ListAvailable Az，查看已安装哪些版本。如果需要安装，请参阅安装 Azure PowerShell 模块。

运行 Connect-AzAccount -Environment AzureChinaCloud 以登录到 Azure。

如果您没有 Azure 试用订阅，请在开始之前创建试用订阅。

必须在存储帐户上具有 Storage Blob Data Reader 该角色。
配置存储网络设置以允许计算机访问存储帐户。
你必须有权在脚本的工作目录中创建文件夹。

示例脚本 - MigratingToExpressConfiguration.ps1

注意

建议使用 Azure Az PowerShell 模块与 Azure 交互。若要开始，请参阅安装 Azure PowerShell。若要了解如何迁移到 Az PowerShell 模块，请参阅将 Azure PowerShell 从 AzureRM 迁移到 Az。

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

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

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

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

.PARAMETER ServerSubscriptionId
    The Subscription id that the server belongs to

.PARAMETER ServerResourceGroupName
    The Resource Group that the server belongs to

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

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

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

#>

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

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

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

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


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

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

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

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

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

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

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

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

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

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


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

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

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

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

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

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


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

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

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

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

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


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

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

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

    Invoke-AzRestMethod @Params
}

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

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

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

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

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

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

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

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

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


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

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

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

    return $false
}

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

    return $false
}

function GetBlobsFromStorage($ContainerName, $Context) {

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

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

    return $blobs
}


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

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

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

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

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

        return $mostRecentBlobName
    }
}

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

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

    return $blobContent
}

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

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

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

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

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

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

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

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

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

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

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


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

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

ClearBaselineFolder

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

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

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

    $canRemoveVa = $true

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

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

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

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

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

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

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

    ClearBaselineFolder

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

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

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

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

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

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

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

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

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

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

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

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

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

例如：

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

详细了解 Azure PowerShell

Last updated on 2026-07-03

使用 Express Configuration 对Azure SQL数据库启用漏洞评估

版本类型

适用于Azure SQL 数据库、Azure SQL 托管实例和Azure Synapse Analytics的统一 API

概述

支持的资源类型

脚本的作用

先决条件

PowerShell

Azure身份验证

权限

参数

如何查找资源 ID

用法示例

SQL Database 服务器

SQL 托管实例

Azure Synapse Analytics

非交互式 （自动化/CI）

示例脚本 - MigrateToExpressConfiguration.ps1

其他资源

非交互式（自动化/CI）