Small MSSQL Server Audit Retention
- Query id: 59acb56b-2b10-4c2c-ba38-f2223c3f5cfc
- Query name: Small MSSQL Server Audit Retention
- Platform: Terraform
- Severity: Medium
- Category: Observability
- URL: Github
Description¶
Make sure for SQL Servers that Auditing Retention is greater than 90 days
Documentation
Code samples¶
Code samples with security vulnerabilities¶
Postitive test num. 1 - tf file
resource "azurerm_sql_database" "positive1" {
name = "myexamplesqldatabase"
resource_group_name = azurerm_resource_group.example.name
location = "West US"
server_name = azurerm_sql_server.example.name
extended_auditing_policy {
storage_endpoint = azurerm_storage_account.example.primary_blob_endpoint
storage_account_access_key = azurerm_storage_account.example.primary_access_key
storage_account_access_key_is_secondary = true
}
tags = {
environment = "production"
}
}
resource "azurerm_sql_database" "positive2" {
name = "myexamplesqldatabase"
resource_group_name = azurerm_resource_group.example.name
location = "West US"
server_name = azurerm_sql_server.example.name
extended_auditing_policy {
storage_endpoint = azurerm_storage_account.example.primary_blob_endpoint
storage_account_access_key = azurerm_storage_account.example.primary_access_key
storage_account_access_key_is_secondary = true
retention_in_days = 90
}
tags = {
environment = "production"
}
}
resource "azurerm_sql_database" "positive3" {
name = "myexamplesqldatabase"
resource_group_name = azurerm_resource_group.example.name
location = "West US"
server_name = azurerm_sql_server.example.name
extended_auditing_policy {
storage_endpoint = azurerm_storage_account.example.primary_blob_endpoint
storage_account_access_key = azurerm_storage_account.example.primary_access_key
storage_account_access_key_is_secondary = true
retention_in_days = 0
}
tags = {
environment = "production"
}
}
resource "azurerm_sql_server" "positive4" {
name = "sqlserver"
resource_group_name = azurerm_resource_group.example.name
location = azurerm_resource_group.example.location
version = "12.0"
administrator_login = "mradministrator"
administrator_login_password = "thisIsDog11"
extended_auditing_policy {
storage_endpoint = azurerm_storage_account.example.primary_blob_endpoint
storage_account_access_key = azurerm_storage_account.example.primary_access_key
storage_account_access_key_is_secondary = true
retention_in_days = 20
}
}
Code samples without security vulnerabilities¶
Negative test num. 1 - tf file
resource "azurerm_sql_database" "negative1" {
name = "myexamplesqldatabase"
resource_group_name = azurerm_resource_group.example.name
location = "West US"
server_name = azurerm_sql_server.example.name
extended_auditing_policy {
storage_endpoint = azurerm_storage_account.example.primary_blob_endpoint
storage_account_access_key = azurerm_storage_account.example.primary_access_key
storage_account_access_key_is_secondary = true
retention_in_days = 91
}
tags = {
environment = "production"
}
}
resource "azurerm_sql_database" "negative2" {
name = "myexamplesqldatabase"
resource_group_name = azurerm_resource_group.example.name
location = "West US"
server_name = azurerm_sql_server.example.name
extended_auditing_policy {
storage_endpoint = azurerm_storage_account.example.primary_blob_endpoint
storage_account_access_key = azurerm_storage_account.example.primary_access_key
storage_account_access_key_is_secondary = true
retention_in_days = 214
}
tags = {
environment = "production"
}
}
resource "azurerm_sql_database" "negative3" {
name = "myexamplesqldatabase"
resource_group_name = azurerm_resource_group.example.name
location = "West US"
server_name = azurerm_sql_server.example.name
extended_auditing_policy {
storage_endpoint = azurerm_storage_account.example.primary_blob_endpoint
storage_account_access_key = azurerm_storage_account.example.primary_access_key
storage_account_access_key_is_secondary = true
retention_in_days = 30000
}
tags = {
environment = "production"
}
}
resource "azurerm_sql_database" "negative4" {
name = "myexamplesqldatabase"
resource_group_name = azurerm_resource_group.example.name
location = "West US"
server_name = azurerm_sql_server.example.name
extended_auditing_policy {
storage_endpoint = azurerm_storage_account.example.primary_blob_endpoint
storage_account_access_key = azurerm_storage_account.example.primary_access_key
storage_account_access_key_is_secondary = true
retention_in_days = 900
}
tags = {
environment = "production"
}
}
resource "azurerm_sql_server" "negative5" {
name = "sqlserver"
resource_group_name = azurerm_resource_group.example.name
location = azurerm_resource_group.example.location
version = "12.0"
administrator_login = "mradministrator"
administrator_login_password = "thisIsDog11"
extended_auditing_policy {
storage_endpoint = azurerm_storage_account.example.primary_blob_endpoint
storage_account_access_key = azurerm_storage_account.example.primary_access_key
storage_account_access_key_is_secondary = true
retention_in_days = 95
}
}