Hinweis
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, sich anzumelden oder das Verzeichnis zu wechseln.
Für den Zugriff auf diese Seite ist eine Autorisierung erforderlich. Sie können versuchen, das Verzeichnis zu wechseln.
Dieser Artikel enthält PowerShell-Skripts zum Aktivieren der SQL-Sicherheitsrisikobewertung (VA) mit Express-Konfiguration in Microsoft Defender for Cloud. Die SQL-Sicherheitsrisikobewertung hilft Ihnen, Sicherheitsrisiken in Ihren Datenbanken zu identifizieren und zu beheben. Die Expresskonfiguration vereinfacht den Einrichtungsprozess, indem Microsoft verwalteter Speicher verwendet wird, anstatt dass Sie ein vom Kunden verwaltetes Speicherkonto konfigurieren müssen. Beide Skriptversionen unterstützen die automatisierte Migration von der klassischen Konfiguration, der Basisplanextraktion und der Erneuterstellung sowie der Datenbanküberprüfung, um die neue Konfiguration widerzuspiegeln.
Versionstypen
Je nach Ressourcentyp sind zwei Skriptversionen verfügbar:
-
Unified API version: Verwendet die einheitliche REST-API (
2026-04-01-preview) und unterstützt Azure SQL-Datenbank, Azure SQL Managed Instance und Azure Synapse Analytics. Diese Version ist der empfohlene Ansatz für neue Bereitstellungen und unterstützt alle drei Ressourcentypen in einem einzigen Skript. -
Version nur für Azure SQL-Datenbank: Unterstützt nur Azure SQL-Datenbank unter Verwendung des Azure PowerShell-Moduls (
Az.Sql). Dokumentiert für Abwärtskompatibilität.
Beide Skripte migrieren von der klassischen Konfiguration (kundenseitig verwalteter Speicher) zur Express-Konfiguration (von Microsoft verwalteter Speicher), einschließlich der Baseline-Migration. Der Scanverlauf wird in beiden Versionen nicht migriert.
Einheitliche API für Azure SQL-Datenbank, Azure SQL Managed Instance und Azure Synapse Analytics
Übersicht
Dieses Skript automatisiert die Migration von Classic Configuration (vom Kunden verwalteter Speicher für Baselines und Überprüfungsergebnisse) zu Express-Konfiguration (Microsoft-verwalteter Speicher) für die SQL-Sicherheitsrisikobewertung in Microsoft Defender for Cloud. Bei der Expresskonfiguration benötigen Sie kein Speicherkonto. Microsoft speichert Basispläne und Scanergebnisse.
Unterstützte Ressourcentypen
| Ressourcentyp | Azure Resource Manager Anbieter |
|---|---|
| Azure SQL-Datenbank | Microsoft.Sql/servers |
| Verwaltete SQL-Instanz | Microsoft.Sql/managedInstances |
| Azure Synapse Analytics | Microsoft.Synapse/workspaces |
Funktionsweise des Skripts
- Liest Ihre aktuellen Classic Configuration-Baselines aus Blobspeicher
- Entfernt Klassische Konfiguration
-
Ermöglicht Expresskonfiguration über die einheitliche API (
2026-04-01-preview) - Überprüft alle Datenbanken (Prebaseline-Überprüfung)
- Erneutes Anwenden Ihrer Basispläne auf die Expresskonfiguration
- Scannt alle Datenbanken erneut (Scan nach der Baseline), um den aktualisierten Baseline-Status widerzuspiegeln.
Note
Der Scanverlauf wird nicht migriert. Die bisherigen Scanergebnisse verbleiben in Ihrem ursprünglichen Speicherkonto.
- Wenn die Aktivierung fehlschlägt, bietet das Skript automatisch die Wiederherstellung der klassischen Konfigurationseinstellungen an.
- Wenn die Baseline-Migration teilweise fehlschlägt, können Sie wählen, wie Sie fortfahren möchten: erneut versuchen, einzeln prüfen, rückgängig machen oder überspringen.
- Das Ausführen des Skripts für eine Ressource, die bereits über Expresskonfiguration verfügt, ist sicher. Sie wird ohne Änderungen beendet.
Voraussetzungen
PowerShell
| Anforderung | Mindestversion |
|---|---|
| PowerShell | 7.0 |
| Az.Accounts-Modul | 2.9.1 |
| Az.Storage-Modul | 4.8.0 |
Installieren oder Aktualisieren der Module:
Install-Module Az.Accounts -MinimumVersion 2.9.1 -Scope CurrentUser
Install-Module Az.Storage -MinimumVersion 4.8.0 -Scope CurrentUser
Azure-Authentifizierung
Melden Sie sich an, bevor Sie das Skript ausführen:
Connect-AzAccount
Set-AzContext -SubscriptionId "<your-subscription-id>"
Erlaubnisse
Die Identität, die das Skript ausführt, benötigt die folgenden Berechtigungen:
| Erlaubnis | Scope | Warum? |
|---|---|---|
| Microsoft.Security/* (oder Mitwirkender) | Server/MI/Arbeitsbereich | Aktivieren der Expresskonfiguration und Verwalten von Basisplanen |
| Microsoft. Sql/* oder Microsoft. Synapse/* | Server/MI/Arbeitsbereich | Einstellungen der klassischen Konfiguration lesen/löschen, Datenbanken auflisten, Scans starten |
| Leser von Speicherblobdaten | Von der klassischen Konfiguration verwendete Speicherkonten | Lesen vorhandener Basispläne aus Blob Storage |
Von Bedeutung
SQL Managed Instance: Ihre SQL Managed Instance muss über eine aktivierte systemseitig zugewiesene verwaltete Identität (SAMI) verfügen. Wenn SAMI deaktiviert ist, schlägt die Expresskonfigurationsaktivierung fehl. Aktivieren Sie SAMI im Azure-Portal unter Ihrem MI → Identity → System Assigned → On.
Von Bedeutung
Firewall für das Speicherkonto: Wenn für Ihr Speicherkonto für die klassische Konfiguration Firewallregeln konfiguriert sind, lassen Sie zu, dass der Computer, auf dem das Skript ausgeführt wird, darauf zugreifen kann. Sie können Ihre IP-Adresse auflisten oder einen privaten Endpunkt verwenden.
Parameters
| Parameter | Erforderlich | Default | Description |
|---|---|---|---|
-ServerResourceId |
Ja | - | Vollständige Azure Resource Manager Ressourcen-ID des Servers, der verwalteten Instanz oder des Synapse-Arbeitsbereichs |
-Force |
No | $false |
Alle Bestätigungsaufforderungen überspringen (nützlich für die Automatisierung) |
-ScanTimeoutSeconds |
No | 300 |
Maximale Wartezeit pro Datenbankscan |
-ScanPollingIntervalSeconds |
No | 10 |
Sekunden zwischen Scan-Statusabfragen |
So finden Sie Ihre Ressourcen-ID
Wechseln Sie im Azure-Portal zu Ihrer Ressource → Properties → Ressourcen-ID oder verwenden Sie Folgendes:
# 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
Verwendungsbeispiele
SQL-Datenbankserver
.\MigrateToExpressConfiguration.ps1 `
-ServerResourceId "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Sql/servers/<server-name>"
Verwaltete SQL-Instanz
.\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>"
Nicht interaktiv (Automatisierung / CI)
.\MigrateToExpressConfiguration.ps1 `
-ServerResourceId "<resource-id>" `
-Force
Mit -Force, das Skript:
- Überspringt die Bestätigungsaufforderung, bevor die klassische Konfiguration entfernt wird.
- Überspringt automatisch alle fehlgeschlagenen Basisplanregeln (kein interaktives Wiederherstellungsmenü)
Beispielskript – 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