How to set up SQL Server Auditing with blob storage in ARM template

Question

I'm trying to update my SQL Server ARM template to use blob storage instead of table storage. What I have doesn't fail entries appear in the blob storage account but it doesn't update the Azure portal. Has anyone else experienced this?

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

Answer 1

What I have doesn't fail entries appear in the blob storage account but it doesn't update the Azure portal.

Do you mean you mean you have update SQL to enable auditing, but you can't find the change on Azure portal?

If I understand it correctly, maybe we should check the status of Azure SQL Auditing with PowerShell script:

PS C:\Users> Get-AzureRmSqlDatabaseAuditingPolicy -ServerName jasontest321 -ResourceGroupName vm -DatabaseName jason


DatabaseName       : jason
AuditAction        : {}
AuditActionGroup   : {SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP, FAILED_DATABASE_AUTHENTICATION_GROUP, BATCH_COMPLETED_GROUP}
ResourceGroupName  : vm
ServerName         : jasontest321
AuditType          : Blob
AuditState         : Enabled
StorageAccountName : 531777eastus
StorageKeyType     : Primary
RetentionInDays    : 0

More information about use ARM template turning on blob auditing, please refer to this link.