Microsoft.Data.SqlClient.SNI not deployed in "Website" projects

[StephenMolloy]

Describe the bug

In the old ASP.Net "Web Site" project, Microsoft.Data.SqlClient.SNI binaries are not being included in the 'bin' directory output. The nuget package is successfully added to packages.config when adding the Microsoft.DataSqlClient package... but the SNI binaries are not deployed.

I believe this is likely because the "Web Site" model - being "project-less" - does not play well with MSBuild. Visual Studio takes some steps to make a majority of Nuget packages work in this model. I don't know the exact mechanics of it, but iirc, either VS or nuget place a *.refresh file in the bin directory of the website for each binary in the 'lib' folder of the nuget package. The *.refresh file simply contains a path pointing to the original dll so VS can check for updates when building and deploy new or missing dll's in the build output. This is how Microsoft.Data.SqlClient.dll gets included.

But the SNI binaries aren't in the 'lib' folder. It seems they rely on a .targets file in the 'build' folder to copy them to build output. But "Website" projects aren't msbuild-based. So that targets file isn't ever looked at or run afaik.

To reproduce

1. Install "Additional project templates..." under “ASP.Net and Web Development” section of Visual Studio Installer
2. Create a "ASP.NET Empty Web Site" project
3. Install SqlSessionStateProviderAsync 2.0 Microsoft.Data.SqlClient 5.0 nuget package
4. Add a "DefaultConnection" connection string to the web.config file.
5. Build the project then press “F5.”
6. Get YSOD error below. Microsoft.Data.SqlClient.SNI.x86/x64.dlls are not copied to bin folder.

(The Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync 2.0 package is not public yet, so you won't get the "error below" since the empty website doesn't try to load MS.Data.SqlClient. But you'll see MS.D.SC.dll in the bin folder, but not the SNI binaries... despite the SNI package being included in packages.config. Contact me if you want a private of the SqlSessionState package to test with - or we do plan to upload a preview to nuget shortly.)

Configuration Error
Parser Error Message: They type initializer for 'Microsoft.Data.SqlClient.TdsParser' threw an exception.

[DllNotFoundException: Unable to load DLL 'Microsoft.Data.SqlClient.SNI.x64.dll': The specified module could not be found. (Exception from HRESULT: 0x8007007E)]
   Microsoft.Data.SqlClient.SNINativeManagedWrapperX64.SNIInitialize(Boolean useSystemDefaultSecureProtocols, IntPtr pmo) +0
   Microsoft.Data.SqlClient.SNILoadHandle..ctor() in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParserSafeHandles.cs:19
   Microsoft.Data.SqlClient.SNILoadHandle..cctor() in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParserSafeHandles.cs:17

[TypeInitializationException: The type initializer for 'Microsoft.Data.SqlClient.SNILoadHandle' threw an exception.]
   Microsoft.Data.SqlClient.TdsParser..cctor() in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:181

[TypeInitializationException: The type initializer for 'Microsoft.Data.SqlClient.TdsParser' threw an exception.]
   Microsoft.Data.SqlClient.TdsParser..ctor(Boolean MARS, Boolean fAsynchronous) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:38
   Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:1840
   Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:1727
   Microsoft.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, DbConnectionPool pool, String accessToken, SqlClientOriginalNetworkAddressInfo originalNetworkAddressInfo, Boolean applyTransientFaultHandling) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:539
   Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnectionFactory.cs:145
   Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionFactory.cs:148
   Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:950
   Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:2014
   Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:1419
   Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen() in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:1216
   System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() +32
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
   Microsoft.AspNet.SessionState.<CreateSessionStateTableAsync>d__49.MoveNext() +283
   System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw() +32
   System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) +62
   Microsoft.AspNet.SessionState.SqlSessionStateRepository.CreateSessionStateTable() +115
   Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync.Initialize(String name, NameValueCollection config, SessionStateSection ssc, ConnectionStringSettings connectionString, Boolean shouldCreateTable) +1051
   Microsoft.AspNet.SessionState.SqlSessionStateProviderAsync.Initialize(String name, NameValueCollection config) +175
   System.Web.Configuration.ProvidersHelper.InstantiateProvider(ProviderSettings providerSettings, Type providerType) +693

Expected behavior

No error. Or at least a different error becuase the site gets past this error because the SNI files are included and found in the '/bin' directory.

Further technical details

Microsoft.Data.SqlClient version: 5.0
.NET target: .Net Framework - net462 or above
SQL Server version: (2019, but I don't think that's relevant here)
Operating system: Windows

[lcheunglci]

Thanks @StephenMolloy for bring this issue to our attention and your insight in root cause of the issue. I believe there are few other similar issues where for ASP.NET publish/deployment where the NativeSNI binaries appear to be missing, and I believe most involved a workaround with a shadow copy mechanism. #745 #727 #1318 #354 and a few others. In #612 (comment) @cheenamalhotra made a suggestion to modify the add Microsoft.Data.SqlClient.SNI.targets from our SNI repro that may resolve the issue, and we can probably make a private nuget to test out, but we'll get back to you on that.