SqlDependency.Start throws FileNotFoundException in Microsoft.Data.SqlClient 1.1.0

[Grauenwolf]

Describe the bug

When running against .NET Framework 4.6.1, SqlDependency.Start(string) throws a FileNotFoundException.

Exception message:
        Test method Tests.Appenders.SqlDependencyTests.SqlServerDataSourceTests_SqlDependency threw exception: 
        System.IO.FileNotFoundException: Could not load file or assembly 'Microsoft.Data.SqlClient, Version=1.10.19324.4, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5' or one of its dependencies. The system cannot find the file specified.

      Stack Trace: 
        RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
        RuntimeAssembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
        RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
        Activator.CreateInstance(String assemblyString, String typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, Evidence securityInfo, StackCrawlMark& stackMark)
        Activator.CreateInstance(String assemblyName, String typeName)
        AppDomain.CreateInstance(String assemblyName, String typeName)
        StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
        StackBuilderSink.SyncProcessMessage(IMessage msg)
        Exception rethrown at [0]: 
        RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
        RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
        _AppDomain.CreateInstance(String assemblyName, String typeName)
        SqlDependency.CreateProcessDispatcher(_AppDomain masterDomain)
        SqlDependency.ObtainProcessDispatcher()
        SqlDependency.Start(String connectionString, String queue, Boolean useDefaults)
        SqlDependency.Start(String connectionString)

To reproduce

Include a complete code listing (or project/solution) that we can run to reproduce the issue.

Partial code listings, or multiple fragments of code, will slow down our response or cause us to push the issue back to you to provide code to reproduce the issue.

Console.WriteLine("Hello World!");

Further technical details

<PackageReference Include="Microsoft.Data.SqlClient" Version="1.1.0" />

Additional context
The same code works correctly when targeting .NET Core 2.1.

The same code works correctly when using System.Data.SqlClient against .NET Framework 4.6.1.

Was reportedly fixed in #260, but I'm still seeing the error.

[cheenamalhotra]

Hi @Grauenwolf

Can you provide a repro to your use-case?

I'm not able to reproduce with a simple example I created:
TestSqlDependency.zip

Results on testing with v1.1.0 against different targets:
net46, net461, net48, netcoreapp2.1 and netcoreapp3.1

[Grauenwolf]

Interesting. When I run your sample, it works fine. But when I put the code in a MSTest project is fails for net461.

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

  <PropertyGroup>
    <TargetFrameworks>net461;netcoreapp3.1;netcoreapp2.1</TargetFrameworks>

    <IsPackable>false</IsPackable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.NET.Test.Sdk" Version="16.2.0" />
    <PackageReference Include="MSTest.TestAdapter" Version="2.0.0" />
    <PackageReference Include="MSTest.TestFramework" Version="2.0.0" />
    <PackageReference Include="coverlet.collector" Version="1.0.1" />
    <PackageReference Include="Microsoft.Data.SqlClient" Version="1.1.0" />
  </ItemGroup>

</Project>


using Microsoft.Data.SqlClient;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace UnitTestProject1
{
    [TestClass]
    public class UnitTest1
    {
        [TestMethod]
        public void TestMethod1()
        {
            string connectionStringNewDb = "Server=.;Database=SqlServerTestDatabase;Trusted_Connection=True;";
            SqlDependency.Start(connectionStringNewDb);
        }
    }
}

[Grauenwolf]

Same results when I set the test architecture to x86 or x64. Are there any other settings I should try?

[BoBiene]

@cheenamalhotra i have the same issue with the release nuget and the current 2.0 preview:
I have tries it with VS2017 and with VS2019 TestRunner (MSTest and Console)

Always, when SqlDependency.Start is called inside the MSTest execution i get the binding Error.
SqlConnection and Commands work fine.

System.IO.FileNotFoundException: Die Datei oder Assembly "Microsoft.Data.SqlClient, Version=2.0.20084.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5" oder eine Abh„ngigkeit davon wurde nicht gefunden. Das System kann die angegebene Datei nicht finden.Der Assemblymanager wurde geladen aus:  C:\Windows\Microsoft.NET\Framework\v4.0.30319\clr.dll
Als EXE-Datei ausgef?hrt.  C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\TestPlatform\testhost.x86.exe
--- Ein detailliertes Fehlerprotokoll folgt. 

=== Zustandsinformationen vor Bindung ===
LOG: DisplayName = Microsoft.Data.SqlClient, Version=2.0.20084.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5
 (Fully-specified)
