Azure-functions-host: Cannot use System.Data.SqlClient 4.6.0 and greater on functions V2.

[paulbatum]

If you follow this tutorial, it will fail:
https://docs.microsoft.com/en-us/azure/azure-functions/functions-scenario-database-table-cleanup

It fails with the error:

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

This is because you'll get 4.6.0 or later, which seems to result in the above error. Downgrading to 4.5.1 will resolve the issue.

The same version (4.6.0) works fine in a .NET Core 2.1 console app. We need to figure out why its failing in a function app. I suspect it has something to do with the fact that we are loading it via reflection, and there are some issues, see here:
https://github.com/dotnet/corefx/issues/24229

[rasmuschristensen]

I just ran into this issue with v2 functions. 4.6.0 didn't work with 2.1. I downgraded to 4.5.1 and it works fine

[rohan3usturge]

Is there any update on this ? Downgrading to 4.5.1 hinders possibility of using AAD authentication for SQL azure databases.

The latest release of System.Data.SqlClient versions greater than 4.6.0 has support AAD based authentication ( This feature was added to .net core after a long time ) and most companies are mandating using AAD auth instead of SQL based authentication.

[JohnReeson]

Before it's fixed, try this workaround.

Right click on Function project and Edit <FunctionAppName>.csproj, add items below to copy related assemblies to output dir.

 <!-- For publish -->
  <ItemGroup>
    <None Include="$(USERPROFILE)\.nuget\packages\system.data.sqlclient\4.6.0\runtimes\win\lib\netcoreapp2.1\System.Data.SqlClient.dll">
      <CopyToOutputDirectory>Always</CopyToOutputDirectory>
    </None>
  </ItemGroup>

  <!-- For local debug -->
  <Target Name="CopyToBin" BeforeTargets="Build">
    <Copy SourceFiles="$(USERPROFILE)\.nuget\packages\system.data.sqlclient\4.6.0\runtimes\win\lib\netcoreapp2.1\System.Data.SqlClient.dll" DestinationFolder="$(OutputPath)\bin" />
  </Target>

Link one identical issue. And #3568 is mitigated by similar solution, just for your reference @paulbatum .

[fabiocav]

