Unable to access SqlClient in Azure app function V3

[mkarthik156]

With the latest Azure function V3 I'm having trouble using Sql client.
Tried System.Data.SqlClient & Microsoft.Data.SqlClient packages, both throws error.
Attaching both the error logs.
System.Data.SqlClient_ErrorLog.txt
Microsoft.Data.SqlClient_ErrorLog.txt

Please provide the following:

• Timestamp: 2020-04-30T10:51:13.167
• Function App version: ~3
• Function App name:
• Function name(s) (as appropriate):
• Invocation ID: 0bdb08df-0b4b-4f3a-bf95-33b8b7d39d3c
• Region: East US

Repro steps

Error1:
Provide the steps required to reproduce the problem:
By default System.Data.SqlClient package being available, try to establish a database connection.
This will throw below error.
"Unable to load DLL 'sni.dll' or one of its dependencies: The specified module could not be found. (0x8007007E)"

Error2:
Install Microsoft.Data.SqlClient package (Latest version 1.1.2), try to establish a database connection.
Following error message is thrown.
"Microsoft.Data.SqlClient is not supported on this platform."

Related information

Provide any related information

• Programming language used C#
• Links to source
• Bindings used

[WinInsider]

Getting "Microsoft.Data.SqlClient is not supported on this platform" too, with Azure app function V3 and .NET Core 3.1

Renamed folder runtimes to bin; and have placed function.deps.json in bin folder... no help, same error

[npnelson]

I just ran into this issue as well. In my case, it runs fine locally on Windows. It also seems to run fine on Azure with Windows or Linux consumption plans.

However, any attempt to run in a linux container, either locally or in Azure, fails with the platform not supported error.

I noticed that the dockerfiles for the containers were recently updated to the latest function runtime Azure/azure-functions-docker#241, but I don't think the updated images have been published yet. I wonder if the older runtime has something to do with it?

You can find a simple reproduction case here:

https://github.com/npnelson/AzureFunctionSqlClientDemo (be sure to read the repo's Readme if you try it out)

[npnelson]

After more investigation, when I build in Release with Visual Studio/Docker it works, but in Debug/Docker it doesn't and all production scenarios for me work.

[brettsam]

For those hitting this:

1. Can you share your csproj here? (thanks @npnelson for the repro proj)
2. Are you seeing this locally or only deployed?
3. Does it only happen with Debug builds or also Release?
4. What version of Functions are you deploying this to?

I just tried the @npnelson repro and it worked locally and deployed in both Debug and Release. Locally I'm running 3.0.13139.0 and in Azure I'm running 3.0.13353.0.

Is everyone else running into this only on Docker+Linux+Debug? Or are there other configurations that aren't working? I'm setting up a repro for this scenario now...

[brendonatkins]

Hello @brettsam

I'm running into the same errors as described in this thread. Below are different scenarios I'm running which encounter same exception. I've also pointed out where exception is not encountered when executing same code. I'll attempt to soon provide a repro project to show issue.

The scenario we have is specific to when Azure Client function is restarted and an Orchestration Function attempts to retry after restart. I've attempted to call out three scenarios below showing error behaviour and also successful behaviour. For the scenarios below I used a SqlCommand to query a table that did not exist to force an exception within Activity Function 1. This would result in exception being thrown and expectation is Orchestration to handle retries.

As a workaround for now - we are invoking the SqlConnectionStringBuilder class on Startup to force DLL to load. This seems to load correct DLL for all scenarios.

Details

• .NetCore = v3.1

• Microsoft.NET.Sdk.Functions = v3.0.3 (Problem also occurs on latest 3.0.7)

• Microsoft.Azure.WebJobs.Extensions.DurableTask = v.2.2.1

• Microsoft.Data.SqlClient = v1.1.2

• Error occurs in Debug and Release

• IDbConnection instance created and injected via Transient Dependency Injection

• Orchestration is set to retry 10 times at a 5 minute retry time with backoff-coefficient equal to 1

• Execution of function is within developer environment using Visual Studio Ctrl+F5 or dotnet run command to execute Azure Function runtime.

• Windows + Debug/Release

• Connecting to Sql Server 2019 Developer Edition - local installed

Scenario A - Exception - After retry and function restart

1. Client Function reacts to Azure Service Bus, initiates Orchestration
2. Orchestration initiates Activity Function 1
3. Activity Function 1 then attempts to interact with database succesfully.
4. Throw exception in Activity Function
5. Client Function is then shut down
6. Client Function is restarted, time elapses and Orchestration retries
7. Activity function initiated, sql connection creation has PlatformNotSupportedException thrown

Scenario B - Successful Behaviour - Retry with no function restart

1. Client Function reacts to Azure Service Bus, initiates Orchestration
2. Orchestration initiates Activity Function 1
3. Activity Function 1 then attempts to interact with database succesfully.
4. Throw exception in Activity Function
5. Time elapses and Orchestration retries
6. Activity function initiated, sql connection created and operates as expected

Scenario C - Successful Behaviour - Retry orchestration works after restart when new orchestration created before retry

1. Client Function reacts to Azure Service Bus, initiates Orchestration A
2. Orchestration A initiates Activity Function 1
3. Activity Function 1 then attempts to interact with database succesfully.
4. Throw exception in Activity Function
5. Client Function is then shut down
6. Client Function is restarted
7. New Azure Service Bus message, initiate new Orchestration B
8. Orchestration B initiates Activity Function 1
9. Activity Function interacts with database successfully
10. Time elapses and Orchestration A retries
11. Activity function initiated, sql connection created and operates as expected

