Fail to connect after database failover

[brunoblank]

Describe the bug

We have a database setup using mirroring.

Connection-String: server=tcp:172.21.6.8;user id=<user>;password=<password>;database=<database>;Failover Partner=tcp:172.21.6.7;TrustServerCertificate=true

When the database fails over from the "secondary" then everything works instantly.
When the database fails over from the "primary" (the one that server points to) then we see the exception below. The connection works if the application is restarted or if there has been no requests for a couple of minutes.

The exception indicates that Named pipes are being used even though we are specifying tcp:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
   at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
   at Microsoft.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, TimeoutTimer timeout, SqlConnectionString connectionOptions, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, SecureString newSecurePassword, TimeoutTimer timeout, Boolean withFailover)
   at Microsoft.Data.SqlClient.SqlInternalConnectionTds.LoginWithFailover(Boolean useFailoverHost, ServerInfo primaryServerInfo, String failoverHost, 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, Boolean applyTransientFaultHandling, String accessToken, DbConnectionPool pool, Func`3 accessTokenCallback)
   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.TryGetConnection(DbConnection owningObject, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at Microsoft.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at Microsoft.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry, SqlConnectionOverrides overrides)
   at Microsoft.Data.SqlClient.SqlConnection.InternalOpenAsync(CancellationToken cancellationToken)
--- End of stack trace from previous location ---
   at Program.<Main>$(String[] args) in C:\temp\SQLFailover\SQLFailover\Program.cs:line 14
   at Program.<Main>$(String[] args) in C:\temp\SQLFailover\SQLFailover\Program.cs:line 18

To reproduce

using Microsoft.Data.SqlClient;

Console.WriteLine("Hello, World!");

var connectionString = "server=tcp:172.21.6.8;user id=<user>;password=<password>;database=<database>;Failover Partner=tcp:172.21.6.7;TrustServerCertificate=true";

while (true)
{
    try
    {
        await using var connection = new SqlConnection(connectionString);
        await connection.OpenAsync();

        var command = new SqlCommand("SELECT 1", connection);
        await command.ExecuteNonQueryAsync();
        Console.WriteLine("Success");
    }
    catch (Exception ex)
    {
        Console.WriteLine($"Error: {ex.Message}\r\n{ex.StackTrace}");
    }
    await Task.Delay(2000);
} 

cs-proj;

<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net8.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.Data.SqlClient" Version="5.2.1" />
  </ItemGroup>

</Project>

Expected behavior

Connection to the failover partner to succeed

Further technical details

Microsoft.Data.SqlClient version: 5.2.1
.NET target: net8.0
SQL Server version: SQL Server 13.0.5698.0
Operating system: Windows (also aks: FROM mcr.microsoft.com/dotnet/aspnet:8.0)

Additional context
Add any other context about the problem here.

[arellegue]

Thank you for reporting this issue with database failover. Could you confirm that you have configured the endpoints correctly. Please refer to the documentation at https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/the-database-mirroring-endpoint-sql-server?view=sql-server-ver16.

[brunoblank]

Yes, the application can connect to both servers. We have only experienced the problem described when fail over from the primary side to the failover partner. The other directions works without any issues.
Thanks
/Bruno

[arellegue]

We are wondering if this is the first time you had this issue and if it has worked before.

I believe the database mirroring endpoints must have authorization to connect to each other in addition to the client application's authorization to connect to each database. Could you confirm that authentication between the database mirroring endpoints are in place. Please refer to the documentation at https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/example-setting-up-database-mirroring-using-windows-authentication-transact-sql?view=sql-server-ver16

[brunoblank]

[brunoblank]

As a side note, our legacy application running with .net framework 4.6.1 with System.Data.SqlClient does not experience connection problems when the database fails over.

[arellegue]

At this point we can assume that it is an initial setup as we noticed that you are using the option "High safety without automatic failover" but did not configure a witness server instance. Please refer to the documentation on using that option at
https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/setting-up-database-mirroring-sql-server?view=sql-server-ver16

[brunoblank]

We are experiencing the problem in the production environment, which is configured with a witness server. We do not choose to do failover in the production environment, but when the database failover we either have to restart our applications or do manual failover back to the original state to restore database connectivity.

It is not possible for us to run these kind of test in the production environment, so we have created the smallest setup both code and databases to be able to reproduce the problem.

Thanks for looking into this problem
/Bruno

[arellegue]

Thank you for the clarification. We will investigate and try to reproduce the issue and then let you know.

[brunoblank]

Hi @arellegue

Is there any updates to this issue?

[josephgrice]

Hey @arellegue

I am also experiencing this error when failing over mirrored instances.

In my case this started when moving hosting patterns from EC2 to ECS Fargate running on net8.0 and using package Microsoft.Data.SqlClient Version 5.2.1

These failovers continue to work successfully for our services still hosted on EC2, running on net8.0 and using package Microsoft.Data.SqlClient Version 5.2.1 but this issue seems to manifest when switching to the container runtime (Ubuntu) and can be reproduced both locally and when hosted in ECS Fargate.

Also, similarly to @brunoblank we also see this -

• When the database fails over from the "secondary" then everything works instantly.
• When the database fails over from the "primary" (the one that server points to) then we see the exception below
• 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: TCP Provider, error: 35 - An internal exception was caught)

Has there been any updates on this?

[David-Engel]

Given the second observance of this issue, I tried to repro this locally and wasn't able to.

I have a mirrored configuration (two SQL Server 2022 instances on two Windows Server 2022 VMs) with no witness. Running the code in the top comment always results in success after failover from primary to secondary or secondary to primary (after 1 or 2 login failure errors during the transition).

I'm running the client on Windows 11 using .NET 8. I tried some variations of native and Managed SNI on Windows along with variations of server=<IP address>, server=tcp:<IP address>, server=tcp:<name>, Encrypt=true/false. I don't have docker or WSL set up locally, so haven't been able to try an Ubuntu client (container or plain WSL).

I'm not sure what else could be different that would cause the issue.

[brunoblank]

Hi @David-Engel,

From the exception we see it looks like the driver is trying a Named Pipes connection

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

We don't have named pipes enabled on our sql-server. We are experience the problem both in aks and on my windows machine.

Is there any other test I can do?

[josephgrice]

Hey @David-Engel ,

Thanks for looking into this.

I notice you mention this in your testing -
I don't have docker or WSL set up locally, so haven't been able to try an Ubuntu client (container or plain WSL).

But as per my comment here -
this issue seems to manifest when switching to the container runtime (Ubuntu) and can be reproduced both locally and when hosted in ECS Fargate.

The same code gracefully handles failovers when hosted locally or on EC2 when not containerised

Would you be able to conduct similar tests with the follow parameters?

• Code running in an Ubuntu container
• Under a constant load (enough for active connections to be in the pool during the failover)