Unrestricted SQL Server Access

• Query id: d7ba74da-2da0-4d4b-83c8-2fd72a3f6c28
• Query name: Unrestricted SQL Server Access
• Platform: Terraform
• Severity: Critical
• Category: Networking and Firewall
• CWE: 284
• Risk score: 8.7
• URL: Github

Description

Azure SQL Server Accessibility should be set to a minimal address range to grant the principle of least privileges, which means the difference between the values of the 'end_ip_address' and 'start_ip_address' must be less than 256. Additionally, both ips must be different from '0.0.0.0'.
Documentation

Code samples

Code samples with security vulnerabilities

Positive test num. 1 - tf file

resource "azurerm_resource_group" "positive1-legacy" {
  name     = "acceptanceTestResourceGroup1"
  location = "West US"
}

resource "azurerm_sql_server" "positive2-legacy" {
  name                         = "mysqlserver"
  resource_group_name          = azurerm_resource_group.example.name
  location                     = "West US"
  version                      = "12.0"
  administrator_login          = "4dm1n157r470r"
  administrator_login_password = "4-v3ry-53cr37-p455w0rd"
}

resource "azurerm_sql_firewall_rule" "positive3-legacy" {
  name                = "FirewallRule1"
  resource_group_name = azurerm_resource_group.example.name
  server_name         = azurerm_sql_server.example.name
  start_ip_address    = "0.0.0.0"
  end_ip_address      = "10.0.27.62"
}

resource "azurerm_sql_firewall_rule" "positive4-legacy" {
  name                = "FirewallRule1"
  resource_group_name = azurerm_resource_group.example.name
  server_name         = azurerm_sql_server.example.name
  start_ip_address    = "10.0.17.62"
  end_ip_address      = "10.0.27.62"
}

# Azure feature "Allow access to Azure services"
resource "azurerm_sql_firewall_rule" "positive5-legacy" {
  resource_group_name   = azurerm_resource_group.example.name
  server_name           = azurerm_sql_server.example.name
  start_ip_address      = "0.0.0.0"
  end_ip_address        = "0.0.0.0"
}

Positive test num. 2 - tf file

resource "azurerm_resource_group" "positive1" {
  name     = "acceptanceTestResourceGroup1"
  location = "West US"
}

resource "azurerm_mssql_server" "positive2" {
  name                         = "mysqlserver"
  resource_group_name          = azurerm_resource_group.positive1.name
  location                     = azurerm_resource_group.positive1.location
  version                      = "12.0"
  administrator_login          = "4dm1n157r470r"
  administrator_login_password = "4-v3ry-53cr37-p455w0rd"
  minimum_tls_version          = "1.2"
}

resource "azurerm_mssql_firewall_rule" "positive3" {
  name              = "FirewallRule1"
  server_id         = azurerm_mssql_server.positive2.id
  start_ip_address  = "0.0.0.0"
  end_ip_address    = "10.0.27.62"
}

resource "azurerm_mssql_firewall_rule" "positive4" {
  name              = "FirewallRule2"
  server_id         = azurerm_mssql_server.positive2.id
  start_ip_address  = "10.0.17.62"
  end_ip_address    = "10.0.27.62"
}

# Azure feature "Allow access to Azure services"
resource "azurerm_mssql_firewall_rule" "positive5" {
  server_id         = azurerm_mssql_server.example.id
  start_ip_address  = "0.0.0.0"
  end_ip_address    = "0.0.0.0"
}

Positive test num. 3 - tf file

resource "azurerm_resource_group" "mariadb_rg" {
  name     = "example-mariadb-rg"
  location = "West US"
}

resource "azurerm_mariadb_server" "mariadb_server" {
  name                = "examplemariadbserver"
  location            = azurerm_resource_group.mariadb_rg.location
  resource_group_name = azurerm_resource_group.mariadb_rg.name
  administrator_login = "mariadbadmin"
  administrator_login_password = "MyS3cureP4ss!"
  sku_name            = "B_Gen5_2"
  storage_mb          = 5120
  version             = "10.2"
  auto_grow_enabled   = true
  backup_retention_days = 7
  geo_redundant_backup_enabled = false
  ssl_enforcement_enabled = true
}

