4

This error started suddenly after upgrading Microsoft.Data.SqlClient from v2 to v4. Reverting the code back had no effect and the error persists. It's a basic connection string with a data source, initial catalog, username, password, and MARS. Identical settings work fine when connecting through SSMS, so it's not a permissions issue; this has all be working fine for a long time.

Encryption is off by default. The only strange thing I noticed coinciding with this error (other than the client version update, which I suspect corrupted something) is that if I turn on encryption, I get an error about a certificate chain being invalid. The only root certificate I can find that expired recently was a GlobalSign root cert. I've since updated Windows and the error persists.

StackTrace:

at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
at Microsoft.Data.SqlClient.SqlConnectionPoolGroupProviderInfo.CreateFailoverPermission(SqlConnectionString userConnectionOptions, String actualFailoverPartner)
at Microsoft.Data.SqlClient.SqlConnectionPoolGroupProviderInfo.FailoverCheck(Boolean actualUseFailoverPartner, SqlConnectionString userConnectionOptions, String actualFailoverPartner)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(TimeoutTimer timeout, SqlConnectionString connectionOptions, SqlCredential credential, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance)
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)
at Microsoft.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnectionPool pool, DbConnection owningObject, DbConnectionOptions options, DbConnectionPoolKey poolKey, DbConnectionOptions userOptions)
at Microsoft.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at Microsoft.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject, DbConnectionOptions userOptions, DbConnectionInternal oldConnection)
at Microsoft.Data.ProviderBase.DbConnectionPool.TryGetConnection(DbConnection owningObject, UInt32 waitForMultipleObjectsTimeout, Boolean allowCreate, Boolean onlyOneCheckConnection, DbConnectionOptions userOptions, DbConnectionInternal& connection)
at Microsoft.Data.ProviderBase.DbConnectionPool.WaitForPendingOpen()
--- End of stack trace from previous location ---
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.ConfiguredTaskAwaitable.ConfiguredTaskAwaiter.GetResult()
at ConnectionRunner.ConnectionInfo.<OpenAsync>d__39.MoveNext()
at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task)
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at System.Runtime.CompilerServices.ConfiguredTaskAwaitable`1.ConfiguredTaskAwaiter.GetResult()
at ConnectionRunner.ConnectionInfo.<TestConnectionAsync>d__35.MoveNext()
Triynko
  • 18,766
  • 21
  • 107
  • 173
  • Are you using Failover, and if so does the failover partner server have a certificate and is otherwise fully setup? – Charlieface Dec 19 '21 at 11:35
  • 1
    No idea; I'm a developer, not an operations guy, so I don't configure RDS. Although, taking your question into consideration, I tried setting the FailoverPartner property of the SqlConnectionStringBuilder explicitly to an empty string, and the connections all succeeded, and the 'key not found' error went away. Having trouble why a property that defaults to null, when set to an empty string, causes the connection to succeed. – Triynko Dec 20 '21 at 02:48
  • What do you mean by RDS? And what is your full connection string currently? – Charlieface Dec 20 '21 at 09:28
  • RDS is Amazon's hosting service for SQL Server. As far as I know, nothing has changed there, and this error started occurring suddenly after upgrading the NuGet package for Microsoft.Data.SqlClient in an app, but persisted after reverting the code back to the old version. The timing of me working on upgrading the code and this error starting could be coincidental. – Triynko Dec 20 '21 at 16:11
  • 1
    The connection string looks like this: `Data Source=tcp:server.url,1433;Initial Catalog=DatabaseName;User ID=username;Password=password;Multiple Active Result Sets=True;Connect Timeout=6` and fails with the key error. However, if I explicitly set FailoverPartner to empy, then this connection string succeeds: `Data Source=tcp:server.url,1433;Failover Partner=;Initial Catalog=DatabaseName;User ID=username;Password=password;Multiple Active Result Sets=True;Connect Timeout=6` – Triynko Dec 20 '21 at 16:11
  • 1
    It's just incredibly bizarre for code that has worked for years to suddenly start failing with a 'key not found in dictionary' error. It's also a very generic error message to be thrown from something like SqlConnection.OpenAsync, and the fix is also unexpected (explicitly setting a FailoverPartner to nothing) in the connection string. – Triynko Dec 20 '21 at 16:14
  • 1
    I was trying to work through the source code on Github, and it looked like it was something to do with trying to find the failover server dynamically from the server after connection. Perhaps RDS has implemented Availability Groups recently? – Charlieface Dec 20 '21 at 16:15
  • We also observed this error after updating an application from Microsoft.Data.SqlClient 2.0 to 4.0. It happened for two customers with high availability clusters, though others with HA worked fine. Adding "Failover Partner=;" to the connection string worked for us as a quick fix. Is this a known issue that Microsoft is aware of? – David Tarulli Feb 02 '22 at 20:31
  • 2
    Hats off to @Triynko for posting their tip about using the empty "Failover Partner=;" in the conn-str as after a week of banging my head against the wall, this solved our issue. Through experimentation I found that this conn-str option appears to be needed only when using Microsoft.Data.SqlClient v4 targeting NET Framework v4, and while connecting to our older SQL 2016 cluster. To clarify, I didn't need this in the conn-string when the exact same code was running in .NET Core. v3.1. And, I didn't need it in either .NET v4 or .NET Core v3.1 when connecting to our SQL 2019 HA cluster. – Kurtbaby Mar 25 '22 at 17:21

2 Answers2

1

It's not posted as an answer, so even if not from my head, let's keep track of what is working.

If you're using Microsoft.Data.SqlClient library with .NET Framework v4 against a SQL cluster (eg. utilizing Availability Groups) you have to add blank Failover Partner=; parameter. Can be added via ConnectionBuilder too.

Jan Zahradník
  • 2,417
  • 2
  • 33
  • 44
0

This is reportedly fixed in Microsoft.Data.SqlClient 5.0.0 (released Aug 5th 2022).

https://github.com/dotnet/SqlClient/blob/main/release-notes/5.0/5.0.0.md https://github.com/dotnet/SqlClient/pull/1614

David Tarulli
  • 929
  • 1
  • 10
  • 13