SNI.dll file locked when ASP.NET (Framework 4.8) App is running in IIS

[jakenuts]

Describe the bug

The SNI.dll file is loaded/locked by IIS when using a Microsft.Data.SqlClient connection in a ASP.NET 4.8 Framework website but is not unloaded when the app is shutdown. Subsequent attempts to build or deploy the site are blocked by the error Unable to delete file "bin\x86\SNI.dll". Access to the path '...\bin\x86\SNI.dll' is denied. Every other dll in the bin folder can be deleted even while the web app is still running save for this one.

To reproduce

1.) Create an ASP.NET MVC website on the 4.8 Framework
2.) Add the Microsoft.Data.SqlClient package
3.) In the HomeController.Index method open a SqlConnection and execute an arbitrary command

using (var connection = new SqlConnection("<connection string here>"))
{
    connection.Open();
    new SqlCommand("SELECT 1", connection).ExecuteNonQuery();
}

4.) Cause the site to shutdown by modifying the web.config or stopping the debugger.
5.) The SNI.dll file remains locked and cannot be replaced or modified without shutting down IIS or the application pool associated with the website

Expected behavior

The SNI.dll should be unlocked (like the rest of the files in the bin folder) so that a site can be updated through deployment or build.

Further technical details

Microsoft.Data.SqlClient version: v1.1.0
.NET target: Framework 4.8
SQL Server version: SQL Azure
Operating system: Windows 10 Professional / Windows Server 2016 Datacenter (Azure VM)

[jakenuts]

The SNI.dll library appears to be loaded in the SNINativeMethodWrapper static initializer but the handle is not kept or freed anywhere else in the code.

 IntPtr pDll = LoadLibrary(localFolder + subfolder + SNI);
 if (pDll == IntPtr.Zero)
 {
    throw new System.ComponentModel.Win32Exception("Failed to load " + localFolder + subfolder + SNI, 
   new System.ComponentModel.Win32Exception(Marshal.GetLastWin32Error()));
}

[cheenamalhotra]

@jakenuts

Could you also verify and confirm if same behaviour is observed with System.Data.SqlClient?

[jakenuts]

I tried removing the SqlClient packages, changing the using statement to using System.Data.SqlClient which creates a SqlConnection from the System.Data 4.0.0 framework assembly and no SNI.dll is copied to the bin folder so no issues there.

I tried installing the System.Data.SqlClient 4.8 package and it does not appear to rely on a SNI.dll for this 4.8 project and all the files in the bin folder can be deleted while the web app is running.

I then replaced that package with Microsoft.Data.SqlClient but kept the using statement as "using System.Data.SqlClient". This copied the SNI.dll to the bin/x86 folder but it is not loaded or locked when executing the command.

Changing the using statement to "using Microsoft.Data.SqlClient" and running the app caused the SNI.dll to be loaded and it is locked until I terminate the IIS process.

[jakenuts]

I tried some alternative setups where the data call DoData() was moved to a .NET Standard 2.0 library as that's the one my project uses. It demonstrated the "could not load file or assembly" error that shows up alot in Google searches, performed as expected when both project referenced System.Data.SqlClient 4.8, but moving to them both the Microsoft.Data.SqlClient replicates the file locking issue above.

[Setup 1]

ASP.NET NET 4.8 Project

• no data assemblies referenced

.NET Standard 2.0 Library

• System.Data.SqlClient 4.8 referenced
• Using System.Data.SqlClient

No SNI dll in bin
Calling DoData() from the webapp into the library generates

System.IO.FileNotFoundException: 'Could not load file or assembly 'System.Data.SqlClient, Version=4.6.1.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. The system cannot find the file specified.'

[Setup 2]

ASP.NET NET 4.8 Project

• System.Data.SqlClient 4.8 referenced

.NET Standard 2.0 Library

• System.Data.SqlClient 4.8 referenced

No SNI dll in bin
Calling DoData() from the webapp into the library succeeds, all files can be deleted

[Setup 3]

ASP.NET NET 4.8 Project

• no data assemblies referenced

.NET Standard 2.0 Library

• Microsoft.Data.SqlClient 1.1.0 referenced
• using Microsoft.Data.SqlClient;

