> ## Documentation Index
> Fetch the complete documentation index at: https://cloudanix.com/docs/llms.txt
> Use this file to discover all available pages before exploring further.

# Check For Publicly Accessible SQL Servers

### More Info:

Ensure that your Microsoft Azure SQL database servers are accessible through private endpoints instead of public IP addresses or service endpoints, in order to eliminate the exposure from the public Internet.

### Risk Level

Medium

### Address

Security

### Compliance Standards

* APRA CPS 234 (Australia)
* BSI C5 (Germany)
* Brazil LGPD
* CCPA / CPRA (California)
* CIS AZURE
* CIS Critical Security Controls v8
* CMMC 2.0
* CSA Cloud Controls Matrix v4
* Cloudanix Best Practice
* DPDPA
* Digital Operational Resilience Act (EU)
* GDPR
* HITRUST CSF
* ISO/IEC 27017
* ISO/IEC 27018
* ISO/IEC 27701
* KSA PDPL
* MAS Technology Risk Management (Singapore)
* MITRE ATT\&CK (Cloud)
* NIS2 Directive
* NIST SP 800-171
* NYDFS 23 NYCRR 500
* Reserve Bank of India (RBI) Master Direction – Information Technology Framework
* SOC2
* SWIFT Customer Security Controls Framework
* Sarbanes-Oxley IT General Controls
* Securities and Exchange Board of India (SEBI) - Cloud Security Adoption Framework
* UK NCSC Cyber Assessment Framework

### Triage and Remediation

<Tabs>
  <Tab title="Remediation">
    ### Remediation

    <AccordionGroup>
      <Accordion title="Using Console" defaultOpen="true">
        Sure, here are the step-by-step instructions to remediate the misconfiguration of "Publicly Accessible SQL Servers" in Azure:

        1. Log in to the Azure Portal ([https://portal.azure.com/](https://portal.azure.com/)).
        2. Navigate to the SQL servers page by clicking on "SQL servers" in the left-hand menu.
        3. Select the SQL server that is publicly accessible.
        4. Click on the "Firewalls and virtual networks" tab.
        5. Under "Firewall settings," select "Selected networks."
        6. Click on the "Add client IP" button to add the IP address of your computer to the list of allowed IPs.
        7. If you want to allow access from other IPs, you can add them by clicking on the "Add IP range" button.
        8. 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.

        #
      </Accordion>

      <Accordion title="Using CLI">
        Sure, here are the step-by-step instructions to remediate the "Publicly Accessible SQL Servers" misconfiguration in AZURE using AZURE CLI:

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

        ```
        az login
        ```

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

        ```
        az sql server list
        ```

        3. Identify the SQL server(s) that are publicly accessible and note down their resource group name and server name.

        4. 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.

        5. 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.
      </Accordion>

      <Accordion title="Using Python">
        To remediate publicly accessible SQL servers in Azure using Python, you can follow these steps:

        1. First, you need to import the necessary libraries:

        ```python theme={null}
        from azure.identity import DefaultAzureCredential
        from azure.mgmt.sql import SqlManagementClient
        ```

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

        ```python theme={null}
        credential = DefaultAzureCredential()
        ```

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

        ```python theme={null}
        subscription_id = 'your-subscription-id'
        sql_client = SqlManagementClient(credential, subscription_id)
        ```

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

        ```python theme={null}
        servers = sql_client.servers.list()
        ```

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

        ```python theme={null}
        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.")
        ```

        6. 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.

        ```python theme={null}
        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)
        ```

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

        ```python theme={null}
        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.
      </Accordion>

      <Accordion title="Using Terraform">
        ```hcl theme={null}
        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.
      </Accordion>
    </AccordionGroup>
  </Tab>
</Tabs>