MDS 4.0+ Fails in .NET 4.8 when using AG (FailoverPartner)

[cmeyertons]

Describe the bug

A clear and concise description of what the bug is.

If you are seeing an exception, include the full exceptions details (message and stack trace).

Exception message:
Stack trace:
System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary. at System.ThrowHelper.ThrowKeyNotFoundException() 
at System.Collections.Generic.Dictionary`2.get_Item(TKey key) 
at Microsoft.Data.SqlClient.SqlConnectionPoolGroupProviderInfo.CreateFailoverPermission(SqlConnectionString userConnectionOptions, String actualFailoverPartner) 
in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\src\Microsoft\Data\SqlClient\SqlConnectionPoolGroupProviderInfo.cs:line 107 
at Microsoft.Data.SqlClient.SqlConnectionPoolGroupProviderInfo.FailoverCheck(Boolean actualUseFailoverPartner, SqlConnectionString userConnectionOptions, String actualFailoverPartner) 
in D:\a\_work\1\s\src\Microsoft.Data.SqlClient\src\Microsoft\Data\SqlClient\SqlConnectionPoolGroupProviderInfo.cs:line 66 
at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString connectionOptions, SqlCredential credential, TimeoutTimer timeout)
....

It looks like we are checking for existence of the FailoverPartner keyword by trying to access a dictionary and seeing if it returns null here?

To reproduce

var csb = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder()
{
	DataSource = db.ServerName, // the raw server name, not AG listener
	InitialCatalog = db.DBname,
	TrustServerCertificate = true,
	IntegratedSecurity = true,
};

using (var c = new Microsoft.Data.SqlClient.SqlConnection(csb.ToString()))
{
	c.Execute("SELECT TOP 1 1 FROM dbo.Entity");
}

Expected behavior

Should be able to connect when database is in an AG.

Further technical details

Microsoft.Data.SqlClient version: 4.0
.NET target: net48
SQL Server version: SQL Server 2019
Operating system: Windows 2019

[lcheunglci]

Hi @cmeyertons, I'll take a look since I did merge to this file into the shared src codebase.

[JRahnama]

@cmeyertons where did you provide the failover partner?

[cmeyertons]

@lcheunglci @JRahnama

We are not providing the failover partner in our connection string, just the Data Source and Initial Catalog.

Our data source is the the direct server name, not AG listener, due to some quirks in how our setup in our networking setup (I will try to figure out some more details on why this is) -- I know in a failover scenario, we will have to manually failover the server name.

That aside, our setup should work -- it is fine in our code base using System.Data.SqlClient -- this behavior only crops up in MDS.

[cmeyertons]

@lcheunglci @JRahnama well I discovered a very hack-y workaround...

var csb = new Microsoft.Data.SqlClient.SqlConnectionStringBuilder()
{
	DataSource = db.ServerName, // the raw server name, not AG listener
	InitialCatalog = db.DBname,
	TrustServerCertificate = true,
	IntegratedSecurity = true,
        FailoverPartner = string.Empty, // <- HACK: workaround
};

[JRahnama]

@cmeyertons, glad to hear you have a workaround 👍🏽 .

[paulmorrishill]

Also experiencing this on AWS RDS instances in multi-az mode - not sure how they have it set up but it seems to be the same issue.

After testing, it seems you can apply @cmeyertons's workaround through the connection string by appending the following:
Failover Partner=""

[JRahnama]

Closing this as the PR #1614 was merged. Feel free to comment or open a new issue if you are still experiencing the problem.