Attachments:

• platformnotsupported_successscenario.png - shows process monitor output when orchestration invokes activity function for first time for new orchestration. Marked red row indicates application loading dll from netcoreapp2.1 sub-directory (Scenario A steps 1 - 3)
  

• platformnotsupported_failscenario.png - shows process monitor output when orchestration invokes activity function after retry. Marked red row indicates when SqlConnection instance is created (Scenario A steps 6-7)
  

• platformnotsupported_orchestrationretrysucceedsafterneworchestration.png shows Scenario C. Text marked when New Orchestration B is invoked and when Orchestration A retries (Steps 6-11)
  

[mkarthik156]

Hey @brettsam ,
I'm facing this issue while using in-portal development method for setting up the function.
The version of function that I use is
Runtime version: 3.0.13353.0 (~3)

[APIWT]

@brettsam @brendonatkins Having a very similar issue here: #6094

It is intermittent but popping up just often enough that we are getting very nervous about it.

[brettsam]

@brendonatkins thank you! When you say "Client Function is then shut down" in your failure scenario -- how is the app shutting down? And you're doing all this locally? I'm trying a bunch of combinations of things to try to reproduce this and everything seems to work as expected for me (I never see a PlatformNotSupportedException).

Were you (or anyone else, for that matter) able to get a small repro together that I can run and try it? Having it fail locally is ideal b/c then I can debug into it directly and figure out what's happening.

[APIWT]

@brettsam We are not able to reproduce this issue locally. This is the full exception (with our application portion redacted) that we get in Azure. Please note this is a very intermittent issue, but it pops up often enough to be very concerning. I assume it has to do with the VM environment that gets unlucky enough to run the code:

{
  "$type": "System.PlatformNotSupportedException, System.Private.CoreLib",
  "ClassName": "System.PlatformNotSupportedException",
  "Message": "Microsoft.Data.SqlClient is not supported on this platform.",
  "Data": null,
  "InnerException": null,
  "HelpURL": null,
  "StackTraceString": "   at Microsoft.Data.SqlClient.SqlConnection..ctor(String connectionString)\r\n   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.CreateDbConnection()\r\n   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.get_DbConnection()\r\n   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.CreateCommand(RelationalCommandParameterObject parameterObject, Guid commandId, DbCommandMethod commandMethod)\r\n   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.InitializeReaderAsync(DbContext _, Boolean result, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func`4 operation, Func`4 verifySucceeded, TState state, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.AsyncEnumerator.MoveNextAsync()\r\n   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)\r\n   at Microsoft.EntityFrameworkCore.Query.ShapedQueryCompilingExpressionVisitor.SingleOrDefaultAsync[TSource](IAsyncEnumerable`1 asyncEnumerable, CancellationToken cancellationToken)\r\n   at REDACTEDFunctionName.Run(NonAppConsumersCreateMessage nonAppConsumersCreateMessage, ILogger logger, ExecutionContext executionContext, CancellationToken cancellationToken) in D:\\a\\1\\s\\src\\Functions\\NonAppConsumers\\Functions\\NonAppConsumersCreateFunction.cs:line 51\r\n   at Microsoft.Azure.WebJobs.Host.Executors.VoidTaskMethodInvoker`2.InvokeAsync(TReflected instance, Object[] arguments) in C:\\projects\\azure-webjobs-sdk-rqm4t\\src\\Microsoft.Azure.WebJobs.Host\\Executors\\VoidTaskMethodInvoker.cs:line 20\r\n   at Microsoft.Azure.WebJobs.Host.Executors.FunctionInvoker`2.InvokeAsync(Object instance, Object[] arguments) in C:\\projects\\azure-webjobs-sdk-rqm4t\\src\\Microsoft.Azure.WebJobs.Host\\Executors\\FunctionInvoker.cs:line 52\r\n   at Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.InvokeAsync(IFunctionInvoker invoker, ParameterHelper parameterHelper, CancellationTokenSource timeoutTokenSource, CancellationTokenSource functionCancellationTokenSource, Boolean throwOnTimeout, TimeSpan timerInterval, IFunctionInstance instance) in C:\\projects\\azure-webjobs-sdk-rqm4t\\src\\Microsoft.Azure.WebJobs.Host\\Executors\\FunctionExecutor.cs:line 585\r\n   at Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithWatchersAsync(IFunctionInstanceEx instance, ParameterHelper parameterHelper, ILogger logger, CancellationTokenSource functionCancellationTokenSource) in C:\\projects\\azure-webjobs-sdk-rqm4t\\src\\Microsoft.Azure.WebJobs.Host\\Executors\\FunctionExecutor.cs:line 532\r\n   at Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithLoggingAsync(IFunctionInstanceEx instance, ParameterHelper parameterHelper, IFunctionOutputDefinition outputDefinition, ILogger logger, CancellationTokenSource functionCancellationTokenSource) in C:\\projects\\azure-webjobs-sdk-rqm4t\\src\\Microsoft.Azure.WebJobs.Host\\Executors\\FunctionExecutor.cs:line 468\r\n   at Microsoft.Azure.WebJobs.Host.Executors.FunctionExecutor.ExecuteWithLoggingAsync(IFunctionInstanceEx instance, FunctionStartedMessage message, FunctionInstanceLogEntry instanceLogEntry, ParameterHelper parameterHelper, ILogger logger, CancellationToken cancellationToken) in C:\\projects\\azure-webjobs-sdk-rqm4t\\src\\Microsoft.Azure.WebJobs.Host\\Executors\\FunctionExecutor.cs:line 278",
  "RemoteStackTraceString": null,
  "RemoteStackIndex": 0,
  "ExceptionMethod": null,
  "HResult": -2146233031,
  "Source": "Microsoft.Data.SqlClient",
  "WatsonBuckets": null
}

