App Insights does not collect SQL dependencies out of the box

[stas-sultanov]

Created Azure Functions project out of the box in Visual Studio

Microsoft.NET.Sdk.Functions 3.0.7 Microsoft.EntityFrameworkCore.SqlServer 3.1.4

AppInsights does not collect SQL dependencies. All other like HTTP, Azure BLOB, Azure Table are collected.

[Basaingeal]

I was running into this too, and I think I found the solution.

According to the first note in this documentation, SqlClient logging only work with Microsoft.Data.SqlClient >= 1.1.0. Looking on Nuget now Microsoft.EntityFrameworkCore.SqlServer depends on Microsoft.Data.SqlClient >= 1.0.19269.1. Since Nuget defaults to the earliest version for dependency resolution, by default your SqlClient won't be a high enough version.

Manually add Microsoft.Data.SqlClient with a later version to your function app and logging will work again.

This documentation note should be in more places, in my opinion, especially Azure Functions docs and not just Azure Monitor. A lot of people don't depend on Microsoft.Data.SqlClient directly and this is very easy to miss.

Edit: Actually, I'm still having trouble even with the latest Microsoft.Data.SqlClient. So I'm not sure there is a resolution. It's mentioned as resolved here: https://github.com/microsoft/ApplicationInsights-dotnet/issues/1347 but maybe it only works on .NET Framework right now and not .NET Core.

[stas-sultanov]

Microsoft.NET.Sdk.Functions 3.0.7 Microsoft.Data.SqlClient 2.0.0 Still no luck.

[nagarjunacspeaks]

We are facing the same issue. Any update on this? We are using .net core 3.1 and below. App insights is not SQL as a dependency in application map.

[sadgit]

Is anyone seeing ServiceBus/EventHub message tracing. That used to work and now seems to have stopped too

[gocha]

We are facing the same issue. After some testing, a few things became clear.

Workaround: Stick with System.Diagnostics.DiagnosticSource 4.7.0. You must use Microsoft.EntityFrameworkCore 3.1.3 or earlier for that.

• In my case, downgrading Microsoft.EntityFrameworkCore.SqlServer to 3.1.3 fixed the issue. https://stackoverflow.com/questions/63053334/enable-sql-dependency-in-application-insights-on-azure-functions-with-ef-core
• When using EF Core 3.1.4 or later, Visual Studio's Application Insights Search shows the following error --> AI (Internal): [Microsoft-Diagnostics-DiagnosticSource] ERROR: Exception in Command Processing for EventSource Microsoft-Diagnostics-DiagnosticSource: An instance of EventSource with Guid xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx already exists.
• The same problem occurred with Microsoft.EntityFrameworkCore.SqlServer 3.1.3 + System.Diagnostics.DiagnosticSource 4.7.1
• I did something similar with the ASP.NET Core Web App and had no problems
• I am using Microsoft.NET.Sdk.Functions 3.0.7
• I am using Azure Functions Core Tools 3.0.2881, Commit hash: fa9641ad925588192888bc977ff51d8a63536267, Function Runtime Version: 3.0.14287.0 (copied from func.exe)
  • It seemed that System.Diagnostics.DiagnosticSource.dll 4.7.0 is bundled with func.exe. I do not know if that is causing the problem.

I hope this helps.

[nagarjunacspeaks]

@gocha thank you. Tested with above workaround. It is working for me as well. But, not sure if there were any major bug fixes between EF Core 3.1.3 and 3.1.7/8 and if it will impact us in later stage. Any idea if this can be fixed in 3.1.8 or later version?

[older]

Not sure why downgrading Microsoft.EntityFrameworkCore.SqlServer to 3.1.3 works. It has dependency on the same version of Microsoft.Data.SqlClient as 3.1.4 This dependency was upgraded in 3.1.7 and starting from 3.1.7 it depends on Microsoft.Data.SqlClient >= 1.1.3

[older]

Could this be related to this issue which is fixed in Application Insights SDK 2.17beta1? But we cannot choose versions of Application Insights SDK used by functions host and have to wait until functions runtime is updated and update is rolled out?

[erwinkramer]

