Powershell or AzureCLI command to enable Microsoft Defender for SQL

Question

Because I am not allowed to enable Microsoft Defender for SQL from Azure Portal, I am tring to find a Powershell or AzureCLI command to enable it from CloudShell. The final step is to use that command in a pipeline. I am expecting to be a command that get as parameters the name of the server and the group and automatically enable the Microsoft Defender for SQL.

I tried this:

az sql server threat-policy update --resource-group <my-resource-group> --server-name <my-sql-server-name> --state Enabled

The answer was:

'threat-policy' is misspelled or not recognized by the system.

EDIT After more investigation I have found a partial solution. I will explain bellow:

1. In the first state the status is disabled: "Enabled status: Disabled"
2. After I run the following command:

Update-AzSqlServerAdvancedThreatProtectionSetting -Enable $true -ResourceGroupName 'my-resource-group' -ServerName 'my-server-name'

the status was changed to "Enabled status: Enabled at the subscription-level" and bellow of the status an warning is shown with an "enable" button saying that "SQL Vulnerability Assessment is not configured. Click to enable express configuration".

3. I would like to have also the "Vulnerability Assesment" enabled in order to have an overview of the findings. I thought this is part of Microsoft Defender for SQL, and enabled it will enable also the "Voulnerability assesment". Are these different?

Answer 1

I finally found a solution. I will describe it below:

1. Activate MICROSOFT DEFENDER FOR SQL using the following command:

Update-AzSqlServerAdvancedThreatProtectionSetting -Enable $true -ResourceGroupName '<your-resource-group-name>' -ServerName '<your-sql-server-name>'

2. Enable VULNERABILITY ASSESSMENT using the following script

$SubscriptionId = '<your-sub-id>'
$ResourceGroupName = '<your-resoruce-group-name>'
$ServerName = '<your-server-name>'

param
(
    [Parameter(Mandatory = $True)]
    [string]$SubscriptionId,
    [Parameter(Mandatory = $True)]
    [string]$ResourceGroupName,
    [Parameter(Mandatory = $True)]
    [string]$ServerName,
    [Parameter(Mandatory = $False)]
    [switch]$Force
)

function SetSqlVulnerabilityAssessmentServerSetting($SubscriptionId, $ResourceGroupName, $ServerName) {
    $Uri = "/subscriptions/$SubscriptionId/resourceGroups/$ResourceGroupName/providers/Microsoft.Sql/servers/$ServerName/sqlVulnerabilityAssessments/default?api-version=2022-02-01-preview"
    
    $Body = @{
        properties = @{
            state = "Enabled"
        }
    }

    $Body = $Body | ConvertTo-Json

    return SendRestRequest -Method "Put" -Uri $Uri -Body $Body
}

function SendRestRequest(
    [Parameter(Mandatory = $True)]
    [string] $Method,
    [Parameter(Mandatory = $True)]
    [string] $Uri,
    [parameter( Mandatory = $false )]
    [string] $Body = "DEFAULT") {
    $Params = @{
        Method       = $Method
        Path         = $Uri
    }

    if (!($Body -eq "DEFAULT")) {
        $Params = @{
            Method       = $Method
            Path         = $Uri
            Payload      = $Body
        }
    }

    Invoke-AzRestMethod @Params
}

SetSqlVulnerabilityAssessmentServerSetting -SubscriptionId $SubscriptionId -ResourceGroupName $ResourceGroupName -ServerName $ServerName