<# .SYNOPSIS SecurityInsight Pro -- prepare a SQL database as the vulnerability close-loop's target (#195). .DESCRIPTION Applies sql\si-vulnerability-target.sql (schema `vuln`: VulnerabilityItem + VulnerabilityEvent). Additive and idempotent: re-running changes nothing. Connects as the SI engine identity with its CERTIFICATE (Entra token), the same way the vulnerability sync does -- so a successful run here also proves the sync can reach the database. The identity needs, in that database: CREATE SCHEMA / CREATE TABLE once (here), then db_datareader + db_datawriter for the sync. On Azure SQL with Entra-only authentication, create it as a contained user: CREATE USER [] FROM EXTERNAL PROVIDER; ALTER ROLE db_datareader ADD MEMBER []; ALTER ROLE db_datawriter ADD MEMBER []; FROM EXTERNAL PROVIDER needs the SQL server to have a directory identity. Without one, name the identity by its client id instead -- the same form serves a container job's managed identity (Pro container hosting): CREATE USER [] WITH SID = 0x, TYPE = E; Container setup (Bootstrap-ContainerAppJob.ps1) prints these statements for the job's identity, ready to run. .EXAMPLE .\setup\Initialize-SIVulnTargetSql.ps1 -Server .database.windows.net -Database sqldb-si-vulntarget .LINK https://invardia.com/docs/si/scripts/Initialize-SIVulnTargetSql/ .NOTES Requirements: PowerShell 7 (pwsh). Windows PowerShell 5.1 is refused at once (#Requires -Version 7.0). Role: the SI engine identity (service principal + certificate) with db_ddladmin -- on the target database Hosts: database.windows.net, invardia.com, login.microsoftonline.com #> [CmdletBinding(SupportsShouldProcess)] param( [string]$Server = [string]$global:SI_VulnSync_SqlServer, [string]$Database = [string]$global:SI_VulnSync_SqlDatabase, [string]$AppId = [string]$global:SI_SPN_AppId, [string]$TenantId = [string]$global:SI_SPN_TenantId, [string]$CertThumbprint = [string]$global:SI_SPN_CertThumbprint ) #Requires -Version 7.0 # SecurityInsight 3.0.79 -- Initialize-SIVulnTargetSql.ps1, standalone (Community edition). Built 2026-11-09T20:28:39Z from commit c1472e2f4c240c971c9559f07eef7e794b322001, source SOLUTIONS/SecurityInsight/setup/Initialize-SIVulnTargetSql.ps1, # by release/Build-SISupportScripts.ps1 (framework SUPPORT-DL-1). Download: Invoke-WebRequest https://invardia.com/support/si/Initialize-SIVulnTargetSql.ps1 -OutFile Initialize-SIVulnTargetSql.ps1 # It carries the 14 SecurityInsight file(s) the script loads (listed in $__siSupportFiles below, readable, each line # prefixed with "|"); at run time it writes them to a private temporary folder, checks each SHA-256, runs the original # script from there with your parameters, and removes the folder. No PowerShell modules are needed. $script:SIScriptVersion = '3.0.79' # SI #547: the version this copy reports first (stamped into its embedded setup/_shared/SI-ScriptRun.ps1 too) $__siSupportVersion = '3.0.79' $__siSupportEntry = 'setup/Initialize-SIVulnTargetSql.ps1' $__siSupportFiles = [ordered]@{} $__siSupportFiles['setup/Initialize-SIVulnTargetSql.ps1'] = @{ Sha256 = 'fec7116c8bf0b4a8f8dc4ffe6dcfeea30cc7368c505d64f57a7895700886b7c5'; Text = @' |<# |.SYNOPSIS | SecurityInsight Pro -- prepare a SQL database as the vulnerability close-loop's target (#195). |.DESCRIPTION | Applies sql\si-vulnerability-target.sql (schema `vuln`: VulnerabilityItem + VulnerabilityEvent). Additive and | idempotent: re-running changes nothing. Connects as the SI engine identity with its CERTIFICATE (Entra token), | the same way the vulnerability sync does -- so a successful run here also proves the sync can reach the database. | | The identity needs, in that database: CREATE SCHEMA / CREATE TABLE once (here), then db_datareader + | db_datawriter for the sync. On Azure SQL with Entra-only authentication, create it as a contained user: | CREATE USER [] FROM EXTERNAL PROVIDER; | ALTER ROLE db_datareader ADD MEMBER []; | ALTER ROLE db_datawriter ADD MEMBER []; | FROM EXTERNAL PROVIDER needs the SQL server to have a directory identity. Without one, name the identity by its | client id instead -- the same form serves a container job's managed identity (Pro container hosting): | CREATE USER [] WITH SID = 0x, TYPE = E; | Container setup (Bootstrap-ContainerAppJob.ps1) prints these statements for the job's identity, ready to run. |.EXAMPLE | .\setup\Initialize-SIVulnTargetSql.ps1 -Server .database.windows.net -Database sqldb-si-vulntarget | |.LINK | https://invardia.com/docs/si/scripts/Initialize-SIVulnTargetSql/ |#> |[CmdletBinding(SupportsShouldProcess)] |param( | [string]$Server = [string]$global:SI_VulnSync_SqlServer, | [string]$Database = [string]$global:SI_VulnSync_SqlDatabase, | [string]$AppId = [string]$global:SI_SPN_AppId, | [string]$TenantId = [string]$global:SI_SPN_TenantId, | [string]$CertThumbprint = [string]$global:SI_SPN_CertThumbprint |) | |#Requires -Version 7.0 # after the help and the param block: before the help, it hides the help from Get-Help |# framework section 12.7 / 12.10: the documentation link first, a transcript of the run, -WhatIf writes nothing |. ([IO.Path]::Combine($PSScriptRoot, '_shared', 'SI-ScriptRun.ps1')) |$siRun = Start-SIScriptRun -Script 'Initialize-SIVulnTargetSql' -Quiet:($false) |try { |$ErrorActionPreference = 'Stop' |$siRoot = Split-Path -Parent $PSScriptRoot |if (-not $Server -or -not $Database) { throw 'Initialize-SIVulnTargetSql: pass -Server and -Database (or set SI_VulnSync_SqlServer / SI_VulnSync_SqlDatabase).' } |if (-not $AppId -or -not $TenantId -or -not $CertThumbprint) { throw 'Initialize-SIVulnTargetSql: the engine identity (SI_SPN_AppId / SI_SPN_TenantId / SI_SPN_CertThumbprint) is not set.' } |. (Join-Path $siRoot 'engine\asset-cmdb\_shared\SI-VulnTarget.ps1') | |$schema = Get-Content -LiteralPath (Join-Path $siRoot 'sql\si-vulnerability-target.sql') -Raw |if ($WhatIfPreference) { | # -WhatIf: the database is not opened -- applying the schema is the script's only change | Write-Host ("-WhatIf: would apply sql\si-vulnerability-target.sql (schema vuln, additive) to {0}/{1}. Nothing was written." -f $Server, $Database) -ForegroundColor Yellow | return |} |$tgt = New-SIVulnTargetSql -Server $Server -Database $Database -AppId $AppId -TenantId $TenantId -CertThumbprint $CertThumbprint |Write-Host ("[STEP] applying the vulnerability target schema to {0}/{1}" -f $Server, $Database) -ForegroundColor Cyan |# #352: the ONE apply every path uses (setup, the internal build, the product updater) -- the additive gate, one transaction |. (Join-Path $siRoot 'engine\pro-settings\SI-ProSchema.ps1') |$applied = Invoke-SIProSchemaApply -SIRoot $siRoot -Invoke $tgt.Invoke -Names vuln -Log { param($m) Write-Host $m } |if ($applied.Action -ne 'applied') { throw ("Initialize-SIVulnTargetSql: {0}" -f $applied.Reason) } |$n = & $tgt.Invoke "SELECT COUNT(*) FROM sys.tables WHERE schema_id = SCHEMA_ID('vuln') AND name IN ('VulnerabilityItem','VulnerabilityEvent')" @{} 'scalar' |if ([int]$n -ne 2) { throw ("Initialize-SIVulnTargetSql: expected 2 tables in schema vuln, found {0}." -f $n) } |$rows = & $tgt.Invoke 'SELECT COUNT(*) FROM vuln.VulnerabilityItem' @{} 'scalar' |Write-Host ("[OK] schema ready -- vuln.VulnerabilityItem holds {0} record(s); the sync can connect." -f $rows) -ForegroundColor Green |} finally { Stop-SIScriptRun -Run $siRun } '@ } $__siSupportFiles['setup/_shared/SI-ScriptRun.ps1'] = @{ Sha256 = 'a5c4d788d516521639611af43e1b88458216ce58bdb455885cac8e2a568afbd2'; Text = @' |#Requires -Version 5.1 |<# | EVERY SCRIPT A PERSON RUNS (framework DOCS/REQUIREMENTS.md section 12.7 SCRIPT-DOC-1 + section 12.10 SECURITY-PACK-1; SI #481 / #484). | One helper, called first by each such script: | | $siRun = Start-SIScriptRun -Script '