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"
}

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"
}