@gocha thanks! It's not just SQL dependencies, also servicebus dependencies are affected and solved the same way, by downgrading every reference to work with System.Diagnostics.DiagnosticSource 4.7.0. in My case I was also using the latest Microsoft.Graph which depended on a higher version, but downgrading to 3.20.0 worked with System.Diagnostics.DiagnosticSource 4.7.0.

[KZeronimo]

@erwinkramer - I checked my func app and it appears to be using System.DiaganosticSource <=4.7.0 but still SQL dependency is not surfacing in App Insights

Using Microsoft.Data.SqlClient 2.1.1 and Dapper 2.0.78 of database interaction

Thoughts?

host.json

{
  "version": "2.0",
  "logging": {
    "applicationInsights": {
      "dependencyTrackingOptions": {
        "enableSqlCommandTextInstrumentation": true
      },
      "samplingSettings": {
        "isEnabled": true,
        "excludedTypes": "Dependency;Request"
      }
    },
    "logLevel": {
      "default": "Information"
    }
  }
}

[ssa3512]

@jeffhollan @anthonychu Can one of you help direct this to the correct repository? This is all fixed in the latest combination of released package versions of App Insights 2.17/SqlClient 2.x and upgrading the functions runtime to to app insights 2.17 should fix the issue for anyone using EF Core >= 3.1.4.

This repository doesn't appear to actually host anything other than documentation and samples so it's not clear the right people have visibility on this issue.

[piotrzbyszynski]

@ssa3512 I tried EF 3.1.13 with Application Insights 2.17 and it didn't work. I had to downgrade to EF 3.1.3 and AI 2.15

[ssa3512]

@pzbyszynski yes - I believe the functions team has to update the host to use 2.17. It's not something someone consuming the Azure Functions runtime/sdk can simply do to make it work.

[anthonychu]

@brettsam can you help with this?

[brettsam]

@soninaren added this functionality in PR https://github.com/Azure/azure-webjobs-sdk/pull/2608. At first glance the host.json above looks correct -- Naren can you investigate this?

[ssa3512]

@brettsam the issue is that no sql dependencies are logged at all, not that the command text is missing.

[soninaren]

You need to add "EnableDependencyTracking": true to host.json for dependencyTrackingOptions to take effect. Adding sample host.json below

{
  "version": "2.0",
  "logging": {
    "applicationInsights": {
      "EnableDependencyTracking": true,
      "DependencyTrackingOptions": {
        "enableSqlCommandTextInstrumentation": true
      }
    }
  }
}

[ssa3512]

@soninaren I pushed the above host.json to a small test app (simple timer trigger, does a single find by ID on a database with a single table using EF Core) and am registering request telemetry and traces, but no dependencies. If I enable EF core logging, I see traces with the query getting logged, but still see zero dependency telemetry. At this point I getting specific query text logged is a nice to have - I just want to get a sense of the count of dependencies by type (SQL/HTTP/etc) and their timings.

As others have noted, there seem to be inconsistencies in behaviors between EF Core <= 3.1.3 and >= 3.1.4, in which they bumped their dependency version of System.Diagnostic.DiagnosticSource from 4.7.0 to 4.7.1. Additionally, I believe Microsoft.Data.SqlClient seems to have changed the namespace of the events they are firing when moving from v1 to v2. This was subsequently addressed in the AppInsights SDK (see AppInsights PR 2034) which fixed dependency capture of SQL emitted from SqlClient >= 2.0

It seems the ideal fix for all of this is to just update the functions host to ApplicationInsights 2.17.0 which forces a newer version of System.Diagnostic.DiagnosticSource, and provides the fix to support the new event name coming from SqlClient 2.0 or greater (for people already using that in their applications). Simply updating the AppInsights SDK package version in my project to 2.17 and updating does NOT resolve the issue, and dependencies are still not tracked. Updating to AppInsights SDK 2.17 on other non-functions projects that are doing plain ADO.NET results in dependencies being tracked fine. Likewise, using the latest version of the app insights SDK with EF Core >= 3.1.4 results in dependency telemetry being tracked as expected.

