SQL Server Database With Alerts Disabled

  • Query id: 574e8d82-1db2-4b9c-b526-e320ede9a9ff
  • Query name: SQL Server Database With Alerts Disabled
  • Platform: AzureResourceManager
  • Severity: Medium
  • Category: Best Practices
  • CWE: 778
  • Risk score: 5.1
  • URL: Github

Description

All Alerts should be enabled in SQL Database Server SecurityAlerts Policy Properties
Documentation

Code samples

Code samples with security vulnerabilities

Positive test num. 1 - bicep file
resource sample_databases_default 'Microsoft.Sql/servers/databases/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sample/databases/default'
  properties: {
    disabledAlerts: ['Sql_Injection']
    emailAccountAdmins: true
    emailAddresses: ['sample@email.com']
    retentionDays: 4
    state: 'Enabled'
  }
}
Positive test num. 2 - json file
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "2.0.0.0",
  "apiProfile": "2019-03-01-hybrid",
  "parameters": {},
  "variables": {},
  "functions": [],
  "resources": [
    {
      "type": "Microsoft.Sql/servers/databases/securityAlertPolicies",
      "apiVersion": "2021-02-01-preview",
      "name": "sample/databases/default",
      "properties": {
        "disabledAlerts": [ "Sql_Injection" ],
        "emailAccountAdmins": true,
        "emailAddresses": [ "sample@email.com" ],
        "retentionDays": 4,
        "state": "Enabled"
      }
    }
  ],
  "outputs": {}
}
Positive test num. 3 - bicep file
resource sample_databases_default 'Microsoft.Sql/servers/databases/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sample/databases/default'
  properties: {
    disabledAlerts: ['Sql_Injection']
    emailAccountAdmins: true
    emailAddresses: ['sample@email.com']
    retentionDays: 4
    state: 'Enabled'
  }
}