An update on this... recent enhancements address this (and the liked issue #3568). Those enhancements will be deployed with the next release.

Assigning this to sprint 40 for validation.

[rajpamamull]

Thanks, when will the fix be released so we can retry?

[paulbatum]

@rajpamamull Keep an eye on https://github.com/Azure/app-service-announcements/issues - we'll announce our next deployment there. Should be within the next two weeks.

[mike1880]

Before it's fixed, try this workaround.

Right click on Function project and Edit <FunctionAppName>.csproj, add items below to copy related assemblies to output dir.

 <!-- For publish -->
  <ItemGroup>
    <None Include="$(USERPROFILE)\.nuget\packages\system.data.sqlclient\4.6.0\runtimes\win\lib\netcoreapp2.1\System.Data.SqlClient.dll">
      <CopyToOutputDirectory>Always</CopyToOutputDirectory>
    </None>
  </ItemGroup>

  <!-- For local debug -->
  <Target Name="CopyToBin" BeforeTargets="Build">
    <Copy SourceFiles="$(USERPROFILE)\.nuget\packages\system.data.sqlclient\4.6.0\runtimes\win\lib\netcoreapp2.1\System.Data.SqlClient.dll" DestinationFolder="$(OutputPath)\bin" />
  </Target>

Link one identical issue. And #3568 is mitigated by similar solution, just for your reference @paulbatum .

That works to a degree:

(1) System.Data.SqlClient 4.6.0 isn't present in that folder on my machine after installing from nuget, nor could I find it anywhere else, I manually downloaded the nupkg from nuget and extracted it.
(2) The "for publish" entry dumps a copy of System.Data.SqlClient in the top level of my project folder on build; removing the file also deletes the entry from the csproj...

Other than that it works for debugging,

[asos-andrewgoad]

Hi @paulbatum, any news on this?? There's been a few releases in the last week or so, but can't see it's been fixed...

Thanks!!

[paulbatum]

@asgasos we discovered that the fix is in two parts. The first part is complete and released. Until the second part is done, some manual steps are required. I need @fabiocav to reply here with the details.

[fabiocav]

Apologies for the lack of information on this.

@asgasos this was resolved in 2.0.12238 and missing in the release notes (we also failed to update and close this). I've validated again with the latest bits to make sure and this seems to work as expected, so I'm closing the issue

As @paulbatum alluded to, there are some additional steps you can take for more advanced binding scenarios, this will eventually be handled automatically by the tools, but I can share the manual steps if needed.

[adrianknight89]

@fabiocav @paulbatum I'm still seeing this issue. I'm on Core 2.1 and started having issues when I upgraded Microsoft.EntityFrameworkCore.SqlServer to latest pre-release (3.0.0-preview3.19153.1) in addition to running Microsoft.NET.Sdk.Functions 1.0.26. The current workaround was to edit the project file and add @JohnReeson's snippet.

[tmogedi]

I'm also on Core 2.1 and @JohnReeson's workaround fixed the issue for me. I also read up on this on Microsoft's docs https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connect-query-dotnet-core

[paulbatum]

@fabiocav Any thoughts on why folks are still reporting issues here? Reopening while we investigate.

[SacredSkull]

I had a similar problem when using Powershell on Azure Functions - specifically with using the SqlServer module - it complained that it could not find System.Data.SqlClient both locally and on a hosted instance.

[AidanTfL]

@fabiocav Can you please give an update, or a known workaround?

I've the same issue with Microsoft.Data.SqlClient Version 1.0.19221.1-Preview, in a .NET Core Service Bus Queue Triggerd Azure Function, where my repository is injected as a singleton in the startup file.

[Methuselah96]

@fabiocav Any update on this? This is still not working for me.

[thedonmon]

@fabiocav Any update on this? This is still not working for me.

For anyone who needs help with this Microsoft.Data.SqlClient solved my issue. No longer seeing this exception even on the server.
I installed Microsoft.Data.SqlClient 1.0.19269.1 judging by that version number...they've clearly had a lot of issues.... 💯
Using Azure Function 2.0 netstandard2.0 and changed lang to netcore2.1 to see if it still worked. All worked the same. Finally can use dapper.

[NickDarvey]

I don't have the option of moving to Microsoft.Data.SqlClient yet, so if anyone is looking for a combination that seems to work. I don't get System.Data.SqlClient is not supported on this platform when running locally with netcoreapp2.1, v2, 1.0.30-beta2 and 4.5.1 (see below).

For me, the System.Data.SqlClient.dll seemed to be correctly copied into the bin folder for each combination I've tried so I have not applied those project file build tasks (but maybe I'll need them when I go to publish).

| Works | Target Framework | Functions Runtime | Functions SDK | System.Data.SqlClient |
|-------|------------------|-------------------|---------------|-----------------------|
| ✅ | netcoreapp2.1 | v2 | 1.0.30-beta2 | 4.5.1 |
| ❌ | netcoreapp2.1 | v2 | 1.0.30-beta2 | 4.7.0 |
| ❌ | netcoreapp2.1 | v3-preview | 1.0.30-beta2 | 4.5.1 |
| ❌ | netcoreapp2.1 | v3-preview | 1.0.30-beta2 | 4.7.0 |
| ❌ | netcoreapp3.0 | v3-preview | 1.0.30-beta2 | 4.5.1 |
| ❌ | netcoreapp3.0 | v3-preview | 1.0.30-beta2 | 4.7.0 |

When it doesn't work, I get a PlatformNotSupportedException:
System.PlatformNotSupportedException: 'System.Data.SqlClient is not supported on this platform.'

Functions Runtime v2 version:

Azure Functions Core Tools (2.7.1724 Commit hash: faf97bb64a53fbf1c7ef7e5c608c392c8662951b)
Function Runtime Version: 2.0.12763.0

Functions Runtime v3-preview version:

