ARM template - turning on blob auditing

Article
01/11/2017

Azure SQL Database Auditing is a popular feature which helps to track events on the databases. Recently Blob Auditing was introduced, at the time of writing this post it is still a preview feature.
The advantages of blob auditing over table auditing are higher performance and higher granularity. To turn on blob auditing you can use the portal, PowerShell, REST API or ARM template. In this article I will show how this is done with ARM template. If you would like to read more about the Auditing feature or the other methods to turning it on, please see the documentation Get started with SQL database auditing.

Note
If blob auditing is turned on at server level, it will always be inherited to its databases.
You can define an additional auditing policy at the database level, the two will co-exist and you can capture additional events on a specific database.

The server level auditing setting is a child resource of the server (just like the firewall rules). Similarly, the database level auditing is a child resource of the database.
For example:

{
"apiVersion": "2015-05-01-preview",
"type": "auditingSettings",
"name": "Default",
"location": "[parameters('serverLocation')]",
"dependsOn": [
"[concat('Microsoft.Sql/servers/', parameters('serverName'))]" ],
"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 }
}

If you create the storage account in the same template, add it to the dependencies.

Using null for auditActionsAndGroups, as above, will create an auditing policy with the default groups:

SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
FAILED_DATABASE_AUTHENTICATION_GROUP
BATCH_COMPLETED_GROUP

To specify your own groups you can list them under "audtiActionsAndGroups".
For example:

"auditActionsAndGroups": [ "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "DATABASE_LOGOUT_GROUP", "USER_CHANGE_PASSWORD_GROUP" ],

You can find the full list of groups at Create or Update server Blob Auditing Policy.

Note
Per resource you can define one auditing policy. This means one for the server and one for the database.
If the ARM template contains multiple auditing policies for a resource, it will be deployed without an error, but the last policy to be deployed will overwrite the previous ones.

Logistics

I used PowerShell to deploy the template file:

# log in Login-AzureRmAccount
# existing resource group where you would like to deploy $RGName="resourcegroupname"
# path to the $TemplateFile="c:\path\file.json"
# validate the template Test-AzureRmResourceGroupDeployment -ResourceGroupName $RGName -TemplateFile $TemplateFile # deploy to the specified resource group New-AzureRmResourceGroupDeployment -ResourceGroupName $RGName -TemplateFile $TemplateFile -Verbose

A template example with all the elements above:

 
{
  "$schema": "https://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {
    "serverName": {
      "type": "string",
      "defaultValue": "yourservername",
      "metadata": {
        "description": "The name of the new database server to create."
      }
    },
    "serverLocation": {
      "type": "string",
      "defaultValue": "West Europe",
      "metadata": {
        "description": "The location of the database server."
      }
    },
    "administratorLogin": {
      "type": "string",
      "defaultValue": "VeryWiseAdmin",
      "metadata": {
        "description": "The account name to use for the database server administrator."
      }
    },
    "administratorLoginPassword": {
      "type": "securestring",
      "defaultValue": "password",
      "metadata": {
        "description": "The password to use for the database server administrator."
      }
    },
    "databaseName": {
      "type": "string",
      "defaultValue": "dbname",
      "metadata": {
        "description": "The name of the new database to create."
      }
    },
    "collation": {
      "type": "string",
      "defaultValue": "SQL_Latin1_General_CP1_CI_AS",
      "metadata": {
        "description": "The database collation for governing the proper use of characters."
      }
    },
    "edition": {
      "type": "string",
      "defaultValue": "Basic",
      "metadata": {
        "description": "The type of database to create. The available options are: Web, Business, Basic, Standard, and Premium."
      }
    },
    "maxSizeBytes": {
      "type": "string",
      "defaultValue": "1073741824",
      "metadata": {
        "description": "The maximum size, in bytes, for the database"
      }
    },
    "requestedServiceObjectiveName": {
      "type": "string",
      "defaultValue": "Basic",
      "metadata": {
        "description": "The name corresponding to the performance level for edition. The available options are: Shared, Basic, S0, S1, S2, S3, P1, P2, and P3."
      }
    },
    "storageAccountName": {
      "type": "string",
      "defaultValue": "storageaccountname"
    },
    "storageEndpoint": {
      "type": "string",
      "defaultValue": "[concat('https://',parameters('storageAccountName'),'.blob.core.windows.net')]"
    },
    "storageType": {
      "type": "string",
      "defaultValue": "Standard_GRS",
      "allowedValues": [
        "Standard_LRS",
        "Standard_ZRS",
        "Standard_GRS",
        "Standard_RAGRS",
        "Premium_LRS"
      ]
    }
  },
  "resources": [
    {
      "name": "[parameters('storageAccountName')]",
      "type": "Microsoft.Storage/storageAccounts",
      "location": "[parameters('serverLocation')]",
      "apiVersion": "2015-06-15",
      "dependsOn": [ "[concat('Microsoft.Sql/servers/', parameters('serverName'))]" ],
      "properties": {
        "accountType": "[parameters('storageType')]"
      }
    },
    {
      "name": "[parameters('serverName')]",
      "type": "Microsoft.Sql/servers",
      "location": "[parameters('serverLocation')]",
      "apiVersion": "2014-04-01-preview",
      "properties": {
        "administratorLogin": "[parameters('administratorLogin')]",
        "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
        "version": "12.0"
      },
      "resources": [
        {
          "name": "[parameters('databaseName')]",
          "type": "databases",
          "location": "[parameters('serverLocation')]",
          "apiVersion": "2014-04-01-preview",
          "dependsOn": [
            "[concat('Microsoft.Sql/servers/', parameters('serverName'))]"
          ],
          "properties": {
            "edition": "[parameters('edition')]",
            "collation": "[parameters('collation')]",
            "maxSizeBytes": "[parameters('maxSizeBytes')]",
            "requestedServiceObjectiveName": "[parameters('requestedServiceObjectiveName')]"
          },
          "resources": [
            {
              "apiVersion": "2015-05-01-preview",
              "type": "auditingSettings",
              "name": "Default",
              "location": "[parameters('serverLocation')]",
              "dependsOn": [
                "[concat('Microsoft.Sql/servers/', parameters('serverName'))]",
                "[concat('Microsoft.Sql/servers/', parameters('serverName'), '/databases/',parameters('databaseName'))]",
                "[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": [ "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP", "DATABASE_LOGOUT_GROUP", "USER_CHANGE_PASSWORD_GROUP" ],
                "storageAccountSubscriptionId": "[subscription().subscriptionId]",
                "isStorageSecondaryKeyInUse": false
              }
            }
          ]
        },
        {
          "apiVersion": "2014-04-01-preview",
          "type": "firewallrules",
          "location": "[parameters('serverLocation')]",
          "name": "AllowAllWindowsAzureIps",
          "dependsOn": [
            "[concat('Microsoft.Sql/servers/', parameters('serverName'))]"
          ],
          "properties": {
            "endIpAddress": "0.0.0.0",
            "startIpAddress": "0.0.0.0"
          }
        },
        {
          "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
          }
        }

      ]
    }
  ],
  "outputs": {

  }
}

Share via

ARM template - turning on blob auditing

Additional resources