SQL Server Database Without Auditing

• Query id: e055285c-bc01-48b4-8aa5-8a54acdd29df
• Query name: SQL Server Database Without Auditing
• Platform: AzureResourceManager
• Severity: Medium
• Category: Observability
• CWE: 778
• Risk score: 6.4
• URL: Github

Description

Every 'Microsoft.Sql/servers/databases' resource should have Auditing Enabled
Documentation

Code samples

Code samples with security vulnerabilities

Positive test num. 1 - bicep file

resource sqlServer1 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: 'sqlServer1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlServer1'
  }
  properties: {
    administratorLogin: 'adminUsername'
    administratorLoginPassword: 'adminPassword'
  }
}

resource sqlServer1_default 'Microsoft.Sql/servers/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'default'
  properties: {
    auditActionsAndGroups: ['DATABASE_LOGOUT_GROUP']
    isAzureMonitorTargetEnabled: true
    isStorageSecondaryKeyInUse: true
    queueDelayMs: 1000
    retentionDays: 100
    state: 'Enabled'
  }
}

resource sqlServer1_sqlDatabase1 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'sqlDatabase1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlDatabase1'
  }
  properties: {
    collation: 'SQL_Latin1_General_CP1_CI_AS'
    edition: 'Basic'
    maxSizeBytes: '1073741824'
    requestedServiceObjectiveName: 'Basic'
  }
}

Positive test num. 2 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {},
  "functions": [],
  "variables": {},
  "resources": [
    {
      "name": "sqlServer1",
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "location": "[resourceGroup().location]",
      "tags": {
        "displayName": "sqlServer1"
      },
      "properties": {
        "administratorLogin": "adminUsername",
        "administratorLoginPassword": "adminPassword"
      },
      "resources": [
        {
          "name": "sqlServer1/sqlDatabase1",
          "type": "Microsoft.Sql/servers/databases",
          "apiVersion": "2021-02-01-preview",
          "location": "[resourceGroup().location]",
          "tags": {
            "displayName": "sqlDatabase1"
          },
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
          ],
          "properties": {
            "collation": "SQL_Latin1_General_CP1_CI_AS",
            "edition": "Basic",
            "maxSizeBytes": "1073741824",
            "requestedServiceObjectiveName": "Basic"
          }
        },
        {
        "type": "Microsoft.Sql/servers/auditingSettings",
        "apiVersion": "2021-02-01-preview",
        "name": "default",
        "dependsOn": [
          "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
        ],
        "properties": {
          "state": "Enabled",
          "isAzureMonitorTargetEnabled": true
        }
        }
      ]
    }
  ],
  "outputs": {}
}

Positive test num. 3 - bicep file

resource sqlServer1 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: 'sqlServer1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlServer1'
  }
  properties: {
    administratorLogin: 'adminUsername'
    administratorLoginPassword: 'adminPassword'
  }
}

resource sqlServer1_sqlDatabase1 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'sqlDatabase1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlDatabase1'
  }
  properties: {
    collation: 'SQL_Latin1_General_CP1_CI_AS'
    edition: 'Basic'
    maxSizeBytes: 1073741824
    requestedServiceObjectiveName: 'Basic'
  }
}

resource sqlServer1_default 'Microsoft.Sql/servers/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'default'
  properties: {
    auditActionsAndGroups: ['DATABASE_LOGOUT_GROUP']
    isAzureMonitorTargetEnabled: true
    isStorageSecondaryKeyInUse: true
    queueDelayMs: 1000
    retentionDays: 100
    state: 'Enabled'
  }
}

resource sqlServer1_sqlDatabase1_default 'Microsoft.Sql/servers/databases/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1_sqlDatabase1
  name: 'default'
  properties: {
    auditActionsAndGroups: ['DATABASE_LOGOUT_GROUP']
    isAzureMonitorTargetEnabled: true
    isStorageSecondaryKeyInUse: true
    queueDelayMs: 1000
    retentionDays: 100
    state: 'Disabled'
  }
}

