This article is contributed. See the original author and article here.
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.
- 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.
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.
$connectionName = “AzureRunAsConnection”
$servicePrincipalConnection=Get-AutomationConnection -Name $connectionName
Add-AzAccount -ServicePrincipal -Tenant $servicePrincipalConnection.TenantId -ApplicationId $servicePrincipalConnection.ApplicationId -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint
set-AzSqlInstance -Name “SQLMI1” -ResourceGroupName “RGName” -VCore 16 -Force
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).
The Automation Account’s service principal doesn’t have an AAD reader permission to get the SQLMI AAD admin information.
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
Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.