resource "azurerm_mariadb_firewall_rule" "mariadb_fw1" {
  name                = "FirewallRule1"
  resource_group_name = azurerm_resource_group.mariadb_rg.name
  server_name         = azurerm_mariadb_server.mariadb_server.name
  start_ip_address    = "0.0.0.0"
  end_ip_address      = "10.0.27.62"
}

resource "azurerm_mariadb_firewall_rule" "mariadb_fw2" {
  name                = "FirewallRule2"
  resource_group_name = azurerm_resource_group.mariadb_rg.name
  server_name         = azurerm_mariadb_server.mariadb_server.name
  start_ip_address    = "10.0.17.62"
  end_ip_address      = "10.0.27.62"
}

resource "azurerm_mariadb_firewall_rule" "mariadb_fw3" {
  name                = "AllowAzure"
  resource_group_name = azurerm_resource_group.mariadb_rg.name
  server_name         = azurerm_mariadb_server.mariadb_server.name
  start_ip_address    = "0.0.0.0"
  end_ip_address      = "0.0.0.0"
}

Positive test num. 4 - tf file

resource "azurerm_resource_group" "psql_rg" {
  name     = "example-postgres-rg"
  location = "West US"
}

resource "azurerm_postgresql_server" "psql_server" {
  name                = "examplepostgresqlserver"
  location            = azurerm_resource_group.psql_rg.location
  resource_group_name = azurerm_resource_group.psql_rg.name
  administrator_login = "psqladmin"
  administrator_login_password = "MyS3cureP4ss!"
  sku_name            = "B_Gen5_2"
  storage_mb          = 5120
  version             = "11"
  auto_grow_enabled   = true
  backup_retention_days = 7
  geo_redundant_backup_enabled = false
  ssl_enforcement_enabled = true
}

resource "azurerm_postgresql_firewall_rule" "psql_fw1" {
  name                = "FirewallRule1"
  resource_group_name = azurerm_resource_group.psql_rg.name
  server_name         = azurerm_postgresql_server.psql_server.name
  start_ip_address    = "0.0.0.0"
  end_ip_address      = "10.0.27.62"
}

resource "azurerm_postgresql_firewall_rule" "psql_fw2" {
  name                = "FirewallRule2"
  resource_group_name = azurerm_resource_group.psql_rg.name
  server_name         = azurerm_postgresql_server.psql_server.name
  start_ip_address    = "10.0.17.62"
  end_ip_address      = "10.0.27.62"
}

# Allow access to Azure services
resource "azurerm_postgresql_firewall_rule" "psql_fw3" {
  name                = "AllowAzure"
  resource_group_name = azurerm_resource_group.psql_rg.name
  server_name         = azurerm_postgresql_server.psql_server.name
  start_ip_address    = "0.0.0.0"
  end_ip_address      = "0.0.0.0"
}

Positive test num. 5 - tf file

resource "azurerm_resource_group" "psqlflex_rg" {
  name     = "example-psqlflex-rg"
  location = "West US"
}

resource "azurerm_postgresql_flexible_server" "psqlflex_server" {
  name                = "examplepsqlflexserver"
  resource_group_name = azurerm_resource_group.psqlflex_rg.name
  location            = azurerm_resource_group.psqlflex_rg.location
  version             = "13"
  administrator_login = "psqlflexadmin"
  administrator_password = "MyS3cureP4ss!"
  sku_name            = "B_Standard_B1ms"
  storage_mb          = 32768
}

resource "azurerm_postgresql_flexible_server_firewall_rule" "psqlflex_fw1" {
  name      = "FirewallRule1"
  server_id = azurerm_postgresql_flexible_server.psqlflex_server.id
  start_ip_address = "0.0.0.0"
  end_ip_address   = "10.0.27.62"
}