Azure Functions Core Tools (3.0.1740 Commit hash: 0b841ce061d005a05af8bb57a341989788266298)
Function Runtime Version: 3.0.12785.0

dotnet SDKs:

2.1.802 [C:\Program Files\dotnet\sdk]
3.0.100-preview8-013656 [C:\Program Files\dotnet\sdk]
3.0.100 [C:\Program Files\dotnet\sdk]
3.1.100-preview1-014459 [C:\Program Files\dotnet\sdk]

Fun!

[NickDarvey]

Is this still being worked on @paulbatum or @fabiocav?

[fabiocav]

@NickDarvey we'll track this as part of the 3.0 work.

I just want to confirm that in the examples you've provided above, the v2 and netcoreapp2.1 and 4.7.0 one was actually using v2 as the AzureFunctionsVersion and what you have there now is a typo. Is that correct?

[fabiocav]

Also, can you confirm you've tried adding the following to your project?

  <Target Name="PostBuild" AfterTargets="PostBuildEvent">
    <Exec Command="copy $(OutDir)$(ProjectName).deps.json $(OutDir)bin\function.deps.json" />
  </Target>
  <Target Name="PostPublish" BeforeTargets="Publish">
    <Exec Command="copy $(PublishDir)$(ProjectName).deps.json $(PublishDir)bin\function.deps.json" />
  </Target>

[NickDarvey]

@fabiocav I tried all the combinations from my previous post but with those function.deps.json-copying targets added to my project but there was no change to the outcome.

See change here.

[NickDarvey]

@NickDarvey we'll track this as part of the 3.0 work.

I just want to confirm that in the examples you've provided above, the v2 and netcoreapp2.1 and 4.7.0 one was actually using v2 as the AzureFunctionsVersion and what you have there now is a typo. Is that correct?

Yes, that was a typo. I've updated my post with a more helpful matrix view of this.

[brettsam]

@NickDarvey -- can you try Microsoft.NET.Sdk.Functions 3.0.0-preview2?

[jdbenn]

Is there a solution for this? I have a v2 Function App that is using EF Core. I have followed all of the "work arounds" and nothing seems to work. The current state is downgrading the Function App to 2.1 and referencing the System.Data.SqlClient 4.5.1 per the grid above. It seems pretty ridiculous this is still an issue almost a year later?

[fabiocav]

@jdbenn the needed tooling changes went live with the last release. Assigning this to the current sprint for the host work and to have this scenario validated.

[jdbenn]

@jdbenn the needed tooling changes went live with the last release. Assigning this to the current sprint for the host work and to have this scenario validated.

I have some additional details. I reverted all of the different work arounds. I am running a v2 Function app targeting netcoreapp2.2 framework.

I am using EF Core, System.Data.SqlClient 4.6.1.

My platform is a Mac connecting to Sql Server for Linux in a docker container.

When my repo attempts to connect to the DB - I get the exception:

[12/01/2019 15:39:51] System.Data.SqlClient is not supported on this platform., at System.Data.SqlClient.SqlConnection..ctor(String connectionString)

I tried this on a PC and I do NOT get this exception - so in my case at least it has something to do with it being a Mac.

I do not think it is the DB (SQL Server on Linux running a docker container) because in the same solution I have an AspNetCore 2.2 Web API project that can connect to the same database without any issues.

This is specific to functions v2 on a Mac.

[fabiocav]

To clarify, what version of the core tools and SDK are you using when you run into the issue?

[jdbenn]

To clarify, what version of the core tools and SDK are you using when you run into the issue?

Turns out I was using 3.0.1740. I use Rider and the Azure plugin updated with pre-release. I reverted back to 2.7.1724 and the error has gone away.

For anyone else using Rider with the Azure plugin - I think there is something wrong with the latest release as it used a pre-release of the Azure Core Tools and I have that unchecked - but it still grabbed one from GitHub anyway.

[brettsam]

I believe this is due to https://github.com/Azure/azure-functions-host/issues/5315. For now it looks like referencing 4.3.1 (or moving to Microsoft.Data.SqlClient) will get it working.