Positive test num. 4 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {},
  "functions": [],
  "variables": {},
  "resources": [
    {
      "name": "sqlServer1",
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "location": "[resourceGroup().location]",
      "tags": {
        "displayName": "sqlServer1"
      },
      "properties": {
        "administratorLogin": "adminUsername",
        "administratorLoginPassword": "adminPassword"
      }
    },
        {
        "type": "Microsoft.Sql/servers/auditingSettings",
        "apiVersion": "2021-02-01-preview",
        "name": "sqlServer1/default",
        "dependsOn": [
          "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
        ],
        "properties": {
          "state": "Enabled",
          "isAzureMonitorTargetEnabled": true
        }
        },
    {
      "name": "sqlServer1/sqlDatabase1",
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2021-02-01-preview",
      "location": "[resourceGroup().location]",
      "tags": {
        "displayName": "sqlDatabase1"
      },
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
      ],
      "properties": {
        "collation": "SQL_Latin1_General_CP1_CI_AS",
        "edition": "Basic",
        "maxSizeBytes": 1073741824,
        "requestedServiceObjectiveName": "Basic"
      }
    },
    {
      "type": "Microsoft.Sql/servers/databases/auditingSettings",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1/sqlDatabase1/default",
      "properties": {
        "auditActionsAndGroups": [ "DATABASE_LOGOUT_GROUP" ],
        "isAzureMonitorTargetEnabled": true,
        "isStorageSecondaryKeyInUse": true,
        "queueDelayMs": 1000,
        "retentionDays": 100,
        "state": "Disabled"
      }
    }
  ],
  "outputs": {}
}

Positive test num. 5 - bicep file

resource sqlServer1 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: 'sqlServer1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlServer1'
  }
  properties: {
    administratorLogin: 'adminUsername'
    administratorLoginPassword: 'adminPassword'
  }
}

resource sqlServer1_default 'Microsoft.Sql/servers/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'default'
  properties: {
    auditActionsAndGroups: ['DATABASE_LOGOUT_GROUP']
    isAzureMonitorTargetEnabled: true
    isStorageSecondaryKeyInUse: true
    queueDelayMs: 1000
    retentionDays: 100
    state: 'Enabled'
  }
}

resource sqlServer1_sqlDatabase1 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'sqlDatabase1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlDatabase1'
  }
  properties: {
    collation: 'SQL_Latin1_General_CP1_CI_AS'
    edition: 'Basic'
    maxSizeBytes: '1073741824'
    requestedServiceObjectiveName: 'Basic'
  }
}

Positive test num. 6 - json file

{
  "properties": {
    "template": {
      "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
      "contentVersion": "1.0.0.0",
      "parameters": {},
      "functions": [],
      "variables": {},
      "resources": [
        {
          "name": "sqlServer1",
          "type": "Microsoft.Sql/servers",
          "apiVersion": "2021-02-01-preview",
          "location": "[resourceGroup().location]",
          "tags": {
            "displayName": "sqlServer1"
          },
          "properties": {
            "administratorLogin": "adminUsername",
            "administratorLoginPassword": "adminPassword"
          },
          "resources": [
            {
              "name": "sqlServer1/sqlDatabase1",
              "type": "Microsoft.Sql/servers/databases",
              "apiVersion": "2021-02-01-preview",
              "location": "[resourceGroup().location]",
              "tags": {
                "displayName": "sqlDatabase1"
              },
              "dependsOn": [
                "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
              ],
              "properties": {
                "collation": "SQL_Latin1_General_CP1_CI_AS",
                "edition": "Basic",
                "maxSizeBytes": "1073741824",
                "requestedServiceObjectiveName": "Basic"
              }
            },
        {
        "type": "Microsoft.Sql/servers/auditingSettings",
        "apiVersion": "2021-02-01-preview",
        "name": "default",
        "dependsOn": [
          "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
        ],
        "properties": {
          "state": "Enabled",
          "isAzureMonitorTargetEnabled": true
        }
        }
          ]
        }
      ],
      "outputs": {}
    },
    "parameters": {}
  },
  "kind": "template",
  "type": "Microsoft.Blueprint/blueprints/artifacts",
  "name": "myTemplate"
}

Positive test num. 7 - bicep file

resource sqlServer1 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: 'sqlServer1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlServer1'
  }
  properties: {
    administratorLogin: 'adminUsername'
    administratorLoginPassword: 'adminPassword'
  }
}

resource sqlServer1_default 'Microsoft.Sql/servers/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'default'
  properties: {
    auditActionsAndGroups: ['DATABASE_LOGOUT_GROUP']
    isAzureMonitorTargetEnabled: true
    isStorageSecondaryKeyInUse: true
    queueDelayMs: 1000
    retentionDays: 100
    state: 'Enabled'
  }
}

