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
}

resource sqlServer1_sqlDatabase1 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'sqlDatabase1'
  location: resourceGroup().location
  properties: {}
}

Positive test num. 2 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1",
      "location": "[resourceGroup().location]",
      "properties": {},
      "resources": [
        {
          "type": "auditingSettings",
          "apiVersion": "2021-02-01-preview",
          "name": "default_1",
          "properties": {
            "state": "Disabled"
          }
        },
        {
          "type": "databases",
          "apiVersion": "2021-02-01-preview",
          "name": "sqlDatabase1",
          "location": "[resourceGroup().location]",
          "properties": {},
          "resources": [
            {
              "type": "auditingSettings",
              "apiVersion": "2021-02-01-preview",
              "name": "default_2",
              "properties": {
                "state": "Enabled"
              }
            }
          ]
        }
      ]
    }
  ]
}

Positive test num. 3 - bicep file

resource sqlServer1 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: 'sqlServer1'
  location: resourceGroup().location
}

resource sqlServer1_default 'Microsoft.Sql/servers/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'default_1'
  properties: {
    state: 'Disabled'
  }
}

resource sqlServer1_sqlDatabase1 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'sqlDatabase1'
  location: resourceGroup().location
  properties: {}
}

resource sqlServer1_sqlDatabase1_default 'Microsoft.Sql/servers/databases/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1_sqlDatabase1
  name: 'default_2'
  properties: {
    state: 'Disabled'
  }
}

Positive test num. 4 - bicep file

resource sqlServer1 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: 'sqlServer1'
  location: resourceGroup().location
}

resource sqlServer1_default 'Microsoft.Sql/servers/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'default_1'
  properties: {
    state: 'Disabled'
  }
}

resource sqlServer1_sqlDatabase1 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'sqlDatabase1'
  location: resourceGroup().location
  properties: {}
}

resource sqlServer1_sqlDatabase1_default 'Microsoft.Sql/servers/databases/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1_sqlDatabase1
  name: 'default_2'
  properties: {
    state: 'Enabled'
  }
}

Positive test num. 5 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1",
      "location": "[resourceGroup().location]",
      "properties": {}
    },
    {
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1/sqlDatabase1",
      "location": "[resourceGroup().location]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
      ],
      "properties": {}
    }
  ]
}

Positive test num. 6 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1",
      "location": "[resourceGroup().location]",
      "properties": {},
      "resources": [
        {
          "type": "databases",
          "apiVersion": "2021-02-01-preview",
          "name": "sqlDatabase1",
          "location": "[resourceGroup().location]",
          "properties": {}
        }
      ]
    }
  ]
}

Positive test num. 7 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1",
      "location": "[resourceGroup().location]",
      "properties": {}
    },
    {
      "type": "Microsoft.Sql/servers/auditingSettings",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1/default_1",
      "properties": {
        "state": "Disabled"
      }
    },
    {
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1/sqlDatabase1",
      "location": "[resourceGroup().location]",
      "properties": {}
    },
    {
      "type": "Microsoft.Sql/servers/databases/auditingSettings",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1/sqlDatabase1/default_2",
      "properties": {
        "state": "Disabled"
      }
    }
  ]
}

Positive test num. 8 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1",
      "location": "[resourceGroup().location]",
      "properties": {},
      "resources": [
        {
          "type": "auditingSettings",
          "apiVersion": "2021-02-01-preview",
          "name": "default_1",
          "properties": {
            "state": "Disabled"
          }
        },
        {
          "type": "databases",
          "apiVersion": "2021-02-01-preview",
          "name": "sqlDatabase1",
          "location": "[resourceGroup().location]",
          "properties": {},
          "resources": [
            {
              "type": "auditingSettings",
              "apiVersion": "2021-02-01-preview",
              "name": "default_2",
              "properties": {
                "state": "Disabled"
              }
            }
          ]
        }
      ]
    }
  ]
}

Positive test num. 9 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1",
      "location": "[resourceGroup().location]",
      "properties": {}
    },
    {
      "type": "Microsoft.Sql/servers/auditingSettings",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1/default_1",
      "properties": {
        "state": "Disabled"
      }
    },
    {
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1/sqlDatabase1",
      "location": "[resourceGroup().location]",
      "properties": {}
    },
    {
      "type": "Microsoft.Sql/servers/databases/auditingSettings",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1/sqlDatabase1/default_2",
      "properties": {
        "state": "Enabled"
      }
    }
  ]
}

Code samples without security vulnerabilities

Negative test num. 1 - bicep file

resource sql_server 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: 'sql_server'
  location: resourceGroup().location
  properties: {}
}

resource sql_server_auditing_settings 'Microsoft.Sql/servers/auditingSettings@2021-02-01-preview' = {
  parent: sql_server
  name: 'default_1'
  properties: {
    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",
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2023-02-01-preview",
      "name": "sql_server",
      "location": "[resourceGroup().location]",
      "properties": {}
    },
    {
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2024-11-01-preview",
      "name": "sql_server/sql_databases",
      "location": "[resourceGroup().location]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', 'sql_server')]"
      ],
      "properties": {}
    },
    {
      "type": "Microsoft.Sql/servers/auditingSettings",
      "apiVersion": "2024-11-01-preview",
      "name": "sql_server/default",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', 'sql_server')]"
      ],
      "properties": {
        "state": "Enabled"
      }
    }
  ]
}

