Question

You created an Azure Automation account with a system-assigned managed identity to run a PowerShell runbook that executes maintenance T-SQL against an Azure SQL Database each night. The runbook authenticates using the managed identity, but every execution fails with a login error stating the token principal cannot connect. What must you do so the runbook can authenticate and run T-SQL successfully?

Accepted Answer

In the target database, create a contained database user mapped to the Automation account's managed identity and grant it the required permissions.

Answer

Add the Automation account's managed identity to the SQL Server Agent operators list on the logical server.

Answer

Store the SQL administrator username and password as unencrypted runbook variables and connect with SQL authentication instead.

Answer

Assign the Contributor RBAC role on the SQL Database resource to the Automation account's managed identity.