LOG: Appbase = file:///C:/Program Files (x86)/Microsoft Visual Studio/2017/Enterprise/Common7/IDE/Extensions/TestPlatform/
LOG: Urspr?nglicher PrivatePath = NULL
Aufruf von Assembly : (Unknown).
===
LOG: Diese Bindung startet im default-Load-Kontext.
LOG: Die Anwendungskonfigurationsdatei wird verwendet: C:\Program Files (x86)\Microsoft Visual Studio\2017\Enterprise\Common7\IDE\Extensions\TestPlatform\testhost.x86.exe.Config
LOG: Die Hostkonfigurationsdatei wird verwendet: 
LOG: Die Computerkonfigurationsdatei von C:\Windows\Microsoft.NET\Framework\v4.0.30319\config\machine.config wird verwendet.
LOG: Verweis nach der Richtlinie: Microsoft.Data.SqlClient, Version=2.0.20084.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5
LOG: Die gleiche Bindung ist bereits aufgetreten und hat den Fehler hr = 0x80070002 verursacht.

Stapelverfolgung:
 
Server stack trace: 
   bei System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
   bei System.Reflection.RuntimeAssembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
   bei System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, IntPtr pPrivHostBinder, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
   bei System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, RuntimeAssembly reqAssembly, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks)
   bei System.Activator.CreateInstance(String assemblyString, String typeName, Boolean ignoreCase, BindingFlags bindingAttr, Binder binder, Object[] args, CultureInfo culture, Object[] activationAttributes, Evidence securityInfo, StackCrawlMark& stackMark)
   bei System.Activator.CreateInstance(String assemblyName, String typeName)
   bei System.AppDomain.CreateInstance(String assemblyName, String typeName)
   bei System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Object[]& outArgs)
   bei System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Object[]& outArgs)
   bei System.Runtime.Remoting.Messaging.StackBuilderSink.SyncProcessMessage(IMessage msg)
Exception rethrown at [0]: 
   bei System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
   bei System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
   bei System._AppDomain.CreateInstance(String assemblyName, String typeName)
   bei Microsoft.Data.SqlClient.SqlDependency.CreateProcessDispatcher(_AppDomain masterDomain) in E:\agent1\_work\28\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlDependency.cs:Zeile 537.
   bei Microsoft.Data.SqlClient.SqlDependency.ObtainProcessDispatcher() in E:\agent1\_work\28\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlDependency.cs:Zeile 566.
   bei Microsoft.Data.SqlClient.SqlDependency.Start(String connectionString, String queue, Boolean useDefaults) in E:\agent1\_work\28\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlDependency.cs:Zeile 783.
   bei Microsoft.Data.SqlClient.SqlDependency.Start(String connectionString) in E:\agent1\_work\28\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlDependency.cs:Zeile 646.
   bei inray.FAS.Utillities.DAL.ChangeNotifications.MSSQLServiceBrokerServerHandle.ServerContainer.TryStartDependency() in D:\Jenkins\workspace\inraySDK\inraySDK BranchBuild\inray.SDK.Data\DAL\ChangeNotifications\SQL\MSSQLServiceBrokerServerHandle.cs:Zeile 72.
   bei inray.FAS.Utillities.DAL.ChangeNotifications.MSSQLServiceBrokerServerHandle.TryStartSqlDependency(MSSQLServiceBrokerNotificationManager notificationManger) in D:\Jenkins\workspace\inraySDK\inraySDK BranchBuild\inray.SDK.Data\DAL\ChangeNotifications\SQL\MSSQLServiceBrokerServerHandle.cs:Zeile 115.
   bei inray.FAS.Utillities.DAL.ChangeNotifications.MSSQLServiceBrokerNotificationManager.StartHandles() in D:\Jenkins\workspace\inraySDK\inraySDK BranchBuild\inray.SDK.Data\DAL\ChangeNotifications\SQL\MSSQLServiceBrokerNotificationManager.cs:Zeile 136.
   bei inray.SDK.Data.Tests.DB.ChangeNotificationTests.DbTestChangeNotificationManagerBase.ChangeNotificationTestStartAfterStopDoesNotCauseDuplicateCallbacks() in D:\Jenkins\workspace\inraySDK\inraySDK BranchBuild\Tests\inray.SDK.Data.Tests\DB\ChangeNotificationTests\ChangeNotificationManagerTest.cs:Zeile 473.

[cheenamalhotra]

Hi @Grauenwolf @BoBiene

The proposed fix in PR #410 is still in discussions, you could give that a try and let us know your feedback! I just uploaded NuGet Package with the fix for trial: #410 (comment)

[Grauenwolf]

I don't have access to a computer right now. @BoBiene , can you test?

[BoBiene]

Hi @cheenamalhotra,

how can i use a nuget file directly in a PackageReference?

[BoBiene]

Hi @cheenamalhotra,

i did a try by uploading your package to our local nuget feed. But it seem like the PublicKeyToken is not set in your PR assembly. In my project i use a nuget package referencing the official nuget-package.
Can you please provide a nuget-package with the correct PublicKeyToken?

[cheenamalhotra]

@BoBiene

We don't use Public Key Tokens for building temporary dev packages, could you skip that check or let me know what error you're receiving?

Please note: This is only a dev package to validate PR changes, not recommended for any other usage.