Search code examples
azureazure-sql-databaseazure-managed-identityazure-runbook

Attempting to create SQL Server resource using managed identity in a runbook gives forbidden result

I'm trying to create an Azure SQL Server in a PowerShell runbook. I am using a managed identity to which I have assigned contributor role to the subscription I'm trying to create it in.

This is the code line:

New-AzSqlServer -ResourceGroupName $rgName -ServerName $newServerName -Location $location -AssignIdentity -EnableActiveDirectoryOnlyAuthentication -ExternalAdminName $adminAccount

I am getting this response:

Operation returned an invalid status code 'Forbidden'

Prior to the offending line I am connecting as follows:

Disable-AzContextAutosave -Scope Process
$context = (Connect-AzAccount -Identity -AccountId "<account-client-id>").context 
Select-AzSubscription -SubscriptionId $subscriptionId
$context = Set-AzContext -SubscriptionName $context.Subscription -DefaultProfile $context

I used to have a simpler version but after checking with Microsoft docs, that was the recommended way to do it. When it connects everything seems to be in its place, tenant, subscription, managed identity client id...

Solution

  • I have one managed identity named srimanaged assigned with Contributor role under subscription like this:

    enter image description here

    Now, I added this managed identity under my automation account:

    enter image description here

    When I ran your code in my environment, I got same error like this:

    Disable-AzContextAutosave -Scope Process
$context = (Connect-AzAccount -Identity -AccountId "<account-client-id>").context 
$subscriptionId = "subId"
Select-AzSubscription -SubscriptionId $subscriptionId
$context = Set-AzContext -SubscriptionName $context.Subscription -DefaultProfile $context

$rgName = "Sri"
$newServerName = "sqlserver0711"
$location = "Central US"
$adminAccount = "Testuser"
New-AzSqlServer -ResourceGroupName $rgName -ServerName $newServerName -ServerVersion "12.0" -Location $location -AssignIdentity -EnableActiveDirectoryOnlyAuthentication -ExternalAdminName $adminAccount

    Response:

    enter image description here

    To resolve the error, try assigning User Administrator role to the managed identity under your directory:

    Go to Azure Portal -> Microsoft Entra ID -> Roles and administrators -> All roles -> Select 'User Administrator' -> Add assignment

    enter image description here

    When I ran same script again after assigning that role, I got response like this:

    Disable-AzContextAutosave -Scope Process
$context = (Connect-AzAccount -Identity -AccountId "<account-client-id>").context 
$subscriptionId = "subId"
Select-AzSubscription -SubscriptionId $subscriptionId
$context = Set-AzContext -SubscriptionName $context.Subscription -DefaultProfile $context

$rgName = "Sri"
$newServerName = "sqlserver0711"
$location = "Central US"
$adminAccount = "Testuser"
New-AzSqlServer -ResourceGroupName $rgName -ServerName $newServerName -ServerVersion "12.0" -Location $location -AssignIdentity -EnableActiveDirectoryOnlyAuthentication -ExternalAdminName $adminAccount

    Response:

    enter image description here

    To confirm that, I checked the same in Portal where SQL Server created successfully with below properties:

    enter image description here

    UPDATE:

    If you prefer least privileges, you can create custom directory role by allowing specific permissions to read the users. Refer this MS Doc for creating custom role in Microsoft Entra ID.

    Alternatively, try assigning Directory Reader role to the managed identity instead of assigning User Administrator role.