Sunday, November 28, 2021

set-AzSqlInstance : Cannot find the Azure Active Directory object in Azure automation for SQLMI

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:

 

Picture1.png

 

Since the admin is set, following requests have been sent to get its details:

 

Picture2.png

 

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,

Posted at https://sl.advdat.com/3lhEszw