[APIWT]

@brettsam It looks like this is what is happening: https://github.com/dotnet/SqlClient/wiki/Frequently-Asked-Questions#11-why-do-i-get-a-platformnotsupported-exception-when-my-application-hits-a-sqlclient-method

Basically, the Nuget package has a fallback DLL that stubs the constructor of SqlConnection and essentially throws a PlatformNotFoundException if it is loaded rather than a platform specific DLL (for example, the DLL for Windows). My best guess here is that the VM that is running the Azure Function is not using the latest version of .NET Core or similar, which leads it to load the fallback version instead of the version necessary for the platform.

@brendonatkins Your suggestion seems interesting:

As a workaround for now - we are invoking the SqlConnectionStringBuilder class on Startup to force DLL to load. This seems to load correct DLL for all scenarios.

Does this mean that if inside the Startup class I new up a SqlConnectionStringBuilder, but do not even use it to initialize my SqlConnection (since we are using EF Core via AddDbContext) then I should be able to work around the error?

[brettsam]

@npnelson -- thanks again for the repro. When you say this fails when running locally in Docker, is that when launching the app via Visual Studio? I think that VS is doing something odd and the function.deps.json file doesn't get seen when doing this, which would cause exactly what you're seeing. I was able to repro this via VS but then when I built and ran my container on my own things worked as expected. That would also explain why it's working in App Service for you.

@APIWT -- is it possible for you to share your .csproj file? That'd help narrow down exactly what libraries, SDKs, etc, you are using. Also, if you could share your Function App name (either explicitly or privately), I can see if I find anything from the backend logs.

And again, if anyone has a simple repro that shows this failing, I'd love to see it -- I'm still not able to repro this (outside of the "launching Docker via VS" scenario from @npnelson). It's also useful to check via kudu (go to https://{yoursite}.scm.azurewebsites.net/DebugConsole) and look in D:\home\site\wwwroot\bin. Do you see a function.deps.json file there? If not, can you move to the latest version of Microsoft.NET.Sdk.Functions (the latest 3.x version if you're on v3 Functions and... confusingly... the latest 1.x version if you're on v2 Functions) and try again?

[APIWT]

@brettsam Thank you so much for getting back to me.

This is the invocation ID and timestamp:

2020-05-28T18:39:23Z   [Information]   Executing 'SomeFunction' (Reason='This function was programmatically called via the host APIs.', Id=8eac5927-5f70-4791-b76b-a9b287a7fb83)

Our region is East US 2.

Here is my Functions CSPROJ (with project name redacted):

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

    <PropertyGroup>
        <TargetFramework>netcoreapp3.1</TargetFramework>
        <AzureFunctionsVersion>v3</AzureFunctionsVersion>
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="Microsoft.Azure.Functions.Extensions" Version="[1.0.0]" />
        <PackageReference Include="Microsoft.Azure.WebJobs.Extensions.DurableTask" Version="[2.2.1]" />
        <PackageReference Include="Microsoft.Azure.WebJobs.Extensions.Http" Version="[3.0.2]" />
        <PackageReference Include="Microsoft.Azure.WebJobs.Extensions.Storage" Version="[3.0.10]" />
        <!-- TODO: Investigate whether this is needed in Functions v3 -->
        <PackageReference Include="Microsoft.Azure.WebJobs.Script.ExtensionsMetadataGenerator" Version="[1.1.5]" />
        <PackageReference Include="Microsoft.Extensions.Http.Polly" Version="[3.1.3]" />
        <PackageReference Include="Microsoft.NET.Sdk.Functions" Version="[3.0.5]" />
    </ItemGroup>

    <ItemGroup>
        <ProjectReference Include="..\Infrastructure\Infrastructure.csproj" />
    </ItemGroup>

    <ItemGroup>
        <None Update="host.json">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
        </None>
        <None Update="local.settings.json">
            <CopyToOutputDirectory>Always</CopyToOutputDirectory>
            <CopyToPublishDirectory>Never</CopyToPublishDirectory>
        </None>
    </ItemGroup>

</Project>

This file is our Directory.Build.targets file for the Functions application (note the workaround for publishing extensions.json properly and the associated Github issue):

