How to EnableSqlCommandTextInstrumentation for Application Insights in a .Net Framework 4.7.2 project

Question

I have a problem adding EnableSqlCommandTextInstrumentation to my MVC .Net Framework 4.7.2 project / we use IIS server.

After following the automatic installation steps ,I followed this documentation https://learn.microsoft.com/en-us/azure/azure-monitor/app/asp-net-dependencies Advanced SQL tracking to get full SQL query

I added the necessary configuration in ApplicationInsights.config and installed Microsoft.Data.SqlClient latest version (4.8.4 at the time of the question)

  <TelemetryModules>
    <Add Type="Microsoft.ApplicationInsights.DependencyCollector.DependencyTrackingTelemetryModule, Microsoft.AI.DependencyCollector">
        <EnableSqlCommandTextInstrumentation>true</EnableSqlCommandTextInstrumentation>

The only part of the documentation I do not understand at all is this :

In the preceding cases, the proper way of validating that the instrumentation engine is correctly installed is by validating that the SDK version of collected DependencyTelemetry is rddp. Use of rdddsd or rddf indicates dependencies are collected via DiagnosticSource or EventSource callbacks, so the full SQL query won't be captured.

My ApplicationInsights on Azure receive all the correct Data but it's still missing the SQL Command details.

UPDATE 1

As suggested I ran the query and found out that in my .Net Core application where SQL commands work I have rdddsc:2.21.0-429 but in my .Net Framework application I have rddf:2.15.0-44797. I installed Microsoft.Data.SqlClient nugget in both my WebView project and my business project . The only place I have System.Data.SqlClient is in the app.configon my business project.

  <entityFramework>
    <providers>
      <provider invariantName="System.Data.SqlClient" type="System.Data.Entity.SqlServer.SqlProviderServices, EntityFramework.SqlServer" />
    </providers>
  </entityFramework>

I changed it to Microsoft.Data.SqlClient (not sure it's the right thing to do) but I still receive rddf:2.15.0-44797

UPDATE 2 And Solution

The problem was indeed that my projects were using System.Data.SqlClient.

I was using .net framework 4.7.2 so I updated all my projects to 4.8. This is a ressource that helped me a lot, I followed these steps and it worked it also explains the problem with System.Data.SqlClient. https://erikej.github.io/ef6/sqlserver/2021/08/08/ef6-microsoft-data-sqlclient.html

TL;DR 1- install the package in the project containing your context.

Install-Package ErikEJ.EntityFramework.SqlServer -Version 1.0.0-rc5

2-add this data annotation to your context. [DbConfigurationType(typeof(System.Data.Entity.SqlServer.MicrosoftSqlDbConfiguration))]

3- change your app.config in your projects to not use System.Data.SqlClient by changing the provider

<entityFramework>
  <providers>
    <provider invariantName="Microsoft.Data.SqlClient" type="System.Data.Entity.SqlServer.MicrosoftSqlProviderServices,
       ErikEJ.EntityFramework.SqlServer" />
  </providers>
</entityFramework>

4- remove any using statements from System.Data.SqlClient to Microsoft.Data.SqlClient if you refer to for example SqlParameter.

Answer 1

the proper way of validating that the instrumentation engine is correctly installed is by validating that the SDK version of collected DependencyTelemetry is rddp. Use of rdddsd or rddf indicates dependencies are collected via DiagnosticSource or EventSource callbacks, so the full SQL query won't be captured

To check what instrumentation engine is used run the following kusto query:

dependencies
| where ['type'] == "SQL"
| summarize count() by sdkVersion

if that does not output something like rddp:2.18.0-315 it isn't using the right mechanism.

Also make sure you are using the Microsoft.Data.SqlClient namespaces in your code, not the System.Data.SqlClient namespace. I once moved to the Nuget package but forgot to adjust the using statement at a few places.