search

dotnet / SqlClient

Microsoft.Data.SqlClient provides database connectivity to SQL Server for .NET applications.
MIT License
838 stars 279 forks source link

TDSParser Error #2022

Open MattH678 opened 1 year ago

MattH678 commented 1 year ago

Hi,

As lots of people have previously mentioned this bug, I will try and keep this short and concise. I am getting the bug within a VSTO Excel plugin whereby an exception is thrown when attempting to open a SQL connection. It appears that the appropriate SNI libraries are not being copied properly in the publish folder, but are in the debug and release folders. Authentication with Azure works fine for KeyVault, It is just AZ Sql that it fails with.

I have tried the solution listed in #446, https://learn.microsoft.com/en-us/sql/connect/ado-net/sqlclient-troubleshooting-guide?view=sql-server-ver15#unable-to-load-native-sni-server-name-indication-library. My attempts to fix the problem range from shadow copying the library, attempting to have a reference within the project to SNI, migrating from package.config to packagereference, forcing a local copy...the list goes on.

I will freely admit I am not an expert at MSBuild and could really do with some help, If I have missed something please let me know, I'm very very lost...

Code:
public DataSet RunReaderCommand(string command)
{
                DataSet ds;
                SqlConnectionStringBuilder = new SqlConnectionStringBuilder();
                SqlConnectionStringBuilder.DataSource = Az_SQLDB_svrName;
                SqlConnectionStringBuilder.InitialCatalog = Initial_DatabaseName;
                SqlConnectionStringBuilder.TrustServerCertificate = true;
                SqlConnectionStringBuilder.PersistSecurityInfo = false;
                SqlConnectionStringBuilder.Authentication = SqlAuthenticationMethod.ActiveDirectoryDefault;
                SqlConnection = new SqlConnection(SqlConnectionStringBuilder.ConnectionString);
                SqlConnection.Open();
                SqlCommand SqlCommand = new SqlCommand(command, SqlConnection);
                SqlCommand.CommandType = CommandType.Text;

                sqlDataAdapter.SelectCommand = SqlCommand;

                ds = new DataSet(Table.ToString());
                sqlDataAdapter.Fill(ds);
                SqlConnection.Close();
                return ds;
}
Exception message:
The type initializer for 'System.Data.SqlClient.TdsParser' threw an exception.:
Stack trace:

   at System.Data.SqlClient.TdsParser..ctor(Boolean MARS, Boolean fAsynchronous)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, TimeoutTimer timeout)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, Boolean applyTransientFaultHandling)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()

Further technical details

Microsoft.Data.SqlClient version: 5.2.0-preview1.23109.1 <--- I switched to the preview to check I couldn't force it to copy (It didn't) Microsoft.Data.SqlClient.SNI version: 5.1.0 .NET Framework 4.8 SQL Server version: AZ Sql Operating system: Azure VM Windows Pro 10/11

Additional context This is my build output:

