Lesson Learned #99: Azure SQL Database - Adding Audit Actions in SQL Azure Auditing

[Jose_Manuel_Jurado]

Today, I worked in a service request that our customer wants to specify the number of actions that to be recorded in the SQL Audit file. 

You know that we have two options to archive this:

• Using PowerShell
• Using REST-API. 

In this example, our customer used REST-API to set up a serie of events instead all events that we are saving in SQL Auditing by default.

      "resources": [
        {
          "name": "SqlLoginAuditing",
          "type": "auditingSettings",
          "apiVersion": "2015-05-01-preview",
          "properties": {
            "state": "[if(parameters('enableSqlAuditing'), 'Enabled', 'Disabled')]",
            "storageEndpoint": "[reference(resourceId('Microsoft.Storage/storageAccounts', variables('logsStorageAccountName')), '2018-03-01-preview').PrimaryEndpoints.Blob]",
            "storageAccountAccessKey": "[listKeys(resourceId('Microsoft.Storage/storageAccounts', variables('logsStorageAccountName')), '2018-03-01-preview').keys[0].value]",
            "storageAccountSubscriptionId": "[subscription().subscriptionId]",
            "retentionDays": "[parameters('sqlAuditingRetentionInDays')]",
            "comments": "Action group explanation: https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-2017#database-level-audit-action-groups",
            "auditActionsAndGroups": [
              "SELECT on SCHEMA::dbo by custom_sql_role",
              "SELECT on SCHEMA::dbo by dbo",
              "UPDATE on SCHEMA::dbo by custom_sql_role",
              "UPDATE on SCHEMA::dbo by dbo",
              "INSERT on SCHEMA::dbo by custom_sql_role",
              "INSERT on SCHEMA::dbo by dbo",
              "DELETE on SCHEMA::dbo by custom_sql_role",
              "DELETE on SCHEMA::dbo by dbo",
              "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP",
              "FAILED_DATABASE_AUTHENTICATION_GROUP",
              "DATABASE_OBJECT_CHANGE_GROUP",
              "DATABASE_OBJECT_OWNERSHIP_CHANGE_GROUP",
              "DATABASE_PERMISSION_CHANGE_GROUP",
              "DATABASE_PRINCIPAL_CHANGE_GROUP",
              "DATABASE_PRINCIPAL_IMPERSONATION_GROUP",
              "DATABASE_ROLE_MEMBER_CHANGE_GROUP",
              "SCHEMA_OBJECT_CHANGE_GROUP",
              "SCHEMA_OBJECT_OWNERSHIP_CHANGE_GROUP",
              "SCHEMA_OBJECT_PERMISSION_CHANGE_GROUP",
              "USER_CHANGE_PASSWORD_GROUP"
            ],
            "isStorageSecondaryKeyInUse": fals

Enjoy!