resource "azurerm_postgresql_flexible_server_firewall_rule" "psqlflex_fw2" {
  name      = "FirewallRule2"
  server_id = azurerm_postgresql_flexible_server.psqlflex_server.id
  start_ip_address = "10.0.17.62"
  end_ip_address   = "10.0.27.62"
}

# Allow access to Azure services (same rule as MSSQL: 0.0.0.0/0)
resource "azurerm_postgresql_flexible_server_firewall_rule" "psqlflex_fw3" {
  name      = "AllowAzure"
  server_id = azurerm_postgresql_flexible_server.psqlflex_server.id
  start_ip_address = "0.0.0.0"
  end_ip_address   = "0.0.0.0"
}

Positive test num. 6 - tf file

resource "azurerm_resource_group" "mysqlflex_rg" {
  name     = "example-mysqlflex-rg"
  location = "West US"
}

resource "azurerm_mysql_flexible_server" "mysqlflex_server" {
  name                = "examplemysqlflexserver"
  resource_group_name = azurerm_resource_group.mysqlflex_rg.name
  location            = azurerm_resource_group.mysqlflex_rg.location
  version             = "8.0.21"
  administrator_login = "mysqlflexadmin"
  administrator_password = "MyS3cureP4ss!"
  sku_name            = "B_Standard_B1ms"
  storage_mb          = 32768
}

resource "azurerm_mysql_flexible_server_firewall_rule" "mysqlflex_fw1" {
  name      = "FirewallRule1"
  server_id = azurerm_mysql_flexible_server.mysqlflex_server.id
  start_ip_address = "0.0.0.0"
  end_ip_address   = "10.0.27.62"
}

resource "azurerm_mysql_flexible_server_firewall_rule" "mysqlflex_fw2" {
  name      = "FirewallRule2"
  server_id = azurerm_mysql_flexible_server.mysqlflex_server.id
  start_ip_address = "10.0.17.62"
  end_ip_address   = "10.0.27.62"
}

# Allow access to Azure services
resource "azurerm_mysql_flexible_server_firewall_rule" "mysqlflex_fw3" {
  name      = "AllowAzure"
  server_id = azurerm_mysql_flexible_server.mysqlflex_server.id
  start_ip_address = "0.0.0.0"
  end_ip_address   = "0.0.0.0"
}

Code samples without security vulnerabilities

Negative test num. 1 - tf file

resource "azurerm_resource_group" "negative1" {
  name     = "acceptanceTestResourceGroup1"
  location = "West US"
}

resource "azurerm_sql_server" "negative2" {
  name                         = "mysqlserver"
  resource_group_name          = azurerm_resource_group.example.name
  location                     = "West US"
  version                      = "12.0"
  administrator_login          = "4dm1n157r470r"
  administrator_login_password = "4-v3ry-53cr37-p455w0rd"
}

resource "azurerm_sql_firewall_rule" "negative3" {
  name                = "FirewallRule1"
  resource_group_name = azurerm_resource_group.example.name
  server_name         = azurerm_sql_server.example.name
  start_ip_address    = "10.0.17.62"
  end_ip_address      = "10.0.17.62"
}

Negative test num. 2 - tf file

resource "azurerm_resource_group" "negative1" {
  name     = "acceptanceTestResourceGroup1"
  location = "West US"
}

resource "azurerm_mssql_server" "negative2" {
  name                         = "mysqlserver"
  resource_group_name          = azurerm_resource_group.negative1.name
  location                     = azurerm_resource_group.negative1.location
  version                      = "12.0"
  administrator_login          = "4dm1n157r470r"
  administrator_login_password = "4-v3ry-53cr37-p455w0rd"
  minimum_tls_version          = "1.2"
}

resource "azurerm_mssql_firewall_rule" "negative3" {
  name              = "FirewallRule1"
  server_id         = azurerm_mssql_server.negative2.id
  start_ip_address  = "10.0.17.62"
  end_ip_address    = "10.0.17.62"
}

Negative test num. 3 - tf file

resource "azurerm_resource_group" "mariadb_negative_rg" {
  name     = "acceptanceTestResourceGroup1"
  location = "West US"
}

