This post has been republished via RSS; it originally appeared at: Microsoft Tech Community - Latest Blogs - .
I worked on a service request where customer is getting the below error when trying to automate scale up/down for SQL Managed Instance using Azure Automation/Runbook.
Applies to:
- Performing management operations on SQL Managed instances using Azure Runbook (PowerShell).
- SQL Managed Instance has an AAD admin set.
- Automation account identity is set to user assigned.
Error:
set-AzSqlInstance : Cannot find the Azure Active Directory object '<removed>. Please make sure that the user or group or application you are authorizing is registered in the current subscription's Azure Active directory.
Runbook content:
$connectionName = "AzureRunAsConnection"
$servicePrincipalConnection=Get-AutomationConnection -Name $connectionName
Add-AzAccount -ServicePrincipal -Tenant $servicePrincipalConnection.TenantId -ApplicationId $servicePrincipalConnection.ApplicationId -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint
#Scale-up SQLMI
set-AzSqlInstance -Name "SQLMI1" -ResourceGroupName "RGName" -VCore 16 -Force
Debug:
We identified the issue by enabling debug mode on the PowerShell command:
Add -Debug -Confirm:$false to the PS command to capture API call to see what is the exact action is failing.
#Scale-up SQLMI debug enabled
set-AzSqlInstance -Name "SQLMI1" -ResourceGroupName "RGName" -VCore 16 -Force -Debug -Confirm:$false
From the debug results, we can see that client first gets the managed instance with its admins:
Since the admin is set, following requests have been sent to get its details:
The requests failed with the HTTP status code 403 (Forbidden).
Cause:
The Automation Account's service principal doesn’t have an AAD reader permission to get the SQLMI AAD admin information.
Resolution:
To fix the issue, you can assign a User Administrator, or Directory Reader build in role to the service principle on the subscription level following these steps: https://docs.microsoft.com/en-us/azure/role-based-access-control/role-assignments-portal?tabs=current
User Administrator: https://docs.microsoft.com/en-us/azure/active-directory/roles/permissions-reference#user-administrator
Or, Directory Reader: https://docs.microsoft.com/en-us/azure/active-directory/roles/permissions-reference#directory-readers
Thank you,