<!-- This is to workaround the issue where the extensions.json file generated at publish time excludes the custom startup -->
<!-- Found here: https://github.com/Azure/azure-functions-host/issues/3386#issuecomment-419565714 -->
<Project>
    <PropertyGroup>
        <_IsFunctionsSdkBuild Condition="$(_FunctionsTaskFramework) != ''">true</_IsFunctionsSdkBuild>
        <_FunctionsExtensionsDir>$(TargetDir)</_FunctionsExtensionsDir>
        <_FunctionsExtensionsDir Condition="$(_IsFunctionsSdkBuild) == 'true'">$(_FunctionsExtensionsDir)</_FunctionsExtensionsDir>
    </PropertyGroup>

    <Target Name="CopyExtensionsJson" AfterTargets="_GenerateFunctionsAndCopyContentFiles">
        <Message Importance="High" Text="Overwritting extensions.json file with one from build." />

        <Copy Condition="$(_IsFunctionsSdkBuild) == 'true' AND Exists('$(_FunctionsExtensionsDir)\extensions.json')"
              SourceFiles="$(_FunctionsExtensionsDir)\extensions.json"
              DestinationFiles="$(PublishDir)bin\extensions.json"
              OverwriteReadOnlyFiles="true"
              ContinueOnError="true"/>
    </Target>
</Project>

