Microsoft.Data.SqlClient.SNI not deployed along with WebForms

[jirikanda]

Describe the bug

SNI.dll are not in build result (bin) - no x86\SNI.dll, no x64\SNI.dll is present in bin folder after WebForms project build.

To reproduce

• Create a new web forms project (.NET Framework 4.7.2)
• Create a new class library project with a new CSPROJ format (.NET Framework 4.7.2)
• Add a Microsoft.Data.SqlClient.SNI (version 1.1.1) nuget package reference to the class library project
• Add a class library project reference to the web forms project (web forms will reference the class library)
• (or get the complete solution from https://github.com/jirikanda/Microsoft.Data.SqlClient.SNI-Repro)
• Build the solution

Expected behavior

The x86\SNI.dll and x64\SNIl.dll are present in a bin folder in class library and in a bin folder of webforms.

Current behavior

The files are only in class library bin folder.

[mirol-h]

I was able to get this working by setting property ContentSNIFiles to true in ClassLibrary1.csproj.

I believe the reason, why this is not working out of the box, is that the files don't get into Content ItemGroup ClassLibrary1.csproj. And this happens, because setting Content ItemGroup in Microsoft.Data.SqlClient.SNI.targets (in the NuGet package) depends on this property not being false or empty.

<!--
******************************************************************************
**                           Library Build Items                            **
******************************************************************************
-->

<ItemGroup>
	<SNIFiles Condition="'$(MSBuildThisFileDirectory)' != '' And
							   HasTrailingSlash('$(MSBuildThisFileDirectory)')"
					Include="$(MSBuildThisFileDirectory)**\SNI.*" />
</ItemGroup>

Is this expected behavior?

[cheenamalhotra]

I believe this is expected, as the variable "MSBuildThisFileDirectory" needs to exist in order to copy SNI files. I will take a deeper look and get back on this soon!

[aconsolati]

Related issue when running MSTests, either in Visual Studio 2019 or from command-line via vstest.console.exe.

The test run creates an output directory like the one below (eg TestResults\Deploy_Administrator 2020-03-04 11_10_41\Out), and the file Microsoft.Data.SqlClient IS copied to the \Out folder but the \x86\SNI.DLL is NOT present in the \Out folder.

Test method A_CreateDefinitionAsync threw exception: System.TypeInitializationException: The type initializer for 'Microsoft.Data.SqlClient.TdsParser' threw an exception. ---> System.TypeInitializationException: The type initializer for 'Microsoft.Data.SqlClient.SNILoadHandle' threw an exception. ---> System.TypeInitializationException: The type initializer for 'Microsoft.Data.SqlClient.SNINativeMethodWrapper' threw an exception. ---> System.ComponentModel.Win32Exception: Failed to load C:\Repos\Genesis\Automation\Build\TestResults\Deploy_Administrator 2020-03-04 11_10_41\Out\x86\SNI.dll ---> System.ComponentModel.Win32Exception: The specified module could not be found

[cheenamalhotra]

@aconsolati

Please see issue #441 for your case.

[cheenamalhotra]

@jirikanda

Could you try your application with Microsoft.Data.SqlClient v2.0.0 and let us know if you still face issue?

[jirikanda]

@cheenamalhotra

Hi,

if I understand the changes there are no more x86\SNI.dll and x64\SNIl.dll files, they has been replaced by Microsoft.Data.SqlClient.SNI.x86.dll and Microsoft.Data.SqlClient.SNI.x64.dll.

I recreated the project ("to reproduce" above) with the new version of Microsoft.Data.SqlClient.

Unfortunatelly, the only change is the name of files. They are present in the class library bin folder but they are not present in the web project bin folder. IMHO the issue is not fixed.

The file Microsoft.Data.SqlClient.dll is also present only in class library, not in web project. Could it be now SDK/MSBuild issue, not SqlClient?

[cheenamalhotra]

Could you provide us your repro with steps to deploy web project where you wish to see the files?

We do support deployment targets but it may not cover everything.

[jirikanda]

Steps to reproduce

• Create a new web forms project (.NET Framework 4.7.2)
• Create a new class library project with a new CSPROJ format (.NET Framework 4.7.2)
• Add a Microsoft.Data.SqlClient (version 2.0.0) nuget package reference to the class library project
• Add a class library project reference to the web forms project (web forms will reference the class library)
  (or get the complete solution from https://github.com/jirikanda/Microsoft.Data.SqlClient-Repro2)
• Build the solution

Current behavior

In the class library bin\Debug\net472 folder there are these files (only bold items are important).

• ClassLibrary1.dll
• ClassLibrary1.pdb
• Microsoft.Data.SqlClient.dll
• Microsoft.Data.SqlClient.SNI.x64.dll
• Microsoft.Data.SqlClient.SNI.x64.pdb
• Microsoft.Data.SqlClient.SNI.x86.dll
• Microsoft.Data.SqlClient.SNI.x86.pdb
• Microsoft.Identity.Client.dll
• Microsoft.IdentityModel.JsonWebTokens.dll
• Microsoft.IdentityModel.Logging.dll
• Microsoft.IdentityModel.Protocols.dll
• Microsoft.IdentityModel.Protocols.OpenIdConnect.dll
• Microsoft.IdentityModel.Tokens.dll
• Newtonsoft.Json.dll
• System.IdentityModel.Tokens.Jwt.dll

In the web application bin folder there are these files:

• ClassLibrary1.dll
• ClassLibrary1.pdb
• Microsoft.Data.SqlClient.dll
• Microsoft.Identity.Client.dll
• Microsoft.IdentityModel.JsonWebTokens.dll
• Microsoft.IdentityModel.Logging.dll
• Microsoft.IdentityModel.Protocols.dll
• Microsoft.IdentityModel.Protocols.OpenIdConnect.dll
• Microsoft.IdentityModel.Tokens.dll
• Newtonsoft.Json.dll
• System.IdentityModel.Tokens.Jwt.dll
• WebApplication1.dll
• WebApplication1.dll.config
• WebApplication1.pdb

Files Microsoft.Data.SqlClient.SNI.x64/x86.dll are missing.

Expected behavior

In the web application bin folder there are also files

• Microsoft.Data.SqlClient.SNI.x64.dll
• Microsoft.Data.SqlClient.SNI.x86.dll

Further steps to reproduce the issue

• Add a class to the class library with static method.
• Let this method create and open SqlConnection
• Run this method from global.asax.cs, Application_Start
• (or get the complete solution from https://github.com/jirikanda/Microsoft.Data.SqlClient-Repro2)
• Run the application

Exception is thrown.
System.DllNotFoundException: Unable to load DLL 'Microsoft.Data.SqlClient.SNI.x86.dll': Uvedený modul nebyl nalezen. (Exception from HRESULT: 0x8007007E)

[UweKeim]

Having the same issue, too. Would urgently need a working "Microsoft.SqlServer.SqlManagementObjects" NuGet package, again.

[ErikEJ]

What if you add a Microsoft.Data.SqlClient (version 2.0.0) nuget package reference to the WebForms project?

[UweKeim]

Thanks, @ErikEJ, does the "Microsoft.Data.SqlClient" client also include the Management Objects?

As a workaround I now reverted back to 150.18208.0 of the "Microsoft.SqlServer.SqlManagementObjects" which did the trick for now.

[jirikanda]

@ErikEJ, thanks for your answer!
Of course adding Microsoft.Data.SqlClient 2.0.0 to the WebForms project makes it working. WebForms use package.config (no PackageReferences) so adding Microsoft.Data.SqlClient also adds all dependencies including Microsoft.Data.SqlClient.SNI to the package.config.

It is a good workaround which we have applied.

But who wants to manually add nuget packages to his project (WebForms) of its dependencies?

[cheenamalhotra]

@jirikanda @UweKeim

Have you tried using SMO v161.41011.9 that references Microsoft.Data.SqlClient v2.0.0 ?

[jirikanda]

@cheenamalhotra
The original issue is not related to SMO. We do not need SMO in our application.

[rsfurlan90]

I can confirm the issue...

It does not make any sense to reference Microsoft.Data.SqlClient in the main project to solve this implicit dependency issue. I have a dedicated project to take care of data access that is referenced by the main project (web api), but the SNI stuff is not getting copied from the output of Data Layer project.

Copying them manually to the main project is not a decent solution as well. How to make visual studio copy any DLL from referenced project's bin folder to the final destination? (main project)

[Seabizkit]

please fix, yeah same @jirikanda explained well, project A(net472) reference project B(multi target net472), project B has Microsoft.Data.SqlClient nuget, so when building all required dll should be copied, and they are not for dotnet framework.

[coderb]

+1

[y2jhotaru]

Hi, I confirm the bug in a WebForms project with class libraries. I recently updated Quartz Nuget package to ver 3.1 on a class library, and this version installed Microsoft.Data.SqlClient 2.0.1 dependency, so, of course it installed also Microsoft.Data.SqlClient.SNI 2.1.0. When I run the site to debug it or publish it, The 'Microsoft.Data.SqlClient.SNI.x64 and x86 are copied to the library's debug folder but not in the Website bin folder, so when I try to use my class library from the website, I get the Microsoft.Data.SqlClient.SNI.xxx file not found error. For now, I had to revert Quartz to the previous version and delete Microsoft.Data.SqlClient Nuget and all its dependencies. I need to use new Quartz features and bug fixes but for now, I'll have to wait. I hope this to be fixed soon. Thanks.

[matrimsaric]

+1 none of the workarounds above worked for me so we are currently looking at using a custom action to force the dlls into the setup project which is far from ideal so would very much also like a new version which either handles this automatically or provides a flag that forces them in.

[cheenamalhotra]

I looked into this today and noticed that the WebApplication projects that depends on ClassLibrary do not import "targets" from SNI NuGet package (Microsoft.Data.SqlClient.SNI) in an indirect reference.

Could someone try by simply referencing the SNI "targets" file in WebApplication's csproj?

  <Import Project="..\packages\Microsoft.Data.SqlClient.SNI.2.0.1\build\net46\Microsoft.Data.SqlClient.SNI.targets" Condition="Exists('..\packages\Microsoft.Data.SqlClient.SNI.2.0.1\build\net46\Microsoft.Data.SqlClient.SNI.targets')" />

Since SNI is a native C++ library, the targets file shipped with it needs to be imported in .NET Framework application projects in order to copy them seamlessly. They're generally auto-imported if you import the Microsoft.Data.SqlClient project itself, you'll find this block in csproj as pasted below but it will not be imported by itself in an indirect reference.

  <Target Name="EnsureNuGetPackageBuildImports" BeforeTargets="PrepareForBuild">
    <PropertyGroup>
      <ErrorText>This project references NuGet package(s) that are missing on this computer. Use NuGet Package Restore to download them.  For more information, see http://go.microsoft.com/fwlink/?LinkID=322105. The missing file is {0}.</ErrorText>
    </PropertyGroup>
    <Error ... />
    <Error Condition="!Exists('..\packages\Microsoft.Data.SqlClient.SNI.2.0.1\build\net46\Microsoft.Data.SqlClient.SNI.targets')" Text="$([System.String]::Format('$(ErrorText)', '..\packages\Microsoft.Data.SqlClient.SNI.2.0.1\build\net46\Microsoft.Data.SqlClient.SNI.targets'))" />
  </Target>
  <Import Project="..\packages\Microsoft.Data.SqlClient.SNI.2.0.1\build\net46\Microsoft.Data.SqlClient.SNI.targets" Condition="Exists('..\packages\Microsoft.Data.SqlClient.SNI.2.0.1\build\net46\Microsoft.Data.SqlClient.SNI.targets')" />

[stuartclode]

I just came across this issue in my Webforms app yesterday after adding in a reference to a class library that references Microsoft.EntityFrameworkCore and Microsoft.Data.SqlClient.SNI.

I can confirm that you need to add a nuget package reference for Microsoft.Data.SqlClient.SNI to the webforms project in order to get the SNI.dlls to copy across to the bin dir. The version of Microsoft.Data.SqlClient.SNI should match the version that you use in the class library which for me was 1.1.1.

After adding this reference the webforms csproj file is edited in the exact way shown by @cheenamalhotra in the post above.

To help others - here is a copy of the error i was seeing in the browser.

[Win32Exception (0x80004005): The specified module could not be found]

[Win32Exception (0x80004005): Failed to load xxx\bin\x86\SNI.dll]
   Microsoft.Data.SqlClient.SNINativeMethodWrapper..cctor() +316

[TypeInitializationException: The type initializer for 'Microsoft.Data.SqlClient.SNINativeMethodWrapper' threw an exception.]
   Microsoft.Data.SqlClient.SNINativeMethodWrapper.UnmanagedIsTokenRestricted(IntPtr token, Boolean& isRestricted) +0

[jirikanda]

@cheenamalhotra
Hi Cheena,

thanks for your answer.

<Import Project="..\packages\Microsoft.Data.SqlClient.SNI.2.0.1\build\net46\Microsoft.Data.SqlClient.SNI.targets" Condition="Exists('..\packages\Microsoft.Data.SqlClient.SNI.2.0.1\build\net46\Microsoft.Data.SqlClient.SNI.targets')" />

Because the Microsoft.Data.SqlClient.SNI package is not referenced directly by WebForms application, there is no ..\packages\Microsoft.Data.SqlClient.SNI.2.0.1 folder.

Feel free to download the reprosteps repository from https://github.com/jirikanda/Microsoft.Data.SqlClient-Repro2 and build it in VS. There is no package folder because WebForms application directly references no nuget package.

[cheenamalhotra]

Yes, there isn't any, so you'll have to create one or reference targets file from NuGet cache folder.
My point was it needs to be referenced from the unzipped nuget location.

[jirikanda]

Thanks.

[Sashir2]

any traction on this issue ?

[janoonaj]

+1 Still waiting for a solution.

[sliddington]

Just worked around this by adding the dlls from their location in the nuget package folder as a linked file in the project (in my case a packages folder in the solution dir - may have to do something a bit cleverer in the csproj if not located there) and then set as copy if newer

You might then like to change the include paths in the csproj to be like this if the project is used in multiple solutions.
<Content Include="$(SolutionDir)packages....

[mtaylorfsmb]

I'd like to add that the above workaround doesn't work for all the publishing cases. We have an ASP.NET MVC app that is using a dependency that relies on SqlClient. To work around the missing SNI files we explicitly reference the Microsoft.Data.SqlClient.SNI package in our web app project. When building the app the SNI file is properly placed in the output directory.

Furthermore if we publish from MSBuild using the command msbuild <solution> /p:DeployOnBuild=true... then it generates the web deployment package (zip file) with the binaries properly embedded as well. So far so good.

The problem comes in with the _PublishedWebsites that is also generated. Historically in web apps if the OutDir build property differs from the default output for the project then the webapplication.targets file, from my understanding, sets a flag indicating this is a publish action. It auto-generates the _PublishedWebsites folder and does the logic needed to copy the binaries and contents into this folder. You can then drop this folder onto IIS and your site is good. It has advantages over the deployment package approach. However the SNI targets file doesn't copy the binaries to this directory so it still fails. I believe the .targets file should be looking at the webapplication.targets MSbuild property for publishing as well, or however it figures it out, so that it also copies to the appropriate locations under _Publishedwebsites.

[wolfgangkutschera]

Is there any solution to this in sight? Unfortunately if you extensivly use SMO it's more DLLs that are missing, and so far the only way I have found to work around this is to add the Nuget package to all projects. (Which is a desaster, given how many dependent projects that thing loads...)

[Tony322]

I'd like to add that the above workaround doesn't work for all the publishing cases. We have an ASP.NET MVC app that is using a dependency that relies on SqlClient. To work around the missing SNI files we explicitly reference the Microsoft.Data.SqlClient.SNI package in our web app project. When building the app the SNI file is properly placed in the output directory.

Furthermore if we publish from MSBuild using the command msbuild <solution> /p:DeployOnBuild=true... then it generates the web deployment package (zip file) with the binaries properly embedded as well. So far so good.

The problem comes in with the _PublishedWebsites that is also generated. Historically in web apps if the OutDir build property differs from the default output for the project then the webapplication.targets file, from my understanding, sets a flag indicating this is a publish action. It auto-generates the _PublishedWebsites folder and does the logic needed to copy the binaries and contents into this folder. You can then drop this folder onto IIS and your site is good. It has advantages over the deployment package approach. However the SNI targets file doesn't copy the binaries to this directory so it still fails. I believe the .targets file should be looking at the webapplication.targets MSbuild property for publishing as well, or however it figures it out, so that it also copies to the appropriate locations under _Publishedwebsites.

Have this exact same issue with publishing a ASP.NET MVC app.

[AndyBevan]

Any update on this? We recently upgraded from System.Data.SqlClient to Microsoft.Data.SqlClient in our WebForms Project and run into this problem. In my scenario I have a Webforms app that references a .net standard assembly that uses Microsoft.Data.SqlClient and when I publish my webforms app using msbuild it's missing microsoft.data.sqlclient.sni in my Webforms bin folder.

I attempted to add the lines suggested by @cheenamalhotra but this isn't a workable solution because the packages can be restored to the user's packages folder - maybe there's something there I'm not understanding.

I'm reluctant to reference microsoft.data.sqlclient.sni directly from my webforms project because we have a large development project and these types of non-standard dependencies are hard to maintain.

[Dyllanjrusher]

Any update on this issue? I'm upgrading from System.Data.SqlClient to Microsoft.Data.SqlClient in .net framework 4.7.2 and have recieved the same error inside an ASP.NET MVC app.

[JMKling]

Getting the same error, but in a WPF application once deployed/ installed on the machines. Runs fine in VS.

[Kaowain]

How did you solve this (not the hard way ==> copy/paste dll in the directory)

[JMKling]

I changed references to Microsoft.Data.SqlClient to System.Data.SqlClient and it went away.

[Kaowain]

not the best way if you're using app with net core and net framework ;-)

[JMKling]

Yeah well, what is the best way to fix it for WPF then? After hours of searching for a fix and only getting results related to web apps and copy/paste into bin/deploy folders, the System.Data change worked for what was needed. I tried doing the reference file in the installer and outputting it into to the applications install location, but still didn't like it so... life's too short to stress over it and if you have a solution, I'd like to see it.

[jirikanda]

Microsoft, by hands of @cheenamalhotra, closed in 2020. The issue was reopened on request the same day. But this is the last information we got from Microsoft. The best workaround we got from Microsoft cannot work in a team environment.

I read it as Microsoft is an author of nuget packages technology but don't know how to use them in this scenario. Instead of solving the issue, they let developers to fall to this trap again and again.

As far as I remember, the best workaround was to install Microsoft.Data.SqlClient.SNI package to the project where you need sql client at runtime (means in all executables like tests, WPF app, WebForms app, etc.). Yes, if you read it this way: "Hm, instead of solving dependencies by msbuild, I have to identify dependencies and spread them to the dependent project myself." then you are right.

[Kaowain]

@jirikanda it's the way I solve it too.

[ristogod]

I am getting the same issue here. I have a .NET Framework 4.8 ASP.NET WebApi project and runs fine locally through debugging using VS and IISExpress. When we deploy, I can see both Microsoft.Data.SqlClient.SNI.x64.dll andMicrosoft.Data.SqlClient.SNI.x84.dll in the bin folder of the site. However, I cannot locate them at runtime.

This is using the latest package Microsoft.Data.SqlClient.4.1.0

[Dyllanjrusher]

I am getting the same issue here. I have a .NET Framework 4.8 ASP.NET WebApi project and runs fine locally through debugging using VS and IISExpress. When we deploy, I can see both Microsoft.Data.SqlClient.SNI.x64.dll andMicrosoft.Data.SqlClient.SNI.x84.dll in the bin folder of the site. However, I cannot locate them at runtime.

This is using the latest package Microsoft.Data.SqlClient.4.1.0

I spent a long while trying to get my projects using both. Just want to warn you that you might be walking down a long dead end, if memory serves me correctly. I'd be grateful to here what you did if you end up figuring it out though. I was upgrading from 4.7.2 -> 4.8 when it happened to me last December.

[ristogod]

I am getting the same issue here. I have a .NET Framework 4.8 ASP.NET WebApi project and runs fine locally through debugging using VS and IISExpress. When we deploy, I can see both Microsoft.Data.SqlClient.SNI.x64.dll and Microsoft.Data.SqlClient.SNI.x84.dll in the bin folder of the site. However, I cannot locate them at runtime.
This is using the latest package Microsoft.Data.SqlClient.4.1.0

I spent a long while trying to get my projects using both. Just want to warn you that you might be walking down a long dead end, if memory serves me correctly. I'd be grateful to here what you did if you end up figuring it out though. I was upgrading from 4.7.2 -> 4.8 when it happened to me last December.

For us, the site is hosted on one machine, while the code is located on Network Attached Storage (NAS), another machine. What we noticed is that, when that is the case, despite Microsoft.Data.SqlClient.SNI.x64.dll being fully present, Microsoft.Data.SqlClient.dll cannot find that dll.

We suspected some type of rights or permissions issue. But we checked and the user has full rights to the location where the code is located.

We then moved the site local, so it was located on the machine hosting the site. Then Microsoft.Data.SqlClient.SNI.x64.dll was found. However, this is not a viable solution for us as we have multiple machines hosting and are not setup to deploy to multiple locations. We could change that, but it would be against company policy.

We were only able to resolve this by doing something that is problematic, and that was to basically copy those 2 dlls to the machines path folder so they could be discovered. We understand this bad in that it now forces all sites to use these same dlls and they could be at different versions to what any application is requesting.

We feel as though Microsoft is choosing an architectural path in their code implementation that is predicated on the idea of a hosted site being done from a container, such as docker or the like. Because there the entirety of the site would be encapsulated within and the dlls discoverable. However, once again, due to reasons out of my control, our company is not embracing such technologies and we are left in this scenario.

[IgorWolbers]

@ristogod - I found the reason why this is happening. See this StackOverflow question.

 This happens because the managed dlls get shadow copied to a temporary location under the .NET Framework directory. See http://msdn.microsoft.com/en-us/library/ms366723.aspx for details.
 Unfortunately, the unmanaged dlls do NOT get copied and the ASP.NET process won't be able to find them when it needs to load them.

There are some work arounds suggested in various answers but at least now I know why the error occurs.

Another GitHub related question and answer to this can be found here: #1416 (comment)

[ristogod]

@ristogod - I found the reason why this is happening. See this StackOverflow question.

 This happens because the managed dlls get shadow copied to a temporary location under the .NET Framework directory. See http://msdn.microsoft.com/en-us/library/ms366723.aspx for details.
 Unfortunately, the unmanaged dlls do NOT get copied and the ASP.NET process won't be able to find them when it needs to load them.

There are some work arounds suggested in various answers but at least now I know why the error occurs.

Another GitHub related question and answer to this can be found here: #1416 (comment)

So the way we resolved this was to set IIS to a local folder and then use symbolic links to the code on the network drive. This seemed to work. So now we can deploy to a single code location, yet run multiple hosted instances of it on separate machines.

We only thought of this solution today and tested just within the last few hours. So good timing.

[naveenxy]

I had a same issue,Add this package Microsoft.Data.SqlClient.SNI to your unit test file, resolves my isssue..

[hietshkumar10]

I can confirm the issue...

It does not make any sense to reference Microsoft.Data.SqlClient in the main project to solve this implicit dependency issue. I have a dedicated project to take care of data access that is referenced by the main project (web api), but the SNI stuff is not getting copied from the output of Data Layer project.

Copying them manually to the main project is not a decent solution as well. How to make visual studio copy any DLL from referenced project's bin folder to the final destination? (main project)
Any Solution?

[kf-gonzalez2]

@hietshkumar10 can you make sure you are importing SNI targets file, that contains targets to perform COPY of DLL files during MsBuild tasks?

[hietshkumar10]

@kf-gonzalez2 yes I have tried If you can specify specific file I can try that