ResolveAssemblyReferences:
1>  Primary reference "Microsoft.Data.SqlClient.SNI".
1>C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\amd64\Microsoft.Common.CurrentVersion.targets(2352,5): warning MSB3245: Could not resolve this reference. Could not locate the assembly "Microsoft.Data.SqlClient.SNI". Check to make sure the assembly exists on disk. If this reference is required by your code, you may get compilation errors.
1>          For SearchPath "{CandidateAssemblyFiles}".
1>          Considered "C:\Users\Mharr\.nuget\packages\microsoft.sqlserver.types\160.1000.6\build\net462\..\..\runtimes\win-x86\native\SqlServerSpatial160.dll",
1>              but its name "SqlServerSpatial160"
1>              didn't match the expected name "Microsoft.Data.SqlClient.SNI".
1>          Considered "C:\Users\Mharr\.nuget\packages\microsoft.sqlserver.types\160.1000.6\build\net462\..\..\runtimes\win-x64\native\SqlServerSpatial160.dll",
1>              but its name "SqlServerSpatial160"
1>              didn't match the expected name "Microsoft.Data.SqlClient.SNI".
1>          Considered "C:\Users\Mharr\.nuget\packages\microsoft.web.webview2\1.0.1722.45\build\\..\runtimes\win-x86\native\WebView2Loader.dll",
1>              but its name "WebView2Loader"
1>              didn't match the expected name "Microsoft.Data.SqlClient.SNI".
1>          Considered "C:\Users\Mharr\.nuget\packages\microsoft.web.webview2\1.0.1722.45\build\\..\runtimes\win-x64\native\WebView2Loader.dll",
1>              but its name "WebView2Loader"
1>              didn't match the expected name "Microsoft.Data.SqlClient.SNI".
1>          Considered "C:\Users\Mharr\.nuget\packages\microsoft.web.webview2\1.0.1722.45\build\\..\runtimes\win-arm64\native\WebView2Loader.dll",
1>              but its name "WebView2Loader"
1>              didn't match the expected name "Microsoft.Data.SqlClient.SNI".
1>          For SearchPath "{TargetFrameworkDirectory}".
1>          Considered "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.8\Microsoft.Data.SqlClient.SNI.winmd", but it didn't exist.
1>          Considered "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.8\Microsoft.Data.SqlClient.SNI.dll", but it didn't exist.
1>          Considered "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.8\Microsoft.Data.SqlClient.SNI.exe", but it didn't exist.
1>          Considered "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.8\Facades\Microsoft.Data.SqlClient.SNI.winmd", but it didn't exist.
1>          Considered "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.8\Facades\Microsoft.Data.SqlClient.SNI.dll", but it didn't exist.
1>          Considered "C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework\.NETFramework\v4.8\Facades\Microsoft.Data.SqlClient.SNI.exe", but it didn't exist.
1>          For SearchPath "{AssemblyFoldersFromConfig:C:\Program Files\Microsoft Visual Studio\2022\Community\MSBuild\Current\Bin\AssemblyFolders.config,v4.8}".
1>          Considered "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\Extensions\Microsoft\SqlDb\Microsoft.Data.SqlClient.SNI.winmd", but it didn't exist.
1>          Considered "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\Extensions\Microsoft\SqlDb\Microsoft.Data.SqlClient.SNI.dll", but it didn't exist.
1>          Considered "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\Extensions\Microsoft\SqlDb\Microsoft.Data.SqlClient.SNI.exe", but it didn't exist.
1>          Considered "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\PublicAssemblies\Microsoft.Data.SqlClient.SNI.winmd", but it didn't exist.
1>          Considered "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\PublicAssemblies\Microsoft.Data.SqlClient.SNI.dll", but it didn't exist.
1>          Considered "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\PublicAssemblies\Microsoft.Data.SqlClient.SNI.exe", but it didn't exist.
1>          Considered "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\ReferenceAssemblies\v4.5\Microsoft.Data.SqlClient.SNI.winmd", but it didn't exist.
1>          Considered "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\ReferenceAssemblies\v4.5\Microsoft.Data.SqlClient.SNI.dll", but it didn't exist.
1>          Considered "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\ReferenceAssemblies\v4.5\Microsoft.Data.SqlClient.SNI.exe", but it didn't exist.
1>          Considered "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\ReferenceAssemblies\v4.0\Microsoft.Data.SqlClient.SNI.winmd", but it didn't exist.
1>          Considered "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\ReferenceAssemblies\v4.0\Microsoft.Data.SqlClient.SNI.dll", but it didn't exist.
1>          Considered "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\ReferenceAssemblies\v4.0\Microsoft.Data.SqlClient.SNI.exe", but it didn't exist.
1>          Considered "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\ReferenceAssemblies\v2.0\Microsoft.Data.SqlClient.SNI.winmd", but it didn't exist.
1>          Considered "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\ReferenceAssemblies\v2.0\Microsoft.Data.SqlClient.SNI.dll", but it didn't exist.
1>          Considered "C:\Program Files\Microsoft Visual Studio\2022\Community\Common7\IDE\ReferenceAssemblies\v2.0\Microsoft.Data.SqlClient.SNI.exe", but it didn't exist.
1>          For SearchPath "{Registry:Software\Microsoft\.NETFramework,v4.8,AssemblyFoldersEx}".
1>          Considered AssemblyFoldersEx locations.
1>          For SearchPath "{AssemblyFolders}".
1>          Considered "C:\Program Files\IIS\Microsoft Web Deploy V3\Microsoft.Data.SqlClient.SNI.winmd", but it didn't exist.
1>          Considered "C:\Program Files\IIS\Microsoft Web Deploy V3\Microsoft.Data.SqlClient.SNI.dll", but it didn't exist.
1>          Considered "C:\Program Files\IIS\Microsoft Web Deploy V3\Microsoft.Data.SqlClient.SNI.exe", but it didn't exist.
1>          For SearchPath "{GAC}".
1>          Considered "Microsoft.Data.SqlClient.SNI", which was not found in the GAC.
1>          For SearchPath "{RawFileName}".
1>          Considered treating "Microsoft.Data.SqlClient.SNI" as a file name, but it didn't exist.
1>          For SearchPath "bin\Release\".
1>          Considered "bin\Release\Microsoft.Data.SqlClient.SNI.winmd", but it didn't exist.
1>          Considered "bin\Release\Microsoft.Data.SqlClient.SNI.dll", but it didn't exist.
1>          Considered "bin\Release\Microsoft.Data.SqlClient.SNI.exe", but it didn't exist.
JRahnama commented 1 year ago

@MattH678 thanks for opening the issue. We will investigate this.