resource sqlServer1_sqlDatabase1 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'sqlDatabase1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlDatabase1'
  }
  properties: {
    collation: 'SQL_Latin1_General_CP1_CI_AS'
    edition: 'Basic'
    maxSizeBytes: 1073741824
    requestedServiceObjectiveName: 'Basic'
  }
}

resource sqlServer1_sqlDatabase1_default 'Microsoft.Sql/servers/databases/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1_sqlDatabase1
  name: 'default'
  properties: {
    auditActionsAndGroups: ['DATABASE_LOGOUT_GROUP']
    isAzureMonitorTargetEnabled: true
    isStorageSecondaryKeyInUse: true
    queueDelayMs: 1000
    retentionDays: 100
    state: 'Disabled'
  }
}

Positive test num. 8 - json file

{
  "properties": {
    "template": {
      "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
      "contentVersion": "1.0.0.0",
      "parameters": {},
      "functions": [],
      "variables": {},
      "resources": [
        {
          "name": "sqlServer1",
          "type": "Microsoft.Sql/servers",
          "apiVersion": "2021-02-01-preview",
          "location": "[resourceGroup().location]",
          "tags": {
            "displayName": "sqlServer1"
          },
          "properties": {
            "administratorLogin": "adminUsername",
            "administratorLoginPassword": "adminPassword"
          }
        },
        {
          "name": "sqlServer1/sqlDatabase1",
          "type": "Microsoft.Sql/servers/databases",
          "apiVersion": "2021-02-01-preview",
          "location": "[resourceGroup().location]",
          "tags": {
            "displayName": "sqlDatabase1"
          },
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
          ],
          "properties": {
            "collation": "SQL_Latin1_General_CP1_CI_AS",
            "edition": "Basic",
            "maxSizeBytes": 1073741824,
            "requestedServiceObjectiveName": "Basic"
          }
        },
        {
          "type": "Microsoft.Sql/servers/databases/auditingSettings",
          "apiVersion": "2021-02-01-preview",
          "name": "sqlServer1/sqlDatabase1/default",
          "properties": {
            "auditActionsAndGroups": [ "DATABASE_LOGOUT_GROUP" ],
            "isAzureMonitorTargetEnabled": true,
            "isStorageSecondaryKeyInUse": true,
            "queueDelayMs": 1000,
            "retentionDays": 100,
            "state": "Disabled"
          }
        },
        {
        "type": "Microsoft.Sql/servers/auditingSettings",
        "apiVersion": "2021-02-01-preview",
        "name": "sqlServer1/default",
        "dependsOn": [
          "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
        ],
        "properties": {
          "state": "Enabled",
          "isAzureMonitorTargetEnabled": true
        }
        }
      ],
      "outputs": {}
    },
    "parameters": {}
  },
  "kind": "template",
  "type": "Microsoft.Blueprint/blueprints/artifacts",
  "name": "myTemplate"
}

Positive test num. 9 - bicep file

resource sqlServer 'Microsoft.Sql/servers@2019-06-01-preview' = {
  name: sqlServerName
  location: location
  tags: {
    displayName: 'SqlServer'
  }
  properties: {
    administratorLogin: sqlAdministratorLogin
    administratorLoginPassword: sqlAdministratorLoginPassword
    version: '12.0'
  }
}

resource sqlServer1_default 'Microsoft.Sql/servers/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'default'
  properties: {
    auditActionsAndGroups: ['DATABASE_LOGOUT_GROUP']
    isAzureMonitorTargetEnabled: true
    isStorageSecondaryKeyInUse: true
    queueDelayMs: 1000
    retentionDays: 100
    state: 'Enabled'
  }
}

resource sqlAdmin 'Microsoft.Sql/servers/administrators@2019-06-01-preview' = {
  name: 'ActiveDirectory'
  parent: sqlServer
  properties: {
    administratorType: 'ActiveDirectory'
    login: ADLogin
    sid: ADobjectID
    tenantId: ADtenantID
  }
}

Positive test num. 10 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {},
  "variables": {},
  "resources": [
    {
      "name": "[variables('sqlServerName')]",
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2019-06-01-preview",
      "location": "[parameters('location')]",
      "tags": {
        "displayName": "SqlServer"
      },
      "properties": {
        "administratorLogin": "[parameters('sqlAdministratorLogin')]",
        "administratorLoginPassword": "[parameters('sqlAdministratorLoginPassword')]",
        "version": "12.0"
      }
    },
    {
      "name": "[concat(variables('sqlServerName'),'/ActiveDirectory')]",
      "type": "Microsoft.Sql/servers/administrators",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', variables('sqlServerName'))]"
      ],
      "apiVersion": "2019-06-01-preview",
      "properties": {
        "administratorType": "ActiveDirectory",
        "login": "[variables('ADLogin')]",
        "sid": "[variables('ADobjectID')]",
        "tenantId": "[variables('ADtenantID')]"
      }
    }
  ]
}