Negative test num. 3 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2023-02-01-preview",
      "name": "sql_server",
      "location": "[resourceGroup().location]",
      "properties": {},
      "resources": [
        {
          "type": "databases",
          "apiVersion": "2024-11-01-preview",
          "name": "sql_databases",
          "location": "[resourceGroup().location]",
          "properties": {}
        },
        {
          "type": "auditingSettings",
          "apiVersion": "2024-11-01-preview",
          "name": "default",
          "properties": {
            "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",
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1",
      "location": "[resourceGroup().location]",
      "properties": {}
    },
    {
      "type": "Microsoft.Sql/servers/auditingSettings",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1/default_1",
      "properties": {
        "state": "Enabled"
      }
    },
    {
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1/sqlDatabase1",
      "location": "[resourceGroup().location]",
      "properties": {}
    },
    {
      "type": "Microsoft.Sql/servers/databases/auditingSettings",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1/sqlDatabase1/default_2",
      "properties": {
        "state": "Disabled"
      }
    }
  ]
}

Negative test num. 5 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "sqlServer1",
      "location": "[resourceGroup().location]",
      "properties": {},
      "resources": [
        {
          "type": "auditingSettings",
          "apiVersion": "2021-02-01-preview",
          "name": "default_1",
          "properties": {
            "state": "Enabled"
          }
        },
        {
          "type": "databases",
          "apiVersion": "2021-02-01-preview",
          "name": "sqlDatabase1",
          "location": "[resourceGroup().location]",
          "properties": {},
          "resources": [
            {
              "type": "auditingSettings",
              "apiVersion": "2021-02-01-preview",
              "name": "default_2",
              "properties": {
                "state": "Disabled"
              }
            }
          ]
        }
      ]
    }
  ]
}

Negative test num. 6 - bicep file

resource sql_databases 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  parent: sql_server
  name: 'sql_databases'
  location: resourceGroup().location
  properties: {}
}

resource sql_databases_auditing_settings 'Microsoft.Sql/servers/databases/auditingSettings@2021-02-01-preview' = {
  parent: sql_databases
  name: 'default_2'
  properties: {
    state: 'Enabled'
  }
}

Negative test num. 7 - bicep file

resource sql_server 'Microsoft.Sql/servers@2023-02-01-preview' = {
  name: 'sql_server'
  location: resourceGroup().location
  properties: {}
}

resource sql_databases 'Microsoft.Sql/servers/databases@2024-11-01-preview' = {
  parent: sql_server
  name: 'sql_databases'
  location: resourceGroup().location
  properties: {}
}

resource sql_server_auditing_settings 'Microsoft.Sql/servers/auditingSettings@2024-11-01-preview' = {
  parent: sql_server
  name: 'default'
  properties: {
    state: 'Enabled'
  }
}

Negative test num. 8 - bicep file

resource sqlServer1 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: 'sqlServer1'
  location: resourceGroup().location
}

resource sqlServer1_default 'Microsoft.Sql/servers/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'default_1'
  properties: {
    state: 'Enabled'
  }
}

resource sqlServer1_sqlDatabase1 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  parent: sqlServer1
  name: 'sqlDatabase1'
  location: resourceGroup().location
  properties: {}
}

resource sqlServer1_sqlDatabase1_default 'Microsoft.Sql/servers/databases/auditingSettings@2021-02-01-preview' = {
  parent: sqlServer1_sqlDatabase1
  name: 'default_2'
  properties: {
    state: 'Disabled'
  }
}

Negative test num. 9 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "sql_server",
      "location": "[resourceGroup().location]",
      "properties": {}
    },
    {
      "type": "Microsoft.Sql/servers/auditingSettings",
      "apiVersion": "2021-02-01-preview",
      "name": "sql_server/default_1",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', 'sql_server')]"
      ],
      "properties": {
        "state": "Enabled"
      }
    }
  ]
}

Negative test num. 10 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "sql_server",
      "location": "[resourceGroup().location]",
      "properties": {},
      "resources": [
        {
          "type": "auditingSettings",
          "apiVersion": "2021-02-01-preview",
          "name": "default_1",
          "properties": {
            "state": "Enabled"
          }
        }
      ]
    }
  ]
}

Negative test num. 11 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2021-02-01-preview",
      "name": "sql_server/sql_databases",
      "location": "[resourceGroup().location]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', 'sql_server')]"
      ],
      "properties": {}
    },
    {
      "type": "Microsoft.Sql/servers/databases/auditingSettings",
      "apiVersion": "2021-02-01-preview",
      "name": "sql_server/sql_databases/default_2",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers/databases', 'sql_server', 'sql_databases')]"
      ],
      "properties": {
        "state": "Enabled"
      }
    }
  ]
}

Negative test num. 12 - json file

{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "resources": [
    {
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2021-02-01-preview",
      "name": "sql_server/sql_databases",
      "location": "[resourceGroup().location]",
      "dependsOn": [
        "[resourceId('Microsoft.Sql/servers', 'sql_server')]"
      ],
      "properties": {},
      "resources": [
        {
          "type": "auditingSettings",
          "apiVersion": "2021-02-01-preview",
          "name": "default_2",
          "properties": {
            "state": "Enabled"
          }
        }
      ]
    }
  ]
}