SQL Server Database With Unrecommended Retention Days

• Query id: c09cdac2-7670-458a-bf6c-efad6880973a
• Query name: SQL Server Database With Unrecommended Retention Days
• Platform: AzureResourceManager
• Severity: Low
• Category: Observability
• CWE: 778
• URL: Github

Description

SQL Server Database Auditing Settings should keep the audit logs in the storage account for at least 90 days
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_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: 50
    state: 'Enabled'
    dependsOn: [sqlServer1_sqlDatabase1.id]
  }
}

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"
          },
          "resources": [
            {
              "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": 50,
                "state": "Enabled",
                "dependsOn": [
                  "[resourceId('Microsoft.Sql/servers/databases', 'sqlServer1/sqlDatabase1')]"
                ]
              }
            }
          ]
        }
      ]
    }
  ],
  "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_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
    state: 'Enabled'
    dependsOn: [sqlServer1_sqlDatabase1.id]
  }
}

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"
      },
      "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"
          },
          "resources": [
            {
              "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,
                "state": "Enabled",
                "dependsOn": [
                  "[resourceId('Microsoft.Sql/servers/databases', 'sqlServer1/sqlDatabase1')]"
                ]
              }
            }
          ]
        }
      ]
    }
  ],
  "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_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: 50
    state: 'Enabled'
    dependsOn: [sqlServer1_sqlDatabase1.id]
  }
}

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"
              },
              "resources": [
                {
                  "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": 50,
                    "state": "Enabled",
                    "dependsOn": [
                      "[resourceId('Microsoft.Sql/servers/databases', 'sqlServer1/sqlDatabase1')]"
                    ]
                  }
                }
              ]
            }
          ]
        }
      ],
      "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_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
    state: 'Enabled'
    dependsOn: [sqlServer1_sqlDatabase1.id]
  }
}

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"
          },
          "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"
              },
              "resources": [
                {
                  "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,
                    "state": "Enabled",
                    "dependsOn": [
                      "[resourceId('Microsoft.Sql/servers/databases', 'sqlServer1/sqlDatabase1')]"
                    ]
                  }
                }
              ]
            }
          ]
        }
      ],
      "outputs": {}
    },
    "parameters": {}
  },
  "kind": "template",
  "type": "Microsoft.Blueprint/blueprints/artifacts",
  "name": "myTemplate"
}

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_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'
    dependsOn: [sqlServer1_sqlDatabase1.id]
  }
}

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": "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"
          },
          "resources": [
            {
              "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",
                "dependsOn": [
                  "[resourceId('Microsoft.Sql/servers/databases', 'sqlServer1/sqlDatabase1')]"
                ]
              }
            }
          ]
        }
      ]
    }
  ],
  "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_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'
    dependsOn: [sqlServer1_sqlDatabase1.id]
  }
}

Negative test num. 4 - 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"
              },
              "resources": [
                {
                  "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",
                    "dependsOn": [
                      "[resourceId('Microsoft.Sql/servers/databases', 'sqlServer1/sqlDatabase1')]"
                    ]
                  }
                }
              ]
            }
          ]
        }
      ],
      "outputs": {}
    },
    "parameters": {}
  },
  "kind": "template",
  "type": "Microsoft.Blueprint/blueprints/artifacts",
  "name": "myTemplate"
}