Automatic instrumentation of System.Data.SqlClient not working.

[ps-weber]

Bug Report

Symptom

Describe the bug
No Spans are created for SQL queries for my ASP.NET Core app with System.Data.SqlClient 4.8.5.

Expected behavior
Spans for SQL queries are created.

Runtime environment (please complete the following information):

• OpenTelemetry Automatic Instrumentation version: 1.1.0
• OS: Windows 10 22H2 (also tested on Windows Server 2019)
• .NET version: .NET 6.0

Additional context

• When I manually instrument the App with the nuget OpenTelemetry.Instrumentation.SqlClient, the spans are generated correctly
• When I use Microsoft.Data.SqlClient, the spans are generated correctly

I found only these two lines regarding SqlClient in the log files:

[2023-10-26T13:48:07.417936600Z] [24020|38408] [debug] ModuleLoadFinished: 140721365861160 System.Data.SqlClient AppDomain 1529395365008 [clrhost]  | IsNGEN = false | IsDynamic = false | IsResource = false
[2023-10-26T13:48:07.418424300Z] [24020|5960] [debug] Requesting Rejit for Module: System.Data.SqlClient

[ps-weber]

Sample Program with which I can reproduce the issue:

using System.Data.SqlClient;

System.Diagnostics.ActivitySource activitySource = new System.Diagnostics.ActivitySource("MyApp.SqlClientTest", "1.0.0");

using var activity = activitySource.StartActivity("SqlClientTest.Main");
using (var sqlconnection = new SqlConnection(new SqlConnectionStringBuilder
{
    InitialCatalog = "test",
    DataSource = "np:(local)",
    Encrypt = false,
}.ConnectionString))
{
    sqlconnection.Open();
    using var cmd = sqlconnection.CreateCommand();
    cmd.CommandText = "select name from testtable";
    using (var reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine(reader.GetString(0));
        }
    }
}

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net6.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="System.Data.SqlClient" Version="4.8.5" />
    <PackageReference Include="System.Diagnostics.DiagnosticSource" Version="7.0.2" />
  </ItemGroup>

</Project>

Register-OpenTelemetryForCurrentSession -OTelServiceName SqlClientTest
$env:OTEL_DOTNET_AUTO_TRACES_ADDITIONAL_SOURCES="MyApp.*"
./MyTestApp.exe

When I switch to Microsoft.Data.SqlClient, the spans appear correctly in jaeger.

dotnet version 6.0.415
Windows 10 22H2 19045.3570

[ps-weber]

Looks like System.Data.SqlClient is missing here:

opentelemetry-dotnet-instrumentation/src/OpenTelemetry.AutoInstrumentation/Loading/Initializers/SqlClientInitializer.cs

Line 30 in d470e80

lazyInstrumentationLoader.Add(new GenericInitializer("Microsoft.Data.SqlClient", InitializeOnFirstCall));

[pellared]

Possibly caused by open-telemetry/opentelemetry-dotnet-contrib#1739.
You can also see #2177

[ps-weber]

I don't think so, I added System.Data.SqlClient in SqlClientInitalizer.cs and that fixed the issue for me.

[RassK]

Seems correct cause:

we have not included https://www.nuget.org/packages/System.Data.SqlClient/ for .NET Core, instead System.Data namespace is expected for .NET Fx only.
Seems easy fix but needs a separate test application.