Share via

Unable to access Azure SQL db ressource from automation account

MoTaar 310 Reputation points
2024-01-24T19:53:56.0033333+00:00

I am Unable to access Azure SQL db ressource from automation account runbook. I've added user access to the automation account and to the SQL server. I couldn't find a way to add the identity to the SQL database too.

After all that I still getting the error saying:

Failed
The running command stopped because the preference variable "ErrorActionPreference" or common parameter is set to Stop: The client '******' with object id '*********' does not have authorization to perform action 'Microsoft.Sql/servers/databases/read' over scope '***********' or the scope is invalid. If access was recently granted, please refresh your credentials.
Azure Role-based access control
Azure Role-based access control
An Azure service that provides fine-grained access management for Azure resources, enabling you to grant users only the rights they need to perform their jobs.
725 questions
Azure Automation
Azure Automation
An Azure service that is used to automate, configure, and install updates across hybrid environments.
1,196 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. James Hamil 23,216 Reputation points Microsoft Employee
    2024-01-26T21:36:04.8266667+00:00

    Hi @MoTaar , you need to ensure that the managed identity of your automation account has the required permissions to access the SQL database resource.

    You mentioned that you have already added user access to the automation account and to the SQL server. However, you also need to add the managed identity of your automation account to the SQL database with the required permissions. To add the managed identity of your automation account to the SQL database, you can follow these steps:

    1. Go to the Azure portal and navigate to your SQL database resource.
    2. Click on "Access control (IAM)" in the left-hand menu.
    3. Click on "Add" and select "Add role assignment".
    4. In the "Add role assignment" blade, select the role you want to assign to the managed identity of your automation account. For example, you can select "Reader" to grant read-only access to the SQL database.
    5. In the "Assign access to" section, select "Azure AD user, group, or service principal".
    6. In the "Select" field, search for the name of your automation account and select it.
    7. Click on "Save" to add the role assignment.

    After adding the managed identity of your automation account to the SQL database with the required permissions, you should be able to access the SQL database resource from your automation account runbook without any issues. Please let me know if you have any questions and I can help you further.

    If this answer helps you please mark "Accept Answer" so other users can reference it.

    Thank you,

    James