SqlException server not found error when connecting to an SQL DB from an Azure Function App-CodePudding

I have deployed a .NET Core Azure Function App running on the consumption pricing plan which connects, through EF Core, to a MS SQL database hosted by my website provider.

I see the following error reported by App Insights when the database connection is attempted:

Microsoft.Data.SqlClient.SqlException (0x80131904): A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.) System.ComponentModel.Win32Exception (10060): A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond. ... Error Number:10060,State:0,Class:20

I followed the instructions here to obtain the function app's outboundIpAddresses (using Azure Resource Explorer which I also double checked with the Azure CLI).

I passed the list of IP's to the support team at my hosting provider & yet still receive the same error.

I know it's not code related as when I run my function app locally, I can connect fine (my local IP is on the SQL Server allow list).

Why can the Azure function not connect to the database?

This is is a small home project so I can't afford the virtual network NAT gateway route.

CodePudding user response：

running on the consumption pricing plan

Outbound IP addresses reported by functions running on the Consumption plan are not reliable.

As per Azure documentation:

When a function app that runs on the Consumption plan or the Premium plan is scaled, a new range of outbound IP addresses may be assigned. When running on either of these plans, you can't rely on the reported outbound IP addresses to create a definitive allowlist. To be able to include all potential outbound addresses used during dynamic scaling, you'll need to add the entire data center to your allowlist.

Instead, provide your hosting provider with the outbound IP addresses for the Azure region(/data center) where your Azure function is hosted in, to cover all possible IPs that your Azure function may be assigned.

The official Azure IP ranges for all regions are in a JSON file available for download here.

Firstly, download this file.

Secondly, search for AzureCloud.[region name] e.g. AzureCloud.uknorth or AzureCloud.centralfrance which will bring up the IP addresses for Azure cloud in your specific region.

{
  "name": "AzureCloud.uknorth",
  "id": "AzureCloud.uknorth",
  "properties": {
    "changeNumber": 11,
    "region": "uknorth",
    "regionId": 29,
    "platform": "Azure",
    "systemService": "",
    "addressPrefixes": [
      "13.87.120.0/22",
      ...
      "2603:1027:1:1c0::/59"
    ],
    "networkFeatures": []
  }
}

Finally, provide your hosting provider with all the IP addresses listed in the fragment.

Your Azure function should then be able to consistently connect to your database.

N.B. The list can & will update over time albeit more to add than to change - currently, the last modified date is 26th April 2022 as stated in the details section on the download page.

If anything breaks, ensure to check the page for updates or to guarantee no possible outages, upgrade your pricing plan.

Extra thoughts...

As you mention this is for a small project, I'm not sure what Azure pricing is like but I'd host the same project on AWS.

For the function itself, AWS Lambda's free tier includes 1M free requests per month (like Azure) & 400,000 GB-seconds of compute time per month which should be plenty.

For the connectivity, you'd need a VPC (free), an Internet Gateway (free negligible data transfer costs), an Elastic IP address (free) and a managed NAT gateway (roughly $1 a day depending on region).

Oh - and you'd get the added benefit of just having 1 Elastic IP address to provide to your hosting provider, which would always stay the same regardless of what 'pricing plan' you're on.

If anything, I'd also take a look at AWS as a potential option for future projects if anything but that's out of scope :)