Positive test num. 4 - json file 
{
  "properties": {
    "template": {
      "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
      "contentVersion": "2.0.0.0",
      "apiProfile": "2019-03-01-hybrid",
      "parameters": {},
      "variables": {},
      "functions": [],
      "resources": [
        {
          "type": "Microsoft.Sql/servers/databases/securityAlertPolicies",
          "apiVersion": "2021-02-01-preview",
          "name": "sample/databases/default",
          "properties": {
            "disabledAlerts": [ "Sql_Injection" ],
            "emailAccountAdmins": true,
            "emailAddresses": [ "sample@email.com" ],
            "retentionDays": 4,
            "state": "Enabled"
          }
        }
      ],
      "outputs": {}
    },
    "parameters": {}
  },
  "kind": "template",
  "type": "Microsoft.Blueprint/blueprints/artifacts",
  "name": "myTemplate"
}
Positive test num. 5 - bicep file 
resource sample_databases_default 'Microsoft.Sql/servers/databases/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sample/databases/default'
  properties: {
    emailAccountAdmins: true
    emailAddresses: ['sample@email.com']
    retentionDays: 4
    state: 'Disabled'
  }
}
Positive test num. 6 - json file 
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "2.0.0.0",
  "apiProfile": "2019-03-01-hybrid",
  "parameters": {},
  "variables": {},
  "functions": [],
  "resources": [
    {
      "type": "Microsoft.Sql/servers/databases/securityAlertPolicies",
      "apiVersion": "2021-02-01-preview",
      "name": "sample/databases/default",
      "properties": {
        "emailAccountAdmins": true,
        "emailAddresses": [ "sample@email.com" ],
        "retentionDays": 4,
        "state": "Disabled"
      }
    }
  ],
  "outputs": {}
}
Positive test num. 7 - bicep file 
resource sample_databases_default 'Microsoft.Sql/servers/databases/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sample/databases/default'
  properties: {
    emailAccountAdmins: true
    emailAddresses: ['sample@email.com']
    retentionDays: 4
    state: 'Disabled'
  }
}
Positive test num. 8 - json file 
{
  "properties": {
    "template": {
      "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
      "contentVersion": "2.0.0.0",
      "apiProfile": "2019-03-01-hybrid",
      "parameters": {},
      "variables": {},
      "functions": [],
      "resources": [
        {
          "type": "Microsoft.Sql/servers/databases/securityAlertPolicies",
          "apiVersion": "2021-02-01-preview",
          "name": "sample/databases/default",
          "properties": {
            "emailAccountAdmins": true,
            "emailAddresses": [ "sample@email.com" ],
            "retentionDays": 4,
            "state": "Disabled"
          }
        }
      ],
      "outputs": {}
    },
    "parameters": {}
  },
  "kind": "template",
  "type": "Microsoft.Blueprint/blueprints/artifacts",
  "name": "myTemplate"
}
Positive test num. 9 - bicep file 
resource sqlDatabase 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  name: 'sample/default'
  location: resourceGroup().location
  properties: {
    collation: 'SQL_Latin1_General_CP1_CI_AS'
    maxSizeBytes: 2147483648
  }
}
Positive test num. 10 - json file 
{
  "properties": {
    "template": {
      "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
      "contentVersion": "2.0.0.0",
      "apiProfile": "2019-03-01-hybrid",
      "parameters": {},
      "variables": {},
      "functions": [],
      "resources": [
        {
          "type": "Microsoft.Sql/servers/databases",
          "apiVersion": "2021-02-01-preview",
          "name": "sample/default",
          "location": "[resourceGroup().location]",
          "properties": {
            "collation": "SQL_Latin1_General_CP1_CI_AS",
            "maxSizeBytes": 2147483648
          }
        }
      ],
      "outputs": {}
    },
    "parameters": {}
  },
  "kind": "template",
  "type": "Microsoft.Blueprint/blueprints/artifacts",
  "name": "myTemplate"
}
Positive test num. 11 - bicep file 
resource sample_server_securityAlert 'Microsoft.Sql/servers/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sampleServer/default'
  properties: {
    disabledAlerts: ['Sql_Injection']
    emailAccountAdmins: true
    emailAddresses: ['sample@email.com']
    retentionDays: 4
    state: 'Enabled'
  }
}
Positive test num. 12 - json file 
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "metadata": {
    "_generator": {
      "name": "bicep",
      "version": "0.37.4.10188",
      "templateHash": "9432867529760988896"
    }
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers/securityAlertPolicies",
      "apiVersion": "2021-02-01-preview",
      "name": "sampleServer/default",
      "properties": {
        "disabledAlerts": ["Sql_Injection"],
        "emailAccountAdmins": true,
        "emailAddresses": [
          "sample@email.com"
        ],
        "retentionDays": 4,
        "state": "Enabled"
      }
    }
  ]
}
Positive test num. 13 - bicep file 
resource sample_server_securityAlert 'Microsoft.Sql/servers/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sampleServer/default'
  properties: {
    disabledAlerts: ['Sql_Injection']
    emailAccountAdmins: true
    emailAddresses: ['sample@email.com']
    retentionDays: 4
    state: 'Disabled'
  }
}
Positive test num. 14 - json file 
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "metadata": {
    "_generator": {
      "name": "bicep",
      "version": "0.37.4.10188",
      "templateHash": "6052430267505263681"
    }
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers/securityAlertPolicies",
      "apiVersion": "2021-02-01-preview",
      "name": "sampleServer/default",
      "properties": {
        "disabledAlerts": ["Sql_Injection"],
        "emailAccountAdmins": true,
        "emailAddresses": [
          "sample@email.com"
        ],
        "retentionDays": 4,
        "state": "Disabled"
      }
    }
  ]
}
Positive test num. 15 - bicep file 
resource sqlServer 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: 'sample'
  location: resourceGroup().location
  properties: {
    administratorLogin: 'sqladminuser'
    administratorLoginPassword: 'P@ssw0rd123!' 
    minimalTlsVersion: '1.2'
    publicNetworkAccess: 'Enabled'
  }
}
Positive test num. 16 - json file 
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "metadata": {
    "_generator": {
      "name": "bicep",
      "version": "0.37.4.10188",
      "templateHash": "15083796068864284852"
    }
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "sample",
      "location": "[resourceGroup().location]",
      "properties": {
        "administratorLogin": "sqladminuser",
        "administratorLoginPassword": "P@ssw0rd123!",
        "minimalTlsVersion": "1.2",
        "publicNetworkAccess": "Enabled"
      }
    }
  ]
}

Code samples without security vulnerabilities

Negative test num. 1 - bicep file
resource sample_databases_default 'Microsoft.Sql/servers/databases/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sample/databases/default'
  properties: {
    disabledAlerts: []
    emailAccountAdmins: true
    emailAddresses: ['sample@email.com']
    retentionDays: 4
    state: 'Enabled'
  }
}
Negative test num. 2 - json file
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "2.0.0.0",
  "apiProfile": "2019-03-01-hybrid",
  "parameters": {},
  "variables": {},
  "functions": [],
  "resources": [
    {
      "type": "Microsoft.Sql/servers/databases/securityAlertPolicies",
      "apiVersion": "2021-02-01-preview",
      "name": "sample/databases/default",
      "properties": {
        "disabledAlerts": [],
        "emailAccountAdmins": true,
        "emailAddresses": [ "sample@email.com" ],
        "retentionDays": 4,
        "state": "Enabled"
      }
    }
  ],
  "outputs": {}
}
Negative test num. 3 - bicep file
resource sample_server_securityAlert 'Microsoft.Sql/servers/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sampleServer/default'
  properties: {
    state: 'Enabled'
    disabledAlerts: []
    emailAccountAdmins: true
  }
}

