locked
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

    docs.microsoft.com/en-us/sql/relational-databases/security/sql-vulnerability-assessment?view=sql-server-ver15

    - 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.

    docs.microsoft.com/en-us/archive/blogs/sqlsecurity/powershell-cmdlets-for-managing-sql-vulnerability-assessments

     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:

    {"Version":"1.0","RuleBaselines":[{"RuleId":"VA2110","Properties":{"Severity":null,"ExpectedResults":[["xp_instance_regread","public"],["xp_regread","public"]],"UpdatedTime":"2020-10-01T10:28:59.5565389Z","SizeInBytes":82}}]}

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

    Thursday, October 1, 2020 10:49 AM