If you want to access a secondary replica in an Always On configuration, the Readable Secondary property must be set to "Read-Intent" or "Yes".
If you set "Read-Intent" then your connection string must specify ApplicationIntent=ReadOnly
otherwise the connection is rejected. If you set "Yes", your connection is allowed without ApplicationIntent=ReadOnly
, however, any non-read queries you run will raise an error.
If you set "No" then all connections to the secondary will return an error. NOTE: That these conditions only apply when specifying an AG-joined database in the connection string. If you connect to a system database or non-AG database then you can connect to a secondary without issue, but you can't access the AG-joined databases. See here for more info.
Lastly, when connecting you have two options. Firstly, you can simply connect to the secondary replica via the normal name for that replica. Secondly, you can connect via the listener name.
When connecting via listener name, if you have configured read-only routing and your connection string specifies ApplicationIntent=ReadOnly
then you will be routed to a readable secondary for your connection. If you do not specify ApplicationIntent=ReadOnly
or you have not configured read-only routing, then you will connect to the primary replica. See here for more info.