For more info about the problem and how it can be solved please check this: https://www.mssqltips.com/sqlservertip/6554/auto-scaling-azure-sql-db-using-automation-runbooks.
I used the original Powershell runbook(from the link above) for a long time but Run As was retired in favor of managed identity. More info here: https://learn.microsoft.com/en-us/azure/automation/migrate-run-as-accounts-managed-identity?tabs=sa-managed-identity
Updated version that works with manged identity and Az module
I updated the original Poweshell runbook to work with managed identity and Az Powerhell module. AzureRM powershell module will retire on 29 February 2024. More info: https://learn.microsoft.com/en-us/powershell/azure/migrate-from-azurerm-to-az?view=azps-10.3.0
How to get and use the updated version
I created a public github repo that has the runbook and info in the readme on update the automation account to be able to use the managed identity and Az module.
Please check the readme file of the repo to get all the info.
Link to the github repo: https://github.com/emanuelpaul/Az-Automation-ScaleAzureSqlDb-Managed-Identity