Programatically add VA baselineto Azure SQL DB

Matias 21 Reputation points
2022-10-26T13:26:15.97+00:00

Calling Get-AzSecuritySqlVulnerabilityAssessmentScanResult returns an error, can someone provide a working example?

The documentation points to a script example that is using the old PS module
https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-vulnerability-assessment?view=azuresql&tabs=azure-powershell#manage-vulnerability-assessments-programmatically

Background
We are using Microsoft Defender for Cloud which weekly executes vulnerability assessments on our Azure SQL databases. On each database a few checks are failing and when investigated we see that everything is in order and that we must add the result as a baseline.

We can do this per database in the Azure Portal but since we have hundreds of databases and new ones are added all the time we wish to do this programmatically.

Command and error output

Get-AzSecuritySqlVulnerabilityAssessmentScanResult `  
   -ResourceId /subscriptions/{our-subscription-giod}/resourceGroups/{our-resorce-grp}/providers/Microsoft.Sql/servers/{our-sql-server-name}/databases/{our-sql-db-name} `  
   -WorkspaceId '{our-workspace-id}' `  
   -Server '{our-sql-server-name}.database.windows.net' `  
   -Database '{our-sql-db-name}';  
  
Get-AzSecuritySqlVulnerabilityAssessmentScanResult : Container '{our-subscription-guid}' was not found  
At line:2 char:1  
+ Get-AzSecuritySqlVulnerabilityAssessmentScanResult `  
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~  
    + CategoryInfo          : CloseError: (:) [Get-AzSecurityS...smentScanResult], CloudException  
    + FullyQualifiedErrorId : Microsoft.Azure.Commands.SecurityCenter.Cmdlets.SqlVulnerabilityAssessment.GetSecuritySqlVulnerabilityAssessmentScanResult
Azure SQL Database
Microsoft Defender for Cloud
Microsoft Defender for Cloud
An Azure service that provides threat protection for workloads running in Azure, on-premises, and in other clouds. Previously known as Azure Security Center and Azure Defender.
1,402 questions
{count} votes

2 answers

Sort by: Most helpful
  1. GeethaThatipatri-MSFT 29,477 Reputation points Microsoft Employee
    2022-10-31T13:29:38.297+00:00

    Hi, @Matias Thanks for providing additional information
    This example takes the results of a specific rule and sets them as a baseline:
    Set-AzSqlDatabaseVulnerabilityAssessmentRuleBaseline (Az.Sql) | Microsoft Learn

    If these rules do have any security value, maybe it would be better to set the baseline to a hardcoded set of results.
    Otherwise, consider just disabling the rules.

    Please let me know if you are looking for additional information.

    Regards
    Geetha

    Please don't forget to click on 130616-image.png or upvote 130671-image.png button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer.


  2. GeethaThatipatri-MSFT 29,477 Reputation points Microsoft Employee
    2022-11-20T14:35:12.583+00:00

    @Matias Sorry for the delay in my response yes you’re correct.
    The following steps should enable you to copy the definitions correctly:

    Get baselines defs

    $ruleBaselineObject = Get-AzSqlDatabaseVulnerabilityAssessmentRuleBaseline -ResourceGroupName $SourceResourceGroupName
    -ServerName $SourceServerName -DatabaseName $SourceDatabaseName
    -RuleId $ruleId

    Reformat baseline definition

    $ruleBaselineFormated.Add($ruleBaselineObject.BaselineResult.ToArray())

    Set baseline on target DB.

    Get-AzSqlDatabase -ResourceGroupName $TargetResourceGroupName -ServerName $TargetServerName
    -DatabaseName $TargetDatabaseName | Set-AzSqlDatabaseVulnerabilityAssessmentRuleBaseline
    -RuleId $ruleId `
    -BaselineResult $ruleBaselineFormated

    We’ll update the documentation to reflect this.

    Thanks for pointing out this issue!

    Regards,
    Geetha


Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.