Connection fails and ignores FailoverPartner port after Linux SQL Server BAG failover, defaults to port 1433

[AbbasLB]

Description

When using Microsoft.Data.SqlClient with Linux-based Basic Availability Groups (2 synchronous replicas) and specifying a custom port (e.g., 1066) in the connection string, automatic failover to the secondary server fails.

After a failover, the client attempts to connect to port 1433, ignoring the explicitly configured FailoverPartner port and actual server port.

✅ This issue does not occur with Windows-based SQL Server using named instances, even with custom ports, since the client uses the named instance to connect ot the failover (server\instance like my-failover-partner\sql64std).

Observed Behavior After Failover for this connection string with Linux host

DataSource = "tcp:my-master-server.mycompany.com,1066",
FailoverPartner = "tcp:my-failover-partner.mycompany.com,1066",

• ❌ If a failover is performed after successfully connecting to the primary instance, the client attempts to connect to my-failover-partner without FQDN or port, defaults to port 1433, and fails.
• ✅ If the application is restarted, it correctly connects to the failover partner using the specified port.
• ✅ If failback occurs, connections resume correctly.

Potential Cause

From source inspection, the FailoverPartner is being overridden by the environment change ENV_LOGSHIPNODE, however it seems that it doesn't contain the port of the sql instance:
🔗 https://github.com/search?q=repo%3Adotnet%2FSqlClient+ENV_LOGSHIPNODE&type=code

Exception Message

A network-related or instance-specific error occurred while establishing a connection to SQL Server.
The server was not found or was not accessible.
(provider: TCP Provider, error: 40 - Could not open a connection to SQL Server)

To Reproduce

1. Deploy a Basic Availability Group on SQL Server on Linux (e.g., Ubuntu 20.04) with a custom port (e.g., 1066).
2. Use this client code:

private static async Task SimpleFailoverTest()
{
    string connectionString = new SqlConnectionStringBuilder()
    {
        InitialCatalog = "DowntimeTestDB",
        DataSource = "tcp:my-master-server.mycompany.com,1066",
        FailoverPartner = "tcp:my-failover-partner.mycompany.com,1066",
        IntegratedSecurity = true,
        TrustServerCertificate = true,
    }.ConnectionString;

    while (true)
    {
        try
        {
            using var conn = new SqlConnection(connectionString);
            await conn.OpenAsync();
            var cmd = new SqlCommand("SELECT * from [DowntimeTestDB].[dbo].[OperationsLog]", conn);
            await cmd.ExecuteNonQueryAsync();
            Console.WriteLine($"Success");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
        await Task.Delay(1000);
    }
}

3. Trigger a manual failover to the secondary server.
4. Observe client fails to connect post-failover.

Expected Behavior

Client should:

• Dynamically retrieve the actual custom port from the sql server instance (e.g., port 1066)
• Or, provide a configurable option to prevent overriding the FailoverPartner in the connection string.
• Or, provide a way to set the failover port explicitly somewhere without being overridden.

Additional Context

• ✅ Wireshark confirmed: Client attempts to connect to port 1433 post-failover, ignoring custom port 1066.
• ⚠️ Failover only works when using port 1433, which is not feasible for us.
• I used the following snippet to setup the availability groups, I think that the instance names in this query are used by the client which is why the connection fails (my-failover-partner was used for the connection) and the availability group fails to setup with including the custom port like my-failover-partner,1066 and my-master-server,1066)

CREATE AVAILABILITY GROUP [ag_DowntimeTestDB]
WITH (
    BASIC,
    CLUSTER_TYPE = NONE,
    REQUIRED_SYNCHRONIZED_SECONDARIES_TO_COMMIT = 0
)
FOR DATABASE [DowntimeTestDB]
REPLICA ON 
    N'my-master-server' WITH (
        ENDPOINT_URL = N'tcp://my-master-server.mycompany.com:5022',
        FAILOVER_MODE = MANUAL,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        SEEDING_MODE = AUTOMATIC,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
    ),
    N'my-failover-partner' WITH (
        ENDPOINT_URL = N'tcp://my-failover-partner.mycompany.com:5022',
        FAILOVER_MODE = MANUAL,
        AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
        SEEDING_MODE = AUTOMATIC,
        SECONDARY_ROLE(ALLOW_CONNECTIONS = NO)
    )
GO

Logs / Traces from SqlClient

• ✅ Working Failover Trace: Windows SQL Server with named instance failover succeeded on non-default port 1066 (it can be seen that the named instance was used for the connection)
  trace_wsl_targets_windows_failover_success.txt
