Remarque
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de vous connecter ou de modifier des répertoires.
L’accès à cette page nécessite une autorisation. Vous pouvez essayer de modifier des répertoires.
Cet article fournit des scripts PowerShell pour activer l’évaluation des vulnérabilités SQL (VA) avec Express Configuration dans Microsoft Defender for Cloud. L’évaluation des vulnérabilités SQL vous permet d’identifier et de corriger les vulnérabilités de sécurité dans vos bases de données. Express Configuration simplifie le processus d’installation à l’aide d’un stockage géré par Microsoft au lieu de vous obliger à configurer un compte de stockage géré par le client. Les deux versions de script prennent en charge la migration automatisée à partir de la configuration classique, de l’extraction de base de référence et de la réapplication et de l’analyse de base de données pour refléter la nouvelle configuration.
Types de versions
Deux versions de script sont disponibles en fonction de votre type de ressource :
-
Version de l’APIUnified : utilise l’API REST unifiée (
2026-04-01-preview) et prend en charge Azure SQL Database, Azure SQL Managed Instance et Azure Synapse Analytics. Cette version est l’approche recommandée pour les nouveaux déploiements et prend en charge les trois types de ressources dans un seul script. -
Azure SQL Database version uniquement : prend en charge Azure SQL Database uniquement, à l’aide du module Azure PowerShell (
Az.Sql). Documenté pour assurer la rétrocompatibilité.
Les deux scripts migrent de la configuration classique (stockage géré par le client) vers Express Configuration (stockage géré par Microsoft), y compris la migration de référence. L’historique des analyses n’est pas migré dans l’une ou l’autre version.
API unifiée pour Azure SQL Database, Azure SQL Managed Instance et Azure Synapse Analytics
Aperçu
Ce script automatise la migration de Classic Configuration (stockage géré par le client pour les bases de référence d’évaluation des vulnérabilités et les résultats d’analyse) vers Express Configuration (stockage géré par le Microsoft) pour l’évaluation des vulnérabilités SQL dans Microsoft Defender for Cloud. Avec Express Configuration, vous n’avez pas besoin d’un compte de stockage. Microsoft stocke les bases de référence et les résultats d’analyse.
Types de ressources pris en charge
| Type de ressource | Fournisseur Azure Resource Manager |
|---|---|
| Azure SQL Database | Microsoft.Sql/servers |
| Instance managée SQL | Microsoft.Sql/managedInstances |
| Azure Synapse Analytics | Microsoft.Synapse/workspaces |
Ce que fait le script
- Lit vos lignes de base actuelles de la configuration classique à partir du stockage Blob
- Supprime Configuration classique
-
Permet la configuration Express via l’API unifiée (
2026-04-01-preview) - Analyser toutes les bases de données (analyse préalable à l’établissement de la référence)
- Applique à nouveau vos bases de référence à Express Configuration
- Analyse à nouveau toutes les bases de données (analyse post-ligne de base) pour refléter l’état de base de référence mis à jour
Note
L’historique des analyses n’est pas migré. Les résultats de l’analyse passée restent dans votre compte de stockage d’origine.
- Si l’activation échoue, le script propose automatiquement de restaurer vos paramètres de configuration classique.
- Si la migration de base échoue partiellement, vous pouvez choisir comment procéder : réessayer, examiner chaque élément individuellement, annuler ou ignorer.
- L’exécution du script sur une ressource qui dispose déjà d’Express Configuration est sécurisée. Il s’arrête sans aucune modification.
Prerequisites
PowerShell
| Requirement | Version minimale |
|---|---|
| PowerShell | 7.0 |
| Module Az.Accounts | 2.9.1 |
| Module Az.Storage | 4.8.0 |
Installez ou mettez à jour les modules :
Install-Module Az.Accounts -MinimumVersion 2.9.1 -Scope CurrentUser
Install-Module Az.Storage -MinimumVersion 4.8.0 -Scope CurrentUser
Authentification d'Azure
Connectez-vous avant d’exécuter le script :
Connect-AzAccount
Set-AzContext -SubscriptionId "<your-subscription-id>"
Autorisations
L’identité exécutant le script a besoin des autorisations suivantes :
| Autorisation | Scope | Pourquoi |
|---|---|---|
| Microsoft. Sécurité/* (ou Contributeur) | Serveur/MI/Espace de travail | Activer Express Configuration et gérer les bases de référence |
| Microsoft. Sql/* ou Microsoft. Synapse/* | Serveur/MI/Espace de travail | Lire/supprimer les paramètres de configuration classique, répertorier les bases de données, lancer des analyses |
| Lecteur de données des Blobs de stockage | Comptes de stockage utilisés par la configuration classique | Lire les lignes de base existantes depuis le stockage Blob |
Important
SQL Managed Instance : votre SQL Managed Instance doit avoir un System-Assigned Managed Identity (SAMI) activé. Si SAMI est désactivé, l’activation d’Express Configuration échoue. Activez SAMI dans le portail Azure, dans votre MI → Identité → Attribuée par le système → On.
Important
Pare-feu du stockage : si votre compte de stockage de configuration classique a des règles de pare-feu, autorisez la machine exécutant le script à y accéder. Vous pouvez autoriser la liste de votre adresse IP ou utiliser un point de terminaison privé.
Parameters
| Paramètre | Obligatoire | Default | Description |
|---|---|---|---|
-ServerResourceId |
Oui | - | ID de ressource Azure Resource Manager complet du serveur, de l’instance managée ou de l’espace de travail Synapse |
-Force |
Non | $false |
Ignorer toutes les invites de confirmation (utiles pour l’automatisation) |
-ScanTimeoutSeconds |
Non | 300 |
Nombre maximal de secondes d’attente par analyse de base de données |
-ScanPollingIntervalSeconds |
Non | 10 |
Nombre de secondes entre les interrogations de l’état d’analyse |
Comment trouver votre ID de ressource
Dans le portail Azure, accédez à votre ressource → Properties → Resource ID ou utilisez :
# 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
Exemples d’utilisation
Serveur SQL Database
.\MigrateToExpressConfiguration.ps1 `
-ServerResourceId "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Sql/servers/<server-name>"
Instance managée SQL
.\MigrateToExpressConfiguration.ps1 `
-ServerResourceId "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Sql/managedInstances/<mi-name>"
Azure Synapse Analytics
.\MigrateToExpressConfiguration.ps1 `
-ServerResourceId "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Synapse/workspaces/<workspace-name>"
Non interactif (automatisation /CI)
.\MigrateToExpressConfiguration.ps1 `
-ServerResourceId "<resource-id>" `
-Force
Avec -Force, le script :
- Ignore l’invite de confirmation avant de supprimer la configuration classique
- Ignore automatiquement les règles de base ayant échoué (aucun menu de récupération interactif)
Exemple de script - 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