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

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

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:

 

 

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!

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.