As you've already found out,
ApplicationIntent=READONLY; isn't a standardized keyword when it comes to connection strings. Most drivers don't support it and generally, you'd either connect to a read-only replica node explicitly or just point to the primary node and resign yourself to not using a read-only replica. There is another option though, and once in place, it will provide client connections with a read-only location that points to a node in the AG of your choosing without requiring any special connection keywords.
Create a Read-Only Listener (not an official term)
One critical component of an Availability Group is that any listener you associate with said Availability Group must connect to the primary node. The great thing though is that regardless of edition, you can have numerous Availability Groups per cluster. A Read-Only Listener is, quite simply, a new, empty Availability Group with a new Listener Name, pointed at your read-only replica.
Make sure you reserve a new IP address before you start, but with a new IP address in-hand, create a new Availability Group as you normally would using a new, blank database (which I drop after the creation of said AG). Create your read-only listener using the new IP address and failover this new listener to your read-only replica.
When this is setup, and you connect to the primary node of your user databases, you will see a secondary Availability Group listed as (Secondary):
What the image above shows is our AG that hosts user databases is named
SQL-P02 (which is the server SSMS is actively connected to in the screenshot). This is the connection I use when I want to connect to my primary node and perform read/write operations. The second AG
SQL-P02-RO is the new AG that I setup. There are no databases associated with this AG, but I did create a new Listener and when
SQL-P02-RO's primary replica is the same as the read-only replica in the
SQL-P02 AG, it functionally becomes a Read-Only Listener. So basically, for any client that I want to point at the read-only replica, I specify
SQL-P02-RO as my server rather than having to find a driver that uses the
Yes, this is a bit clunky, but the benefit here is that you can point any connections you wish to deem "read-only" to any replica in your AG. Most of the time you would choose your read-only replica as the target, but in the event of patching, a failover, etc. this could just as easily be pointed to your primary so you can minimize downtime for your read-only connections as well.
I use this approach across a number of servers currently with much fanfare. For whatever reason, our company is littered with apps that use the ancient jTDS drivers which most definitely do NOT support the
ApplicationIntent keyword, but we needed to specify read-only connections and manually adjusting connection strings every time a failover occurred was just not a feasible approach.
If you have questions about this, leave a comment and I'll do my best to answer, but hopefully, this helps demonstrate you can specify read-only connections for an AG, albeit in a way that's not exactly as straightforward as using the
ApplicationIntent keyword in a connection string.