Missing AppDependencies entry when using Microsoft.Data.SqlClient v2.0.0

[karinazhou]

Environment

• NuGet packages:
  "Microsoft.ApplicationInsights.Wcf" 0.28.0-build06820
  "Microsoft.ApplicationInsights.WindowsServer" 2.14.0
  "Microsoft.Data.SqlClient" 2.0.0

• Runtime version

  net48

• Hosting environment:
  Windows

Describe the bug

This issue is originally reported here :
dotnet/SqlClient#678

Detailed investigation can be found here:
dotnet/SqlClient#678 (comment)

Since the EventSource name in Microsoft.Data.SqlClient v2.0.0 has been changed from Microsoft-AdoNet-SystemData to Microsoft.Data.SqlClient.EventSource, the DependencyCollector is not able to capture the EventSource from Microsoft.Data.SqlClient.

https://github.com/microsoft/ApplicationInsights-dotnet/blob/develop/WEB/Src/DependencyCollector/DependencyCollector/Implementation/FrameworkSqlEventListener.cs#L24

To Reproduce

The repo WCF Service application is attached. Before you compile it, modify the SQL Server connection string with any server you have access to. It's by default using the localhost server.

You can use the following queries to create the database and table:

CREATE DATABASE [DB1] 
GO 

USE [DB1]
CREATE TABLE [dbo].[Users]( [Id] [int] NOT NULL, [Name] [nvarchar](50) NOT NULL, CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] )
GO

USE [DB1]
GO
INSERT INTO [Users] VALUES(1, 'one')
GO

Run the WCF Service application from Visual Studio 2019 and attach the service to WCF Test Client. When invoking the GetData(), there are only AppRequests entry being captured in output window.

Application Insights Telemetry (unconfigured): {"name":"AppRequests","time":"2020-09-04T21:51:51.5458149Z","tags":

Expected

The following AppDependencies entry with type SQL is captured by Application Insights when using Microsoft.Data.SqlClient v1.1.3:

Application Insights Telemetry (unconfigured): {"name":"AppDependencies","time":"2020-08-31T18:25:00.4838283Z","tags"... ,"success":true,"type":"SQL","target":"localhost | DB1","properties":{"DeveloperMode":"true","_MS.ProcessedByMetricExtractors":"(Name:'Dependencies', Ver:'1.1')"}}}}

WcfService2.zip

[ulfaxelssoncab]

Also see dotnet/SqlClient#436.

Right now nothing that uses Microsoft.Data.SqlClient 2.0+ and is hosted on Classic Net Fwk gets SQL dependencies logged.

And anything that uses Microsoft.Data.SqlClient (any version) and happens to also reference/use System.Data.SqlClient so that both tries to initialize the EventSource that got renamed for MDS 2.0 and is hosted on Classic Net Fwk also gets no SQL dependencies logged from the SqlClient that had it's registration fail.

[MillenniumV3]

Why is this labeled enhancement instead of Bug? Replaced System.Data.SqlClient with Microsoft.Data.SqlClient yesterday to add CommandText to the dependency Loggin (Framework 4.8) like the official documentation says. Just to find out I ended up with no SQL dependency at all. Looks to me like something that has to be fixed quickly (or at least mention the problem in the documentation)

[SamNutkins]

@karinazhou

I definitely agree with @MillenniumV3 - this is a bug. I'm experiencing the issue described here after upgrading from .NET/EF Core 2.2 to 3.1. SQL dependencies are no longer logged to app insights. That issue lead me here, but this is an enhancement? What is the timeline for a fix?

I'm also not sure why this issue and dotnet/SqlClient#678 say this is limited to .NET FX, since it is occurring in Azure Functions on .NET Core 3.1.

[zx48]

I agree with @MillenniumV3 also, this is a bug and needs fixing ASAP.

I've wrestled with this issue for the last couple of days and it's not straightforward to realise that compatibility with Microsoft.Data.SqlClient was dropped when they released 2.0. Many others are going to find this confusing too.

PR #2034 suggests a fix, can we get this or an alternative in vNext please?

[devvitesse]

Another vote to fix this from us. We've upgraded our SqlClient to the latest version and have lost our SQL telemetry. This is a bug.

[karinazhou]

@SamNutkinsPM360 If you see this happens on .NET Core 3.1, it could be due to the same reason because the driver uses the same EventSource name for both target frameworks. One thing to be noticed is that the Microsoft.Data.SqlClient driver didn't have EventSource support for .Net Core before. The support was added to the driver recently:
dotnet/SqlClient#461

Also, I agree that the same change should also cover .Net Core if there are missing EventSource name for MDS driver.

[rhythmnewt]

Is there any movement on this bug?

[SamNutkins]

@karinazhou

It's been over a month now. I just want to reiterate that this is a bug from the perspective of someone upgrading to the latest .NET/EF Core 3.1 libraries on Azure functions. Our team is considering downgrading libraries in order to restore this functionality, but want a timeline for this fix as part of the consideration. Any update?

[karinazhou]

Hi @SamNutkinsPM360 , I am afraid that the temporary workaround is to downgrade the libraries before the fix is out.

Hi @cjindustries , is there any update on this one? And can we know that any timeline for the fix can be estimated?

[zx48]

@karinazhou there is no update that I know of (I'm not the author of PR #2034) and to be honest I have no idea how this project is being managed or how things get triaged/included! This feels like a very big bug but it's not getting any attention.

PR #2034 is a working fix but the author doesn't have the time to fufill all of the submission criteria. I sympathise; I think the barrier to entry is too high given most of us don't work full time for Microsoft.

[karinazhou]

@cjindustries Sorry for my mistake. I mentioned to you by accident.

Hi @cijothomas , is there any update on this one? And can we know that any timeline for the fix can be estimated?

[cijothomas]

@karinazhou Sorry for the delay. The team has been occupied with other products, and we didn't have the bandwidth to include this bug fix in the coming release (2.16).
Tagging this for 2.17. Don't have an ETA on when 2.17 GA will release, but we'll release a 2.17-beta1 in next 1-2 weeks.

[TimothyMothra]

Hello all, this issue has been fixed and will be available in 2.17-beta1.
We're starting our release process and hoping to have this available by Friday (12/18) or Monday (12/21).

[MaximG1234]

Hi, I am using version 2.17-beta1 and still not getting any dependency data. Was this change committed to the beta patch?

https://stackoverflow.com/questions/65528423/application-insights-in-console-wpf-application-with-dependencytracking

[ulfaxelssoncab]

Hi, I am using version 2.17-beta1 and still not getting any dependency data. Was this change committed to the beta patch?

https://stackoverflow.com/questions/65528423/application-insights-in-console-wpf-application-with-dependencytracking

This change was never about getting SQL query text in the dependency logging but rather about getting any SQL dependency loggning when using (strictly) Microsoft.Data.SqlClient v2+.

If I read the SO question correctly you are not using Microsoft.Data.SqlClient v2+ ?

[SamWhitby]

I'm seeing the same issue where no dependency calls are logged when using .net framework 4.8 with the latest version of Microsoft.Data.SqlClient (v3.0.0) and the Microsof.ApplicationInsights packages at v2.17.0.

Downgrading Microsoft.Data.SqlClient to v2.1.3 resolves the issue and dependencies are logged correctly.

Is there something else that needs to be done to make Microsoft.Data.SqlClient v3.0.0 work with .net framework or is this another bug?