Skip to content

EnableDefenderForSqlOnMachines.ps1 failed to assign roles to Managed ID on subscription level. #966

@mattu0119

Description

@mattu0119

Describe the bug
I was trying to enable Defender for SQL servers on machine with below PowerShell script, but this script failed with following error.

Assigning policy initiative definition with parameters...
Setting role assignment to policy assignment's Managed Identity...
Failed to assign role A Using variable cannot be retrieved. A Using variable can be used only with Invoke-Command, Start-Job, or InlineScript in the script workflow. When it is used with Invoke-Command, the Using variable is valid only if the script block is invoked on a remote computer. to policy assignment's Managed Identity on subscription level.
Failed to enable SQL ATP on-premises in scale. Exception: A Using variable cannot be retrieved. A Using variable can be used only with Invoke-Command, Start-Job, or InlineScript in the script workflow. When it is used with Invoke-Command, the Using variable is valid only if the script block is invoked on a remote computer.

To Reproduce
Steps to reproduce the behavior:

  1. Go to 'https://github.com/Azure/Microsoft-Defender-for-Cloud/blob/main/Powershell%20scripts/Enable%20Defender%20for%20SQL%20servers%20on%20machines/EnableDefenderForSqlOnMachines.ps1', and download the script on main branch.
  2. create Azure resources such as log analytics workspaces, DCR and UserAssignedManaged ID, then collect these resource ID.
  3. Run the script with following parameters that is on the document below.
    https://github.com/Azure/Microsoft-Defender-for-Cloud/blob/main/Powershell%20scripts/Enable%20Defender%20for%20SQL%20servers%20on%20machines/readme.md#examples
Write-Host "------ Enable Defender for SQL on Machines example ------" 
$SubscriptionId = "5320f111-d736-4793-acfb-64451ae625de" 
$RegisterSqlVmAgnet = "false" 
$WorkspaceResourceId = "/subscriptions/5320f111-d736-4793-acfb-64451ae625de/resourceGroups/someResourceGroup/providers/Microsoft.OperationalInsights/workspaces/someWorkspace" 
$DataCollectionRuleResourceId = "/subscriptions/5320f111-d736-4793-acfb-64451ae625de/resourceGroups/someOtherResourceGroup/providers/Microsoft.Insights/dataCollectionRules/someDcr" 
$UserAssignedIdentityResourceId = "/subscriptions/5320f111-d736-4793-acfb-64451ae625de/resourceGroups/someElseResourceGroup/providers/Microsoft.ManagedIdentity/userAssignedIdentities/someManagedIdentity" 
.\EnableDefenderForSqlOnMachines.ps1 -SubscriptionId $SubscriptionId -RegisterSqlVmAgnet $RegisterSqlVmAgnet -WorkspaceResourceId $WorkspaceResourceId -DataCollectionRuleResourceId $DataCollectionRuleResourceId -UserAssignedIdentityResourceId $UserAssignedIdentityResourceId 
  1. See error

Expected behavior
Roles will be assigned to UserAssignedManagedID, and Defender for SQL servers on machine plan will be enabled.

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions