Internal server error when deploying ARM Template

Question

I am deploying an arm template that contains the following resources

Microsoft.Storage/storageAccount
Microsoft.Sql/servers
Microsoft.Sql/servers/auditPolicies

Now everything worked until I started changing the values for the auditPolicies object. Here are the steps I took until the InternalServerError occurred.

1. Added the auditState property and set its value to Disabled. Deployment Successful.
2. Changed the auditState property to Enabled. Deployment failed. Error states that the storageAccountName is required.
3. Added storageAccountName and set its value to the name of the storage account. Deployment failed. Error states that storageAccountKey.
4. Added storageAccountKey and set its value to key1 of the storage account's keys object. Deployment failed. Internal Server Error - "An Error has occurred while saving Auditing settings, please try again later". Additionally, the errors cause the deployment to run indefinitely. Though I am not concerned about that aspect.

The following is the complete template.

{
  "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",

  "parameters": {
    "app-name-prefix": {
      "type": "string",
      "minLength": 1
    },
    "app-locations": {
      "type": "array",
      "minLength": 1
    },
    "app-friendly-names": {
      "type": "array",
      "minLength": 1
    },
    "db-user-admin-username": {
      "type": "securestring"
    },
    "db-user-admin-password": {
      "type": "securestring"
    },
    "database-audit-enabled": {
      "defaultValue": "Enabled",
      "allowedValues": [
        "Enabled",
        "Disabled"
      ],
      "type": "string"
    },
    "storage-kind": {
      "defaultValue": "BlobStorage",
      "allowedValues": [
        "StorageV2",
        "BlobStorage"
      ],
      "type": "string"
    },
    "storage-sku": {
      "defaultValue": "Standard_LRS",
      "allowedValues": [
        "Standard_LRS",
        "Standard_ZRS",
        "Standard_GRS",
        "Standard_RAGRS",
        "Premium_LRS"
      ],
      "type": "string"
    }
  },
  "variables": {
    "db-service-name": "[concat(parameters('app-name-prefix'), '-database-service-')]",
    "storage-name": "[concat(toLower(parameters('app-name-prefix')), 'auditstorage')]"
  },
  "resources": [
    {
      "name": "[concat(variables('storage-name'), parameters('app-friendly-names')[copyIndex()])]",
      "type": "Microsoft.Storage/storageAccounts",
      "sku": {
        "name": "[parameters('storage-sku')]"
      },
      "kind": "[parameters('storage-kind')]",
      "apiVersion": "2018-02-01",
      "location": "[parameters('app-locations')[copyIndex()]]",
      "copy": {
        "count": "[length(parameters('app-locations'))]",
        "name": "storageCopy"
      },
      "properties": {
        "supportsHttpsTrafficOnly": true,
        "accessTier": "Hot",
        "encryption": {
          "services": {
            "blob": {
              "enabled": true
            },
            "file": {
              "enabled": true
            }
          },
          "keySource": "Microsoft.Storage"
        }
      }
    },
    {
      "type": "Microsoft.Sql/servers",
      "name": "[concat(variables('db-service-name'), parameters('app-friendly-names')[copyIndex()])]",
      "apiVersion": "2014-04-01",
      "location": "[parameters('app-locations')[copyIndex()]]",
      "copy": {
        "name": "databaseServiceCopy",
        "count": "[length(parameters('app-locations'))]"
      },
      "properties": {
        "administratorLogin": "[parameters('db-user-admin-username')]",
        "administratorLoginPassword": "[parameters('db-user-admin-password')]",
        "version": "12.0"
      },
      "resources": [
        {
          "type": "auditingPolicies",
          "name": "Default",
          "apiVersion": "2014-04-01",
          "location": "[parameters('app-locations')[copyIndex()]]",
          "properties": {
            "auditingState": "[parameters('database-audit-enabled')]",
            "storageAccountName": "[concat(variables('storage-name'), parameters('app-friendly-names')[copyIndex()])]",
            "storageAccountKey": "[listKeys(concat(variables('storage-name'), parameters('app-friendly-names')[copyIndex()]), '2018-02-01').keys[0].value]"
          },
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', concat(variables('db-service-name'), parameters('app-friendly-names')[copyIndex()]))]",
            "storageCopy"
          ]
        }
      ]
    }
  ]
}

What am I missing that will help resolve this issue? What do I need to do to stop this internal server error?

---

I have added the complete template as was requested by @Pete

Answer 1

I have found the answer after connecting with Azure Support.

The resource type: Microsoft.Sql/servers/auditingPolicies is no longer supported and in the next few weeks Azure Resource Manager will no longer support this completely.

This resource type refers directly to table auditing, which has been reported as being deprecated for blob auditing. Though the documentation at this time does not directly report it. The docs will be updated in the coming days after this post, by the owners.

To enable the auditing you need to use the Microsoft.Sql/servers/auditingSettings object. The documentation on this is coming and until it does you will be directed to documentation for the database version of this resource type Microsoft.Sql/servers/databases/auditingSettings.

Auditing settings work much like the Auto-Tuning advisors. You can set either server or database level settings. The server settings will be inherited by the database if the database has not been configured directly.

This is a sample of the auditingSettings object that I use instead of the auditingPolicies object above. It is nested just the same.

{
  "apiVersion": "2017-03-01-preview",
  "type": "auditingSettings",
  "name": "DefaultAuditingSettings",
  "dependsOn": [
    "[resourceId('Microsoft.Sql/servers', concat(variables('db-service-name'), parameters('app-friendly-names')[copyIndex()]))]",
    "storageCopy"
  ],
  "properties": {
    "state": "Enabled",
    "storageEndpoint": "[reference(concat('Microsoft.Storage/storageAccounts', '/', variables('storage-name'), parameters('app-friendly-names')[copyIndex()]), '2018-02-01').primaryEndpoints.blob]",
    "storageAccountAccessKey": "[listKeys(concat(variables('storage-name'), parameters('app-friendly-names')[copyIndex()]), '2018-02-01').keys[0].value]",
    "storageAccountSubscriptionId": "[subscription().subscriptionId]",
    "isStorageSecondaryKeyInUse": false,
    "retentionDays": "30"
  }
}