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
- 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_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"
}
}
]
}
],
"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
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"
}
},
{
"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_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"
}
}
]
}
],
"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
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"
}
}
],
"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_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"
}
}
]
}
]
}
],
"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'
}
}
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"
}
}
],
"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_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"
}
}
]
}
]
}
],
"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_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"
}
}
],
"outputs": {}
},
"parameters": {}
},
"kind": "template",
"type": "Microsoft.Blueprint/blueprints/artifacts",
"name": "myTemplate"
}