Code samples without security vulnerabilities

Negative test num. 1 - bicep file

resource sqlServer1 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: 'sqlServer1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlServer1'
  }
  properties: {
    administratorLogin: 'adminUsername'
    administratorLoginPassword: 'adminPassword'
  }
}

resource sqlServer1_default 'Microsoft.Sql/servers/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'default'
  properties: {
    auditActionsAndGroups: ['DATABASE_LOGOUT_GROUP']
    isAzureMonitorTargetEnabled: true
    isStorageSecondaryKeyInUse: true
    queueDelayMs: 1000
    retentionDays: 100
    state: 'Enabled'
  }
}

resource sqlServer1_ssqlDatabase1 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'ssqlDatabase1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlDatabase1'
  }
  properties: {
    collation: 'SQL_Latin1_General_CP1_CI_AS'
    edition: 'Basic'
    maxSizeBytes: 107374182
    requestedServiceObjectiveName: 'Basic'
  }
}

resource sqlServer1_ssqlDatabase1_default 'Microsoft.Sql/servers/databases/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1_ssqlDatabase1
  name: 'default'
  properties: {
    auditActionsAndGroups: ['DATABASE_LOGOUT_GROUP']
    isAzureMonitorTargetEnabled: true
    isStorageSecondaryKeyInUse: true
    queueDelayMs: 1000
    retentionDays: 100
    state: 'Enabled'
  }
}

Negative test num. 2 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {},
  "functions": [],
  "variables": {},
  "resources": [
    {
      "name": "sqlServer1",
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "location": "[resourceGroup().location]",
      "tags": {
        "displayName": "sqlServer1"
      },
      "properties": {
        "administratorLogin": "adminUsername",
        "administratorLoginPassword": "adminPassword"
      },
      "resources": [
        {
          "name": "ssqlDatabase1",
          "type": "databases",
          "apiVersion": "2021-02-01-preview",
          "location": "[resourceGroup().location]",
          "tags": {
            "displayName": "sqlDatabase1"
          },
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
          ],
          "properties": {
            "collation": "SQL_Latin1_General_CP1_CI_AS",
            "edition": "Basic",
            "maxSizeBytes": 107374182,
            "requestedServiceObjectiveName": "Basic"
          },
          "resources": [
            {
              "type": "auditingSettings",
              "apiVersion": "2021-02-01-preview",
              "name": "default",
              "properties": {
                "auditActionsAndGroups": [ "DATABASE_LOGOUT_GROUP" ],
                "isAzureMonitorTargetEnabled": true,
                "isStorageSecondaryKeyInUse": true,
                "queueDelayMs": 1000,
                "retentionDays": 100,
                "state": "Enabled"
              }
            }
          ]
        },
        {
        "type": "Microsoft.Sql/servers/auditingSettings",
        "apiVersion": "2021-02-01-preview",
        "name": "default",
        "dependsOn": [
          "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
        ],
        "properties": {
          "state": "Enabled",
          "isAzureMonitorTargetEnabled": true
        }
        }
      ]
    }
  ],
  "outputs": {}
}

Negative test num. 3 - bicep file

resource sqlServer1 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: 'sqlServer1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlServer1'
  }
  properties: {
    administratorLogin: 'adminUsername'
    administratorLoginPassword: 'adminPassword'
  }
}

resource sqlServer1_default 'Microsoft.Sql/servers/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'default'
  properties: {
    auditActionsAndGroups: ['DATABASE_LOGOUT_GROUP']
    isAzureMonitorTargetEnabled: true
    isStorageSecondaryKeyInUse: true
    queueDelayMs: 1000
    retentionDays: 100
    state: 'Enabled'
  }
}

resource sqlServer1_sqlDatabase1 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'sqlDatabase1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlDatabase1'
  }
  properties: {
    collation: 'SQL_Latin1_General_CP1_CI_AS'
    edition: 'Basic'
    maxSizeBytes: 1073741824
    requestedServiceObjectiveName: 'Basic'
  }
}

