Nota
O acesso a esta página requer autorização. Pode tentar iniciar sessão ou alterar os diretórios.
O acesso a esta página requer autorização. Pode tentar alterar os diretórios.
Este artigo fornece scripts PowerShell para ativar a Avaliação de Vulnerabilidades SQL (VA) com Configuração Express no Microsoft Defender para a Cloud. A Avaliação de Vulnerabilidades SQL ajuda-o a identificar e remediar vulnerabilidades de segurança nas suas bases de dados. O Express Configuration simplifica o processo de configuração ao utilizar armazenamento gerido pela Microsoft em vez de exigir que configure uma conta de armazenamento gerida pelo cliente. Ambas as versões do script suportam a migração automatizada da Configuração clássica, a extração da linha de base e a sua reaplicação, bem como a análise da base de dados para refletir a nova configuração.
Tipos de versões
Estão disponíveis duas versões de script dependendo do seu tipo de recurso:
-
Versão da API unificada: Utiliza a API REST unificada (
2026-04-01-preview) e é compatível com o Base de Dados SQL do Azure, o Azure SQL Managed Instance e o Azure Synapse Analytics. Esta versão é a abordagem recomendada para novas implementações e suporta os três tipos de recursos num único script. -
Versão apenas para o Base de Dados SQL do Azure: Suporta apenas o Base de Dados SQL do Azure, utilizando o módulo Azure PowerShell (
Az.Sql). Documentado para compatibilidade retroativa.
Ambos os scripts migram do Classic Configuration (armazenamento gerido pelo cliente) para o Express Configuration (armazenamento gerido pela Microsoft), incluindo a migração baseline. O histórico de digitalização não é migrado em qualquer uma das versões.
API unificada para Base de Dados SQL do Azure, Azure SQL Managed Instance e Azure Synapse Analytics
Descrição geral
Este script automatiza a migração de Classic Configuration (armazenamento gerido pelo cliente para referências de avaliação de vulnerabilidades e resultados de análise) para Express Configuration (armazenamento gerido Microsoft) para Avaliação de Vulnerabilidades SQL em Microsoft Defender para a Cloud. Com o Express Configuration, não precisa de uma conta de armazenamento. A Microsoft armazena linhas de base e resultados de análise.
Tipos de recursos suportados
| Tipo de recurso | fornecedor do Azure Resource Manager |
|---|---|
| Base de Dados SQL do Azure | Microsoft.Sql/servers |
| SQL Managed Instance | Microsoft.Sql/managedInstances |
| Azure Synapse Analytics | Microsoft.Synapse/workspaces |
O que o guião faz
- Lê as linhas de base da Configuração Clássica atuais a partir do armazenamento de blobs
- Remove Configuração Clássica
-
Possibilita Configuração Expressa via API unificada (
2026-04-01-preview) - Analisa todas as bases de dados (análise prévia à linha de base)
- Volta a aplicar as suas configurações de base ao Express Configuration
- Analisa novamente todas as bases de dados (após a análise da linha base) para refletir o estado atualizado da linha de base
Note
O histórico de digitalização não é transferido. Os resultados das análises passadas permanecem na sua conta de armazenamento original.
- Se a habilitação falhar, o script oferece automaticamente a possibilidade de restaurar as definições da Configuração Clássica.
- Se a migração de base falhar parcialmente, escolhe como proceder: tentar novamente, rever individualmente, reverter ou saltar.
- Executar o script num recurso que já tenha Express Configuration é seguro. Sai sem alterações.
Pré-requisitos
PowerShell
| Requisito | Versão mínima |
|---|---|
| PowerShell | 7.0 |
| Módulo Az.Accounts | 2.9.1 |
| Módulo Az.Storage | 4.8.0 |
Instale ou atualize os módulos:
Install-Module Az.Accounts -MinimumVersion 2.9.1 -Scope CurrentUser
Install-Module Az.Storage -MinimumVersion 4.8.0 -Scope CurrentUser
Autenticação do Azure
Iniciar sessão antes de executar o script:
Connect-AzAccount
Set-AzContext -SubscriptionId "<your-subscription-id>"
Permissões
A identidade que executa o script necessita das seguintes permissões:
| Permissão | Scope | Porquê |
|---|---|---|
| Microsoft.Security/* (ou Contribuidor) | Servidor/MI/Espaço de Trabalho | Ativar a Configuração Express e gerir as linhas de base |
| Microsoft. Sql/* ou Microsoft. Sinapse/* | Servidor/MI/Espaço de Trabalho | Ler/eliminar definições de configuração clássicas, listar bases de dados, iniciar análises |
| Leitor de Dados de Armazenamento Blob | Contas de armazenamento usadas pela Configuração Clássica | Leia as linhas de base existentes a partir do armazenamento de blob |
Importante
SQL Managed Instance: O seu SQL Managed Instance deve ter uma identidade gerida atribuída pelo sistema (SAMI) ativada. Se o SAMI estiver desativado, a habilitação de configuração Express falha. Ative o SAMI no portal Azure no seu MI → Identidade → Sistema Atribuído → On.
Importante
Firewall de armazenamento: Se a sua conta de armazenamento Classic Configuration tiver regras de firewall, permita que a máquina do script a acedeça. Pode permitir listar o seu IP ou usar um Endpoint Privado.
Parameters
| Parâmetro | Required | Default | Description |
|---|---|---|---|
-ServerResourceId |
Yes | - | ID completo do recurso do Azure Resource Manager do servidor, da instância gerida ou do espaço de trabalho do Synapse |
-Force |
No | $false |
Ignora todos os prompts de confirmação (útil para automação) |
-ScanTimeoutSeconds |
No | 300 |
Máximo de segundos de espera por análise da base de dados |
-ScanPollingIntervalSeconds |
No | 10 |
Segundos entre verificações do estado da análise |
Como encontrar o seu ID de recurso
No portal Azure, vá ao seu recurso → Properties → ID de Recursos, ou use:
# SQL Database server
(Get-AzSqlServer -ResourceGroupName "myRG" -ServerName "myServer").ResourceId
# SQL Managed Instance
(Get-AzSqlInstance -ResourceGroupName "myRG" -InstanceName "myMI").Id
# Synapse workspace
(Get-AzSynapseWorkspace -ResourceGroupName "myRG" -Name "myWorkspace").Id
Exemplos de utilização
servidor da Base de Dados SQL
.\MigrateToExpressConfiguration.ps1 `
-ServerResourceId "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Sql/servers/<server-name>"
SQL Managed Instance
.\MigrateToExpressConfiguration.ps1 `
-ServerResourceId "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Sql/managedInstances/<mi-name>"
Azure Synapse Analytics
.\MigrateToExpressConfiguration.ps1 `
-ServerResourceId "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Synapse/workspaces/<workspace-name>"
Não interativo (automação / CI)
.\MigrateToExpressConfiguration.ps1 `
-ServerResourceId "<resource-id>" `
-Force
Com -Force, o script:
- Ignora o prompt de confirmação antes de remover a Configuração Clássica
- Ignora automaticamente quaisquer regras de base falhadas (sem menu interativo de recuperação)
Script de exemplo - MigrateToExpressConfiguration.ps1
#Requires -Modules @{ ModuleName="Az.Accounts"; ModuleVersion="2.9.1" }
#Requires -Modules @{ ModuleName="Az.Storage"; ModuleVersion="4.8.0" }
#Requires -Version 7.0
<#
.SYNOPSIS
Migrates Azure SQL resources from Classic VA configuration to Express Configuration
using the unified v2026-04-01-preview API.
.DESCRIPTION
Supports: Azure SQL Database, SQL Managed Instance, and Azure Synapse Analytics.
The script:
1. Detects the resource type from the ARM resource ID
2. Checks if Express Configuration is already enabled
3. Extracts existing baselines from Classic Configuration storage (if any)
4. Removes Classic VA settings (blocking - aborts and restores on failure)
5. Enables Express Configuration via the new unified API
- On failure: automatically offers to restore Classic Configuration
6. Discovers and scans all databases
7. Applies migrated baselines
- On failure: offers interactive recovery (retry, per-rule review, or revert)
8. Reports a detailed migration summary
Scan history is NOT migrated - it remains in the original storage account.
To revert manually, see:
https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration
.PARAMETER ServerResourceId
Server-level ARM resource ID. Examples:
/subscriptions/{sub}/resourceGroups/{rg}/providers/Microsoft.Sql/servers/{server}
/subscriptions/{sub}/resourceGroups/{rg}/providers/Microsoft.Sql/managedInstances/{mi}
/subscriptions/{sub}/resourceGroups/{rg}/providers/Microsoft.Synapse/workspaces/{ws}
.PARAMETER Force
Skip confirmation prompts (removal of Classic VA, baseline recovery choices).
.PARAMETER ScanTimeoutSeconds
Maximum time in seconds to wait for each database scan to complete. Default: 300.
.PARAMETER ScanPollingIntervalSeconds
Interval in seconds between scan status polls. Default: 10.
.EXAMPLE
.\MigrateToExpressConfiguration.ps1 -ServerResourceId "/subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.Sql/servers/<server>"
.EXAMPLE
.\MigrateToExpressConfiguration.ps1 -ServerResourceId "/subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.Sql/managedInstances/<mi>" -Force
.EXAMPLE
.\MigrateToExpressConfiguration.ps1 -ServerResourceId "/subscriptions/<sub>/resourceGroups/<rg>/providers/Microsoft.Synapse/workspaces/<ws>"
#>
param(
[Parameter(Mandatory = $true)]
[string]$ServerResourceId,
[switch]$Force,
[int]$ScanTimeoutSeconds = 300,
[int]$ScanPollingIntervalSeconds = 10
)
$ErrorActionPreference = "Stop"
$ExpressApiVersion = "2026-04-01-preview"
# Classic VA API versions per resource type
$ClassicApiVersions = @{
SqlServer = "2021-11-01"
SqlManagedInstance = "2023-08-01"
Synapse = "2021-06-01"
}
# ARM API versions for listing databases
$DatabaseListApiVersions = @{
SqlServer = "2021-11-01"
SqlManagedInstance = "2023-08-01"
Synapse = "2021-06-01-preview"
}
# ======================================================================
#region --- Logging helpers ---
# ======================================================================
function Write-Log {
param([string]$Message)
Write-Host ("{0} - {1}" -f (Get-Date -Format "yyyy-MM-dd HH:mm:ss"), $Message)
}
function Write-LogError {
param([string]$Message)
Write-Host ("{0} - ERROR: {1}" -f (Get-Date -Format "yyyy-MM-dd HH:mm:ss"), $Message) -ForegroundColor Red
}
function Write-LogWarn {
param([string]$Message)
Write-Host ("{0} - WARN: {1}" -f (Get-Date -Format "yyyy-MM-dd HH:mm:ss"), $Message) -ForegroundColor Yellow
}
function Write-LogSuccess {
param([string]$Message)
Write-Host ("{0} - {1}" -f (Get-Date -Format "yyyy-MM-dd HH:mm:ss"), $Message) -ForegroundColor Green
}
function Write-LogDetail {
param([string]$Message)
Write-Host ("{0} {1}" -f (Get-Date -Format "yyyy-MM-dd HH:mm:ss"), $Message) -ForegroundColor DarkGray
}
function Write-Section {
param([string]$Title)
Write-Host ""
Write-Host ([string]::new([char]0x2501, 60)) -ForegroundColor Cyan
Write-Host " $Title" -ForegroundColor Cyan
Write-Host ([string]::new([char]0x2501, 60)) -ForegroundColor Cyan
}
function Write-SubSection {
param([string]$Title)
Write-Host ""
Write-Host " --- $Title ---" -ForegroundColor Yellow
}
function Write-Box {
param([string[]]$Lines)
Write-Host ""
Write-Host " $([char]0x250C)$([string]::new([char]0x2500, 56))" -ForegroundColor DarkCyan
foreach ($line in $Lines) {
Write-Host " $([char]0x2502) $line" -ForegroundColor DarkCyan
}
Write-Host " $([char]0x2514)$([string]::new([char]0x2500, 56))" -ForegroundColor DarkCyan
}
#endregion
# ======================================================================
#region --- Retry ---
# ======================================================================
function Invoke-WithRetry {
param(
[scriptblock]$Action,
[int]$MaxAttempts = 3,
[string]$Description = "operation"
)
for ($attempt = 1; $attempt -le $MaxAttempts; $attempt++) {
try {
return (& $Action)
}
catch {
if ($attempt -eq $MaxAttempts) {
Write-LogError "Failed '$Description' after $MaxAttempts attempts: $($_.Exception.Message)"
throw
}
$delay = [math]::Pow(2, $attempt) - 1
Write-LogDetail "Attempt $attempt/$MaxAttempts failed for '$Description'. Retrying in ${delay}s..."
Start-Sleep -Seconds $delay
}
}
}
#endregion
# ======================================================================
#region --- REST helper ---
# ======================================================================
function Invoke-ArmRequest {
param(
[string]$Method,
[string]$Path,
[object]$Body = $null
)
$params = @{ Method = $Method; Path = $Path }
if ($Body) {
$params.Payload = ($Body | ConvertTo-Json -Depth 10)
}
Write-LogDetail "$Method $Path"
$resp = Invoke-AzRestMethod @params
Write-LogDetail "=> HTTP $($resp.StatusCode)"
return $resp
}
function Get-ErrorMessage {
param($Response)
try {
$parsed = $Response.Content | ConvertFrom-Json
if ($parsed.error.message) { return $parsed.error.message }
if ($parsed.error.code) { return "$($parsed.error.code): $($parsed.error.message)" }
}
catch {}
return $Response.Content
}
#endregion
# ======================================================================
#region --- Resource ID Parsing ---
# ======================================================================
function Parse-ServerResourceId {
param([string]$Id)
$Id = $Id.Trim("/")
$result = @{
FullId = $Id
SubscriptionId = $null
ResourceGroup = $null
ResourceName = $null
ResourceType = $null
}
if ($Id -match "subscriptions/([^/]+)/resourceGroups/([^/]+)/") {
$result.SubscriptionId = $Matches[1]
$result.ResourceGroup = $Matches[2]
}
else {
throw "Could not parse subscription and resource group from: $Id"
}
if ($Id -match "providers/Microsoft\.Sql/servers/([^/]+)$") {
$result.ResourceType = "SqlServer"
$result.ResourceName = $Matches[1]
}
elseif ($Id -match "providers/Microsoft\.Sql/managedInstances/([^/]+)$") {
$result.ResourceType = "SqlManagedInstance"
$result.ResourceName = $Matches[1]
}
elseif ($Id -match "providers/Microsoft\.Synapse/workspaces/([^/]+)$") {
$result.ResourceType = "Synapse"
$result.ResourceName = $Matches[1]
}
else {
throw @"
Unsupported or non-server-level resource ID. Expected one of:
.../Microsoft.Sql/servers/{name}
.../Microsoft.Sql/managedInstances/{name}
.../Microsoft.Synapse/workspaces/{name}
Got: $Id
"@
}
return $result
}
#endregion
# ======================================================================
#region --- Classic VA Functions ---
# ======================================================================
function Get-ClassicVAStorageFromContent {
param([string]$ResponseContent)
$content = $ResponseContent | ConvertFrom-Json
$path = $content.properties.storageContainerPath
if ([string]::IsNullOrEmpty($path)) { return $null }
$parts = $path -split "/"
return @{
StorageAccount = $parts[2].Split(".")[0]
ContainerName = $parts[3]
}
}
function Get-StorageKey {
param([hashtable]$Storage)
return "$($Storage.StorageAccount)/$($Storage.ContainerName)"
}
# Check if ADS/ATP is enabled at a given resource path
function Get-AdsEnabled {
param([string]$AtpUri)
try {
$resp = Invoke-ArmRequest -Method GET -Path $AtpUri
if ($resp.StatusCode -eq 200) {
$content = $resp.Content | ConvertFrom-Json
$state = $content.properties.state
return ($state -eq "Enabled")
}
}
catch {}
return $false
}
# Determine effective storage for a database using the same logic as the service:
# 1. If DB VA defined (has storagePath):
# - If server VA defined AND server ADS ON AND DB ADS OFF → server storage
# - Else → DB storage
# 2. Else → server storage
function Get-EffectiveStorage {
param(
[hashtable]$ServerStorage,
[bool]$IsServerVaDefined,
[bool]$IsServerAdsEnabled,
[hashtable]$DbStorage, # $null if DB VA has no storagePath
[bool]$IsDatabaseAdsEnabled
)
$isDatabaseVaDefined = $null -ne $DbStorage
if ($isDatabaseVaDefined) {
if ($IsServerVaDefined -and $IsServerAdsEnabled -and -not $IsDatabaseAdsEnabled) {
return @{ Storage = $ServerStorage; Source = "Server (ADS override)" }
}
else {
return @{ Storage = $DbStorage; Source = "Database" }
}
}
else {
return @{ Storage = $ServerStorage; Source = "Server" }
}
}
function Get-ClassicVASettings {
param([hashtable]$Resource, [string[]]$Databases)
$classicApiVersion = $ClassicApiVersions[$Resource.ResourceType]
$settings = @{
HasClassicVA = $false
ServerStorage = $null
DatabaseStorage = @{} # dbName → storage (only when effective storage differs from server)
# Saved response bodies for restore
RestoreData = @{
ServerUri = $null
ServerBody = $null
Databases = @{} # dbName → @{ Uri; Body }
}
}
# --- ATP URI templates per resource type ---
$atpPaths = @{
SqlServer = @{
Server = "/$($Resource.FullId)/advancedThreatProtectionSettings/Default?api-version=$classicApiVersion"
Db = { param($db) "/$($Resource.FullId)/databases/$db/advancedThreatProtectionSettings/Default?api-version=$classicApiVersion" }
}
SqlManagedInstance = @{
Server = "/$($Resource.FullId)/advancedThreatProtectionSettings/Default?api-version=$classicApiVersion"
Db = { param($db) "/$($Resource.FullId)/databases/$db/advancedThreatProtectionSettings/Default?api-version=$classicApiVersion" }
}
Synapse = @{
Server = "/$($Resource.FullId)/securityAlertPolicies/Default?api-version=$($ClassicApiVersions['Synapse'])"
Db = { param($pool) "/$($Resource.FullId)/sqlPools/$pool/securityAlertPolicies/Default?api-version=$($ClassicApiVersions['Synapse'])" }
}
}
$atp = $atpPaths[$Resource.ResourceType]
# Check server-level ADS/ATP
$isServerAdsEnabled = Get-AdsEnabled -AtpUri $atp.Server
Write-LogDetail "Server ADS enabled: $isServerAdsEnabled"
switch ($Resource.ResourceType) {
"SqlServer" {
# Server-level VA
$uri = "/$($Resource.FullId)/vulnerabilityAssessments/default?api-version=$classicApiVersion"
$resp = Invoke-ArmRequest -Method GET -Path $uri
$isServerVaDefined = $false
if ($resp.StatusCode -eq 200) {
$storage = Get-ClassicVAStorageFromContent $resp.Content
if ($storage) {
$settings.HasClassicVA = $true
$settings.ServerStorage = $storage
$settings.RestoreData.ServerUri = $uri
$settings.RestoreData.ServerBody = $resp.Content
$isServerVaDefined = $true
Write-Log " Server-level Classic VA: $(Get-StorageKey $storage)"
}
}
# Master database
$masterUri = "/$($Resource.FullId)/databases/master/vulnerabilityAssessments/default?api-version=$classicApiVersion"
$resp = Invoke-ArmRequest -Method GET -Path $masterUri
if ($resp.StatusCode -eq 200) {
$dbStorage = Get-ClassicVAStorageFromContent $resp.Content
if ($dbStorage) {
$settings.HasClassicVA = $true
if (-not $settings.ServerStorage) { $settings.ServerStorage = $dbStorage }
$settings.RestoreData.Databases["master"] = @{ Uri = $masterUri; Body = $resp.Content }
Write-Log " master Classic VA: $(Get-StorageKey $dbStorage)"
$isMasterAdsEnabled = Get-AdsEnabled -AtpUri (& $atp.Db "master")
$effective = Get-EffectiveStorage -ServerStorage $settings.ServerStorage -IsServerVaDefined $isServerVaDefined `
-IsServerAdsEnabled $isServerAdsEnabled -DbStorage $dbStorage -IsDatabaseAdsEnabled $isMasterAdsEnabled
if ($effective.Storage -and (Get-StorageKey $effective.Storage) -ne (Get-StorageKey $settings.ServerStorage)) {
$settings.DatabaseStorage["master"] = $effective.Storage
Write-LogDetail " master effective: $(Get-StorageKey $effective.Storage) ($($effective.Source))"
}
}
}
# User databases
foreach ($db in $Databases) {
if ($db -eq "master") { continue }
$dbUri = "/$($Resource.FullId)/databases/$db/vulnerabilityAssessments/default?api-version=$classicApiVersion"
$resp = Invoke-ArmRequest -Method GET -Path $dbUri
if ($resp.StatusCode -eq 200) {
$dbStorage = Get-ClassicVAStorageFromContent $resp.Content
if ($dbStorage) {
if (-not $settings.ServerStorage) {
$settings.HasClassicVA = $true
$settings.ServerStorage = $dbStorage
}
$settings.RestoreData.Databases[$db] = @{ Uri = $dbUri; Body = $resp.Content }
Write-Log " '$db' Classic VA: $(Get-StorageKey $dbStorage)"
$isDbAdsEnabled = Get-AdsEnabled -AtpUri (& $atp.Db $db)
$effective = Get-EffectiveStorage -ServerStorage $settings.ServerStorage -IsServerVaDefined $isServerVaDefined `
-IsServerAdsEnabled $isServerAdsEnabled -DbStorage $dbStorage -IsDatabaseAdsEnabled $isDbAdsEnabled
if ($effective.Storage -and (Get-StorageKey $effective.Storage) -ne (Get-StorageKey $settings.ServerStorage)) {
$settings.DatabaseStorage[$db] = $effective.Storage
Write-LogDetail " '$db' effective: $(Get-StorageKey $effective.Storage) ($($effective.Source))"
}
}
}
}
}
"SqlManagedInstance" {
$uri = "/$($Resource.FullId)/vulnerabilityAssessments/default?api-version=$classicApiVersion"
$resp = Invoke-ArmRequest -Method GET -Path $uri
$isServerVaDefined = $false
if ($resp.StatusCode -eq 200) {
$storage = Get-ClassicVAStorageFromContent $resp.Content
if ($storage) {
$settings.HasClassicVA = $true
$settings.ServerStorage = $storage
$settings.RestoreData.ServerUri = $uri
$settings.RestoreData.ServerBody = $resp.Content
$isServerVaDefined = $true
Write-Log " Server-level Classic VA: $(Get-StorageKey $storage)"
}
}
foreach ($db in $Databases) {
if ($db -eq "master") { continue }
$dbUri = "/$($Resource.FullId)/databases/$db/vulnerabilityAssessments/default?api-version=$classicApiVersion"
$resp = Invoke-ArmRequest -Method GET -Path $dbUri
if ($resp.StatusCode -eq 200) {
$dbStorage = Get-ClassicVAStorageFromContent $resp.Content
if ($dbStorage) {
if (-not $settings.ServerStorage) {
$settings.HasClassicVA = $true
$settings.ServerStorage = $dbStorage
}
$settings.RestoreData.Databases[$db] = @{ Uri = $dbUri; Body = $resp.Content }
Write-Log " '$db' Classic VA: $(Get-StorageKey $dbStorage)"
$isDbAdsEnabled = Get-AdsEnabled -AtpUri (& $atp.Db $db)
$effective = Get-EffectiveStorage -ServerStorage $settings.ServerStorage -IsServerVaDefined $isServerVaDefined `
-IsServerAdsEnabled $isServerAdsEnabled -DbStorage $dbStorage -IsDatabaseAdsEnabled $isDbAdsEnabled
if ($effective.Storage -and (Get-StorageKey $effective.Storage) -ne (Get-StorageKey $settings.ServerStorage)) {
$settings.DatabaseStorage[$db] = $effective.Storage
Write-LogDetail " '$db' effective: $(Get-StorageKey $effective.Storage) ($($effective.Source))"
}
}
}
}
}
"Synapse" {
$synapseApiVer = $ClassicApiVersions["Synapse"]
$uri = "/$($Resource.FullId)/vulnerabilityAssessments/default?api-version=$synapseApiVer"
$resp = Invoke-ArmRequest -Method GET -Path $uri
$isServerVaDefined = $false
if ($resp.StatusCode -eq 200) {
$storage = Get-ClassicVAStorageFromContent $resp.Content
if ($storage) {
$settings.HasClassicVA = $true
$settings.ServerStorage = $storage
$settings.RestoreData.ServerUri = $uri
$settings.RestoreData.ServerBody = $resp.Content
$isServerVaDefined = $true
Write-Log " Workspace-level Classic VA: $(Get-StorageKey $storage)"
}
}
foreach ($pool in $Databases) {
if ($pool -eq "master") { continue }
$poolUri = "/$($Resource.FullId)/sqlPools/$pool/vulnerabilityAssessments/default?api-version=$synapseApiVer"
$resp = Invoke-ArmRequest -Method GET -Path $poolUri
if ($resp.StatusCode -eq 200) {
$dbStorage = Get-ClassicVAStorageFromContent $resp.Content
if ($dbStorage) {
if (-not $settings.ServerStorage) {
$settings.HasClassicVA = $true
$settings.ServerStorage = $dbStorage
}
$settings.RestoreData.Databases[$pool] = @{ Uri = $poolUri; Body = $resp.Content }
Write-Log " SQL pool '$pool' Classic VA: $(Get-StorageKey $dbStorage)"
$isPoolAdsEnabled = Get-AdsEnabled -AtpUri (& $atp.Db $pool)
$effective = Get-EffectiveStorage -ServerStorage $settings.ServerStorage -IsServerVaDefined $isServerVaDefined `
-IsServerAdsEnabled $isServerAdsEnabled -DbStorage $dbStorage -IsDatabaseAdsEnabled $isPoolAdsEnabled
if ($effective.Storage -and (Get-StorageKey $effective.Storage) -ne (Get-StorageKey $settings.ServerStorage)) {
$settings.DatabaseStorage[$pool] = $effective.Storage
Write-LogDetail " '$pool' effective: $(Get-StorageKey $effective.Storage) ($($effective.Source))"
}
}
}
}
}
}
return $settings
}
function Get-DatabaseList {
param([hashtable]$Resource)
$databases = @()
$listApiVersion = $DatabaseListApiVersions[$Resource.ResourceType]
switch ($Resource.ResourceType) {
"SqlServer" {
$uri = "/$($Resource.FullId)/databases?api-version=$listApiVersion"
while ($uri) {
$resp = Invoke-ArmRequest -Method GET -Path $uri
if ($resp.StatusCode -eq 200) {
$content = $resp.Content | ConvertFrom-Json
foreach ($db in $content.value) {
if ($db.name -ne "master") { $databases += $db.name }
}
$uri = $content.nextLink
}
else { break }
}
}
"SqlManagedInstance" {
$uri = "/$($Resource.FullId)/databases?api-version=$listApiVersion"
while ($uri) {
$resp = Invoke-ArmRequest -Method GET -Path $uri
if ($resp.StatusCode -eq 200) {
$content = $resp.Content | ConvertFrom-Json
foreach ($db in $content.value) { $databases += $db.name }
$uri = $content.nextLink
}
else { break }
}
}
"Synapse" {
$uri = "/$($Resource.FullId)/sqlPools?api-version=$listApiVersion"
while ($uri) {
$resp = Invoke-ArmRequest -Method GET -Path $uri
if ($resp.StatusCode -eq 200) {
$content = $resp.Content | ConvertFrom-Json
foreach ($pool in $content.value) { $databases += $pool.name }
$uri = $content.nextLink
}
else { break }
}
}
}
return $databases
}
function Get-BaselineFromStorage {
param(
[string]$StorageAccountName,
[string]$ContainerName,
[string]$ResourceName,
[string]$DatabaseName
)
try {
$ctx = New-AzStorageContext -StorageAccountName $StorageAccountName
$prefix = "scans/$ResourceName/$DatabaseName/baseline"
$blobs = Get-AzStorageBlob -Container $ContainerName -Context $ctx -Prefix $prefix -ErrorAction Stop
}
catch {
Write-LogError "Cannot access storage '$StorageAccountName/$ContainerName'."
Write-LogDetail "Ensure you have Storage Blob Data Reader role on the storage account."
Write-LogDetail "$($_.Exception.Message)"
return $null
}
if ($blobs.Count -eq 0) { return $null }
$latestBlob = $blobs | Sort-Object LastModified -Descending | Select-Object -First 1
Write-LogDetail "Blob: $($latestBlob.Name) (modified: $($latestBlob.LastModified))"
try {
$tempFile = [System.IO.Path]::GetTempFileName()
$null = Get-AzStorageBlobContent -Blob $latestBlob.Name -Container $ContainerName -Context $ctx -Destination $tempFile -Force
$content = Get-Content -Path $tempFile -Raw
Remove-Item -Path $tempFile -Force -ErrorAction SilentlyContinue
return $content
}
catch {
Write-LogError "Failed to download baseline blob: $($_.Exception.Message)"
return $null
}
}
# Returns $null if storage is inaccessible - caller must abort
function Get-AllBaselines {
param(
[hashtable]$Resource,
[hashtable]$ClassicSettings,
[string[]]$AllDatabases
)
$baselines = @{}
if (-not $ClassicSettings.ServerStorage) {
Write-Log "No Classic VA storage found - no baselines to extract."
return $baselines
}
$serverStorage = $ClassicSettings.ServerStorage
# Pre-flight: verify we can access ALL distinct storage targets
$allStorageTargets = @{}
$allStorageTargets[(Get-StorageKey $serverStorage)] = $serverStorage
foreach ($entry in $ClassicSettings.DatabaseStorage.GetEnumerator()) {
$key = Get-StorageKey $entry.Value
if (-not $allStorageTargets.ContainsKey($key)) {
$allStorageTargets[$key] = $entry.Value
}
}
Write-Log "Verifying access to $($allStorageTargets.Count) storage target(s)..."
foreach ($target in $allStorageTargets.Values) {
$targetKey = "$(($target).StorageAccount)/$(($target).ContainerName)"
Write-Log " Checking '$targetKey'..."
try {
$ctx = New-AzStorageContext -StorageAccountName $target.StorageAccount
$null = Get-AzStorageBlob -Container $target.ContainerName -Context $ctx -MaxCount 1 -ErrorAction Stop
Write-LogSuccess " '$targetKey' is accessible."
}
catch {
Write-LogError "Cannot access storage '$targetKey'."
Write-LogDetail "$($_.Exception.Message)"
Write-Host ""
Write-Host " ┌────────────────────────────────────────────────────────┐" -ForegroundColor Red
Write-Host " │ STORAGE ACCESS FAILED - MIGRATION ABORTED │" -ForegroundColor Red
Write-Host " │ │" -ForegroundColor Red
Write-Host " │ Cannot read baselines from storage '$targetKey'." -ForegroundColor Red
Write-Host " │ Migration cannot proceed because baselines │" -ForegroundColor Red
Write-Host " │ would be permanently lost. │" -ForegroundColor Red
Write-Host " │ │" -ForegroundColor Red
Write-Host " │ Fix one of the following and re-run: │" -ForegroundColor Red
Write-Host " │ - Grant 'Storage Blob Data Reader' role on the │" -ForegroundColor Red
Write-Host " │ storage account to your current identity │" -ForegroundColor Red
Write-Host " │ - Allowlist your IP in the storage firewall │" -ForegroundColor Red
Write-Host " │ - Verify the storage account still exists │" -ForegroundColor Red
Write-Host " └────────────────────────────────────────────────────────┘" -ForegroundColor Red
return $null
}
}
foreach ($db in $AllDatabases) {
Write-Log " Extracting baseline for '$db'..."
$storage = if ($ClassicSettings.DatabaseStorage.ContainsKey($db)) {
$ClassicSettings.DatabaseStorage[$db]
}
else {
$serverStorage
}
$baseline = Get-BaselineFromStorage `
-StorageAccountName $storage.StorageAccount `
-ContainerName $storage.ContainerName `
-ResourceName $Resource.ResourceName `
-DatabaseName $db
# Fallback: if DB-specific storage yielded nothing, try server storage
if (-not $baseline -and $ClassicSettings.DatabaseStorage.ContainsKey($db)) {
Write-LogDetail "No baseline in DB-specific storage, trying server storage..."
$baseline = Get-BaselineFromStorage `
-StorageAccountName $serverStorage.StorageAccount `
-ContainerName $serverStorage.ContainerName `
-ResourceName $Resource.ResourceName `
-DatabaseName $db
}
if ($baseline) {
$parsed = $baseline | ConvertFrom-Json
$ruleCount = if ($parsed.RuleBaselines) { $parsed.RuleBaselines.Count } else { 0 }
$baselines[$db] = $baseline
Write-LogSuccess " Baseline found for '$db' ($ruleCount rule(s))."
}
else {
Write-LogDetail "No baseline found for '$db'."
}
}
return $baselines
}
function Remove-ClassicVASettings {
param(
[hashtable]$Resource,
[string[]]$Databases,
[hashtable]$RestoreData
)
Write-Log "Removing Classic VA settings..."
$classicApiVersion = $ClassicApiVersions[$Resource.ResourceType]
$errors = @()
switch ($Resource.ResourceType) {
"SqlServer" {
foreach ($db in $Databases) {
if ($db -eq "master") { continue }
Write-Log " Clearing VA for database '$db'..."
try {
$uri = "/$($Resource.FullId)/databases/$db/vulnerabilityAssessments/default?api-version=$classicApiVersion"
$resp = Invoke-ArmRequest -Method DELETE -Path $uri
if ($resp.StatusCode -notin @(200, 204, 404)) {
$errors += "Database '$db': HTTP $($resp.StatusCode) - $(Get-ErrorMessage $resp)"
}
}
catch { $errors += "Database '$db': $($_.Exception.Message)" }
}
if ($errors.Count -eq 0) {
Write-Log " Clearing VA for 'master'..."
try {
$uri = "/$($Resource.FullId)/databases/master/vulnerabilityAssessments/default?api-version=$classicApiVersion"
$resp = Invoke-ArmRequest -Method DELETE -Path $uri
if ($resp.StatusCode -notin @(200, 204, 404)) {
$errors += "master: HTTP $($resp.StatusCode) - $(Get-ErrorMessage $resp)"
}
}
catch { $errors += "master: $($_.Exception.Message)" }
}
if ($errors.Count -eq 0) {
Write-Log " Clearing server-level VA..."
try {
$uri = "/$($Resource.FullId)/vulnerabilityAssessments/default?api-version=$classicApiVersion"
$resp = Invoke-ArmRequest -Method DELETE -Path $uri
if ($resp.StatusCode -notin @(200, 204, 404)) {
$errors += "Server: HTTP $($resp.StatusCode) - $(Get-ErrorMessage $resp)"
}
}
catch { $errors += "Server: $($_.Exception.Message)" }
}
}
"SqlManagedInstance" {
foreach ($db in $Databases) {
if ($db -eq "master") { continue }
Write-Log " Clearing VA for database '$db'..."
try {
$uri = "/$($Resource.FullId)/databases/$db/vulnerabilityAssessments/default?api-version=$classicApiVersion"
$resp = Invoke-ArmRequest -Method DELETE -Path $uri
if ($resp.StatusCode -notin @(200, 204, 404)) {
$errors += "Database '$db': HTTP $($resp.StatusCode) - $(Get-ErrorMessage $resp)"
}
}
catch { $errors += "Database '$db': $($_.Exception.Message)" }
}
if ($errors.Count -eq 0) {
Write-Log " Clearing server-level VA..."
try {
$uri = "/$($Resource.FullId)/vulnerabilityAssessments/default?api-version=$classicApiVersion"
$resp = Invoke-ArmRequest -Method DELETE -Path $uri
if ($resp.StatusCode -notin @(200, 204, 404)) {
$errors += "Server: HTTP $($resp.StatusCode) - $(Get-ErrorMessage $resp)"
}
}
catch { $errors += "Server: $($_.Exception.Message)" }
}
}
"Synapse" {
$synapseApiVer = $ClassicApiVersions["Synapse"]
foreach ($pool in $Databases) {
if ($pool -eq "master") { continue }
Write-Log " Clearing VA for SQL pool '$pool'..."
try {
$uri = "/$($Resource.FullId)/sqlPools/$pool/vulnerabilityAssessments/default?api-version=$synapseApiVer"
$resp = Invoke-ArmRequest -Method DELETE -Path $uri
if ($resp.StatusCode -notin @(200, 204, 404)) {
$errors += "SQL pool '$pool': HTTP $($resp.StatusCode) - $(Get-ErrorMessage $resp)"
}
}
catch { $errors += "SQL pool '$pool': $($_.Exception.Message)" }
}
if ($errors.Count -eq 0) {
Write-Log " Clearing workspace-level VA..."
try {
$uri = "/$($Resource.FullId)/vulnerabilityAssessments/default?api-version=$synapseApiVer"
$resp = Invoke-ArmRequest -Method DELETE -Path $uri
if ($resp.StatusCode -notin @(200, 204, 404)) {
$errors += "Workspace: HTTP $($resp.StatusCode) - $(Get-ErrorMessage $resp)"
}
}
catch { $errors += "Workspace: $($_.Exception.Message)" }
}
}
}
if ($errors.Count -gt 0) {
Write-LogError "Failed to remove some Classic VA settings:"
foreach ($e in $errors) { Write-LogError " - $e" }
# Auto-rollback: restore already-deleted Classic policies
Write-LogWarn "Attempting to restore already-deleted Classic VA policies..."
Restore-ClassicVASettings -Resource $Resource -RestoreData $RestoreData | Out-Null
return $false
}
Write-LogSuccess "Classic VA settings removed."
return $true
}
#endregion
# ======================================================================
#region --- Restore Classic VA ---
# ======================================================================
function Restore-ClassicVASettings {
param(
[hashtable]$Resource,
[hashtable]$RestoreData
)
Write-Section "Restoring Classic VA Configuration"
$anyFailure = $false
# Step 1: Delete Express Configuration if it was enabled
Write-Log "Deleting Express Configuration (if active)..."
$uri = "/$($Resource.FullId)/providers/Microsoft.Security/sqlVulnerabilityAssessments/default?api-version=$ExpressApiVersion"
try {
$resp = Invoke-ArmRequest -Method DELETE -Path $uri
if ($resp.StatusCode -in @(200, 204, 404)) {
Write-LogSuccess "Express Configuration removed."
}
else {
Write-LogWarn "Express Configuration DELETE returned HTTP $($resp.StatusCode). Continuing with restore..."
}
}
catch {
Write-LogWarn "Could not delete Express Configuration: $($_.Exception.Message). Continuing..."
}
# Step 2: Restore server-level Classic VA policy
if ($RestoreData.ServerUri -and $RestoreData.ServerBody) {
Write-Log "Restoring server-level Classic VA policy..."
try {
$resp = Invoke-AzRestMethod -Method PUT -Path $RestoreData.ServerUri -Payload $RestoreData.ServerBody
if ($resp.StatusCode -in @(200, 201)) {
Write-LogSuccess "Server-level Classic VA restored."
}
else {
Write-LogError "Server-level restore failed (HTTP $($resp.StatusCode)): $(Get-ErrorMessage $resp)"
$anyFailure = $true
}
}
catch {
Write-LogError "Server-level restore error: $($_.Exception.Message)"
$anyFailure = $true
}
}
# Step 3: Restore database-level Classic VA policies
foreach ($entry in $RestoreData.Databases.GetEnumerator()) {
$dbName = $entry.Key
$dbRestore = $entry.Value
Write-Log "Restoring Classic VA for '$dbName'..."
try {
$resp = Invoke-AzRestMethod -Method PUT -Path $dbRestore.Uri -Payload $dbRestore.Body
if ($resp.StatusCode -in @(200, 201)) {
Write-LogSuccess "Classic VA restored for '$dbName'."
}
else {
Write-LogError "Restore failed for '$dbName' (HTTP $($resp.StatusCode)): $(Get-ErrorMessage $resp)"
$anyFailure = $true
}
}
catch {
Write-LogError "Restore error for '$dbName': $($_.Exception.Message)"
$anyFailure = $true
}
}
if ($anyFailure) {
Write-LogError "Some Classic VA settings could not be restored."
Write-Host ""
Write-Host " Manual restore instructions:" -ForegroundColor Yellow
Write-Host " https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration" -ForegroundColor Yellow
return $false
}
Write-LogSuccess "Classic VA configuration restored successfully."
return $true
}
#endregion
# ======================================================================
#region --- Express Configuration Functions (v2026-04-01-preview) ---
# ======================================================================
function Get-ExpressConfigStatus {
param([hashtable]$Resource)
$uri = "/$($Resource.FullId)/providers/Microsoft.Security/sqlVulnerabilityAssessments/default?api-version=$ExpressApiVersion"
$resp = Invoke-ArmRequest -Method GET -Path $uri
if ($resp.StatusCode -eq 200) {
$content = $resp.Content | ConvertFrom-Json
return $content.properties.state
}
return "Unknown"
}
function Enable-ExpressConfig {
param([hashtable]$Resource)
$uri = "/$($Resource.FullId)/providers/Microsoft.Security/sqlVulnerabilityAssessments/default?api-version=$ExpressApiVersion"
$body = @{ properties = @{ state = "Enabled" } }
$resp = Invoke-ArmRequest -Method PUT -Path $uri -Body $body
if ($resp.StatusCode -in @(200, 201)) {
return @{ Success = $true; Error = $null }
}
$errorMsg = Get-ErrorMessage $resp
Write-LogError "Failed to enable Express Configuration (HTTP $($resp.StatusCode)): $errorMsg"
return @{ Success = $false; Error = $errorMsg }
}
function Invoke-DatabaseScan {
param(
[hashtable]$Resource,
[string]$DatabaseName,
[int]$TimeoutSeconds,
[int]$PollingIntervalSeconds
)
$encodedDb = [System.Uri]::EscapeDataString($DatabaseName)
$initUri = "/$($Resource.FullId)/providers/Microsoft.Security/sqlVulnerabilityAssessments/default/scans/initiateScan?api-version=$ExpressApiVersion&databaseName=$encodedDb"
$resp = Invoke-ArmRequest -Method POST -Path $initUri
if ($resp.StatusCode -notin @(200, 202)) {
$errorMsg = Get-ErrorMessage $resp
Write-LogError "InitiateScan failed for '$DatabaseName' (HTTP $($resp.StatusCode)): $errorMsg"
return @{ Status = "InitiateFailed"; Error = $errorMsg }
}
if ($resp.StatusCode -eq 200) {
Write-Log " Scan for '$DatabaseName' completed synchronously."
$content = $resp.Content | ConvertFrom-Json
return @{ Status = $content.properties.state; Error = $null }
}
# 202 - extract operation ID from Location header
$operationId = $null
try {
$location = $null
# HttpResponseHeaders uses TryGetValues (not ContainsKey)
$locationValues = $null
if ($resp.Headers -and $resp.Headers.TryGetValues("Location", [ref]$locationValues)) {
$location = $locationValues | Select-Object -First 1
}
if ($location -and $location -match "scanOperationResults/([^?/&]+)") {
$operationId = $Matches[1]
}
}
catch {}
if (-not $operationId) {
try {
$content = $resp.Content | ConvertFrom-Json
$operationId = $content.properties.operationId
}
catch {}
}
if (-not $operationId) {
Write-LogError "Could not extract operation ID from InitiateScan response for '$DatabaseName'."
return @{ Status = "InitiateFailed"; Error = "No operation ID" }
}
Write-Log " Scan initiated for '$DatabaseName' (operation: $operationId). Polling..."
$sw = [System.Diagnostics.Stopwatch]::StartNew()
while ($sw.Elapsed.TotalSeconds -lt $TimeoutSeconds) {
Start-Sleep -Seconds $PollingIntervalSeconds
$pollUri = "/$($Resource.FullId)/providers/Microsoft.Security/sqlVulnerabilityAssessments/default/scans/scanOperationResults/${operationId}?api-version=$ExpressApiVersion&databaseName=$encodedDb"
$pollResp = Invoke-ArmRequest -Method GET -Path $pollUri
if ($pollResp.StatusCode -eq 200) {
$pollContent = $pollResp.Content | ConvertFrom-Json
$scanStatus = $pollContent.properties.scanStatus
Write-Log " '$DatabaseName' [$([int]$sw.Elapsed.TotalSeconds)s]: $scanStatus"
if ($scanStatus -in @("Passed", "Failed", "FailedToRun")) {
return @{ Status = $scanStatus; Error = $null }
}
}
}
Write-LogError "Scan timed out for '$DatabaseName' after ${TimeoutSeconds}s"
return @{ Status = "Timeout"; Error = "Exceeded ${TimeoutSeconds}s" }
}
# Converts 0/1 values to False/True in baseline results.
# Returns @{ WasConverted=$true/$false; Results=<converted array> }
function Convert-BinaryResults {
param([array]$Results)
$wasConverted = $false
$converted = @()
foreach ($row in $Results) {
$newRow = @($row)
for ($i = 0; $i -lt $newRow.Count; $i++) {
if ($newRow[$i] -eq "1") { $newRow[$i] = "True"; $wasConverted = $true }
elseif ($newRow[$i] -eq "0") { $newRow[$i] = "False"; $wasConverted = $true }
}
$converted += , $newRow
}
return @{ WasConverted = $wasConverted; Results = $converted }
}
# Returns: @{ Applied=N; Failed=N; FailedRules=@( @{ RuleId; Error; Results }, ... ) }
function Set-BaselineRulesForDatabase {
param(
[hashtable]$Resource,
[string]$DatabaseName,
[string]$BaselineJson
)
$baseline = $BaselineJson | ConvertFrom-Json
if (-not $baseline.RuleBaselines -or $baseline.RuleBaselines.Count -eq 0) {
Write-Log " No baseline rules to apply for '$DatabaseName'."
return @{ Applied = 0; Failed = 0; FailedRules = @() }
}
$encodedDb = [System.Uri]::EscapeDataString($DatabaseName)
$applied = 0
$failed = 0
$failedRules = @()
$total = $baseline.RuleBaselines.Count
$idx = 0
foreach ($rule in $baseline.RuleBaselines) {
$idx++
$ruleId = $rule.RuleId
$expectedResults = @($rule.Properties.ExpectedResults)
$body = @{
latestScan = $false
results = $expectedResults
}
$uri = "/$($Resource.FullId)/providers/Microsoft.Security/sqlVulnerabilityAssessments/default/baselineRules/${ruleId}?api-version=$ExpressApiVersion&databaseName=$encodedDb"
try {
$resp = Invoke-ArmRequest -Method PUT -Path $uri -Body $body
if ($resp.StatusCode -in @(200, 201)) {
$applied++
Write-LogDetail "[$idx/$total] Rule $ruleId - applied"
}
elseif ($resp.StatusCode -eq 400) {
# Try converting 0/1 → False/True (binary rules use different format in Express)
$converted = Convert-BinaryResults $expectedResults
if ($converted.WasConverted) {
Write-LogDetail "[$idx/$total] Rule $ruleId - retrying with True/False conversion..."
$body.results = $converted.Results
$retryResp = Invoke-ArmRequest -Method PUT -Path $uri -Body $body
if ($retryResp.StatusCode -in @(200, 201)) {
$applied++
Write-LogDetail "[$idx/$total] Rule $ruleId - applied (after binary conversion)"
}
else {
$errMsg = Get-ErrorMessage $retryResp
Write-LogError " [$idx/$total] Rule $ruleId - failed after conversion (HTTP $($retryResp.StatusCode)): $errMsg"
$failed++
$failedRules += @{ RuleId = $ruleId; Error = "HTTP $($retryResp.StatusCode): $errMsg"; Results = $converted.Results }
}
}
else {
$errMsg = Get-ErrorMessage $resp
Write-LogError " [$idx/$total] Rule $ruleId - failed (HTTP 400): $errMsg"
$failed++
$failedRules += @{ RuleId = $ruleId; Error = "HTTP 400: $errMsg"; Results = $expectedResults }
}
}
else {
$errMsg = Get-ErrorMessage $resp
Write-LogError " [$idx/$total] Rule $ruleId - failed (HTTP $($resp.StatusCode)): $errMsg"
$failed++
$failedRules += @{ RuleId = $ruleId; Error = "HTTP $($resp.StatusCode): $errMsg"; Results = $expectedResults }
}
}
catch {
Write-LogError " [$idx/$total] Rule $ruleId - error: $($_.Exception.Message)"
$failed++
$failedRules += @{ RuleId = $ruleId; Error = $_.Exception.Message; Results = $expectedResults }
}
}
return @{ Applied = $applied; Failed = $failed; FailedRules = $failedRules }
}
# Apply a single rule baseline by ID
function Set-SingleBaselineRule {
param(
[hashtable]$Resource,
[string]$DatabaseName,
[string]$RuleId,
[array]$Results
)
$encodedDb = [System.Uri]::EscapeDataString($DatabaseName)
$body = @{ latestScan = $false; results = $Results }
$uri = "/$($Resource.FullId)/providers/Microsoft.Security/sqlVulnerabilityAssessments/default/baselineRules/${RuleId}?api-version=$ExpressApiVersion&databaseName=$encodedDb"
$resp = Invoke-ArmRequest -Method PUT -Path $uri -Body $body
if ($resp.StatusCode -in @(200, 201)) { return $true }
# Try binary conversion on 400 (0/1 → False/True)
if ($resp.StatusCode -eq 400) {
$converted = Convert-BinaryResults $Results
if ($converted.WasConverted) {
Write-LogDetail " Rule $RuleId - retrying with True/False conversion..."
$body.results = $converted.Results
$retryResp = Invoke-ArmRequest -Method PUT -Path $uri -Body $body
if ($retryResp.StatusCode -in @(200, 201)) { return $true }
Write-LogError " Rule $RuleId failed after conversion (HTTP $($retryResp.StatusCode)): $(Get-ErrorMessage $retryResp)"
return $false
}
}
Write-LogError " Rule $RuleId failed (HTTP $($resp.StatusCode)): $(Get-ErrorMessage $resp)"
return $false
}
#endregion
# ======================================================================
#region --- Interactive Baseline Recovery ---
# ======================================================================
function Invoke-BaselineRecovery {
param(
[hashtable]$Resource,
[hashtable]$AllBaselineFailures, # dbName → @( @{ RuleId; Error; Results }, ... )
[hashtable]$ClassicRestoreData,
[bool]$HadClassicVA
)
$totalFailed = ($AllBaselineFailures.Values | ForEach-Object { $_.Count } | Measure-Object -Sum).Sum
Write-Section "Baseline Recovery"
Write-Host ""
Write-Host " $totalFailed baseline rule(s) failed across $($AllBaselineFailures.Count) database(s):" -ForegroundColor Yellow
foreach ($entry in $AllBaselineFailures.GetEnumerator()) {
$ruleIds = $entry.Value | ForEach-Object { $_.RuleId }
Write-Host " $($entry.Key): $($ruleIds -join ', ')" -ForegroundColor Yellow
}
Write-Host ""
Write-Host " How would you like to proceed?" -ForegroundColor White
Write-Host " [R] Retry all failed rules" -ForegroundColor White
Write-Host " [I] Interactive - review and retry each rule individually" -ForegroundColor White
if ($HadClassicVA) {
Write-Host " [V] Revert - undo migration and restore Classic Configuration" -ForegroundColor White
}
Write-Host " [S] Skip - keep Express Configuration, accept missing baselines" -ForegroundColor White
Write-Host ""
$validChoices = if ($HadClassicVA) { @("R", "I", "V", "S") } else { @("R", "I", "S") }
do {
$choice = (Read-Host " Enter choice ($($validChoices -join '/'))").Trim().ToUpper()
} while ($choice -notin $validChoices)
switch ($choice) {
"R" { return Invoke-RetryFailedBaselines -Resource $Resource -AllBaselineFailures $AllBaselineFailures }
"I" { return Invoke-InteractiveBaselineReview -Resource $Resource -AllBaselineFailures $AllBaselineFailures }
"V" {
$restored = Restore-ClassicVASettings -Resource $Resource -RestoreData $ClassicRestoreData
return @{ Action = "Reverted"; Restored = $restored }
}
"S" {
Write-Log "Skipping failed baselines. Express Configuration remains active."
return @{ Action = "Skipped" }
}
}
}
function Invoke-RetryFailedBaselines {
param(
[hashtable]$Resource,
[hashtable]$AllBaselineFailures
)
Write-SubSection "Retrying Failed Baseline Rules"
$retrySucceeded = 0
$retryFailed = 0
$stillFailed = @{}
foreach ($entry in $AllBaselineFailures.GetEnumerator()) {
$dbName = $entry.Key
$failedRules = $entry.Value
Write-Log "Retrying $($failedRules.Count) rule(s) for '$dbName'..."
$dbStillFailed = @()
foreach ($rule in $failedRules) {
$ok = Set-SingleBaselineRule -Resource $Resource -DatabaseName $dbName `
-RuleId $rule.RuleId -Results $rule.Results
if ($ok) {
Write-LogSuccess " Rule $($rule.RuleId) - applied"
$retrySucceeded++
}
else {
$retryFailed++
$dbStillFailed += $rule
}
}
if ($dbStillFailed.Count -gt 0) { $stillFailed[$dbName] = $dbStillFailed }
}
Write-Log "Retry complete: $retrySucceeded succeeded, $retryFailed still failing."
return @{ Action = "Retried"; Succeeded = $retrySucceeded; StillFailed = $stillFailed }
}
function Invoke-InteractiveBaselineReview {
param(
[hashtable]$Resource,
[hashtable]$AllBaselineFailures
)
Write-SubSection "Interactive Baseline Review"
$reviewed = 0
$applied = 0
$skipped = 0
$stillFailed = @{}
$quit = $false
foreach ($entry in $AllBaselineFailures.GetEnumerator()) {
if ($quit) { break }
$dbName = $entry.Key
$failedRules = $entry.Value
Write-Host ""
Write-Host " Database: $dbName ($($failedRules.Count) failed rule(s))" -ForegroundColor Cyan
$dbStillFailed = @()
foreach ($rule in $failedRules) {
if ($quit) {
$dbStillFailed += $rule
continue
}
$resultPreview = try {
($rule.Results | ForEach-Object { "[$($_ -join ', ')]" }) -join ", "
}
catch { "(could not format)" }
Write-Box @(
"Database : $dbName",
"Rule : $($rule.RuleId)",
"Error : $($rule.Error)",
"Results : $resultPreview"
)
Write-Host ""
$action = ""
do {
$action = (Read-Host " Retry this rule? (Y=retry / N=skip / Q=quit review)").Trim().ToUpper()
} while ($action -notin @("Y", "N", "Q"))
$reviewed++
switch ($action) {
"Y" {
$ok = Set-SingleBaselineRule -Resource $Resource -DatabaseName $dbName `
-RuleId $rule.RuleId -Results $rule.Results
if ($ok) {
Write-LogSuccess " Rule $($rule.RuleId) - applied"
$applied++
}
else {
$dbStillFailed += $rule
}
}
"N" {
Write-Log " Skipped rule $($rule.RuleId)."
$skipped++
$dbStillFailed += $rule
}
"Q" {
Write-Log " Quit interactive review."
$quit = $true
$dbStillFailed += $rule
}
}
}
if ($dbStillFailed.Count -gt 0) { $stillFailed[$dbName] = $dbStillFailed }
}
Write-Log "Interactive review: $reviewed reviewed, $applied applied, $skipped skipped."
return @{ Action = "Interactive"; Applied = $applied; Skipped = $skipped; StillFailed = $stillFailed }
}
#endregion
# ======================================================================
#region --- Summary ---
# ======================================================================
function Write-MigrationSummary {
param(
[hashtable]$Resource,
$PreBaselineScanResults,
$PostBaselineScanResults,
[hashtable]$BaselineResults, # dbName → @{ Applied; Failed; FailedRules }
[hashtable]$Baselines,
[string[]]$AllDatabases,
[string]$FinalState # "Completed", "Reverted", "CompletedWithErrors"
)
Write-Section "Migration Summary"
# Header
Write-Host ""
Write-Host " Resource : $($Resource.ResourceName)" -ForegroundColor White
Write-Host " Resource Type : $($Resource.ResourceType)" -ForegroundColor White
Write-Host " API Version : $ExpressApiVersion" -ForegroundColor White
$stateColor = switch ($FinalState) {
"Completed" { "Green" }
"Reverted" { "Yellow" }
"CompletedWithErrors" { "Red" }
default { "White" }
}
Write-Host " Final State : $FinalState" -ForegroundColor $stateColor
Write-Host ""
# Pre-baseline scan results table
if ($PreBaselineScanResults -and $PreBaselineScanResults.Count -gt 0) {
Write-Host " Pre-Baseline Scan Results:" -ForegroundColor White
Write-Host (" {0,-20} {1,-15}" -f "Database", "Status") -ForegroundColor DarkGray
Write-Host (" {0,-20} {1,-15}" -f ("─" * 20), ("─" * 15)) -ForegroundColor DarkGray
foreach ($entry in $PreBaselineScanResults.GetEnumerator()) {
$color = if ($entry.Value -in @("Passed", "Failed")) { "Green" } else { "Red" }
Write-Host (" {0,-20} {1,-15}" -f $entry.Key, $entry.Value) -ForegroundColor $color
}
Write-Host ""
}
# Baseline results table
if ($Baselines -and $Baselines.Count -gt 0) {
Write-Host " Baseline Migration:" -ForegroundColor White
Write-Host (" {0,-20} {1,-12} {2,-12}" -f "Database", "Applied", "Failed") -ForegroundColor DarkGray
Write-Host (" {0,-20} {1,-12} {2,-12}" -f ("─" * 20), ("─" * 12), ("─" * 12)) -ForegroundColor DarkGray
foreach ($db in $AllDatabases) {
if ($BaselineResults.ContainsKey($db)) {
$r = $BaselineResults[$db]
$fColor = if ($r.Failed -gt 0) { "Red" } else { "Green" }
Write-Host (" {0,-20} " -f $db) -NoNewline -ForegroundColor White
Write-Host ("{0,-12} " -f $r.Applied) -NoNewline -ForegroundColor Green
Write-Host ("{0,-12}" -f $r.Failed) -ForegroundColor $fColor
}
elseif (-not $Baselines.ContainsKey($db)) {
Write-Host (" {0,-20} {1,-12} {2,-12}" -f $db, "-", "-") -ForegroundColor Gray
}
}
Write-Host ""
}
# Post-baseline scan results table
if ($PostBaselineScanResults -and $PostBaselineScanResults.Count -gt 0) {
Write-Host " Post-Baseline Scan Results:" -ForegroundColor White
Write-Host (" {0,-20} {1,-15}" -f "Database", "Status") -ForegroundColor DarkGray
Write-Host (" {0,-20} {1,-15}" -f ("─" * 20), ("─" * 15)) -ForegroundColor DarkGray
foreach ($entry in $PostBaselineScanResults.GetEnumerator()) {
$color = if ($entry.Value -in @("Passed", "Failed")) { "Green" } else { "Red" }
Write-Host (" {0,-20} {1,-15}" -f $entry.Key, $entry.Value) -ForegroundColor $color
}
Write-Host ""
}
# Footer
switch ($FinalState) {
"Completed" {
Write-LogSuccess "Migration completed successfully!"
}
"CompletedWithErrors" {
Write-LogWarn "Migration completed with some baseline failures. See details above."
Write-Host " To revert: https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration" -ForegroundColor Yellow
}
"Reverted" {
Write-LogWarn "Migration was reverted. Classic VA configuration has been restored."
}
}
}
#endregion
# ======================================================================
# ======================================================================
# MAIN FLOW
# ======================================================================
# ======================================================================
Write-Host ""
Write-Host " SQL Vulnerability Assessment" -ForegroundColor Magenta
Write-Host " Migration to Express Configuration" -ForegroundColor Magenta
Write-Host " API Version: $ExpressApiVersion" -ForegroundColor Magenta
Write-Host ""
# --- Verify Azure auth ---
try {
$context = Get-AzContext
if (-not $context) { throw "No context" }
Write-Log "Azure context: $($context.Account.Id) (Subscription: $($context.Subscription.Name))"
}
catch {
Write-LogError "Not authenticated. Run Connect-AzAccount first."
return
}
# --- Parse resource ID ---
$resource = Parse-ServerResourceId $ServerResourceId
Write-Log "Resource Type : $($resource.ResourceType)"
Write-Log "Resource Name : $($resource.ResourceName)"
Write-Log "Subscription : $($resource.SubscriptionId)"
Write-Log "Resource Group: $($resource.ResourceGroup)"
# Ensure correct subscription context
$currentSubId = $context.Subscription.Id
if ($currentSubId -ne $resource.SubscriptionId) {
Write-Log "Switching subscription context to $($resource.SubscriptionId)..."
$null = Set-AzContext -SubscriptionId $resource.SubscriptionId
}
# ======================================================================
# Step 1: Check Express Configuration
# ======================================================================
Write-Section "Step 1: Check Express Configuration Status"
$expressState = Get-ExpressConfigStatus -Resource $resource
Write-Log "Express Configuration state: $expressState"
if ($expressState -eq "Enabled") {
Write-LogSuccess "Express Configuration is already enabled on this resource. No migration needed."
return
}
# ======================================================================
# Step 2: Discover Databases
# ======================================================================
Write-Section "Step 2: Discover Databases"
$databases = Get-DatabaseList -Resource $resource
# System databases per resource type
$systemDatabases = switch ($resource.ResourceType) {
"SqlServer" { @("master") }
"SqlManagedInstance" { @("master", "msdb", "model") }
"Synapse" { @("master") }
}
# Combine and de-duplicate (ARM listing may include system DBs)
$allDatabases = @($systemDatabases) + @($databases) | Select-Object -Unique
Write-Log "Found $($databases.Count) user database(s) + $($systemDatabases.Count) system database(s): $($allDatabases -join ', ')"
if ($databases.Count -eq 0) {
Write-LogWarn "No user databases found. Proceeding with system database(s) only."
}
# ======================================================================
# Step 3: Check Classic VA Configuration
# ======================================================================
Write-Section "Step 3: Check Classic VA Configuration"
$classicSettings = Get-ClassicVASettings -Resource $resource -Databases $allDatabases
$baselines = @{}
$hadClassicVA = $classicSettings.HasClassicVA
if ($hadClassicVA) {
Write-Log "Classic VA configuration detected."
if ($classicSettings.ServerStorage) {
Write-Log " Server storage : $($classicSettings.ServerStorage.StorageAccount)/$($classicSettings.ServerStorage.ContainerName)"
}
if ($classicSettings.DatabaseStorage.Count -gt 0) {
Write-Log " Storage overrides: $($classicSettings.DatabaseStorage.Keys -join ', ')"
}
# ==================================================================
# Step 4: Extract Baselines
# ==================================================================
Write-Section "Step 4: Extract Baselines from Classic Storage"
$baselines = Get-AllBaselines -Resource $resource -ClassicSettings $classicSettings -AllDatabases $allDatabases
if ($null -eq $baselines) {
Write-LogError "Migration aborted - cannot proceed without storage access."
return
}
$baselinesFound = ($baselines.Keys | Measure-Object).Count
$baselineDbNames = ($baselines.Keys | Sort-Object) -join ", "
Write-Log "Baselines extracted for $baselinesFound of $($allDatabases.Count) database(s): $baselineDbNames"
# ==================================================================
# Step 5: Confirm & Remove Classic VA
# ==================================================================
if (-not $Force) {
Write-Host ""
Write-Host " ┌────────────────────────────────────────────────────────┐" -ForegroundColor Yellow
Write-Host " │ WARNING │" -ForegroundColor Yellow
Write-Host " │ │" -ForegroundColor Yellow
Write-Host " │ This will remove the current Classic VA settings for │" -ForegroundColor Yellow
Write-Host " │ this resource and all databases. │" -ForegroundColor Yellow
Write-Host " │ │" -ForegroundColor Yellow
Write-Host " │ • Baselines have been extracted and will be │" -ForegroundColor Yellow
Write-Host " │ re-applied after enabling Express Configuration. │" -ForegroundColor Yellow
Write-Host " │ • Scan history will NOT be migrated. │" -ForegroundColor Yellow
Write-Host " │ • If enablement fails, the script will offer to │" -ForegroundColor Yellow
Write-Host " │ automatically restore Classic Configuration. │" -ForegroundColor Yellow
Write-Host " └────────────────────────────────────────────────────────┘" -ForegroundColor Yellow
Write-Host ""
$confirmation = Read-Host " Do you want to proceed? (y/n)"
if ($confirmation -ne "y") {
Write-Log "Migration cancelled by user."
return
}
}
Write-Section "Step 5: Remove Classic VA Settings"
$removeSuccess = Remove-ClassicVASettings -Resource $resource -Databases $allDatabases -RestoreData $classicSettings.RestoreData
if (-not $removeSuccess) {
Write-LogError "Failed to fully remove Classic VA settings. Migration aborted."
Write-Host ""
Write-Host " Express Configuration cannot be enabled while Classic VA policies remain." -ForegroundColor Yellow
Write-Host " Fix the errors above and re-run this script." -ForegroundColor Yellow
return
}
}
else {
Write-Log "No Classic VA configuration found. Proceeding directly to enable Express Configuration."
}
# ======================================================================
# Step 6: Enable Express Configuration
# ======================================================================
$stepNum = if ($hadClassicVA) { 6 } else { 4 }
Write-Section "Step $stepNum`: Enable Express Configuration"
$enableResult = Enable-ExpressConfig -Resource $resource
if (-not $enableResult.Success) {
Write-LogError "Failed to enable Express Configuration."
# Detect identity-related errors (SQL MI specific)
$isIdentityError = $false
if ($resource.ResourceType -eq "SqlManagedInstance" -and $enableResult.Error) {
$errLower = $enableResult.Error.ToLower()
if ($errLower -match "identity" -or $errLower -match "managed.?identity" -or
$errLower -match "systemassigned" -or $errLower -match "authentication" -or
$errLower -match "principal") {
$isIdentityError = $true
}
}
if ($isIdentityError) {
Write-Host ""
Write-Host " ┌────────────────────────────────────────────────────────┐" -ForegroundColor Red
Write-Host " │ SQL MANAGED INSTANCE - IDENTITY ERROR │" -ForegroundColor Red
Write-Host " │ │" -ForegroundColor Red
Write-Host " │ Express Configuration requires a System-Assigned │" -ForegroundColor Red
Write-Host " │ Managed Identity (SAMI) on the Managed Instance. │" -ForegroundColor Red
Write-Host " │ │" -ForegroundColor Red
Write-Host " │ To fix: │" -ForegroundColor Red
Write-Host " │ 1. Go to Azure Portal > your MI > Identity │" -ForegroundColor Red
Write-Host " │ 2. Enable System-Assigned Managed Identity │" -ForegroundColor Red
Write-Host " │ 3. Re-run this script │" -ForegroundColor Red
Write-Host " │ │" -ForegroundColor Red
Write-Host " │ Or via CLI: │" -ForegroundColor Red
Write-Host " │ az sql mi update --name <mi> --resource-group <rg> \ │" -ForegroundColor Red
Write-Host " │ --assign-identity │" -ForegroundColor Red
Write-Host " │ │" -ForegroundColor Red
Write-Host " │ NOTE: If both UAMI and SAMI are enabled, enablement │" -ForegroundColor Red
Write-Host " │ may also fail. Try temporarily removing the UAMI. │" -ForegroundColor Red
Write-Host " └────────────────────────────────────────────────────────┘" -ForegroundColor Red
}
# Offer to restore Classic VA if we had one (server-level or database-level)
$hasRestoreData = $classicSettings.RestoreData.ServerBody -or ($classicSettings.RestoreData.Databases.Count -gt 0)
if ($hadClassicVA -and $hasRestoreData) {
if (-not $isIdentityError) {
Write-Host ""
Write-Host " Express Configuration could not be enabled after removing Classic VA." -ForegroundColor Red
Write-Host " Common causes:" -ForegroundColor Yellow
Write-Host " - Classic VA policy removal is still propagating (wait a few minutes)" -ForegroundColor Yellow
if ($resource.ResourceType -eq "SqlManagedInstance") {
Write-Host " - System-Assigned Managed Identity (SAMI) is not enabled on the MI" -ForegroundColor Yellow
}
}
Write-Host ""
$restoreChoice = "R"
if (-not $Force) {
Write-Host " Would you like to restore Classic Configuration?" -ForegroundColor White
Write-Host " [R] Restore Classic Configuration now (recommended)" -ForegroundColor White
if (-not $isIdentityError) {
# Wait & Retry only makes sense for propagation delays, not identity issues
Write-Host " [W] Wait 60 seconds and retry enablement" -ForegroundColor White
}
Write-Host " [Q] Quit (leaves resource without VA - manual action needed)" -ForegroundColor White
Write-Host ""
$validChoices = if ($isIdentityError) { @("R", "Q") } else { @("R", "W", "Q") }
do {
$restoreChoice = (Read-Host " Enter choice ($($validChoices -join '/'))").Trim().ToUpper()
} while ($restoreChoice -notin $validChoices)
}
switch ($restoreChoice) {
"R" {
$restored = Restore-ClassicVASettings -Resource $resource -RestoreData $classicSettings.RestoreData
if ($restored) {
Write-MigrationSummary -Resource $resource -PreBaselineScanResults $null `
-PostBaselineScanResults $null -BaselineResults @{} -Baselines @{} `
-AllDatabases $allDatabases -FinalState "Reverted"
}
else {
Write-LogError "Classic VA restore encountered errors. Check messages above."
Write-Host " Manual restore: https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration" -ForegroundColor Yellow
}
return
}
"W" {
Write-Log "Waiting 60 seconds for propagation..."
Start-Sleep -Seconds 60
$retryResult = Enable-ExpressConfig -Resource $resource
if (-not $retryResult.Success) {
Write-LogError "Retry failed. Restoring Classic Configuration..."
$restored = Restore-ClassicVASettings -Resource $resource -RestoreData $classicSettings.RestoreData
if (-not $restored) {
Write-LogError "Classic VA restore encountered errors. Check messages above."
Write-Host " Manual restore: https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration" -ForegroundColor Yellow
}
Write-MigrationSummary -Resource $resource -PreBaselineScanResults $null `
-PostBaselineScanResults $null -BaselineResults @{} -Baselines @{} `
-AllDatabases $allDatabases -FinalState "Reverted"
return
}
Write-LogSuccess "Express Configuration enabled on retry."
}
"Q" {
Write-LogError "Migration aborted. Resource may be without VA configuration."
Write-Host " Manual restore: https://learn.microsoft.com/azure/defender-for-cloud/troubleshoot-vulnerability-findings#revert-back-to-the-classic-configuration" -ForegroundColor Yellow
return
}
}
}
else {
if (-not $isIdentityError) {
Write-Host ""
Write-Host " Common causes:" -ForegroundColor Yellow
if ($resource.ResourceType -eq "SqlManagedInstance") {
Write-Host " - System-Assigned Managed Identity (SAMI) is not enabled on the MI" -ForegroundColor Yellow
}
Write-Host " - Classic VA policy on a child resource was not fully removed" -ForegroundColor Yellow
}
Write-Host ""
Write-Host " Fix the issue above and re-run this script." -ForegroundColor Yellow
return
}
}
else {
Write-LogSuccess "Express Configuration enabled successfully."
}
# ======================================================================
# Step 7: Pre-Baseline Scan
# ======================================================================
$stepNum++
Write-Section "Step $stepNum`: Pre-Baseline Scan"
$preBaselineScanResults = [ordered]@{}
$i = 0
foreach ($db in $allDatabases) {
$i++
$pct = [int](($i / $allDatabases.Count) * 100)
Write-Progress -Activity "Pre-baseline scan" -Status "$db ($i/$($allDatabases.Count))" -PercentComplete $pct
Write-Log "Scanning '$db'..."
try {
$result = Invoke-WithRetry -Description "scan '$db'" -Action {
Invoke-DatabaseScan -Resource $resource -DatabaseName $db `
-TimeoutSeconds $ScanTimeoutSeconds -PollingIntervalSeconds $ScanPollingIntervalSeconds
}
$preBaselineScanResults[$db] = $result.Status
}
catch {
$preBaselineScanResults[$db] = "Error"
Write-LogError "Scan error for '$db': $($_.Exception.Message)"
}
$statusColor = if ($preBaselineScanResults[$db] -in @("Passed", "Failed")) { "Green" } else { "Red" }
Write-Host (" {0}: {1}" -f $db, $preBaselineScanResults[$db]) -ForegroundColor $statusColor
}
Write-Progress -Activity "Pre-baseline scan" -Completed
# Report scan issues
$scanFailures = $preBaselineScanResults.GetEnumerator() | Where-Object { $_.Value -in @("FailedToRun", "InitiateFailed", "Timeout", "Error") }
if ($scanFailures) {
Write-Host ""
Write-LogWarn "Some scans could not complete:"
foreach ($f in $scanFailures) { Write-LogWarn " $($f.Key): $($f.Value)" }
Write-Host " Baselines will still be applied where possible." -ForegroundColor Yellow
}
# ======================================================================
# Step 8: Apply Baselines
# ======================================================================
$stepNum++
$baselineResults = @{} # dbName → @{ Applied; Failed; FailedRules }
$allBaselineFailures = @{} # dbName → @( @{ RuleId; Error; Results }, ... )
if ($baselines.Count -gt 0) {
Write-Section "Step $stepNum`: Apply Migrated Baselines"
$i = 0
foreach ($db in $allDatabases) {
$i++
$pct = [int](($i / $allDatabases.Count) * 100)
Write-Progress -Activity "Applying baselines" -Status "$db ($i/$($allDatabases.Count))" -PercentComplete $pct
if ($baselines.ContainsKey($db) -and -not [string]::IsNullOrEmpty($baselines[$db])) {
Write-Log "Applying baseline for '$db'..."
try {
$result = Invoke-WithRetry -Description "baseline '$db'" -MaxAttempts 1 -Action {
Set-BaselineRulesForDatabase -Resource $resource -DatabaseName $db -BaselineJson $baselines[$db]
}
$baselineResults[$db] = $result
Write-Log " '$db': $($result.Applied) applied, $($result.Failed) failed"
if ($result.FailedRules.Count -gt 0) {
$allBaselineFailures[$db] = $result.FailedRules
}
}
catch {
Write-LogError "Baseline error for '$db': $($_.Exception.Message)"
$baselineResults[$db] = @{ Applied = 0; Failed = -1; FailedRules = @() }
$allBaselineFailures[$db] = @( @{ RuleId = "(all)"; Error = $_.Exception.Message; Results = @() } )
}
}
}
Write-Progress -Activity "Applying baselines" -Completed
# ===== Baseline failure recovery =====
if ($allBaselineFailures.Count -gt 0 -and -not $Force) {
$recovery = Invoke-BaselineRecovery `
-Resource $resource `
-AllBaselineFailures $allBaselineFailures `
-ClassicRestoreData $classicSettings.RestoreData `
-HadClassicVA $hadClassicVA
if ($recovery.Action -eq "Reverted") {
Write-MigrationSummary -Resource $resource -PreBaselineScanResults $preBaselineScanResults `
-PostBaselineScanResults $null -BaselineResults $baselineResults -Baselines $baselines `
-AllDatabases $allDatabases -FinalState "Reverted"
return
}
# Update baseline results with recovery outcomes
if ($recovery.StillFailed) {
foreach ($entry in $recovery.StillFailed.GetEnumerator()) {
$db = $entry.Key
if ($baselineResults.ContainsKey($db)) {
$orig = $baselineResults[$db]
$recovered = $allBaselineFailures[$db].Count - $entry.Value.Count
$baselineResults[$db] = @{
Applied = $orig.Applied + $recovered
Failed = $entry.Value.Count
FailedRules = $entry.Value
}
}
}
# Also clear databases that were fully recovered
foreach ($db in @($allBaselineFailures.Keys)) {
if (-not $recovery.StillFailed.ContainsKey($db)) {
if ($baselineResults.ContainsKey($db)) {
$orig = $baselineResults[$db]
$baselineResults[$db] = @{
Applied = $orig.Applied + $allBaselineFailures[$db].Count
Failed = 0
FailedRules = @()
}
}
}
}
$allBaselineFailures = $recovery.StillFailed
}
elseif ($recovery.Action -in @("Retried", "Interactive")) {
# All were resolved
foreach ($db in @($allBaselineFailures.Keys)) {
if ($baselineResults.ContainsKey($db)) {
$orig = $baselineResults[$db]
$baselineResults[$db] = @{
Applied = $orig.Applied + $allBaselineFailures[$db].Count
Failed = 0
FailedRules = @()
}
}
}
$allBaselineFailures = @{}
}
}
}
else {
Write-Log "No baselines to migrate."
}
# ======================================================================
# Step 9: Post-Baseline Scan
# ======================================================================
$stepNum++
Write-Section "Step $stepNum`: Post-Baseline Scan"
$postBaselineScanResults = [ordered]@{}
$i = 0
foreach ($db in $allDatabases) {
$i++
$pct = [int](($i / $allDatabases.Count) * 100)
Write-Progress -Activity "Post-baseline scan" -Status "$db ($i/$($allDatabases.Count))" -PercentComplete $pct
Write-Log "Scanning '$db'..."
try {
$result = Invoke-WithRetry -Description "post-baseline scan '$db'" -Action {
Invoke-DatabaseScan -Resource $resource -DatabaseName $db `
-TimeoutSeconds $ScanTimeoutSeconds -PollingIntervalSeconds $ScanPollingIntervalSeconds
}
$postBaselineScanResults[$db] = $result.Status
}
catch {
$postBaselineScanResults[$db] = "Error"
Write-LogError "Scan error for '$db': $($_.Exception.Message)"
}
$statusColor = if ($postBaselineScanResults[$db] -in @("Passed", "Failed")) { "Green" } else { "Red" }
Write-Host (" {0}: {1}" -f $db, $postBaselineScanResults[$db]) -ForegroundColor $statusColor
}
Write-Progress -Activity "Post-baseline scan" -Completed
# ======================================================================
# Final Summary
# ======================================================================
$finalState = if ($allBaselineFailures.Count -gt 0) { "CompletedWithErrors" } else { "Completed" }
Write-MigrationSummary -Resource $resource -PreBaselineScanResults $preBaselineScanResults `
-PostBaselineScanResults $postBaselineScanResults -BaselineResults $baselineResults -Baselines $baselines `
-AllDatabases $allDatabases -FinalState $finalState