SQL Alert Policy Without Emails
- Query id: 89b79fe5-49bd-4d39-84ce-55f5fc6f7764
- Query name: SQL Alert Policy Without Emails
- Platform: AzureResourceManager
- Severity: Info
- Category: Best Practices
- URL: Github
Description¶
SQL Database Server should contain emails to be notified in the event of a Security Alert
Documentation
Code samples¶
Code samples with security vulnerabilities¶
Postitive test num. 1 - 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": "2014-04-01",
"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/securityAlertPolicies",
"apiVersion": "2021-02-01-preview",
"name": "sqlServer1/sqlDatabase1/securityPolicy1",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
],
"properties": {
"emailAccountAdmins": true,
"retentionDays": 4,
"state": "Enabled"
}
}
]
}
],
"outputs": {}
}
Postitive 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": "2014-04-01",
"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/securityAlertPolicies",
"apiVersion": "2021-02-01-preview",
"name": "sqlServer1/sqlDatabase1/securityPolicy1",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
],
"properties": {
"emailAccountAdmins": true,
"emailAddresses": [],
"retentionDays": 4,
"state": "Enabled"
}
}
]
}
],
"outputs": {}
}
Postitive test num. 3 - 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": "2014-04-01",
"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/securityAlertPolicies",
"apiVersion": "2021-02-01-preview",
"name": "sqlServer1/sqlDatabase1/securityPolicy1",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
],
"properties": {
"emailAccountAdmins": true,
"emailAddresses": [ "", "" ],
"retentionDays": 4,
"state": "Enabled"
}
}
]
}
],
"outputs": {}
}
Postitive 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": "2014-04-01",
"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/securityAlertPolicies",
"apiVersion": "2021-02-01-preview",
"name": "sqlServer1/sqlDatabase1/securityPolicy1",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
],
"properties": {
"emailAccountAdmins": true,
"retentionDays": 4,
"state": "Enabled"
}
}
]
}
],
"outputs": {}
},
"parameters": {}
},
"kind": "template",
"type": "Microsoft.Blueprint/blueprints/artifacts",
"name": "myTemplate"
}
Postitive test num. 5 - 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": "2014-04-01",
"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/securityAlertPolicies",
"apiVersion": "2021-02-01-preview",
"name": "sqlServer1/sqlDatabase1/securityPolicy1",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
],
"properties": {
"emailAccountAdmins": true,
"emailAddresses": [],
"retentionDays": 4,
"state": "Enabled"
}
}
]
}
],
"outputs": {}
},
"parameters": {}
},
"kind": "template",
"type": "Microsoft.Blueprint/blueprints/artifacts",
"name": "myTemplate"
}
Postitive 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": "2014-04-01",
"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/securityAlertPolicies",
"apiVersion": "2021-02-01-preview",
"name": "sqlServer1/sqlDatabase1/securityPolicy1",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
],
"properties": {
"emailAccountAdmins": true,
"emailAddresses": [ "", "" ],
"retentionDays": 4,
"state": "Enabled"
}
}
]
}
],
"outputs": {}
},
"parameters": {}
},
"kind": "template",
"type": "Microsoft.Blueprint/blueprints/artifacts",
"name": "myTemplate"
}
Code samples without security vulnerabilities¶
Negative test num. 1 - 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": "2014-04-01",
"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/securityAlertPolicies",
"apiVersion": "2021-02-01-preview",
"name": "sqlServer1/sqlDatabase1/securityPolicy1",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
],
"properties": {
"emailAccountAdmins": true,
"emailAddresses": [ "sample@email.com" ],
"retentionDays": 4,
"state": "Enabled"
}
}
]
}
],
"outputs": {}
}
Negative test num. 2 - 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": "2014-04-01",
"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/securityAlertPolicies",
"apiVersion": "2021-02-01-preview",
"name": "sqlServer1/sqlDatabase1/securityPolicy1",
"dependsOn": [
"[resourceId('Microsoft.Sql/servers', 'sqlServer1')]"
],
"properties": {
"emailAccountAdmins": true,
"emailAddresses": [ "sample@email.com" ],
"retentionDays": 4,
"state": "Enabled"
}
}
]
}
],
"outputs": {}
},
"parameters": {}
},
"kind": "template",
"type": "Microsoft.Blueprint/blueprints/artifacts",
"name": "myTemplate"
}