• ❌ Failing Failover Trace: Linux SQL Server failover, client doesn’t connect to custom port 1066 (we can see that the instance name with no port was used)
  trace_wsl_targets_linux_failover_failure.txt
  Note: Focus on sc.SqlInternalConnectionTds.LoginWithFailover to see the client updating the failover and ignoring the instance custom port.

Current Workarounds (Undesirable)

1. External port redirection from 1433 → 1066
   ⛔ Not sure how feasable and complex it is

2. Standardize to default port 1433 across all clients and servers
   ⛔ Not feasible due to massive deployment and client base

3. Fork and patch SqlClient to use 1066 as the default
   ⛔ Maintenance overhead and unclear reliability

Technical Details

• Microsoft.Data.SqlClient version: 5.2.2, 6.0.2
• .NET Target Framework: .NET 8.0
• SQL Server Version:
  • Linux: SQL Server 2022 CU19 with non-default port
  • Windows: SQL Server 2022 CU11 with non-default port
• OS Configuration:
  • ❌ Failing: WSL Linux client / Windows client → Linux SQL Server
  • ✅ Working: WSL Linux client / Windows client → Windows SQL Server (named instances)

---

Please advise on a potential fix or workaround that would allow failover to connect to the custom SQL Server port or provide a way to prevent overriding FailoverPartner provided in the connection string.

[paulmedynski]

Hi @AbbasLB , I have assigned this issue over to @samsharma2700 to continue the investigation. I will add him to the associated email thread as well. Your issue description and details are excellent and will help us get an environment setup to reproduce the issue!

[AbbasLB]

Thanks, don't hesitate to reach out if you need any more information, logs or traces, the setup can be easily done on our side.

[jsomers-MSFT]

Hi @AbbasLB , do you have a listener set up? If not, you should try setting up a listener and connecting to it instead. I believe this will likely resolve your issue. Can you try this and report back?

[AbbasLB]

Hi @jsomers-MSFT,
I don't think that we can do so. We’re currently in the process of moving from bare-metal Windows servers to Linux containers running in Kubernetes.
On Windows, we have a synchronous SQL Server mirroring setup for DR, but since mirroring isn’t supported on Linux, we’re migrating to a similar setup using a Basic Availability Group (BAG) with two synchronous replicas (no cluster manager).

1. Can we setup a listener with Linux containers on k8s? (without relying on DH2i DxEnterprise)
2. How could we migrate our databases from Windows to Linux with minimal downtime without having the FailoverPartner partner fixed? We have 200+ databases, thousands of application instances that connect to them, and our largest database is ~1.5TB. Our goal is to minimize business impact as much as possible.

[samsharma2700]

Hi Abbas, afaik FailoverPartner is a legacy connection property and SqlClient is not port aware for AGs, it re-queries server metadata after the first connection and when metadata does not contain a port it falls back to 1433. This might be an issue with driver but in this particular case it will still not give seamless-failover because clusterless BAGs don't offer it (https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-overview?view=sql-server-ver17).

jsomers is right here, since there is no listener(DNS + IP + port abstraction) to redirect to the correct replica and port after a failover.
Without a listener there are two options:
1). Manual reconnection: app closes old TCP socket, resolve a new DNS name or port, open a new connection.
2). Emulate a listener outside SQL Server: in your case it can be done via a Service(ClusterIP) or side-car controller(script that watches replica state and patches the service endpoint)

Can we setup a listener with Linux containers on k8s? (without relying on DH2i DxEnterprise)

Microsoft supported path would be to use Pacemakers in containers : https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-cluster-pacemaker?view=sql-server-ver17&tabs=rhel

or you can use kubernetes Service as a pseudo‑listener (clusterless BAG) : https://www.dbi-services.com/blog/first-steps-into-sql-server-2019-availability-groups-on-k8s/

How could we migrate our databases from Windows to Linux with minimal downtime without having the FailoverPartner partner fixed? We have 200+ databases, thousands of application instances that connect to them, and our largest database is ~1.5TB. Our goal is to minimize business impact as much as possible.

I am not an expert on migration but I would suggest looking in Distributed Availability Groups :
1). https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups?view=sql-server-ver17
2). This particularly talks about business continuity : https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-business-continuity-dr?view=sql-server-ver17

Basic idea would be to setup a distributed AG between one windows replica and one linux replica. Let it stay synchronized for some time, then you can do a listener switch. This may help : https://sqlundercover.com/2025/02/05/sql-server-migration-using-a-distributed-availability-group/