Azure Audit SQL Server Publicly Accessible Remediation

Triage and Remediation

Remediation

Remediation

Using Console

Sure, here are the step-by-step instructions to remediate the misconfiguration of "Publicly Accessible SQL Servers" in Azure:

Log in to the Azure Portal (https://portal.azure.com/).
Navigate to the SQL servers page by clicking on "SQL servers" in the left-hand menu.
Select the SQL server that is publicly accessible.
Click on the "Firewalls and virtual networks" tab.
Under "Firewall settings," select "Selected networks."
Click on the "Add client IP" button to add the IP address of your computer to the list of allowed IPs.
If you want to allow access from other IPs, you can add them by clicking on the "Add IP range" button.
Click on the "Save" button to save the changes.

By following these steps, you have now remediated the misconfiguration of "Publicly Accessible SQL Servers" in Azure and restricted access to only the allowed IPs.

Using CLI

Sure, here are the step-by-step instructions to remediate the "Publicly Accessible SQL Servers" misconfiguration in AZURE using AZURE CLI:

Open the AZURE CLI and login to your AZURE account using the following command:

az login

Run the following command to list all the SQL servers in your subscription:

az sql server list

Identify the SQL server(s) that are publicly accessible and note down their resource group name and server name.
Run the following command to set the "public network access" property to "Disabled" for the identified SQL server(s):

az sql server update --resource-group <resource-group-name> --name <server-name> --public-network-access Disabled

Make sure to replace <resource-group-name> and <server-name> with the actual names of the resource group and server that you identified in step 3.

Verify that the "public network access" property has been set to "Disabled" for the SQL server(s) by running the following command:

az sql server show --resource-group <resource-group-name> --name <server-name> --query 'publicNetworkAccess'

This command should return "Disabled" for the identified SQL server(s).

That's it! You have successfully remediated the "Publicly Accessible SQL Servers" misconfiguration in AZURE using AZURE CLI.

Using Python

To remediate publicly accessible SQL servers in Azure using Python, you can follow these steps:

First, you need to import the necessary libraries:

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

Next, you need to authenticate with Azure using the DefaultAzureCredential class.

credential = DefaultAzureCredential()

Then, you need to create an instance of the SqlManagementClient class.

subscription_id = 'your-subscription-id'
sql_client = SqlManagementClient(credential, subscription_id)

After that, you can use the sql_client instance to get a list of all the SQL servers in your subscription.

servers = sql_client.servers.list()

For each SQL server, you can check if it is publicly accessible by getting its firewall rules.

for server in servers:
    firewall_rules = sql_client.firewall_rules.list_by_server(resource_group_name='<resource-group-name>', 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':
            print(f"Server {server.name} has a publicly accessible firewall rule.")

If you find a SQL server with a publicly accessible firewall rule, you can delete the rule using the delete method of the FirewallRulesOperations class.

if rule.start_ip_address == '0.0.0.0' and rule.end_ip_address == '255.255.255.255':
    print(f"Server {server.name} has a publicly accessible firewall rule. Deleting rule {rule.name}...")
    sql_client.firewall_rules.delete(resource_group_name='<resource-group-name>', server_name=server.name, firewall_rule_name=rule.name)

Finally, you can confirm that the firewall rule has been deleted by checking the list of firewall rules again.

firewall_rules = sql_client.firewall_rules.list_by_server(resource_group_name='<resource-group-name>', 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':
        print(f"Server {server.name} still has a publicly accessible firewall rule.")

Note: You will need to replace <resource-group-name> with the name of the resource group containing your SQL servers.

Using Terraform

resource "azurerm_mssql_server" "sql_server" {
  name                         = "SQL_SERVER_NAME"          # replace with your server name
  resource_group_name          = azurerm_resource_group.rg.name
  location                     = azurerm_resource_group.rg.location
  version                      = "12.0"
  administrator_login          = "SQL_ADMIN_USERNAME"       # replace with admin username
  administrator_login_password = "SQL_ADMIN_PASSWORD"       # replace with admin password

  # Key setting to eliminate public Internet exposure
  public_network_access_enabled = false
}

resource "azurerm_virtual_network" "vnet" {
  name                = "VNET_NAME"                         # replace with your VNet name
  address_space       = ["10.10.0.0/16"]
  location            = azurerm_resource_group.rg.location
  resource_group_name = azurerm_resource_group.rg.name
}

resource "azurerm_subnet" "private_endpoint_subnet" {
  name                 = "PRIVATE_ENDPOINT_SUBNET_NAME"     # replace with your subnet name
  resource_group_name  = azurerm_resource_group.rg.name
  virtual_network_name = azurerm_virtual_network.vnet.name
  address_prefixes     = ["10.10.1.0/24"]

  # Required for private endpoints
  enforce_private_link_endpoint_network_policies = true
}

resource "azurerm_private_dns_zone" "sql_private_dns" {
  name                = "privatelink.database.windows.net"
  resource_group_name = azurerm_resource_group.rg.name
}

resource "azurerm_private_dns_zone_virtual_network_link" "sql_private_dns_link" {
  name                  = "SQL_PRIVATE_DNS_LINK_NAME"       # replace with link name
  resource_group_name   = azurerm_resource_group.rg.name
  private_dns_zone_name = azurerm_private_dns_zone.sql_private_dns.name
  virtual_network_id    = azurerm_virtual_network.vnet.id
}

resource "azurerm_private_endpoint" "sql_private_endpoint" {
  name                = "SQL_PRIVATE_ENDPOINT_NAME"         # replace with PE name
  location            = azurerm_resource_group.rg.location
  resource_group_name = azurerm_resource_group.rg.name
  subnet_id           = azurerm_subnet.private_endpoint_subnet.id

  private_service_connection {
    name                           = "SQL_PRIVATE_CONNECTION_NAME"  # replace with connection name
    is_manual_connection           = false
    private_connection_resource_id = azurerm_mssql_server.sql_server.id
    subresource_names              = ["sqlServer"]
  }

  private_dns_zone_group {
    name                 = "SQL_PRIVATE_DNS_ZONE_GROUP_NAME" # replace with zone group name
    private_dns_zone_ids = [azurerm_private_dns_zone.sql_private_dns.id]
  }
}

No changes above force replacement of the existing azurerm_mssql_server resource; they are in‑place updates and additions.

Verification: terraform plan should show public_network_access_enabled changing from true to false (or being added as false), plus new azurerm_private_endpoint, azurerm_private_dns_zone, and related link resources being created, with no -/+ replacement on the SQL server.

Triage and Remediation​

Remediation​

Triage and Remediation

Remediation