CODEWITHSUNDEEP
azure-resource-manager
Kenny_I
Kenny_I

Reputation: 2513

Deployment of ARM: Authorization failed for template resource 'sql

I try to deply SQL Server Logical server with PS and ARM. I can succesfully create logical server at portal with contributor rights, but cannot figure out what is wrong here.

I have here PowerShell ISE on Windows.

ARM template is copy and paste from https://github.com/Azure/azure-quickstart-templates/tree/master/101-sql-logical-server/

//CODE
Connect-AzAccount -Credential $Credential -Tenant $tenant -Subscription $subscription

#ARM Deployment
$templateFile = "C:\Azure\SQLServer\azuredeploy.json"

New-AzResourceGroupDeployment `
  -Name SQLDeployment `
  -ResourceGroupName my-rg `
  -TemplateFile $templateFile

ERROR: New-AzResourceGroupDeployment : 17.35.18 - Error: Code=InvalidTemplateDeployment; Message=The template deployment failed with error: 'Authorization failed for template resource 'sql vasvtmcp42o3wko/Microsoft.Authorization/11fd61df-2336-5b96-9b45-ffc7160df111' of type 'Microsoft.Storage/storageAccounts/providers/roleAssignments'. The client 'john.smith@mycompany. com' with object id '1115f3de-834b-4d28-a48f-ecaad01e3111' does not have permission to perform action 'Microsoft.Authorization/roleAssignments/write' at scope '/subscriptions/1111111 11111111111111/resourceGroups/my-rg/providers/Microsoft.Storage/storageAccounts/sqlvasvtmcp42o3wko/providers/Microsoft.Authorization/roleAssignments/11111df -2336-5b96-9b45-ffc7160df168'.'.

Upvotes: 3

Views: 9462

Answers (2)

Joy Wang
Joy Wang

Reputation: 42133

I can succesfully create logical server at portal with contributor rights, but cannot figure out what is wrong here.

Because the template you used will enable the Advanced data security for you, this will create a storage account and service principal for your sql server, then assign the service principal to the storage account as a Storage Blob Data Contributor role automatically.

enter image description here

To do this operation, your user account need to be the Owner or User Access Administrator in the resource group or subscription. Or you can also create a custom role which has Microsoft.Authorization/roleAssignments/write in its actions, then the role will also be able to do that.

So in conclusion, you have two options to fix the issue.

1.Navigate to the Resource group or Subscription in the portal -> Access control (IAM) -> Add -> add your user account as a role mentioned above e.g. Owner, then it will work fine. See details here.

2.When you deploy the template, specify the enableADS with false in the azuredeploy.parameters.json file. Then it will not enable the Advanced data security for you, and you will be able to create the sql server with the Contributor via the template.

{
    "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentParameters.json#",
    "contentVersion": "1.0.0.0",
    "parameters": {
        "serverName": {
            "value": "GEN-UNIQUE"
        },
        "administratorLogin": {
            "value": "GEN-UNIQUE"
        },
        "administratorLoginPassword": {
            "value": "GEN-PASSWORD"
        },
        "enableADS": {
            "value": "false"
        }
    }
}

Upvotes: 2

Kyle Monteagudo
Kyle Monteagudo

Reputation: 79

The error clearly states the account that is being used for the action doesn't have the proper role assignment to perform the action.

the client 'john.smith@mycompany. com' with object id '1115f3de-834b-4d28-a48f-ecaad01e3111' does not have permission to perform action 'Microsoft.Authorization/roleAssignments/write' at scope '/subscriptions/1111111 11111111111111

This means your next step should be validating what role assignment is assigned to that user, and then checking that the role does have the permission to perform Microsoft.Authorization/roleAssignments/write

Upvotes: -1

Related Questions