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

by | Nov 28, 2021 | Technology | 0 comments

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.


 


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,

Brought to you by Dr. Ware, Microsoft Office 365 Silver Partner, Charleston SC.

Submit a Comment