이 문서에서는 클라우드용 Microsoft Defender Express Configuration을 사용하여 VA(SQL 취약성 평가)를 사용하도록 설정하기 위한 PowerShell 스크립트를 제공합니다. SQL 취약성 평가는 데이터베이스의 보안 취약성을 식별하고 수정하는 데 도움이 됩니다. Express Configuration은 고객 관리 스토리지 계정을 구성하도록 요구하는 대신 Microsoft 관리형 스토리지를 사용하여 설정 프로세스를 간소화합니다. 두 스크립트 버전 모두 클래식 구성에서 자동화된 마이그레이션, 기준 추출 및 다시 적용, 새 구성을 반영하기 위한 데이터베이스 검색을 지원합니다.
버전 유형
리소스 종류에 따라 두 가지 스크립트 버전을 사용할 수 있습니다.
-
Preview 버전: 통합 REST API(
2026-04-01-preview)를 사용하고 Azure SQL Database, Azure SQL Managed Instance(미리 보기) 및 Azure Synapse Analytics(미리 보기)를 지원합니다. 이 버전은 새 배포에 권장되는 접근 방식이며 단일 스크립트에서 세 가지 리소스 유형을 모두 지원합니다. -
제공적으로 사용 가능한 버전: Azure PowerShell(
Az.Sql) 모듈을 사용하여 Azure SQL Database만 지원합니다.
두 스크립트는 기본 마이그레이션을 포함하여 클래식 구성(고객 관리 스토리지)에서 Express Configuration(Microsoft 관리 스토리지)으로 마이그레이션됩니다. 검사 기록은 두 버전에서 마이그레이션되지 않습니다.
Azure SQL Managed Instance 및 Azure Synapse Analytics 작업 영역에 대한 공개 미리 보기(통합 API)
개요
이 스크립트는 클라우드용 Microsoft Defender SQL 취약성 평가를 위해 Classic Configuration(취약성 평가 기준 및 검사 결과에 대한 고객 관리 스토리지)에서 Express Configuration(Microsoft 관리 스토리지)으로 마이그레이션을 자동화합니다. Express Configuration을 사용하면 스토리지 계정이 필요하지 않습니다. Microsoft 기준선을 저장하고 결과를 검사합니다.
지원되는 리소스 유형
| 리소스 유형 | Azure Resource Manager 공급자 |
|---|---|
| Azure SQL 데이터베이스 | Microsoft.Sql/servers |
| SQL 관리형 인스턴스 | Microsoft.Sql/managedInstances |
| Azure Synapse Analytics | Microsoft.Synapse/workspaces |
스크립트가 수행하는 작업
- Blob Storage에서 현재 클래식 구성 기준을 읽습니다.
- 클래식 구성 제거
-
통합 API(
2026-04-01-preview)를 통해 Express Configuration을 사용할 수 있습니다 - 모든 데이터베이스를 검사합니다 (베이스라인 이전 검사)
- 기본 기준을 Express 구성에 다시 적용합니다.
- 업데이트된 기준 상태를 반영하기 위해 모든 데이터베이스를 다시 검사합니다(사후 기준 검사).
비고
검사 기록이 마이그레이션되지 않습니다. 이전 검사 결과는 원래 스토리지 계정에 남아 있습니다.
- 사용이 실패하면 스크립트는 클래식 구성 설정을 복원하도록 자동으로 제공합니다 .
- 기준 마이그레이션이 부분적으로 실패하는 경우 다시 시도, 개별적으로 검토, 되돌리기 또는 건너뛰는 방법을 선택합니다.
- Express Configuration이 이미 있는 리소스에서 스크립트를 실행하는 것은 안전합니다. 변경 없이 종료됩니다.
필수 조건
PowerShell
| 요구 사항 | 최소 버전 |
|---|---|
| PowerShell | 7.0 |
| Az.Accounts 모듈 | 2.9.1 |
| Az.Storage 모듈 | 4.8.0 |
모듈을 설치하거나 업데이트합니다.
Install-Module Az.Accounts -MinimumVersion 2.9.1 -Scope CurrentUser
Install-Module Az.Storage -MinimumVersion 4.8.0 -Scope CurrentUser
Azure 인증
스크립트를 실행하기 전에 로그인합니다.
Connect-AzAccount
Set-AzContext -SubscriptionId "<your-subscription-id>"
사용 권한
스크립트를 실행하는 ID에는 다음 권한이 필요합니다.
| 허가 | Scope | 이유 |
|---|---|---|
| Microsoft.Security/* (또는 기여자) | 서버/MI/작업 영역 | Express 구성 사용 및 기준선 관리 |
| Microsoft. Sql/* 또는 Microsoft. Synapse/* | 서버/MI/작업 영역 | 클래식 구성 설정 읽기/삭제, 데이터베이스 나열, 검사 시작 |
| Storage Blob 데이터 판독기 | 클래식 구성에서 사용하는 스토리지 계정 | Blob Storage에서 기존 기준 읽기 |
Important
SQL Managed Instance: 해당 SQL Managed Instance에는 시스템 할당 관리 ID(SAMI)가 사용하도록 설정되어 있어야 합니다. SAMI를 사용하지 않도록 설정하면 Express 구성 사용이 실패합니다. MI → ID → 시스템 할당 → On의 Azure 포털에서 SAMI를 사용하도록 설정합니다.
Important
스토리지 방화벽: 클래식 구성 스토리지 계정에 방화벽 규칙이 있는 경우 스크립트의 컴퓨터가 방화벽에 도달하도록 허용합니다. IP 목록을 허용하거나 프라이빗 엔드포인트를 사용할 수 있습니다.
Parameters
| 매개 변수 | 필수 | 기본값 | Description |
|---|---|---|---|
-ServerResourceId |
예 | - | 서버, 관리되는 인스턴스 또는 Synapse 작업 영역의 전체 Azure Resource Manager 리소스 ID |
-Force |
No | $false |
모든 확인 프롬프트 건너뛰기(자동화에 유용) |
-ScanTimeoutSeconds |
No | 300 |
데이터베이스 검사당 대기할 최대 시간(초) |
-ScanPollingIntervalSeconds |
No | 10 |
스캔 상태 폴링 간격(초) |
리소스 ID를 찾는 방법
Azure 포털에서 리소스 → Properties → Resource ID로 이동하거나 다음을 사용합니다.
# SQL Database server
(Get-AzSqlServer -ResourceGroupName "myRG" -ServerName "myServer").ResourceId
# SQL Managed Instance
(Get-AzSqlInstance -ResourceGroupName "myRG" -InstanceName "myMI").Id
# Synapse workspace
(Get-AzSynapseWorkspace -ResourceGroupName "myRG" -Name "myWorkspace").Id
사용 예제
SQL Database 서버
.\MigrateToExpressConfiguration.ps1 `
-ServerResourceId "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Sql/servers/<server-name>"
SQL 관리형 인스턴스
.\MigrateToExpressConfiguration.ps1 `
-ServerResourceId "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Sql/managedInstances/<mi-name>"
Azure Synapse Analytics
.\MigrateToExpressConfiguration.ps1 `
-ServerResourceId "/subscriptions/<sub-id>/resourceGroups/<rg>/providers/Microsoft.Synapse/workspaces/<workspace-name>"
비대화형(자동화/CI)
.\MigrateToExpressConfiguration.ps1 `
-ServerResourceId "<resource-id>" `
-Force
-Force와 함께, 스크립트:
- 클래식 구성을 제거하기 전에 확인 프롬프트를 건너뜁니다.
- 실패한 기준 규칙(대화형 복구 메뉴 없음)을 자동으로 건너뜁니다.
샘플 스크립트 - MigrateToExpressConfiguration.ps1
#Requires -Modules @{ ModuleName="Az.Accounts"; ModuleVersion="2.9.1" }
#Requires -Modules @{ ModuleName="Az.Storage"; ModuleVersion="4.8.0" }
#Requires -Version 7.0
<#
.SYNOPSIS
Migrates Azure SQL resources from Classic VA configuration to Express Configuration
using the unified v2026-04-01-preview API.
.DESCRIPTION
Supports: Azure SQL Database, SQL Managed Instance, and Azure Synapse Analytics.
The script:
1. Detects the resource type from the ARM resource ID
2. Checks if Express Configuration is already enabled
3. Extracts existing baselines from Classic Configuration storage (if any)
4. Removes Classic VA settings (blocking - aborts and restores on failure)
5. Enables Express Configuration via the new unified API
- On failure: automatically offers to restore Classic Configuration
6. Discovers and scans all databases
7. Applies migrated baselines
- On failure: offers interactive recovery (retry, per-rule review, or revert)
8. Reports a detailed migration summary
Scan history is NOT migrated - it remains in the original storage account.
To revert manually, see:
https://dotnet.territoriali.olinfo.it/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://dotnet.territoriali.olinfo.it/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://dotnet.territoriali.olinfo.it/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://dotnet.territoriali.olinfo.it/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://dotnet.territoriali.olinfo.it/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://dotnet.territoriali.olinfo.it/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