How to set AuditingType as Blob using ARMtemplate in AzureSqlServer

Question

I am deploying AzureSqlServer using ARMTemplate. I want to enable Auditing & Threat Detection using ARMTemplate. Below is the code that sets auditingtype as "Table":

    {
    "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
    "contentVersion": "1.0.0.0",
  "parameters": {
    "serverName": {
      "type": "string",
      "minLength": 1,
      "metadata": {
        "description": "The name of the SQL Server."
      }
    },
    "location": {
      "type": "string",
      "defaultValue": "South Central US",
      "allowedValues": [
        "Central US",
        "East Asia",
        "East US",
        "Japan East",
        "Japan West",
        "North Europe",
        "South Central US",
        "Southeast Asia",
        "West Europe",
        "West US",
        "East US 2"
      ],
      "metadata": {
        "description": "The location where SQL server will be deployed."
      }
    },
    "administratorLogin": {
      "type": "string",
      "minLength": 1,
      "metadata": {
        "description": "The account name to use for the database server administrator."
      }
    },
    "administratorLoginPassword": {
      "type": "securestring",
      "minLength": 1,
      "metadata": {
        "description": "The password to use for the database server administrator."
      }
    },
    "serverVersion": {
      "type": "string",
      "defaultValue": "12.0",
      "allowedValues": [
        "12.0"
      ],
      "metadata": {
        "description": "The server version."
      }
    },
    "deploymentVersion": {
      "type": "string",
      "minLength": 1,
      "metadata": {
        "description": "The deployment version tag."
      }
    },
    "deploymentType": {
      "type": "string",
      "minLength": 1,
      "metadata": {
        "description": "The deployment type tag."
      }
    },
    "auditStorageAccountName": {
      "type": "string",
      "metadata": {
        "description": "Specifies the name of the storage account where diagnostics logs will be written"
      }
    },
    "auditAlertEmailaddress": {
      "type": "string",
      "metadata": {
        "description": "Specifies the email address for alerts"
      }
    },
    "logsRetentionInDays": {
      "type": "string",
      "metadata": {
        "description": "Specifies the number of days that logs are gonna be kept. If you do not want to apply any retention policy and retain data forever, set value to 0."
      }
    },
    "threatDetection": {
      "type": "string",
      "defaultValue": "Disabled",
      "allowedValues": [
        "Enabled",
        "Disabled"
      ],
      "metadata": {
        "description": "Azure SQL Server Threat Detection."
      }
    },
    "auditing": {
      "type": "string",
      "defaultValue": "Enabled",
      "allowedValues": [
        "Enabled",
        "Disabled"
      ],
      "metadata": {
        "description": "Azure SQL Server auditing."
      }
    }
  },
    "variables": { },
    "resources": [
        {
            "name": "[parameters('serverName')]",
            "type": "Microsoft.Sql/servers",
            "location": "[parameters('location')]",
            "apiVersion": "2014-04-01-preview",
            "properties": {
                "administratorLogin": "[parameters('administratorLogin')]",
                "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
                "version": "[parameters('serverVersion')]"
            },
            "tags": {
                "deploymentVersion": "[parameters('deploymentVersion')]",
                "deploymentType": "[parameters('deploymentType')]"
            },
          "resources": [
            {
              "apiVersion": "2014-04-01-preview",
              "dependsOn": [
                "[concat('Microsoft.Sql/servers/', parameters('serverName'))]"
              ],
              "location": "[parameters('location')]",
              "name": "AllowAllWindowsAzureIps",
              "properties": {
                "endIpAddress": "0.0.0.0",
                "startIpAddress": "0.0.0.0"
              },
              "type": "firewallrules"
            },
            {
              "apiVersion": "2014-04-01-preview",
              "type": "auditingPolicies",
              "name": "DefaultAuditPolicy",
              "dependsOn": [
                "[parameters('serverName')]"
              ],
              "properties": {
                "State": "[parameters('auditing')]",
                "storageAccountName": "[parameters('auditStorageAccountName')]",
                "storageEndpoint": "[concat('https://', parameters('auditStorageAccountName'), '.blob.core.windows.net/')]",
                "storageAccountKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', parameters('auditStorageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]",
                "storageAccountResourceGroupName": "[resourceGroup().name]",
                "storageAccountSubscriptionId": "[subscription().subscriptionId]",               
                "eventTypesToAudit": "All"
              }

            },
            {
              "apiVersion": "2015-05-01-preview",
              "type": "securityAlertPolicies",
              "name": "DefaultSecurityAlert",
              "dependsOn": [
                "[parameters('serverName')]",
                "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/auditingPolicies/DefaultAuditPolicy')]"
              ],
              "properties": {
                "state": "[parameters('threatDetection')]",
                "disabledAlerts": "",
                "emailAddresses": "[parameters('auditAlertEmailaddress')]",
                "emailAccountAdmins": "Enabled",
                "retentionDays": "[parameters('logsRetentionInDays')]",
                "storageEndpoint": "[concat('https://', parameters('auditStorageAccountName'), '.blob.core.windows.net/')]",
                "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', parameters('auditStorageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]"
              }
            }
          ]
        }
    ]
}

This piece of code gives error as :

Deployment template validation failed: 'The resource 'Microsoft.Sql/servers/test2aa/auditingPolicies/DefaultAudit‌​Policy' is not defined in the template

If I change the "type": "auditingPolicies" then I need to change the api version also. When I modified the apiversion and re-deployed the template, then I get "An error occured while processing the request" and template fails

How do I set AuditingType as "Blob" using ARMTemplate?

Answer 1

Yes, it is possible. Please refer to this link:Microsoft.Sql/servers/databases/auditingSettings template reference.

storageEndpoint string  No  Specifies the blob storage endpoint (e.g. https://MyAccount.blob.core.windows.net). If state is Enabled, storageEndpoint is required.

In your template add storageEndpoint. Use Azure storage blob as value. Your template should be like below:

      "storageEndpoint": {
          "type": "string",
          "defaultValue": "[concat('https://',parameters('storageAccountName'),'.blob.core.windows.net')]"
        },        


     {
              "apiVersion": "2015-05-01-preview",
              "type": "auditingSettings",
              "name": "Default",
              "location": "[parameters('serverLocation')]",
              "dependsOn": [
                "[concat('Microsoft.Sql/servers/', parameters('serverName'))]",
                "[concat('Microsoft.Storage/storageAccounts/',parameters('storageAccountName'))]"
              ],
              "properties": {
                "State": "Enabled",
                "storageEndpoint": "[parameters('storageEndpoint')]",
                "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', parameters('storageAccountName')), providers('Microsoft.Storage', 'storageAccounts').apiVersions[0]).keys[0].value]",
                "retentionDays": 0,
                "auditActionsAndGroups": null,
                "storageAccountSubscriptionId": "[subscription().subscriptionId]",
                "isStorageSecondaryKeyInUse": false
              }
            }

More information please refer to this blog:ARM template - turning on blob auditing.

Update:

You could use template to deploy your SQL with Auditing type as blob.