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

Question

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

Answer 1

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

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

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:

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

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:

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

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.