Allow Azure Services and resources to access this server – How it works in Azure SQL Database?

One of the first options that Azure SQL Database users encounter when creating this resource is the “Allow Azure Services and resources to access this server” option, along with other network settings related to our server. While allowing traffic from a specific IP address or range of IP addresses to our server is fairly intuitive and easy to understand, the mentioned option may not be, which is why I decided to write a few words about it.

So, what does this option mean? It simply means that, from a network perspective, we allow all connections originating from within Azure to access our server. It is worth noting that the term “connection originating from within Azure” does not necessarily refer to our subscription – it could be a service or any virtual machine from any subscription. It is important to keep this in mind and use this option with full awareness of the consequences – for some clients, this option is acceptable because even though network security is slightly reduced, we still have authentication via AAD, etc. For others, it may be an unacceptable breach in their firewall, which could be a threat to their data.

Let’s test the above option from two different perspectives. I have a test SQL server set up in one subscription and an Azure Data Factory located entirely in another subscription and location. I provided all the necessary data required to establish a connection from the ADF side, and at this point, the “Allow Azure services…” option is set to “No.” As you might expect, we received an error:

After enabling the option, the connection was successful:

 

So, as we expected, the connection to the server is possible. If we take a closer look at what is happening, querying the system view sys.firewall_rules, which displays the Firewall rules at the SQL Server level, we will notice a quite interesting entry:

SELECT * FROM sys.firewall_rules

 

The option we set is visible there and clearly indicates that every Azure service can access our server because the public endpoint of our server and the firewall within it allow connections from within Azure. ADF is just an example, but you can notice similar behavior with other services, including Power BI, which is also an Azure service.So, how can we prevent enabling this option in our subscription? As usual in such situations, we can use the Azure Policy mechanism, which allows us to enforce certain settings on Azure resources or audit them. In this particular case, we will create a rule that prevents adding IP addresses 0.0.0.0 (and we know that the described option is actually adding the IP address 0.0.0.0, so the created rule should block enabling this option).

Creating your own rules in JSON can sometimes be problematic, but in this specific case, the content looks as follows:

{
    "properties": {
        "displayName": "testSQLPolicy",
        "policyType": "Custom",
        "mode": "All",
        "metadata": {
            "createdBy": "dd9e754f-ea8a-4d4d-b51f-2ba953bfeab2",
            "createdOn": "2021-09-23T18:20:49.3902023Z",
            "updatedBy": null,
            "updatedOn": null
        },
        "parameters": {},
        "policyRule": {
            "if": {
                "anyOf": [
                    {
                        "field": "Microsoft.Sql/servers/firewallRules/startIpAddress",
                        "equals": "0.0.0.0"
                    },
                    {
                        "field": "Microsoft.Sql/servers/firewallRules/endIpAddress",
                        "equals": "0.0.0.0"
                    }
                ]
            },
            "then": {
                "effect": "Deny"
            }
        }
    },
    "id": "/subscriptions/2934496f-e4ed-49c7-a474-9f8c1c4a78c8/providers/Microsoft.Authorization/policyDefinitions/7ded4bea-72b4-497c-bac4-503d520fbae0",
    "type": "Microsoft.Authorization/policyDefinitions",
    "name": "7ded4bea-72b4-497c-bac4-503d520fbae0",
    "systemData": {
        "createdBy": "adrian.chodkowski@hotmail.com",
        "createdByType": "User",
        "createdAt": "2021-09-25T16:20:49.3274034Z",
        "lastModifiedBy": "adrian.chodkowski@hotmail.com",
        "lastModifiedByType": "User",
        "lastModifiedAt": "2021-09-25T16:20:49.3274034Z"
    }
}

The above definition indicates that the effect of its operation is “Deny”, which means we are blocking modification – if there was a need, we could set it to “Audit” to simply get information about non-compliance. It’s worth remembering that the above rule will not affect existing items.

Let’s go to the Azure Policy service from the portal by simply searching for “Policy”. Once we are in the right place, we can select “Definitions” to add a new policy definition:

Here you just need to select + Policy definition:

In the policy definition window, we can add the JSON definition listed above and assign it to an existing or new category:

 

Once our policy is ready, we can assign it to a specific Azure resource hierarchy element.

 

The assignment definition is quite clear and boils down to filling in the parameter values (if any are present in our policy) and, above all, indicating whether the policy should be enforced (Policy enforcement -> Enabled) or not (Policy enforcement -> Disabled):

 

 

When we go to the Compliance tab, we can see existing resources and whether they are compliant with the policy we created. As you can see in the screenshot below, none of the three SQL Server instances are compliant:

After clicking on any of the messages, we see a detailed description of the non-compliance issue.

 

With policies, it’s worth noting that they should be active approximately 30 minutes after they’re defined. If we want, we can trigger a compliance check on-demand using Azure CLI:

az policy state trigger-scan

However, the above command will also take some time depending on how many resources we have and what scope we have selected.

In summary, it’s important to remember what the “Allow Azure Services and resources to access this server” setting actually means and how it impacts the security of our data. Ideally, all communication should be limited to traffic within a given VNET. However, if we need to grant access via IP addresses in the firewall for some reason, we should keep the above option in mind. Best regards!