Search code examples
azure-functionsazure-sql-databaseazure-managed-identity

Azure Functions v4 in Private vNet Cannot Connect to Azure Sql

I've been through several articles and posts and cannot get my v4 Functions application to connect to my azure sql server. I have a regular web app in another vnet that can access the DB out of the box with no extra configuration.

Here's my vNet configuration:

Vnet: 10.0.0.0/24
DMZ Subnet: 10.0.0.0/27
Web App Integration Subnet: 10.0.0.32/29 (this application connects to an api in a peered vnet)
Storage Subnet: 10.0.0.40/29
Function App Integration Subnet: 10.0.0.48/29
Resources Subnet: 10.0.0.64/27

My Function app is set up so the incoming traffic comes through the DMZ and the vNet integration is through the integration subnet.

For my vNet Integration I have the following options enabled:

  • Outbound internet traffic
  • Content Storage

The Integration subnet has Service endpoints added for:

  • Microsoft.Sql
  • Microsoft.Storage

I have Private DNS Zones in the vNet for both privatelink.blob.core.windows.net and privatelink.file.core.windows.net pointing to my secured storage.

My Sql Server instance has a Virtual Network rule for the Function App Integration subnet

My connection string (which works for other applications) is this:

Server=tcp:mydb.database.windows.net,1433;Database=MyDatabase;User Id=<Managed Ident Client Id>;Authentication=Active Directory Managed Identity;MultipleActiveResultSets=True;App=MyFunction

where <Managed Ident Client Id> is the client id of a user-assigned managed identity. We use this kind of connection for all our app services and works fine elsewhere.

Relevant Function App Configuration:

  • AzureWebJobsStorage is deleted
  • AzureWebJobsStorage__accountname = <my storage account name>
  • AzureWebJobsStorage__credential = managedidentity
  • AzureWebJobsStorage__clientId = <Managed Identity Client Id>
  • WEBSITE_CONTENTOVERVNET = 1
  • WEBSITE_VNET_ROUTE_ALL = 1

The overrides for AzureWebJobsStorage let me connect to storage using a user-assigned managed identity. Before I set this up, the below health check would fail with a grpc error.

In my function app I have a basic health check that looks like this:

[Function(FunctionConstants.HealthCheck.Basic)]
public async Task<HttpResponseData> BasicHealthCheckAsync([HttpTrigger(AuthorizationLevel.Admin, "post", Route = "health-check/basic")]
    HttpRequestData request, FunctionContext context)
{
    var logger = context.GetLogger<HealthCheckHttpActivator>();

    logger.LogTrace("Request made to basic health check");

    var response = request.CreateResponse(HttpStatusCode.OK);

    await response.WriteAsJsonAsync(new
    {
        ServerTimeUtc = DateTime.UtcNow
    });

    return response;
}

This endpoint works fine and runs.

I have another endpoint for verifying connection to the database, which throws this error:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

This error started before I added the service endpoint. Per some articles and posts it was recommended to add, so I did. However, my function app still cannot connect.

Using the console on my function app, I can run this command and get success:

tcpping mydb.database.windows.net:1433

So I'm confused why the console works but the function app itself cannot resolve to the database - especially when a regular app service on another vnet set up similarly can connect out of the box.

Solution

  • Refer my SO answer to enable VNET integration between Azure Function App and Azure SQL successfully. I have integrated Azure Web app with Azure SQL in the answer.

    I have created one VNET with 2 subnets:- default and default2 and added 2 service endpoints, Microsoft.Web and Microsoft.Sql:-

    enter image description here

    enter image description here

    I allowed VNET + default subnet in Azure Functions Networking Inbound rule:-

    enter image description here

    I allowed VNET + default2 subnet in Azure SQL Networking > Selected Networks and Checked Allow Azure services and resources to access this server :-

    enter image description here

    enter image description here

    Even if your VNET's are peered and Subnets are in different VNET's this configuration will work.

    Also, Add your Function App managed atleast contributor role at the Resource group or subscription level inorder to connect to Azure SQL properly.