Deployment of ARM: Authorization failed for template resource 'sql

Question

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'.'.

Any update this issue? Could you let me know if it works or not? — Joy Wang-MSFT

Joy Wang-MSFT Joy Wang-MSFT · Accepted Answer · 2020-04-09T09:45:43

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.

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"
        }
    }
}

Deployment of ARM: Authorization failed for template resource 'sql

2 Answers