Isolated Azure Function - SQL not being tracked inside Application Insights

[HarshitS00D]

Description

We are running Isolated Azure Function in .NET 8 and we have followed the steps mentioned in the documentation to configure application insights properly but, we are not able to track the SQL queries inside Application Insights.

We have gone through the similar issue reported https://github.com/Azure/azure-functions-dotnet-worker/issues/822 and have tried the workaround as well https://github.com/Azure/azure-functions-dotnet-worker/issues/822#issuecomment-1088012705 but it didn't help. I have attached our program.cs, host.json and .csproj file in the steps to reproduce.

Steps to reproduce

.csproj

<Project Sdk="Microsoft.NET.Sdk">
    <PropertyGroup>
        <TargetFramework>net8.0</TargetFramework>
        <AzureFunctionsVersion>v4</AzureFunctionsVersion>
        <OutputType>Exe</OutputType>
        <ImplicitUsings>enable</ImplicitUsings>
        <Nullable>enable</Nullable>
    </PropertyGroup>
    <ItemGroup>
        <PackageReference Include="ClosedXML" Version="0.102.2" />
        <PackageReference Include="Microservices.Common.API" Version="1.0.76" />
        <PackageReference Include="Microservices.Common.Repository" Version="1.2.9" />
        <PackageReference Include="Microsoft.Azure.Functions.Worker" Version="1.21.0" />
        <PackageReference Include="Microsoft.Azure.Functions.Worker.Sdk" Version="1.17.1" />
        <PackageReference Include="Microsoft.Azure.Functions.Worker.Extensions.Http.AspNetCore" Version="1.2.1" />
        <PackageReference Include="Microsoft.ApplicationInsights.WorkerService" Version="2.22.0" />
        <PackageReference Include="Microsoft.Azure.Functions.Worker.ApplicationInsights" Version="1.2.0" />
        <PackageReference Include="Microsoft.Azure.AppConfiguration.Functions.Worker" Version="7.1.0" />
        <PackageReference Include="Microsoft.Azure.Functions.Worker.Extensions.Http" Version="3.1.0" />
        <PackageReference Include="Microsoft.Azure.Functions.Worker.Extensions.Timer" Version="4.3.0" />
        <PackageReference Include="AutoMapper" Version="13.0.1" />
        <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="8.0.2" />
        <PackageReference Include="Microsoft.Extensions.Configuration.AzureAppConfiguration" Version="7.1.0" />
        <PackageReference Include="Microsoft.FeatureManagement" Version="3.1.1" />
    </ItemGroup>
    <ItemGroup>
        <None Update="host.json">
            <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
        </None>
        <None Update="local.settings.json">
            <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
            <CopyToPublishDirectory>Never</CopyToPublishDirectory>
        </None>
    </ItemGroup>
    <ItemGroup>
        <Using Include="System.Threading.ExecutionContext" Alias="ExecutionContext" />
    </ItemGroup>
</Project>

host.json

{
  "version": "2.0",
  "functionTimeout": "02:00:00",
  "logging": {
    "logLevel": {
      "default": "Information",
      "Host.Results": "Information",
      "Function": "Information",
      "Host.Aggregator": "Information"
    },
    "applicationInsights": {
      "samplingSettings": {
        "isEnabled": false,
        "excludedTypes": "Request"
      },
      "enableDependencyTracking": true,
      "dependencyTrackingOptions": {
        "enableSqlCommandTextInstrumentation": true
      },
      "enableLiveMetricsFilters": true
    }
  }
}

program.cs

using Microsoft.ApplicationInsights.WorkerService;
using Microsoft.Azure.Functions.Worker;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Microsoft.Extensions.Logging;
using Microsoft.FeatureManagement.FeatureFilters;
using Microsoft.FeatureManagement;

IHost host = new HostBuilder()
    .ConfigureFunctionsWebApplication(worker =>
    {
        worker.UseMiddleware<BindHttpRequestDataMiddleware>();
    })
    .ConfigureAppConfiguration((config) =>
    {
        -------------
    })
    .ConfigureServices(services =>
    {
        services.AddApplicationInsightsTelemetryWorkerService(new ApplicationInsightsServiceOptions
        {
            ConnectionString = Environment.GetEnvironmentVariable("APPLICATIONINSIGHTS_CONNECTION_STRING"),
            EnableAdaptiveSampling = false
        });
        services.ConfigureFunctionsApplicationInsights();

        services.Configure<LoggerFilterOptions>(options =>
        {
            LoggerFilterRule? toRemove = options.Rules.FirstOrDefault(rule => rule.ProviderName
                == "Microsoft.Extensions.Logging.ApplicationInsights.ApplicationInsightsLoggerProvider");

            if (toRemove is not null)
            {
                options.Rules.Remove(toRemove);
            }
        });

        services.AddAzureAppConfiguration();
        services.AddFeatureManagement().AddFeatureFilter<ContextualTargetingFilter>();
       ...
    })
    .Build();

host.Run();

[jviau]

host.json settings do not apply to the worker process: only to the host process. You will need to configure SQL dependency tracking yourself via services.ConfigureTelemetryModule<DependencyTrackingTelemetryModule>((module, o) => { /* configure */);

[HarshitS00D]

Adding comment to keep this alive, I'll verify with the above mentioned info and will update here after.

[rhythmnewt]

host.json settings do not apply to the worker process: only to the host process. You will need to configure SQL dependency tracking yourself via services.ConfigureTelemetryModule<DependencyTrackingTelemetryModule>((module, o) => { /* configure */);

You just blew my mind. While in hindsight this makes complete sense, I do not remember mentioned anywhere in documentation that host and worker process need to be configured separately for logging. If it exists and I just missed it, can you please provide a link?

[HarshitS00D]

Issue resolved from recommended solution as above, hence closing this issue. Thank you for the support.