Is "Read only routing" the only way to force the connection with ApplicationIntent=ReadOnly to connect secondary replica? I have no problem with setup "Read-only routing".

My point is if we use "Read-only routing". We need to add the "ApplicationIntent=ReadOnly" into our application connection string. That's beyond our DBA's role.

Is there any other way to let one application such as SAS to connect to the listener and the SQL AG will point it to the secondary?

Many thanks,
Is there any other way to let one application such as SAS to connect to the listener and the SQL AG will point it to the secondary?


Unfortunately no, not within SQL Server.

You must specify ApplicationIntent=ReadOnly in the connection string in order for the connection to be eligible for read-only routing, as is documented:


  Requirements and Recommendations for Client Connection-Strings
  
  For a client application to use read-only routing, its connection string must satisfy the following requirements:
  
  
  ...
  Set the application intent attribute/property to readonly.
  ...