Powershell automatisation for Vulnerability Assessment Report on multiple SQL servers RRS feed

  • Question

  • I have about SQL 280+ servers where i need to run SQL Vulnerability Assessment report


    - as result I should have JSON report files for each server and each DB on it which can be imported back into SSMS.

    So I would like to automate this activity somehow as this is huge amount of hand work.

    SSMS itself doesnt provide possibility to script this report in TSQL, so I was looking to powershell scripting.


     but it didnt work for me.

    I dont know how to make Export-SqlVulnerabilityAssessmentBaselineSet cmdlet to gather full report as JSON file which i could import into SSMS (and which actually SSMS can generate manually).

    By code provided in MS article

     $scanResult = Invoke-SqlVulnerabilityAssessmentScan -ServerInstance "MyComputer\MainInstance" -Database some_database
      $failedResults = @{}
     $scanResult.Results.GetEnumerator() | Where {$_.Value.Status -eq "Failed"} | foreach {$failedResults[$_.Key] = $_.Value }
     $failedCheckResults = $failedResults["VA2110"].QueryResults
     $baseline = New-SqlVulnerabilityAssessmentBaseline -SecurityCheckId "VA2110" -ExpectedResult $failedCheckResults
     $baselineSet = New-SqlVulnerabilityAssessmentBaselineSet -Baselines $baseline
     $baselineSet | Export-SqlVulnerabilityAssessmentBaselineSet -FolderPath ".\baseline.json"

    only generated JSON file with one single baseline with output and it cannot be read by SSMS. 

    JSON file:


    Can someone please advice how to automate this process? Any solution would be helpful. Many thanks

    Thursday, October 1, 2020 10:49 AM