SSMS connection to Availability group listener doesn't failover I've set up an Availability Group on SQL Server (on Azure VMs) and created a Distributed Network Name as the listener for this. (SQL Server 2019 on Windows 2019)
If I connect to that listener in SMS then it works fine. If I manually failover and re-connect in SSMS it again works fine.  Running select @@SERVERNAME in both cases returns the primary server.
However, if I leave the original connection open, and later try select @@SERVERNAME then it still returns the server it connected to (which is now secondary). I can verify this by trying to INSERT a row and it tells me this server now needs a READ ONLY INTENT connection and I can't do an update.
Is it expected that you have to open a new connection after a (manual) failover event?
What you describe is normal behavior for SSMS, at least all the times I've done failover testing.
But Management Studio is a poor tool to test this type of thing.  Best practice for an application is to have retry logic built into it that will upon error dispose of the current connection and re-establish, which will connect to the now primary side.  And SSMS does not do that.
I've never seen or caught SSMS auto-magically re-connecting to the primary side once it's connection is established.