resource sqlServer1_sqlDatabase1_default 'Microsoft.Sql/servers/databases/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1_sqlDatabase1
  name: 'default'
  properties: {
    auditActionsAndGroups: ['DATABASE_LOGOUT_GROUP']
    isAzureMonitorTargetEnabled: true
    isStorageSecondaryKeyInUse: true
    queueDelayMs: 1000
    retentionDays: 100
    state: 'Enabled'
  }
}

Negative test num. 4 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "parameters": {},
  "functions": [],
  "variables": {},
  "resources": [
    {
      "name": "sqlServer1",
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "location": "[resourceGroup().location]",
      "tags": {
        "displayName": "sqlServer1"
      },
      "properties": {
        "administratorLogin": "adminUsername",
        "administratorLoginPassword": "adminPassword"
      }
    },
    {
      "name": "sqlServer1/sqlDatabase1",
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2021-02-01-preview",
      "location": "[resourceGroup().location]",
      "tags": {
        "displayName": "sqlDatabase1"
      },
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
      ],
      "properties": {
        "collation": "SQL_Latin1_General_CP1_CI_AS",
        "edition": "Basic",
        "maxSizeBytes": 1073741824,
        "requestedServiceObjectiveName": "Basic"
      }
    },
    {
      "type": "Microsoft.Sql/servers/databases/auditingSettings",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1/sqlDatabase1/default",
      "properties": {
        "auditActionsAndGroups": [ "DATABASE_LOGOUT_GROUP" ],
        "isAzureMonitorTargetEnabled": true,
        "isStorageSecondaryKeyInUse": true,
        "queueDelayMs": 1000,
        "retentionDays": 100,
        "state": "Enabled"
      }
    },
      {
      "type": "Microsoft.Sql/servers/auditingSettings",
      "apiVersion": "2021-02-01-preview",
      "name": "default",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
      ],
      "properties": {
        "state": "Enabled",
        "isAzureMonitorTargetEnabled": true
      }
      }
  ],
  "outputs": {}
}

Negative test num. 5 - bicep file

resource sqlServer1 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: 'sqlServer1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlServer1'
  }
  properties: {
    administratorLogin: 'adminUsername'
    administratorLoginPassword: 'adminPassword'
  }
}

resource sqlServer1_default 'Microsoft.Sql/servers/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'default'
  properties: {
    auditActionsAndGroups: ['DATABASE_LOGOUT_GROUP']
    isAzureMonitorTargetEnabled: true
    isStorageSecondaryKeyInUse: true
    queueDelayMs: 1000
    retentionDays: 100
    state: 'Enabled'
  }
}

resource sqlServer1_ssqlDatabase1 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'ssqlDatabase1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlDatabase1'
  }
  properties: {
    collation: 'SQL_Latin1_General_CP1_CI_AS'
    edition: 'Basic'
    maxSizeBytes: 107374182
    requestedServiceObjectiveName: 'Basic'
  }
}

resource sqlServer1_ssqlDatabase1_default 'Microsoft.Sql/servers/databases/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1_ssqlDatabase1
  name: 'default'
  properties: {
    auditActionsAndGroups: ['DATABASE_LOGOUT_GROUP']
    isAzureMonitorTargetEnabled: true
    isStorageSecondaryKeyInUse: true
    queueDelayMs: 1000
    retentionDays: 100
    state: 'Enabled'
  }
}

Negative test num. 6 - json file

{
  "properties": {
    "template": {
      "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
      "contentVersion": "1.0.0.0",
      "parameters": {},
      "functions": [],
      "variables": {},
      "resources": [
        {
          "name": "sqlServer1",
          "type": "Microsoft.Sql/servers",
          "apiVersion": "2021-02-01-preview",
          "location": "[resourceGroup().location]",
          "tags": {
            "displayName": "sqlServer1"
          },
          "properties": {
            "administratorLogin": "adminUsername",
            "administratorLoginPassword": "adminPassword"
          },
          "resources": [
            {
              "name": "ssqlDatabase1",
              "type": "databases",
              "apiVersion": "2021-02-01-preview",
              "location": "[resourceGroup().location]",
              "tags": {
                "displayName": "sqlDatabase1"
              },
              "dependsOn": [
                "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
              ],
              "properties": {
                "collation": "SQL_Latin1_General_CP1_CI_AS",
                "edition": "Basic",
                "maxSizeBytes": 107374182,
                "requestedServiceObjectiveName": "Basic"
              },
              "resources": [
                {
                  "type": "auditingSettings",
                  "apiVersion": "2021-02-01-preview",
                  "name": "default",
                  "properties": {
                    "auditActionsAndGroups": [ "DATABASE_LOGOUT_GROUP" ],
                    "isAzureMonitorTargetEnabled": true,
                    "isStorageSecondaryKeyInUse": true,
                    "queueDelayMs": 1000,
                    "retentionDays": 100,
                    "state": "Enabled"
                  }
                }
              ]
            },
        {
        "type": "Microsoft.Sql/servers/auditingSettings",
        "apiVersion": "2021-02-01-preview",
        "name": "default",
        "dependsOn": [
          "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
        ],
        "properties": {
          "state": "Enabled",
          "isAzureMonitorTargetEnabled": true
        }
        }
          ]
        }
      ],
      "outputs": {}
    },
    "parameters": {}
  },
  "kind": "template",
  "type": "Microsoft.Blueprint/blueprints/artifacts",
  "name": "myTemplate"
}