As an aside, it appears service bus telemetry may also be fixed by the update to a newer version of System.Diagnostic.DiagnosticSource (https://github.com/Azure/azure-functions-host/issues/7081)

Some of the traces getting logged in my sample functions app also seem to point to the issue: AI (Internal): [Microsoft-Diagnostics-DiagnosticSource] ERROR: Exception in Command Processing for EventSource Microsoft-Diagnostics-DiagnosticSource: An instance of EventSource with Guid adb401e1-5296-51f8-c125-5fda75826144 already exists. AI (Internal): [Microsoft-Diagnostics-DiagnosticSource] ERROR: Exception in Command Processing for EventSource Microsoft-Diagnostics-DiagnosticSource: Object reference not set to an instance of an object.

[mrpmorris]

I can confirm that downgrading my dependencies from 3.1.6 to 3.1.3 fixed the issue for me.

[rhythmnewt]

Any idea when the functions host can be updated with Application Insights 2.17.0?

[gsosij]

Any update on this would be really appreciated, we are also suffering with the same issue.

[russpect]

We are also experiencing the same issue. The rollback to Microsoft.EntityFrameworkCore.SqlServer version 3.1.3 resulted in the dependency tracking for SQL and Service Bus to log as before. It does mean however we are stuck on version 3.1.3 until the bug is fixed and it would be great to be able to upgrade soon. Are there any plans to fix the issue? Thanks

[nicklasjepsen]

We are not using EF in our functions and we have the same problem. We are using Microsoft.Data.SqlClient 2.1.2 and still don't see any SQL dependencies logged to AppInsights. This is quite problematic and we currently do not have a work around for this.

Any know workarounds? Timeline for a fix?

[Planche95]

The same problem here, downgrade to 3.1.3 also works for me

[erwinkramer]

@ankitkumarr I see you provided a (possible?) fix. Can you update us on the status?

[piotrzbyszynski]

This is soooo frustrating... @erwinkramer asked a question over a month ago, with no answer...

I take part in several issues that seem a quick fix, still not fixed after several years. This particular issue was submitted over a year ago. As a user of your services I fell completely ignored. And just to remind you, AI is not some random open source project, based on good will of it's maintainer. You are charging some serious bucks for it!!! And support definitely does not match price tag unfortunately 😣

[HenrikSommer-Energinet]

I reported the problem to dotnet/efcore, but they closed it: https://github.com/dotnet/efcore/issues/25613 And ask me to post here.

This is still a problem 3.1.4 to 3.1.18 And it is not just SQL dependencies that are not logged.

[HenrikSommer-Energinet]

As other have pointed out the problem is System.Diagnostic.DiagnosticSource I look into it abit more and found that 4.7.0 does not include the dll for System.Diagnostic.DiagnosticSource and 4.7.1 does.

So as a workaround i have just made a script that remove System.Diagnostic.DiagnosticSource.dll before I deploy the functoin and it fix the problem. So I can now use EF 3.1.19

[ttq-ak]

Hi @HenrikSommer-eng, is there any chance you could post the script you've used? Thanks

[HenrikSommer-Energinet]

@ttq-ak sure this is part of pipeline task: (remove the file from the zip file we deploy)

    - task: PowerShell@2
      displayName: 'Fix log bug:'
      inputs:
        targetType: 'inline'
        script: |
          # EF core 3.1.4+ include System.Diagnostics.DiagnosticSource.dll and it is breaking all our logging
          # https://github.com/Azure/Azure-Functions/issues/1613
          
          [Reflection.Assembly]::LoadWithPartialName('System.IO.Compression')
          $zipfile = (Get-Item -Path "$(build.artifactstagingdirectory)\\**.zip").FullName
          $files   = "System.Diagnostics.DiagnosticSource.dll"
          Write-Host "Zip: $zipfile"

          $stream = New-Object IO.FileStream($zipfile, [IO.FileMode]::Open)
          $mode   = [IO.Compression.ZipArchiveMode]::Update
          $zip    = New-Object IO.Compression.ZipArchive($stream, $mode)
          ($zip.Entries | ? { $files -contains $_.Name }) | % { $_.Delete() }
          $zip.Dispose()
          $stream.Close()
          $stream.Dispose()