We are configuring an availability group with a readable secondary replica. A login can connect to the secondary replica using "ApplicationIntent=ReadOnly" in its connection string. But we came across a problem that could make our headache in future.
If the secondary replica is unavailable due to any causes when the login establishes connection, this login will be directed to the primary replica whatever it's using "ApplicationIntent=ReadOnly" or not.
So, my question - is there any way to ban particular logins from connecting to the primary replica if they configured for working with the secondary one?
I mean if the primary replica isn't able to direct the connection to the secondary one the connection should be closed instead of going on working on the primary replica.
Microsoft SQL Server 2016 (SP2-CU1) (KB4135048) - 13.0.5149.0 (X64) May 19 2018 09:41:57 Copyright (c) Microsoft Corporation Enterprise Edition: Core-based Licensing (64-bit) on Windows Server 2016 Standard 10.0 (Build 14393: )