Negative test num. 7 - bicep file

resource sqlServer1 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: 'sqlServer1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlServer1'
  }
  properties: {
    administratorLogin: 'adminUsername'
    administratorLoginPassword: 'adminPassword'
  }
}

resource sqlServer1_default 'Microsoft.Sql/servers/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'default'
  properties: {
    auditActionsAndGroups: ['DATABASE_LOGOUT_GROUP']
    isAzureMonitorTargetEnabled: true
    isStorageSecondaryKeyInUse: true
    queueDelayMs: 1000
    retentionDays: 100
    state: 'Enabled'
  }
}

resource sqlServer1_sqlDatabase1 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'sqlDatabase1'
  location: resourceGroup().location
  tags: {
    displayName: 'sqlDatabase1'
  }
  properties: {
    collation: 'SQL_Latin1_General_CP1_CI_AS'
    edition: 'Basic'
    maxSizeBytes: 1073741824
    requestedServiceObjectiveName: 'Basic'
  }
}

resource sqlServer1_sqlDatabase1_default 'Microsoft.Sql/servers/databases/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1_sqlDatabase1
  name: 'default'
  properties: {
    auditActionsAndGroups: ['DATABASE_LOGOUT_GROUP']
    isAzureMonitorTargetEnabled: true
    isStorageSecondaryKeyInUse: true
    queueDelayMs: 1000
    retentionDays: 100
    state: 'Enabled'
  }
}

Negative test num. 8 - json file

{
  "properties": {
    "template": {
      "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
      "contentVersion": "1.0.0.0",
      "parameters": {},
      "functions": [],
      "variables": {},
      "resources": [
        {
          "name": "sqlServer1",
          "type": "Microsoft.Sql/servers",
          "apiVersion": "2021-02-01-preview",
          "location": "[resourceGroup().location]",
          "tags": {
            "displayName": "sqlServer1"
          },
          "properties": {
            "administratorLogin": "adminUsername",
            "administratorLoginPassword": "adminPassword"
          }
        },
        {
          "name": "sqlServer1/sqlDatabase1",
          "type": "Microsoft.Sql/servers/databases",
          "apiVersion": "2021-02-01-preview",
          "location": "[resourceGroup().location]",
          "tags": {
            "displayName": "sqlDatabase1"
          },
          "dependsOn": [
            "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
          ],
          "properties": {
            "collation": "SQL_Latin1_General_CP1_CI_AS",
            "edition": "Basic",
            "maxSizeBytes": 1073741824,
            "requestedServiceObjectiveName": "Basic"
          }
        },
        {
          "type": "Microsoft.Sql/servers/databases/auditingSettings",
          "apiVersion": "2021-02-01-preview",
          "name": "sqlServer1/sqlDatabase1/default",
          "properties": {
            "auditActionsAndGroups": [ "DATABASE_LOGOUT_GROUP" ],
            "isAzureMonitorTargetEnabled": true,
            "isStorageSecondaryKeyInUse": true,
            "queueDelayMs": 1000,
            "retentionDays": 100,
            "state": "Enabled"
          }
        },
        {
        "type": "Microsoft.Sql/servers/auditingSettings",
        "apiVersion": "2021-02-01-preview",
        "name": "default",
        "dependsOn": [
          "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
        ],
        "properties": {
          "state": "Enabled",
          "isAzureMonitorTargetEnabled": true
        }
        }
      ],
      "outputs": {}
    },
    "parameters": {}
  },
  "kind": "template",
  "type": "Microsoft.Blueprint/blueprints/artifacts",
  "name": "myTemplate"
}