Negative test num. 4 - json file 
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "metadata": {
    "_generator": {
      "name": "bicep",
      "version": "0.37.4.10188",
      "templateHash": "8371526641644790449"
    }
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers/securityAlertPolicies",
      "apiVersion": "2021-02-01-preview",
      "name": "sampleServer/Default",
      "properties": {
        "state": "Enabled",
        "disabledAlerts": [],
        "emailAccountAdmins": true
      }
    }
  ]
}
Negative test num. 5 - bicep file 
resource sample_databases_default 'Microsoft.Sql/servers/databases/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sample/databases/default'
  properties: {
    emailAccountAdmins: true
    emailAddresses: ['sample@email.com']
    retentionDays: 4
    state: 'Enabled'
  }
}
Negative test num. 6 - json file 
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "2.0.0.0",
  "apiProfile": "2019-03-01-hybrid",
  "parameters": {},
  "variables": {},
  "functions": [],
  "resources": [
    {
      "type": "Microsoft.Sql/servers/databases/securityAlertPolicies",
      "apiVersion": "2021-02-01-preview",
      "name": "sample/databases/default",
      "properties": {
        "emailAccountAdmins": true,
        "emailAddresses": [ "sample@email.com" ],
        "retentionDays": 4,
        "state": "Enabled"
      }
    }
  ],
  "outputs": {}
}
Negative test num. 7 - bicep file 
resource sample_databases_default 'Microsoft.Sql/servers/databases/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sample/databases/default'
  properties: {
    disabledAlerts: []
    emailAccountAdmins: true
    emailAddresses: ['sample@email.com']
    retentionDays: 4
    state: 'Enabled'
  }
}
Negative test num. 8 - json file 
{
  "properties": {
    "template": {
      "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
      "contentVersion": "2.0.0.0",
      "apiProfile": "2019-03-01-hybrid",
      "parameters": {},
      "variables": {},
      "functions": [],
      "resources": [
        {
          "type": "Microsoft.Sql/servers/databases/securityAlertPolicies",
          "apiVersion": "2021-02-01-preview",
          "name": "sample/databases/default",
          "properties": {
            "disabledAlerts": [],
            "emailAccountAdmins": true,
            "emailAddresses": [ "sample@email.com" ],
            "retentionDays": 4,
            "state": "Enabled"
          }
        }
      ],
      "outputs": {}
    },
    "parameters": {}
  },
  "kind": "template",
  "type": "Microsoft.Blueprint/blueprints/artifacts",
  "name": "myTemplate"
}
Negative test num. 9 - bicep file 
resource sample_databases_default 'Microsoft.Sql/servers/databases/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sample/databases/default'
  properties: {
    emailAccountAdmins: true
    emailAddresses: ['sample@email.com']
    retentionDays: 4
    state: 'Enabled'
  }
}
Negative test num. 10 - json file 
{
  "properties": {
    "template": {
      "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
      "contentVersion": "2.0.0.0",
      "apiProfile": "2019-03-01-hybrid",
      "parameters": {},
      "variables": {},
      "functions": [],
      "resources": [
        {
          "type": "Microsoft.Sql/servers/databases/securityAlertPolicies",
          "apiVersion": "2021-02-01-preview",
          "name": "sample/databases/default",
          "properties": {
            "emailAccountAdmins": true,
            "emailAddresses": [ "sample@email.com" ],
            "retentionDays": 4,
            "state": "Enabled"
          }
        }
      ],
      "outputs": {}
    },
    "parameters": {}
  },
  "kind": "template",
  "type": "Microsoft.Blueprint/blueprints/artifacts",
  "name": "myTemplate"
}
Negative test num. 11 - bicep file 
resource sample_database 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  name: 'sampleServer/sampleDatabase'
  location: 'Sample'
  properties: {
    sampleName: 'AdventureWorksLT'
  }
}

resource sample_databases_default 'Microsoft.Sql/servers/databases/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sample/databases/default'
  location: 'Sample'
  properties: {
    disabledAlerts: []
    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",
  "metadata": {
    "_generator": {
      "name": "bicep",
      "version": "0.37.4.10188",
      "templateHash": "6537157491087265147"
    }
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2021-02-01-preview",
      "name": "sampleServer/sampleDatabase",
      "location": "Sample",
      "properties": {
        "sampleName": "AdventureWorksLT"
      }
    },
    {
      "type": "Microsoft.Sql/servers/databases/securityAlertPolicies",
      "apiVersion": "2021-02-01-preview",
      "name": "sample/databases/Default",
      "location": "Sample",
      "properties": {
        "disabledAlerts": [],
        "state": "Enabled"
      }
    }
  ]
}
Negative test num. 13 - bicep file 
resource sample_database 'Microsoft.Sql/servers/databases@2021-02-01-preview' = {
  name: 'sampleServer/sampleDatabase'
  location: 'Sample'
  properties: {
    sampleName: 'AdventureWorksLT'
  }
}

