1

I'm getting an exception when attempting to open a SQL Server connection from a class library when the same connection / connection string code works fine within the main application.

The following code opens a connection from the main application (ASP.NET Framework application targeting .NET Framework 4.8):

const string connStr = "Data Source=(local);Initial Catalog=DatabaseTest1;Integrated Security=True;MultipleActiveResultSets=True";
var connection = new SqlConnection(connStr);
connection.Open();
connection.Close();

If I then (or before) try to call exactly the same code from a class library (net48 and net6.0) I get a 20 second pause on the Open() statement, during which the output window reports around 24 of the following:

Exception thrown: 'Microsoft.Data.SqlClient.SqlException' in Microsoft.Data.SqlClient.dll

eventually throwing the following exception (no inner exception):

[Microsoft.Data.SqlClient.SqlException]
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: HTTP Provider, error: 0 - )

I'm suspecting some kind of conflict between .NET Framework versions and/or [System/Microsoft].Data.SqlClientbut I can't get to the bottom of it.

The class library uses these nuget packages:

  • Dapper 2.0.123 (latest)
  • Microsoft.Data.SqlClient 5.0.1 (latest)
  • Ninject 3.3.6 (latest)

The main ASP.NET Framework application has lots more but those that I think are relevant are:

  • EntityFramework 6.4.4 (latest)
  • Ninject 3.3.6 (latest)

It also has a reference (non nuget) to System.Data 4.0.0.0 which is where it's getting SqlConnection class from. Could this be the cause? Some kind of conflict here?

The class library code has been working until recently has it's been in production for a couple of years or more.

Any advise would be welcome. Thanks.

UPDATE

  1. When I add ;TrustServerCertificate=True to the connection string, I see the following in the output window during the Open() operation and the whole application bombs out!

Exception thrown: 'System.AccessViolationException' in Microsoft.Data.SqlClient.dll

  1. I've created a test .NET Framework console app and included the class library. The database connection works but only after I [a] changed both library and console app from "Any CPU" to "x86" and [b] manually added the Microsoft.Data.SqlClient.SNI.x64.dll/pdb files to the bin folder. ("Any CPU" looked for the equally absent Microsoft.Data.SqlClient.SNI.x86.dll file). My main application still failing after changing all assemblies from "Any CPU" to "x64".

UPDATE 2 (2023-01-19)

Logging the exception shows below, but the database name and server name are correct. The connection string is:

Data Source=(local);Initial Catalog=ActivbaseTest1;Integrated Security=True;MultipleActiveResultSets=True;TrustServerCertificate=True;Encrypt=false

The stack trace shows:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: HTTP Provider, error: 0 - )
Microsoft.Data.SqlClient.SqlException
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnection.cs:line 768
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:line 1775
at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnectionString connectionOptions, Boolean withFailover, Boolean isFirstTransparentAttempt, ServerCertificateValidationCallback serverCallback, ClientCertificateRetrievalCallback clientCallback, Boolean useOriginalAddressInfo, Boolean disableTnir) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\TdsParser.cs:line 653
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, Boolean withFailover, Boolean isFirstTransparentAttempt, Boolean disableTnir) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlInternalConnectionTds.cs:line 2309
at 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:line 1922
at 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:line 1727
at 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:line 554
at 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:line 145
at 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:line 148
at 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:line 950
at 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:line 2014
at 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:line 1419
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionPool.cs:line 1302
at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionFactory.cs:line 339
at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\ProviderBase\DbConnectionInternal.cs:line 770
at Microsoft.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 2130
at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 2101
at Microsoft.Data.SqlClient.SqlConnection.Open(SqlConnectionOverrides overrides) in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\netfx\src\Microsoft\Data\SqlClient\SqlConnection.cs:line 1658
at RSPlatform.DataDap.Services.DbConnectionFactory.GetConnection()

Could this be an issue with what user the connection is using? The main part of the app (using EF) connects with the site's application pool user account and works but I don't know how to check or how to set Dapper to to use the same?

Chris Walsh
  • 3,423
  • 2
  • 42
  • 62

1 Answers1

0

Is the connection string exactly the same to your application and the test console app (2.) that you created?

I would suggest to add "Trusted_Connection=True;" to your connection string since you are connecting to the database without user and password, i.e. using the windows auth.

theCuriousOne
  • 1,022
  • 10
  • 22
  • Thanks. I have gone over the exact connection string many times and yes, they are the same. I've added Trusted_Connection=True; and this doesn't fix the issue - the w3c application pool still crashes. I've also tried adding TrustServerCertificate=True;Encrypt=false. As I say, the same connection string (except for database name) works in Dev but not on test/live environment when using Dapper. – Chris Walsh Jan 31 '23 at 11:25
  • Also, according to [the answer to this post](https://stackoverflow.com/questions/3077412/what-is-the-difference-between-trusted-connection-and-integrated-security-in-a-c), **Trusted_Connection=True** is synonymous with **Integrated Security=True** which I already have in my connection string. – Chris Walsh Jan 31 '23 at 11:31
  • Have you also checked if the database server can be pinged, i.e there are no other networking issues (or firewalls)? – theCuriousOne Jan 31 '23 at 14:08
  • The database is on the same server as the running application. Additionally, the connection works via EntityFramework within the same application (just not using Dapper). There's nothing wrong with the database as I access it frequently. Thanks – Chris Walsh Feb 07 '23 at 17:58
  • Stumbled upon this (which might be of use for) https://www.josephguadagno.net/2022/12/06/encryption-is-now-required-for-sql-server-connection-strings – theCuriousOne Feb 12 '23 at 16:16