we host an MS SQL 2019 database using a read replica and were wondering if it is possible to use applicationintent=readonly in the Linked Table Manager connection string in MS Access to force it to use the read replica.

When I tried, it still allowed me to write data using the Access connection to the SQL table, so I am not sure if I did something wrong or if it is just not possible to do this in Access.

Please see connection string below:

  • Does your replica topology include a Listener?
    – J.D.
    Nov 13 at 19:54
  • Yeah we have an AGL. I am able to use the read replica with SSMS and Jetbrain's DataGrip tool, just trying to get it to also work with MS Access. Nov 13 at 21:37

1 Answer 1


This documentation lists all the requirements Connect to an Always On availability group listener

Read-only routing refers to automatically routing incoming listener connections to a readable secondary replica that is configured to allow read-only workloads.

Connections are automatically routed to the read-only replica if the following are true:

  • At least one secondary replica is set to read-only access, and each read-only secondary replica and the primary replica are configured to support read-only routing.

  • The connection string references a database involved in the Availability Group. An alternative to this would be the login used in the connection has the database configured as its default database. For more information, see this article on how the algorithm works with read-only routing.

  • The connection string references an availability group listener, and the application intent of the incoming connection is set to read-only (for example, by using the Application Intent=ReadOnly keyword in the ODBC or OLEDB connection strings or connection attributes or properties).

Since you already specify Database and ApplicationIntent in your connection query, you only need to check for the listener and the read-only routing list.

You can check the listener with this DMV:

from sys.availability_group_listeners as agl

And the read-only routing list with this snippet

    ar.replica_server_name AS RoutingListOwner
    , arr.replica_server_name AS ReadOnlyReplica
    , arorl.routing_priority
    , arr.read_only_routing_url
    , arr.secondary_role_allow_connections_desc
FROM sys.availability_read_only_routing_lists AS arorl 
JOIN sys.availability_replicas AS ar
    ON ar.replica_id = arorl.replica_id
JOIN sys.availability_replicas AS arr
    ON arr.replica_id = arorl.read_only_replica_id
order by ar.replica_server_name, arorl.routing_priority
  • Neither of those commands seem to be possible to run in MS Access. Looking into it it seems to be due to MS Access not liking Namespaces(?) I know the AGL is working fine as we are able to use the read replica fine in SSMS, it is just when I add that applicationintent=readonly in MS Access and then save it, refresh the connection and then try and edit I am still able to edit the DB. Was hoping someone maybe had done it before themselves but if not then that is fine. Thanks for your comment though! Nov 13 at 21:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.