No SNI dll in bin
Calling DoData() from the webapp fails with the 'could not load' exception

[Alternate Setup 4]

ASP.NET NET 4.8 Project

• System.Data.SqlClient 4.8 referenced

.NET Standard 2.0 Library

• Microsoft.Data.SqlClient 1.1.0 referenced
• using Microsoft.Data.SqlClient;

No SNI dll in bin
Calling DoData() from the webapp fails with the 'could not load' exception

[Alternate Setup 4]

ASP.NET NET 4.8 Project

• Microsoft.Data.SqlClient 1.1.0 referenced

.NET Standard 2.0 Library

• Microsoft.Data.SqlClient 1.1.0 referenced
• using Microsoft.Data.SqlClient;

SNI dll copied to bin
Calling DoData() succeeds, SNI.dll is locked until IIS process killed

[jakenuts]

Looking at the System.Data.SqlClient repository, there are some references to the necessity of holding onto and freeing the handle from SNI.dll (can't dig up where they load it). It doesn't appear that the handle loaded in Microsoft.Data.SqlClient is kept or later released which might explain why it sticks around.

From LocalDBAPI.Common.cs in System.Data.SqlClient

    //This is copy of handle that SNI maintains, so we are responsible for freeing it - therefore there we are not using SafeHandle
    private static IntPtr s_userInstanceDLLHandle = IntPtr.Zero;

   internal static void ReleaseDLLHandles()
   {
      s_userInstanceDLLHandle = IntPtr.Zero;
      s_localDBFormatMessage = null;
   }

[cheenamalhotra]

Thanks for the details @jakenuts

I just opened up System.Data.SqlClient .NET framework library source code and I didn't see any call to "LoadLibrary" there (since our code is imported from there), here's the only way it's loaded for every API:

[DllImport("SNI.dll", CallingConvention = CallingConvention.Cdecl, EntryPoint = "SNIAddProviderWrapper")]
internal static extern uint SNIAddProvider(SNIHandle pConn, ProviderEnum ProvNum, [In] ref uint pInfo);

Which is also present in our case in SNINativeMethodWrapper.cs

I'm trying to understand why we added LoadLibrary call in there, which seems to be primary difference and cause of this behaviour.

[cheenamalhotra]

Hi @jakenuts

[edit]
Turns out it's necessary to load DLLs in Microsoft.Data.SqlClient as SNI.dll is external from NuGet package, unlike System.Data.SqlClient, I'll continue to investigate for possible solution, if any.

[cheenamalhotra]

Hi @jakenuts

I tried creating ASP.NET application to try reproduce issue but I cannot reproduce the problem using steps you mentioned above. I get file in use errors for "Roslyn" instead which I cannot delete, everything else I'm able to clean normally.

Application I tried:
WebApplication1.zip

Can you provide me a simple application which you're able to reproduce with if I missed something as I'm new to ASP.NET MVC architecture.

[jakenuts]

Sorry, I should have included that the Roslyn folder cannot be deleted while the app is running. Here is a minimal ASP.NET 4.8 project that demonstrates the issue (at least on my machine). You'll need to run it on IIS or IIS Express to reproduce the locking. I'm also connecting to Azure SQL in my connection string (stupidly posted that earlier, thus the deletion) but have replaced that with the localhost one from yours. Later I'll take a look at the two projects and see if there are any relevant differences.

LockingWebApp.zip

[jakenuts]

Here's another sample app that is even more 'minimal'. It uses owin and creates a connection to a non-existent LocalDb which is enough to use the SNI.dll imports. Once you open the project, restore the packages and run the app it should demonstrate the problem. Replacing the using statement for Microsoft.Data.SqlClient with System.Data.SqlClient resolves the issue.

LockingWebApp.zip

[cheenamalhotra]

Hi @jakenuts

I tried your samples but still cannot reproduce any problem.
I apparently have localdb installed, but I also tested with random server name in connection string just to trigger exception case. I don't see any locking problems with SNI.dll.

Just to confirm the steps:

1. Open application in VS and run with IIS
2. Stop debugging app
3. Delete bin and obj folders manually
   • I can delete totally fine!

My Environment details are:

• Windows 10 Enterprise
• Visual Studio Enterprise 2019 (16.4.1)
• .NET Framework 4.8.03752
• IIS Version 10.0.18362.1

Did I miss something?

P.S. I'm also checking with my team internally if someone can reproduce in their environment.

[jakenuts]

Thanks! I tried loading the project on a different machine and for whatever reason it differs in that "stop debugging" or closing the browser window also closes IISExpress which then releases the DLL. On my machine IISExpress stays active even after I've stopped debugging.

When I tried with "Start without debugging" on the new machine, IISExpress stays active and when you try to rebuild, or if you delete all the rest of the files the SNI.dll remains locked. This replicates what is happening in production where full IIS remains running and once the dll is loaded it cannot be moved or updated until you kill the application pool.

[jakenuts]

I just tried creating an Azure App Service for the sample and published it there. Unfortunately it's way better at detecting changes than my VM deployment so it doesn't try to update the SNI.dll as they match so deployment succeeded without an issue. I was able to go into the console there and confirm that all the files in the bin directory could be deleted save for the SNI.dll though.

[cheenamalhotra]

Hi @jakenuts

Yes I can reproduce the behavior now. The behavior with "Start without Debugging" happens because application is running on IIS in a detached mode and is Active on IIS Server from "bin" directory. What you mean to do is rebuild and redeploy on top of Active application's "bin" directory. SNI.dll is locked in the process since it's a loaded library, from where you pointed above. Since the application is "Active", the DLL will stay loaded there.

If you stop the site, that would free the lock and SNI.dll can be deleted.

This seems to be expected behavior with Microsoft.Data.SqlClient.

With System.Data.SqlClient, you would not see this behavior because SNI code is inbuilt in "System.Data.dll" hence there was no external loading done. The Native SNI code in there is shared between all ADO.NET providers. This is not the case with Microsoft.Data.SqlClient as SNI.dll needs to be loaded from an external NuGet package "Microsoft.Data.SqlClient.SNI" in current process.

You can say that SqlClient (for .NET Framework) is incomplete without this referenced "SNI.dll". The reason why it's referenced externally from Microsoft.Data.SqlClient is that SNI native implementation continues to be a closed source, and is not open sourced yet.

[cheenamalhotra]

I was wondering if SNI.dll stayed loaded even after App was Shutdown/Stopped, as mentioned in the first comment, that doesn't seem to be the case.

[jakenuts]

It is a bit of a standout though as you can update/delete every file in a website (save for the roslyn folder) during deployment save for this one. Even after adding an App_Offline.htm file which is part of the standard deployment process the DLL remains locked so the only way to deploy is to stop the site entirely while the files are copied over. It's not a terrible workaround but it does mean that requests will come back with 500(?) responses instead of the App_Offline content which is slightly more polite.

Is it possible that the rest of the bin files are shadow copied somewhere such that they don't appear locked even when they loaded into the IIS process?

[cheenamalhotra]

I'm not an IIS expert, but there seem to be workarounds, with "Touch" tasks, you can try. I got some info from Developer Community Post.

Ideally, as discussed on Stack Overflow, the best approach is:

• Take the outage and allow IIS to cleanly release locks and clean up after itself to preserve server stability.
• If not feasible, you can either create another site on the same box, or set up a new box with the new content, and move the domain name/IP across to "promote" the new content to production.

[cheenamalhotra]

I will close the issue as the behaviour is "By Design".

Thanks!

[jakenuts]

It seems impervious to the App_Offline method, but I can live with the "restart application pool" workaround as I'm hoping to eventually migrate away from the 4.8 legacy bits anyway. I really appreciate your help, thanks, have a great holiday!

[vishnu4]

I'm surprised this issue is locked. while it might be 'as it worked before' with the previous version of sqlclient, it's not really ideal behavior, right? recycling the app pool isn't always reliable, and additionally I hit this issue frequently on my development box on just compiling.

[cheenamalhotra]

Hi @vishnu4

Firstly, this happens only with apps active or running in IIS, and you must stop the app safely in order to clean the bin directory and recompile.

Secondly, Microsoft.Data.SqlClient (.NET Framework) needs to load native library Microsoft.Data.SqlClient.SNI (SNI.dll) and since it is not part of Full Framework and is an external compoenent it needs to be loaded explicitly to work. It is not unloaded due to the fact that driver cannot function at all without SNI.dll, and as I mentioned above their separation was done for security and legal reasons.

This design is not the same as System.Data.SqlClient, so even if you may think 'it worked before for S.D.S', technically, their designs are very different from each other.

Microsoft.Data.SqlClient (.Net Core) on the other hand also references SNI.dll from runtime native libraries but they're part of .NET Core SDK so you'll not encounter issues there. And since moving forward .NET Core is the primary focus and .NET Framework is not longer active, we recommend users to port over to .NET Core if this issue becomes a big hurdle in development.

[vishnu4]

I get that it happens when apps are active or running in IIS. i'm not debating that. I understand that the designs are very different as well, i'm not debating that either. I'm just saying my use cases are not edge or unique, and even though i don't have a solution to this issue, i don't think it should be closed or ignored.

The 2 places i see this issue are:

• on deploy via azure devops, i've now added a 'recycle app pool' task, which runs fine, but SNI.dll was still seen as locked during some of my deploys (while it is trying to purge the bin directory). so either the recycle wasn't good enough, or i need to change the task so that IIS is stopped entirely during the deployment process.
• when i'm on my development machine I can run the app, work in it, and then rebuild again. 50% of the time my rebuild fails because the file is locked. this definitely makes development difficult if i have to add the extra step of recycling the app pool or restarting IIS between every build.

I understand i'm in .net framework, and that is a purgatory that i should try to get myself out of, but the move to use microsoft.data.sqlclient was part of that move for me, but it's actually making the move harder because of these issues. I have a large monolith .net framework app and i am gradually moving projects towards .net standard. This gradual move is what is causing my issue apparently.

So the solution is that i can't move gradually? I have to move everything to .net core immediately, all in one go without doing things in steps? That just doesn't seem realistic, which is the only point i'm trying to make here. This issue is making development more difficult, and the only solution seems to be to move everything BACK to .net framework so i can use System.Data.SQLClient instead, which prevents me from ever really moving to .net core.

.Net core is the primary focus. Great, totally down with that. Shouldn't that mean that aiding users from migrating from .Net Framework to .Net core should be important then?

[StevenQuickGS1]

I published my approach at https://github.com/lscorcia/sqlclient.snishadowcopy . It is similar to @Tasteful approach in the sense that it copies the native assembly under the shadow folder, but it takes advantage of the fact that Windows will first try to load the dll from the same location of the Microsoft.Data.SqlClient shadow assembly - so by copying the native assembly there, no explicit call to LoadLibrary is needed, and it is not necessary to redistribute the SNI libraries as resources.

If anyone could confirm that this approach is actually working and it's not just a fluke in my tests, I would go on and publish a Nuget package.

Thanks!

Worked for me also in .net 4x mvc5 app in global.asax

[Marfyy]

I published my approach at https://github.com/lscorcia/sqlclient.snishadowcopy . It is similar to @Tasteful approach in the sense that it copies the native assembly under the shadow folder, but it takes advantage of the fact that Windows will first try to load the dll from the same location of the Microsoft.Data.SqlClient shadow assembly - so by copying the native assembly there, no explicit call to LoadLibrary is needed, and it is not necessary to redistribute the SNI libraries as resources.

If anyone could confirm that this approach is actually working and it's not just a fluke in my tests, I would go on and publish a Nuget package.

Thanks!

This solution worked for our .net 4.8 webforms application, with a .netstandard2.0 reference to Microsoft.Data.SqlClient. First line inside Application_Start in Global.asax. Sharing the same data layer between .NET Framework and .NET 5+ projects is the goal, and works well so far.

I packaged this solution as a nuget in our companys internal feed in our local Azure DevOps, and installed the nuget on the webforms project only.

[JRahnama]

@jakenuts is this still an issue or the solution provided by @lscorcia solved your issue? If the issue is solved please feel free to close the issue.

[waf]

@JRahnama: It's still an issue I'd say, having a locked file on the filesystem causes all sorts of development time and operational issues. They can be worked around (e.g. https://github.com/lscorcia/sqlclient.snishadowcopy) but it's a pretty poor experience by default.

Possibly the SqlClient project could start using the shadow copy approach that @lscorcia showed?

[macwier]

Has anyone tried adding explicit reference to Microsoft.Data.Sql 2.x?
I'm comming from a bit different issue, but seems like it could be a solution here as well.

[EikeSchwass]

The workaround by @lscorcia didn't work for me. I don't have a Microsoft.Data.SqlClient.x86.SNI.dll, but
Microsoft.Data.SqlClient.dll and the SNI.dll in a subfolder x86.
If I remove the SNI.dll from the x86 folder the app crashed when I try to access the database because the dll is not found, even if I copy it to the shadow copy location (it appears it always searches in the bin folder). Any ideas?

Edit: The following workaround "fixed" the issue for me:
In the publisher profile file (.pubxml) I added this MsDeploySkipRules:

<MsDeploySkipRules Include="SkipDeleteSNI">
    <SkipAction>Delete</SkipAction>
    <ObjectName>filePath</ObjectName>
    <AbsolutePath>.*\\x(86|64)\\SNI\.dll</AbsolutePath>
    <XPath>
    </XPath>
</MsDeploySkipRules>

This doesn't fix the locking, but as long as the file doesn't need to be updated it works.

[JRahnama]

@EikeStein is this referring to Microsoft.Data,SqlClient or System.Data.SqlClient?

[EikeSchwass]

@EikeStein is this referring to Microsoft.Data,SqlClient or System.Data.SqlClient?

@JRahnama To Microsoft.Data.SqlClient (unless I'm totally lost)

[Kaur-Parminder]

@EikeStein What version of Microsoft.Data.SqlClient are you using? If you are not seeing Microsoft.Data.SqlClient.SNI.x64 /x86.dll in bin folder. I suggest to update the NuGet version to latest release.

SNI.dll was part of x64/x86 subfolder for versions prior to 2.0-preview4. Please see, the above comment(#354 (comment)) is a workaround to perform shadow copy of DLLs to a different folder and load DLLs from there.

[sinanaltaii]

I have the same issue with Visual Studio 2022 using .NET Framework 4.7.2.
When I try to clean the project, I get the following message:

Unable to delete file "bin\Microsoft.Data.SqlClient.SNI.x64.dll". Access to the path 'C:\myprojcts\WebApplicationProject.Web\bin\Microsoft.Data.SqlClient.SNI.x64.dll' is denied.

[jaryn-kubik]

Ran into the same issue - website running in IIS and doing a rebuild in VS would error out on cleaning the bin folder because the SNI dll is locked.

Normally IIS copies all the dlls to some temp directory like "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\8b727de3\bc3cd64\assembly\dl3\52c41fab\0079fa1e_3661d901" and runs it from there. That way the original bin folder can be cleared and rebuild.
For some reason this isn't happening for these SNI dlls.

I came up with this workaround (needs to be run somewhere at start, before anything loads the SqlClient dlls):

[System.Diagnostics.Conditional("DEBUG")]
private static void FixSqlClientRebuild()
{
    var assembly = AppDomain.CurrentDomain.GetAssemblies().FirstOrDefault(x => x.GetName().Name == "Microsoft.Data.SqlClient");
    if (assembly is not null)
    {
        var folder = Path.GetDirectoryName(assembly.Location);
        var path = Path.Combine(folder, "Microsoft.Data.SqlClient.SNI.x64.dll");
        var dll = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "bin", "Microsoft.Data.SqlClient.SNI.x64.dll");
        File.Move(dll, path);
    }
}

Basically just moving the dll from the bin folder to the temp location where "Microsoft.Data.SqlClient.dll" is.

[shazadishaque]

Ran into the same issue - website running in IIS and doing a rebuild in VS would error out on cleaning the bin folder because the SNI dll is locked.

Normally IIS copies all the dlls to some temp directory like "C:\Windows\Microsoft.NET\Framework64\v4.0.30319\Temporary ASP.NET Files\root\8b727de3\bc3cd64\assembly\dl3\52c41fab\0079fa1e_3661d901" a runs it from there. That way the original bin folder can be cleared and rebuild. For some reason this isn't happening for these SNI dlls.

I came up with this workaround (needs to be run somewhere at start, before anything loads the SqlClient dlls):

[System.Diagnostics.Conditional("DEBUG")]
private static void FixSqlClientRebuild()
{
    var assembly = AppDomain.CurrentDomain.GetAssemblies().FirstOrDefault(x => x.GetName().Name == "Microsoft.Data.SqlClient");
    if (assembly is not null)
    {
        var folder = Path.GetDirectoryName(assembly.Location);
        var path = Path.Combine(folder, "Microsoft.Data.SqlClient.SNI.x64.dll");
        var dll = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "bin", "Microsoft.Data.SqlClient.SNI.x64.dll");
        File.Move(dll, path);
    }
}

Basically just moving the dll from the bin folder to the temp location where "Microsoft.Data.SqlClient.dll" is.

We've experienced the same problem after migrating to Microsoft.Data.SqlClient. When we launch the app it loads fine but IIS Express holds a lock on the Microsoft.Data.SqlClient.SNI.x64.dll. When recompiling our .Net Framework 4.6.2 WebForms app, due to the lock we get the error "Unable to delete file "bin\Microsoft.Data.SqlClient.SNI.x64.dll". Access to the path '..\bin\Microsoft.Data.SqlClient.SNI.x64.dll' is denied."

I tried @lscorcia's workaround but it didn't work for us. I tried @jaryn-kubik's workaround and it works!! We can now recompile our project in visual studio without the compilation error occurring.

As someone previously mentioned in this thread, this compilation error only occurs for WebForms projects and it works fine in .Net Core. I hope to remove this patch when we eventually upgrade to .Net Core.

[matdavies]

To add our experience to this thread in case it helps anyone.

Like @EikeSchwass (comment), we were using a version of Microsoft.Data.SqlClient (1.1.X) that copied x86\SNI.dll and x64\SNI.dll assemblies into the bin folder, and caused IIS to lock them there. In versions of Microsoft.Data.SqlClient prior to 2.0-preview4 (per @Kaur-Parminder 's comment) the SNI.dlls are loaded from the bin directory no matter what you do (owing to a hard coded path in the static constructor of SNINativeMethodWrapper.

So, in order to use @lscorcia 's workaround, we needed to update the version of Microsoft.Data.SqlClient we were using. I tried to upgrade to 3.1.5 first, but when doing so, ended up with errors connecting to the database via EntityFramework ("error: 26 - Error Locating Server/Instance Specified"). So I updated to 4.1.5 which worked, but with the caveat that there is a breaking change; specifically, versions of Microsoft.Data.SqlClient > 4.0.0 assume that the connection will be encrypted, so if it is not, you either need to make it so, or add "Encrypt=false" to the end of your connection string.

Hope that helps someone else. Not a fun issue to diagnose and fix...

[ProVega]

With the release of 5.2 does any know if this issue was fixed?

[omccully]

@ProVega I'm using v5.2.0 and .NET Framework 4.8 and still experiencing it. Although one of our apps using 5.2.0 has not experienced it and one of them is, and I can't figure out why. They're running on different servers. Perhaps different IIS versions, not sure. Not sure what else would make the difference.

[jberke]

@ProVega still broken on 4.7.2 using 5.2.0 of Microsoft.Data.SqlClient.
Iscorcia's solution worked https://github.com/lscorcia/sqlclient.snishadowcopy. We are using OWIN so I tied into Owins Configuration class. Copied his one class into my project and it seems to be working!

Thank you @Iscorcia I owe you a beer!

[cheenamalhotra]

We're closing the issue as there are no plans to address this in .NET Framework SNI DLLs.
There are solutions discussed in the thread that should help mitigate the issue.

[caspChristian]

@cheenamalhotra how can you say that the old sql package is obsolete, and we must upgrade, and then not actually make sure it is working?
This issue MUST be fixed, or retract the statement that we should upgrade.
It is also beyond me that you are using non managed parts for the sql client Reopen and fix ASAP!

[oconnor0]

This is disappointing. The new library, Microsoft.Data.SqlClient, is in important ways worse than the old, deprecated library, System.Data.SqlClient. The "new" library both has more dependencies and has this weird DLL locking issue which requires various attempted workarounds.