resource "azurerm_mariadb_server" "mariadb_negative_server" {
  name                = "negativemariadbserver"
  location            = azurerm_resource_group.mariadb_negative_rg.location
  resource_group_name = azurerm_resource_group.mariadb_negative_rg.name
  administrator_login = "mariadbadmin"
  administrator_login_password = "MyS3cureP4ss!"
  sku_name            = "B_Gen5_2"
  storage_mb          = 5120
  version             = "10.2"
  auto_grow_enabled   = true
  backup_retention_days = 7
  geo_redundant_backup_enabled = false
  ssl_enforcement_enabled = true
}

resource "azurerm_mariadb_firewall_rule" "mariadb_negative_fw" {
  name                = "FirewallRule1"
  resource_group_name = azurerm_resource_group.mariadb_negative_rg.name
  server_name         = azurerm_mariadb_server.mariadb_negative_server.name
  start_ip_address    = "10.0.17.62"
  end_ip_address      = "10.0.17.62"
}

Negative test num. 4 - tf file

resource "azurerm_resource_group" "psql_negative_rg" {
  name     = "acceptanceTestResourceGroup1"
  location = "West US"
}

resource "azurerm_postgresql_server" "psql_negative_server" {
  name                = "negativepostgresqlserver"
  location            = azurerm_resource_group.psql_negative_rg.location
  resource_group_name = azurerm_resource_group.psql_negative_rg.name
  administrator_login = "psqladmin"
  administrator_login_password = "MyS3cureP4ss!"
  sku_name            = "B_Gen5_2"
  storage_mb          = 5120
  version             = "11"
  auto_grow_enabled   = true
  backup_retention_days = 7
  geo_redundant_backup_enabled = false
  ssl_enforcement_enabled = true
}

resource "azurerm_postgresql_firewall_rule" "psql_negative_fw" {
  name                = "FirewallRule1"
  resource_group_name = azurerm_resource_group.psql_negative_rg.name
  server_name         = azurerm_postgresql_server.psql_negative_server.name
  start_ip_address    = "10.0.17.62"
  end_ip_address      = "10.0.17.62"
}

Negative test num. 5 - tf file

resource "azurerm_resource_group" "psqlflex_negative_rg" {
  name     = "acceptanceTestResourceGroup1"
  location = "West US"
}

resource "azurerm_postgresql_flexible_server" "psqlflex_negative_server" {
  name                   = "negativepsqlflexserver"
  resource_group_name    = azurerm_resource_group.psqlflex_negative_rg.name
  location               = azurerm_resource_group.psqlflex_negative_rg.location
  version                = "13"
  administrator_login    = "psqlflexadmin"
  administrator_password = "MyS3cureP4ss!"
  sku_name               = "B_Standard_B1ms"
  storage_mb             = 32768
}

resource "azurerm_postgresql_flexible_server_firewall_rule" "psqlflex_negative_fw" {
  name             = "FirewallRule1"
  server_id        = azurerm_postgresql_flexible_server.psqlflex_negative_server.id
  start_ip_address = "10.0.17.62"
  end_ip_address   = "10.0.17.62"
}

Negative test num. 6 - tf file

resource "azurerm_resource_group" "mysqlflex_negative_rg" {
  name     = "acceptanceTestResourceGroup1"
  location = "West US"
}

resource "azurerm_mysql_flexible_server" "mysqlflex_negative_server" {
  name                   = "negativemysqlflexserver"
  resource_group_name    = azurerm_resource_group.mysqlflex_negative_rg.name
  location               = azurerm_resource_group.mysqlflex_negative_rg.location
  version                = "8.0.21"
  administrator_login    = "mysqlflexadmin"
  administrator_password = "MyS3cureP4ss!"
  sku_name               = "B_Standard_B1ms"
  storage_mb             = 32768
}

resource "azurerm_mysql_flexible_server_firewall_rule" "mysqlflex_negative_fw" {
  name             = "FirewallRule1"
  server_id        = azurerm_mysql_flexible_server.mysqlflex_negative_server.id
  start_ip_address = "10.0.17.62"
  end_ip_address   = "10.0.17.62"
}