Here is the Infrastructure.csproj:

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

    <PropertyGroup>
        <TargetFramework>netstandard2.1</TargetFramework>
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="Newtonsoft.Json" Version="[12.0.3]" />
        <!-- TODO: upgrade to Azure.Storage.Blobs -->
        <PackageReference Include="Microsoft.Azure.Storage.Blob" Version="[11.1.3]" />
        <!-- TODO: upgrade to Azure.Storage.Queues -->
        <PackageReference Include="Microsoft.Azure.Storage.Queue" Version="[11.1.3]" />
        <!-- TODO: replace with MSAL (https://github.com/AzureAD/microsoft-authentication-library-for-dotnet/wiki/adal-to-msal) -->
        <PackageReference Include="Microsoft.IdentityModel.Clients.ActiveDirectory" Version="[5.2.7]" />
    </ItemGroup>

    <ItemGroup>
        <ProjectReference Include="../Data/Data.csproj" />
    </ItemGroup>

</Project>

And here is Data.csproj:

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

    <PropertyGroup>
        <TargetFramework>netstandard2.1</TargetFramework>
        <!-- Ignore warnings that appear in generated migrations files -->
        <NoWarn>$(NoWarn),CS1591,CA1814,CA1062</NoWarn>
    </PropertyGroup>

    <ItemGroup>
        <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="[3.1.3]" />
    </ItemGroup>

</Project>

[npnelson]

@brettsam Yes, you are correct - only when launching the app via Visual Studio.

I swear I previously had this problem in Azure environments, but at this point, I'd say I am an unreliable eyewitness. I think the functions SDK and Runtime updated while I was trying to get this to work which may have corrected the issue.

@APIWT You might want to try upgrading your Microsoft.NET.Sdk.Functions to the latest (3.0.7) if you can, that has a shot at fixing your problem, although the fact that your issue is intermittent is a bit perplexing.

[APIWT]

@npnelson I'll try that out and let you know how it goes :) Yes the intermittent part of it is what scares us.

[brettsam]

@APIWT -- can you check if you have a function.deps.json deployed? I'd also recommend moving to the latest Microsoft.Net.Sdk.Functions and removing your custom build targets (they are no longer needed).

This all has to do with whether or not we can find the native deployed assembly -- it's really odd if this only happens sometimes.

[APIWT]

@brettsam We do indeed have a function.deps.json in the ZIP that is currently deployed. It is quite large, but this is probably the part you are interested in seeing:

"Microsoft.Data.SqlClient/1.0.19269.1": {
        "dependencies": {
          "Microsoft.Identity.Client": "3.0.8",
          "Microsoft.Win32.Registry": "4.5.0",
          "System.Configuration.ConfigurationManager": "4.5.0",
          "System.Runtime.Caching": "4.5.0",
          "System.Security.Principal.Windows": "4.5.0",
          "System.Text.Encoding.CodePages": "4.5.0",
          "runtime.native.System.Data.SqlClient.sni": "4.4.0"
        },
        "runtime": {
          "lib/netcoreapp2.1/Microsoft.Data.SqlClient.dll": {
            "assemblyVersion": "1.0.19269.1",
            "fileVersion": "1.0.19269.1"
          }
        },
        "runtimeTargets": {
          "runtimes/unix/lib/netcoreapp2.1/Microsoft.Data.SqlClient.dll": {
            "rid": "unix",
            "assetType": "runtime",
            "assemblyVersion": "1.0.19269.1",
            "fileVersion": "1.0.19269.1"
          },
          "runtimes/win/lib/netcoreapp2.1/Microsoft.Data.SqlClient.dll": {
            "rid": "win",
            "assetType": "runtime",
            "assemblyVersion": "1.0.19269.1",
            "fileVersion": "1.0.19269.1"
          }
        }
      },

Also, we will totally upgrade to the latest Microsoft.Net.Sdk.Functions and remove the workaround. We have been trying to upgrade stuff, but we are super nervous that we might break something if we aren't being careful. So we appreciate you taking a look at what we have and letting us know!

[brendonatkins]

Hello @brettsam

Thank you for getting back. I've attached a repro project which simplifies our app down just to the core concern I raised earlier. I tried several variations which did not require actually querying a DB but it seems the reproducable error pattern is when attempt is made to query on database, activity function fails, and then application is restarted before durable function retries. I've attached a README with steps to reproduce.

As for your questions. I am running locally using Visual Studio F5 build and run command. And to shutdown using Ctrl+C within function application runtime window.

DurablePlatformNotFoundRepro.zip

Let me know if you have issues reproducing. I tried to automate the scenario but to ensure speedy response I thought best just to pass through would I could.

Thank you

Brendon

[IanKemp]

I'm having a similar issue, and it's trivial to replicate. I've attached a set of sample projects that demonstrate the issue, read on for the explanation and/or a way to recreate the scenario if you don't trust my download:

AzureFunctionsSqlClientBug.zip

Firstly, create a .NET Core 3.1 Azure Functions v3 project with an HttpTrigger. Leave everything as-is.
Then create a .NET Framework 4.6.1 Class Library project. Add some code that uses System.Data.SqlClient, e.g. tries to open a SqlConnection.
Make the Functions project reference the Class Library.
Make the function in the Functions project call the SqlClient code in the Class Library.

1. Run the Functions project and invoke the function:

System.Private.CoreLib: Exception while executing function: Function1. DataAccess: Could not load file or assembly 'System.Data.SqlClient, Version=4.6.1.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The system cannot find the file specified.

2. Add a reference to the NuGet package System.Data.SqlClient (note: must be version 4.8.0) to the Functions project and run the latter:

System.Private.CoreLib: Exception while executing function: Function1. System.Data.SqlClient: System.Data.SqlClient is not supported on this platform.

3. Add a call to a SqlClient class in the Functions project and run the latter:

System.Private.CoreLib: Exception while executing function: Function1. DataAccess: Could not load file or assembly 'System.Data.SqlClient, Version=4.6.1.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. Could not find or load a specific file. (0x80131621).

Now, create a solution containing a .NET Core Console or Web Application project and make that project reference the same Class Library created above. Then, add the same System.Data.SqlClient 4.8.0 NuGet package to the Console/Web project, and the same call to the Class Library. The app will run successfully.

This demonstrates that the issue is not with .NET Core or ASP.NET Core or the SqlClient library, but manifestly within the Azure Functions runtime.

[APIWT]

@IanKemp I think in your case, you might need to use Microsoft.Data.SqlClient as of .NET Core 3.1

[IanKemp]

Ah, very interesting.

For the .NET Core and ASP.NET Core solutions, the Visual Studio Modules windows shows the following as I'd expect:

But the Azure Functions run shows an additional System.Data.SqlClient.dll loaded from the Azure Functions CLI directory:

That additional DLL corresponds with the one shipped with System.Data.SqlClient NuGet 4.3.1.

If I overwrite it with the 4.8.0 version from my function's bin\Debug\netcoreapp3.1\bin\runtimes\win\lib\netcoreapp2.1 directory, the function loads the DLL from AzureFunctionsTools\Releases\3.6.0\cli_x64 and runs as expected (i.e. the same as the console and ASP.NET apps).

I think somebody forgot to update System.Data.SqlClient in the Azure Functions CLI distribution to the latest version - latest NuGet package for SqlClient (4.8.1) has version 4.700.20.6702 of System.Data.SqlClient.dll - every DLL in my AzureFunctionsTools\Releases\3.6.0\cli_x64 directory is also version 4.700.20.6702 - except for the shipped System.Data.SqlClient.dll!

[IanKemp]

@APIWT Can you point me to any documentation stating this? I've searched extensively and haven't found anything.

If that's true then it essentially prohibits anyone with a Framework project that uses SqlClient, from using that project in a Functions V3 app, without refactoring the Framework project to use Microsoft.Data.SqlClient first. That's a pretty big ask...

[IanKemp]

Okay, this is definitely an assembly load problem, but I'm no longer sure if it's an Azure Functions or SqlClient one.

Note how in the .NET Core console/ASP.NET Core modules shown in my previous comment, the SqlClient DLL is being loaded from bin\Debug\netcoreapp3.1\runtimes\win\lib\netcoreapp2.1.

But in the Azure Functions modules, that DLL is being loaded directly from the bin\Debug\netcoreapp3.1 directory.

The DLL in bin\Debug\netcoreapp3.1 is the no-op one that tries to load the platform-specific one, and throws the PlatformNotSupportedException if it fails.

So what's happening in the .NET Core console/ASP.NET Core success case:

• System.Data.SqlClient.dll is initially loaded from bin\Debug\netcoreapp3.1
• It does assembly-loading shenanigans, locates and loads the version from runtimes\
• Since that version is correct, the loader DLL unloads itself
• Everything works

But in the Azure Functions failure case:

• System.Data.SqlClient.dll is initially loaded from bin\Debug\netcoreapp3.1
• It does assembly-loading shenanigans, locates and loads the version from AzureFunctionsTools\Releases\3.6.0\cli_x64
• Since that version is incorrect, the loader fails to unload itself but the load failure does not trigger an appropriate exception (I guess Could not find or load a specific file is appropriate, if you understand what you're looking at!)
• Everything crashes and burns

[APIWT]

@IanKemp To be honest, I don't really know if it is a requirement. I just know that when we migrated our ASP.NET Core application from 2.2 to 3.0 we weren't able to compile unless we did. And since we use EF Core as part of our functions application, it was a change we assumed would carry forward into both projects.

This is what we read at the time:
https://docs.microsoft.com/en-us/dotnet/core/compatibility/2.2-3.1#caching-microsoftextensionscachingsqlserver-uses-new-sqlclient-package
https://devblogs.microsoft.com/dotnet/introducing-the-new-microsoftdatasqlclient/

[APIWT]

@IanKemp Do you have a Startup class? If so, what happens if you add some code like this in the Configure function?

var builder = new SqlConnectionStringBuilder();
var unused = builder.ConnectionString;

I realize how ridiculous this looks, but I am curious if it forces the correct assembly to load.

[IanKemp]

@IanKemp Do you have a Startup class? If so, what happens if you add some code like this in the Configure function?

var builder = new SqlConnectionStringBuilder();
var unused = builder.ConnectionString;

I realize how ridiculous this looks, but I am curious if it forces the correct assembly to load.

It does!

... kinda...

Unfortunately, although the correct assembly is loaded, the application still fails with:

System.Private.CoreLib: Exception while executing function: Function1. DataAccess: Could not load file or assembly 'System.Data.SqlClient, Version=4.6.1.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. Could not find or load a specific file. (0x80131621).

If only I knew what specific file (framework source stepping doesn't help, since by the time that exception is thrown the path of the file it's trying to load has been optimised away...)

[APIWT]

That's a bummer. This is kind of what we are trying (although we are using Microsoft.Data.SqlClient instead) to try to work around this issue. Not sure what else to do at this point.

[brettsam]

(focusing on @brendonatkins repro first) -- Thanks for that readme! I was able to reproduce this now. It seems like something is happening when Durable Functions starts the function from a retry -- but now I have something to try to debug. Will come back with my findings.

[APIWT]

@brettsam That's awesome news! Thank you and best of luck.

[IanKemp]

Thanks to the magic of .NET Core assembly loading trace flags, some more critical info:

For the .NET Core console solution:

Adding runtime asset lib/netcoreapp2.1/System.Data.SqlClient.dll assemblyVersion=4.6.1.0 fileVersion=4.700.19.56404 from System.Data.SqlClient/4.8.0

...

Reconciling library System.Data.SqlClient/4.8.0
Parsed runtime deps entry 2 for asset name: System.Data.SqlClient from package: System.Data.SqlClient, library version: 4.8.0, relpath: runtimes/win/lib/netcoreapp2.1/System.Data.SqlClient.dll, assemblyVersion 4.6.1.0, fileVersion 4.700.19.56404

...

Processing TPA for deps entry [System.Data.SqlClient, 4.8.0, runtimes/win/lib/netcoreapp2.1/System.Data.SqlClient.dll]
  Considering entry [System.Data.SqlClient/4.8.0/runtimes/win/lib/netcoreapp2.1/System.Data.SqlClient.dll], probe dir [], probe fx level:0, entry fx level:0
    Relative path query exists C:\Users\iank\source\repos\AzureFunctionsSqlClientBug\NetCoreConsole\NetCoreConsole\bin\Debug\netcoreapp3.1\runtimes\win\lib\netcoreapp2.1\System.Data.SqlClient.dll
    Probed deps dir and matched 'C:\Users\iank\source\repos\AzureFunctionsSqlClientBug\NetCoreConsole\NetCoreConsole\bin\Debug\netcoreapp3.1\runtimes\win\lib\netcoreapp2.1\System.Data.SqlClient.dll'
Adding tpa entry: C:\Users\iank\source\repos\AzureFunctionsSqlClientBug\NetCoreConsole\NetCoreConsole\bin\Debug\netcoreapp3.1\runtimes\win\lib\netcoreapp2.1\System.Data.SqlClient.dll, AssemblyVersion: 4.6.1.0, FileVersion: 4.700.19.56404

But for the Azure Function sln:

Adding runtime asset runtimes/win/lib/netstandard1.3/System.Data.SqlClient.dll assemblyVersion=4.1.1.1 fileVersion=4.6.25220.1 from System.Data.SqlClient/4.3.1

...

Reconciling library System.Data.SqlClient/4.3.1
Parsed runtime deps entry 97 for asset name: System.Data.SqlClient from package: System.Data.SqlClient, library version: 4.3.1, relpath: runtimes/win/lib/netstandard1.3/System.Data.SqlClient.dll, assemblyVersion 4.1.1.1, fileVersion 4.6.25220.1

...

Processing TPA for deps entry [System.Data.SqlClient, 4.3.1, runtimes/win/lib/netstandard1.3/System.Data.SqlClient.dll]
  Considering entry [System.Data.SqlClient/4.3.1/runtimes/win/lib/netstandard1.3/System.Data.SqlClient.dll], probe dir [], probe fx level:0, entry fx level:0
    Local path query exists C:\Users\iank\AppData\Local\AzureFunctionsTools\Releases\3.6.0\cli_x64\System.Data.SqlClient.dll
    Probed deps dir and matched 'C:\Users\iank\AppData\Local\AzureFunctionsTools\Releases\3.6.0\cli_x64\System.Data.SqlClient.dll'
Adding tpa entry: C:\Users\iank\AppData\Local\AzureFunctionsTools\Releases\3.6.0\cli_x64\System.Data.SqlClient.dll, AssemblyVersion: 4.1.1.1, FileVersion: 4.6.25220.1

The .deps files entries for System.Data.SqlClient are identical for both solutions - it's like the Azure Functions runtime is either ignoring that file or parsing it incorrectly, leading it to load the lowest version that it finds...

[APIWT]

@IanKemp Dumb question, but is the test that you have just run with or without the Startup workaround of forcing loading the right assembly?

[brettsam]

@brendonatkins -- you're a hero! Your repro helped us figure out the issue here and I've split this out into a separate issue -- #6143. Follow along there for the fix and any possible workarounds (I'm looking into a workaround now).

[IanKemp]

@IanKemp Dumb question, but is the test that you have just run with or without the Startup workaround of forcing loading the right assembly?

The traces are identical in both scenarios.

[IanKemp]

In summary for anyone experiencing this issue with System.Data.SqlClient and trying to do local Azure Function V3 development with .NET Core 3.1 on Windows x64, a temporary workaround until MS gets around to issuing a proper fix:

1. Copy <your-azure-function-project-dir>\bin\Debug\netcoreapp3.1\bin\runtimes\win\lib\netcoreapp2.1\System.Data.SqlClient.dll to %localappdata%\AzureFunctionsTools\Releases\3.6.0\cli_x64\ (overwrite the existing file, or back it up first).
2. Copy <your-azure-function-project-dir>\bin\Debug\netcoreapp3.1\bin\runtimes\win-x64\native\sni.dll to %localappdata%\AzureFunctionsTools\Releases\3.6.0\cli_x64\.

(If you're not on Windows x64 I can't help you, but there are other DLLs in <your-azure-function-project-dir>\bin\Debug\netcoreapp3.1\bin\runtimes\ that might do the trick for your configuration. YMMV.)

Then it all Just Works like it should have from the start - no need for extra code to bootstrap SqlClient either.

[amtandon]

@brettsam I am running into this issue pretty easily. I just created a brand new v3 ServiceBusTrigger Function with docker support and added Entity Framework 3.1 and when I try to query the DB, I get the error.

Here is my .csproj file:

<Project Sdk="Microsoft.NET.Sdk">
  <PropertyGroup>
    <TargetFramework>netcoreapp3.1</TargetFramework>
    <AzureFunctionsVersion>v3</AzureFunctionsVersion>
    <DockerFastModeProjectMountDirectory>/home/site/wwwroot</DockerFastModeProjectMountDirectory>
    <DockerDefaultTargetOS>Linux</DockerDefaultTargetOS>
  </PropertyGroup>
  <ItemGroup>
    <PackageReference Include="Microsoft.Azure.Functions.Extensions" Version="1.0.0" />
    <PackageReference Include="Microsoft.Azure.WebJobs.Extensions.ServiceBus" Version="4.1.2" />
    <PackageReference Include="Microsoft.Azure.WebJobs.Logging.ApplicationInsights" Version="3.0.14" />
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="3.1.5" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="3.1.5" />
    <PackageReference Include="Microsoft.Extensions.Configuration.KeyPerFile" Version="3.1.5" />
    <PackageReference Include="Microsoft.NET.Sdk.Functions" Version="3.0.7" />
    <PackageReference Include="Microsoft.VisualStudio.Azure.Containers.Tools.Targets" Version="1.10.9" />
  </ItemGroup>
  <ItemGroup>
    <None Update="host.json">
      <CopyToOutputDirectory>PreserveNewest</CopyToOutputDirectory>
    </None>
  </ItemGroup>
</Project>

Here is my docker file:

FROM mcr.microsoft.com/azure-functions/dotnet:3.0 AS base
WORKDIR /home/site/wwwroot
EXPOSE 80

FROM mcr.microsoft.com/dotnet/core/sdk:3.1 AS build
WORKDIR /src
COPY ["OneCall.GroupHealth.Integration.TripService/OneCall.GroupHealth.Integration.TripService.csproj", "OneCall.GroupHealth.Integration.TripService/"]
RUN dotnet restore "OneCall.GroupHealth.Integration.TripService/OneCall.GroupHealth.Integration.TripService.csproj"
COPY . .
WORKDIR "/src/OneCall.GroupHealth.Integration.TripService"
RUN dotnet build "OneCall.GroupHealth.Integration.TripService.csproj" -c Release -o /app/build

FROM build AS publish
RUN dotnet publish "OneCall.GroupHealth.Integration.TripService.csproj" -c Release -o /app/publish

FROM base AS final
WORKDIR /home/site/wwwroot
COPY --from=publish /app/publish .
ENV AzureWebJobsScriptRoot=/home/site/wwwroot
AzureFunctionsJobHost__Logging__Console__IsEnabled=true

Any assistance will be helpful. Right now the only way for me to get this to work is to downgrade to function v2

[brettsam]

@amtandon -- did you see the workarounds and discussion here -- #6143?

[amtandon]

@amtandon -- did you see the workarounds and discussion here -- #6143?

@brettsam if you are referring to this, then yes. It gives me:

System.IO.FileNotFoundException
  HResult=0x80070002
  Message=Could not load file or assembly 'Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5'. The system cannot find the file specified.

  Source=Microsoft.EntityFrameworkCore.SqlServer
  StackTrace:
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.CreateDbConnection()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.get_DbConnection()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.CreateCommand(RelationalCommandParameterObject parameterObject, Guid commandId, DbCommandMethod commandMethod)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
...

Is there anything else I should be trying?

One more thing - This seems to be a Linux issue. When I run this locally outside of docker there are no errors.

[amtandon]

@amtandon -- did you see the workarounds and discussion here -- #6143?

@brettsam if you are referring to this, then yes. It gives me:

System.IO.FileNotFoundException
  HResult=0x80070002
  Message=Could not load file or assembly 'Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5'. The system cannot find the file specified.

  Source=Microsoft.EntityFrameworkCore.SqlServer
  StackTrace:
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.CreateDbConnection()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.get_DbConnection()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.CreateCommand(RelationalCommandParameterObject parameterObject, Guid commandId, DbCommandMethod commandMethod)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
...

Is there anything else I should be trying?

One more thing - This seems to be a Linux issue. When I run this locally outside of docker there are no errors.

The issue I have is the same that @npnelson is having - here

Does not work locally in DEBUG on docker, does work locally in RELEASE on docker.

[IanKemp]

@amtandon -- did you see the workarounds and discussion here -- #6143?

@brettsam if you are referring to this, then yes. It gives me:

System.IO.FileNotFoundException
  HResult=0x80070002
  Message=Could not load file or assembly 'Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5'. The system cannot find the file specified.

  Source=Microsoft.EntityFrameworkCore.SqlServer
  StackTrace:
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.CreateDbConnection()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.get_DbConnection()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.CreateCommand(RelationalCommandParameterObject parameterObject, Guid commandId, DbCommandMethod commandMethod)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
...

Is there anything else I should be trying?
One more thing - This seems to be a Linux issue. When I run this locally outside of docker there are no errors.

The issue I have is the same that @npnelson is having - here

Does not work locally in DEBUG on docker, does work locally in RELEASE on docker.

@amtandon You are almost certainly stuck waiting for 3.0.13904 to roll out to Azure globally.

Then you'll have to wait for Microsoft to push a new version of the Microsoft.NET.Sdk.Functions package since 3.0.13904 has updated dependencies that Microsoft.NET.Sdk.Functions shares, and if those dependencies' versions aren't in sync, nothing works.

Then you'll have to wait for Microsoft to push a new version of the core tools, since that's tied to the functions runtime version, and again if versions aren't in sync it all goes to the proverbial location on a handbasket.

So basically, you're gonna be waiting for a while.

[amtandon]

@amtandon -- did you see the workarounds and discussion here -- #6143?

@brettsam if you are referring to this, then yes. It gives me:

System.IO.FileNotFoundException
  HResult=0x80070002
  Message=Could not load file or assembly 'Microsoft.Data.SqlClient, Version=1.0.19269.1, Culture=neutral, PublicKeyToken=23ec7fc2d6eaa4a5'. The system cannot find the file specified.

  Source=Microsoft.EntityFrameworkCore.SqlServer
  StackTrace:
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerConnection.CreateDbConnection()
   at Microsoft.EntityFrameworkCore.Storage.RelationalConnection.get_DbConnection()
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.CreateCommand(RelationalCommandParameterObject parameterObject, Guid commandId, DbCommandMethod commandMethod)
   at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReader(RelationalCommandParameterObject parameterObject)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.InitializeReader(DbContext _, Boolean result)
   at Microsoft.EntityFrameworkCore.SqlServer.Storage.Internal.SqlServerExecutionStrategy.Execute[TState,TResult](TState state, Func`3 operation, Func`3 verifySucceeded)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable`1.Enumerator.MoveNext()
   at System.Collections.Generic.LargeArrayBuilder`1.AddRange(IEnumerable`1 items)
   at System.Collections.Generic.EnumerableHelpers.ToArray[T](IEnumerable`1 source)
   at System.Linq.Enumerable.ToArray[TSource](IEnumerable`1 source)
...

Is there anything else I should be trying?
One more thing - This seems to be a Linux issue. When I run this locally outside of docker there are no errors.

The issue I have is the same that @npnelson is having - here
Does not work locally in DEBUG on docker, does work locally in RELEASE on docker.

@amtandon You are almost certainly stuck waiting for 3.0.13904 to roll out to Azure globally.

Then you'll have to wait for Microsoft to push a new version of the Microsoft.NET.Sdk.Functions package since 3.0.13904 has updated dependencies that Microsoft.NET.Sdk.Functions shares, and if those dependencies' versions aren't in sync, nothing works.

Then you'll have to wait for Microsoft to push a new version of the core tools, since that's tied to the functions runtime version.

So basically, you're gonna be waiting for a while.

Yeah, at least it will work in Docker when we deploy to AKS. For local debugging, I can do without docker.

[brettsam]

@amtandon -- can you spawn a separate issue with the details for how I can repro this? This issue is getting pretty overloaded. Mention me there and I'll jump over and look.

I'm wondering if it's the same issue that I saw when using the VS Docker tools? See comment here: #5950 (comment). But we'll follow up in the new issue.

[amtandon]

@amtandon -- can you spawn a separate issue with the details for how I can repro this? This issue is getting pretty overloaded. Mention me there and I'll jump over and look.

I'm wondering if it's the same issue that I saw when using the VS Docker tools? See comment here: #5950 (comment). But we'll follow up in the new issue.

@brettsam I have opened issue #6303.

Ir definitely looks like it is an issue with VS Docker tools as mentioned in comment [#5950 (comment)]

[brettsam]

I think that we've fixed all issues identified in this thread (with the exception of the "debugging in VS with Docker" scenario in #6303) and that the fixes have all been released now. I'm going to close this issue -- if you run into this again please open a new issue (and feel free to tag me in it) so we can start a fresh investigation.