Entity Framework Core for SQL Server Failover Basic Availability Groups does not work

I have set up an SQL Server Standard 2019 with Basic Availability Groups on a Windows Failover cluster. It is not like the Always On AG, but more of a limited version of it where DB operations are on the primary replica and the secondary assumes the primary role only if there is a failover.

I have a .NET Core 2.1 application with two instances that runs in docker Windows containers on Windows Server 2019, on different VMs with SQL. Since our host does not have nested virtualization enabled, we are stuck with Windows containers. They connect to the SQL using the following connection string:
Server=TCP:172.18.18.11; Database=db; User Id=**; Password=**;
Connection Timeout=120;MultipleActiveResultSets=True;
Failover Partner=TCP:192.168.11.24


There are some weak points though. Since we had a small number of VMs and we use containers mostly, we did not care much for setting up the Active Directory due to overhead and some bad previous experience with Windows Container Networking (especially DNS issues) when using AD.

We have successfully created the Failover Cluster but they are on different subnets and different physical locations 172.18.18.0/24 and 192.168.11.0/24. This caused some problems with allocating the cluster IP resource, and since we do not have an internal DNS, we are stuck with IP addresses. And we cannot use cluster domain name or use the listener IP address since instances are on different subnets as well. This is a problem with our host and we could not really resolve.

So the problem is, SQL Failover works, but client applications do not get aware of the change, despite the "Failover Partner" in the connection string. In case of a failover, our app responds with a SQL Exception:

The target database, 'db', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the

Ok, so I had to go out of my way and took a risk to setup DNS without AD. It wasn't a big deal since we had only 2 SQL instances and 3 client VMs. Apparently "Failover Partner" keyword only works with the deprecated "mirroring" feature. And the listeners are to be only accessed with their names. I don't think there is a solution to not using DNS without a number of ugly hacks.

StackExchange Database Administrators Q#291456, answer score: 4