resource sample_databases_default 'Microsoft.Sql/servers/databases/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sample/databases/default'
  location: 'Sample'
  properties: {
    state: 'Enabled'
}}
Negative test num. 14 - json file 
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "metadata": {
    "_generator": {
      "name": "bicep",
      "version": "0.37.4.10188",
      "templateHash": "3186770193589075684"
    }
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers/databases",
      "apiVersion": "2021-02-01-preview",
      "name": "sampleServer/sampleDatabase",
      "location": "Sample",
      "properties": {
        "sampleName": "AdventureWorksLT"
      }
    },
    {
      "type": "Microsoft.Sql/servers/databases/securityAlertPolicies",
      "apiVersion": "2021-02-01-preview",
      "name": "sample/databases/Default",
      "location": "Sample",
      "properties": {
        "state": "Enabled"
      }
    }
  ]
}
Negative test num. 15 - bicep file 
resource sample_server 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: 'sampleServer'
  location: resourceGroup().location
  properties: {
    administratorLogin: 'sqladminuser'
    administratorLoginPassword: 'P@ssw0rd123!' 
  }
}

resource sample_server_securityAlert 'Microsoft.Sql/servers/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sampleServer/default'
  properties: {
    state: 'Enabled'
    disabledAlerts: []
    emailAccountAdmins: true
  }
}
Negative test num. 16 - json file 
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "metadata": {
    "_generator": {
      "name": "bicep",
      "version": "0.37.4.10188",
      "templateHash": "7042966288860294010"
    }
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "sampleServer",
      "location": "[resourceGroup().location]",
      "properties": {
        "administratorLogin": "sqladminuser",
        "administratorLoginPassword": "P@ssw0rd123!"
      }
    },
    {
      "type": "Microsoft.Sql/servers/securityAlertPolicies",
      "apiVersion": "2021-02-01-preview",
      "name": "sampleServer/Default",
      "properties": {
        "state": "Enabled",
        "disabledAlerts": [],
        "emailAccountAdmins": true
      }
    }
  ]
}
Negative test num. 17 - bicep file 
resource sample_server 'Microsoft.Sql/servers@2021-02-01-preview' = {
  name: 'sampleServer'
  location: resourceGroup().location
  properties: {
    administratorLogin: 'sqladminuser'
    administratorLoginPassword: 'P@ssw0rd123!' 
  }
}

resource sample_server_securityAlert 'Microsoft.Sql/servers/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sampleServer/default'
  properties: {
    state: 'Enabled'
    emailAccountAdmins: true
  }
}
Negative test num. 18 - json file 
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "metadata": {
    "_generator": {
      "name": "bicep",
      "version": "0.37.4.10188",
      "templateHash": "6333281479513473266"
    }
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers",
      "apiVersion": "2021-02-01-preview",
      "name": "sampleServer",
      "location": "[resourceGroup().location]",
      "properties": {
        "administratorLogin": "sqladminuser",
        "administratorLoginPassword": "P@ssw0rd123!"
      }
    },
    {
      "type": "Microsoft.Sql/servers/securityAlertPolicies",
      "apiVersion": "2021-02-01-preview",
      "name": "sampleServer/Default",
      "properties": {
        "state": "Enabled",
        "emailAccountAdmins": true
      }
    }
  ]
}
Negative test num. 19 - bicep file 
resource sample_server_securityAlert 'Microsoft.Sql/servers/securityAlertPolicies@2021-02-01-preview' = {
  name: 'sampleServer/default'
  properties: {
    state: 'Enabled'
    emailAccountAdmins: true
  }
}
Negative test num. 20 - json file 
{
  "$schema": "https://schema.management.azure.com/schemas/2019-04-01/deploymentTemplate.json#",
  "contentVersion": "1.0.0.0",
  "metadata": {
    "_generator": {
      "name": "bicep",
      "version": "0.37.4.10188",
      "templateHash": "4912734550592086719"
    }
  },
  "resources": [
    {
      "type": "Microsoft.Sql/servers/securityAlertPolicies",
      "apiVersion": "2021-02-01-preview",
      "name": "sampleServer/Default",
      "properties": {
        "state": "Enabled",
        "emailAccountAdmins": true
      }
    }
  ]
}