Skip to main content

Unrestricted MSSQL Server Access

More Info:​

Ensure that all your Microsoft Azure network security groups (NSGs) restrict inbound/ingress access on TCP port 1433 to trusted IP addresses only in order to implement the principle of least privilege and significantly reduce the attack surface. TCP port 1433 is used by Microsoft Azure SQL Server, the relational database management system developed by Microsoft.

Risk Level​

High

Address​

Security

Compliance Standards​

  • APRA CPS 234 (Australia)
  • BSI C5 (Germany)
  • Brazil LGPD
  • CCPA / CPRA (California)
  • CIS Critical Security Controls v8
  • CMMC 2.0
  • CSA Cloud Controls Matrix v4
  • DPDPA
  • Digital Operational Resilience Act (EU)
  • FedRAMP
  • GDPR
  • HIPAA
  • ISO/IEC 27017
  • ISO/IEC 27018
  • ISO/IEC 27701
  • KSA PDPL
  • MAS Technology Risk Management (Singapore)
  • MITRE ATT&CK (Cloud)
  • NIS2 Directive
  • NIST CSF
  • NIST SP 800-171
  • NYDFS 23 NYCRR 500
  • PCI
  • SOC2
  • SWIFT Customer Security Controls Framework
  • Sarbanes-Oxley IT General Controls
  • UK NCSC Cyber Assessment Framework

Triage and Remediation​

Remediation​

Using Console

To remediate the unrestricted MSSQL Server Access misconfiguration in AZURE, please follow the below steps:

  1. Open the Azure Portal and login with your credentials.

  2. Navigate to the Azure SQL Server that you want to remediate.

  3. Click on "Firewalls and virtual networks" under the "Security" section in the left-hand menu.

  4. Ensure that the "Allow Azure services and resources to access this server" option is turned off.

  5. Under the "Firewall rules" section, click on "Add client IP".

  6. Enter the IP address of the client that needs to access the SQL server.

  7. Click on "Save" to apply the changes.

  8. Repeat steps 5-7 for all the clients that require access to the SQL server.

  9. Once you have added all the required client IP addresses, turn on the "Allow Azure services and resources to access this server" option.

  10. Click on "Save" to apply the changes.

With these steps, you have now remediated the unrestricted MSSQL Server Access misconfiguration in AZURE.

Using CLI

To remediate unrestricted MSSQL Server access in Azure using Azure CLI, you can follow the below steps:

Step 1: Login to Azure CLI

az login

Step 2: Get the resource group name and MSSQL server name where the misconfiguration is present

az sql server list --query '[].{ResourceGroup:resourceGroup, ServerName:name}'

Step 3: Set the resource group and server name variables

$resourceGroup = "<resource-group-name>"
$serverName = "<mssql-server-name>"

Step 4: Get the firewall rules for the MSSQL server

az sql server firewall-rule list --resource-group $resourceGroup --server $serverName

Step 5: Identify the unrestricted firewall rule that allows all IP addresses to access the MSSQL server

Step 6: Delete the unrestricted firewall rule

az sql server firewall-rule delete --resource-group $resourceGroup --server $serverName --name <firewall-rule-name>

Step 7: Create a new firewall rule that allows only specific IP addresses to access the MSSQL server

az sql server firewall-rule create --resource-group $resourceGroup --server $serverName --name <firewall-rule-name> --start-ip-address <start-ip-address> --end-ip-address <end-ip-address>

Note: Replace the placeholders <resource-group-name>, <mssql-server-name>, <firewall-rule-name>, <start-ip-address> and <end-ip-address> with the actual values.

Using Python

To remediate the unrestricted MSSQL Server Access misconfiguration in AZURE using python, follow the below steps:

Step 1: Import the required libraries

import os
from azure.identity import DefaultAzureCredential
from azure.mgmt.sql import SqlManagementClient

Step 2: Set the credentials and subscription ID

credential = DefaultAzureCredential()
subscription_id = 'your_subscription_id'

Step 3: Create the SQL Management client

sql_client = SqlManagementClient(credential, subscription_id)

Step 4: Get the list of MSSQL servers in the subscription

servers = sql_client.servers.list()

Step 5: For each server, check if the firewall rules allow unrestricted access and remove them

for server in servers:
    firewall_rules = sql_client.firewall_rules.list_by_server(resource_group_name=server.resource_group, server_name=server.name)
    for rule in firewall_rules:
        if rule.start_ip_address == '0.0.0.0' and rule.end_ip_address == '255.255.255.255':
            sql_client.firewall_rules.delete(resource_group_name=server.resource_group, server_name=server.name, firewall_rule_name=rule.name)

Step 6: Run the python script to remediate the unrestricted MSSQL Server Access misconfiguration in AZURE.

Note: The above code will remove all the firewall rules that allow unrestricted access to the MSSQL server. It is recommended to review the firewall rules before removing them to ensure that no legitimate access is blocked.

Using Terraform
resource "azurerm_network_security_group" "MSSQL_NSG" {
  name                = "MSSQL-NSG"
  location            = azurerm_resource_group.RG.location
  resource_group_name = azurerm_resource_group.RG.name

  security_rule {
    name                       = "allow-mssql-from-trusted"
    priority                   = 100
    direction                  = "Inbound"
    access                     = "Allow"
    protocol                   = "Tcp"
    source_port_range          = "*"
    destination_port_range     = "1433"
    source_address_prefixes    = [
      "TRUSTED_CIDR_1", # e.g. "203.0.113.0/24"
      "TRUSTED_CIDR_2", # e.g. "198.51.100.10/32"
    ]
    destination_address_prefix = "*"
  }

  # REMOVE or tighten any existing rule that allows TCP/1433 from "*" or "Internet":
  # security_rule {
  #   name                       = "insecure-mssql-anywhere"
  #   priority                   = 200
  #   direction                  = "Inbound"
  #   access                     = "Allow"
  #   protocol                   = "Tcp"
  #   source_port_range          = "*"
  #   destination_port_range     = "1433"
  #   source_address_prefix      = "*"
  #   destination_address_prefix = "*"
  # }
}

Substitute:

  • TRUSTED_CIDR_1, TRUSTED_CIDR_2 with the specific trusted IP ranges that should reach SQL over 1433.
  • azurerm_resource_group.RG with your actual resource group resource.

This change updates rules in place and does not force replacement of the NSG itself, but traffic from untrusted IPs to port 1433 will be blocked after apply.

Verification: terraform plan should show any existing Allow rule with destination_port_range = "1433" and source_address_prefix = "*" (or Internet) being removed or modified, and a rule added/updated so that only the specified trusted CIDR ranges are allowed on TCP port 1433.