We have an Always On availability group with a primary and a read-enabled secondary. We have a user for the implementation team who uses the database to check the correctness of the data that they are intending to put in the database.
The users only have rights to read from the database but when they connect (through SSMS) through the AG Listener they always connect to the active node.
I've tried to get them to access the read only instance directly but they're stuck in their ways and after a day or two they're all back on the active node again.
Is there a way for SQL Server to say this user is always going to be with intent readonly and redirect them there?
NOTE: I've tried setting 'ApplicationIntent = ReadOnly' in the additional connection parameters but this doesn't seem to redirect to the secondary node, and isn't the ideal solution as they will inevitably forget to set it up for new starters.
SQL Server 2012 Enterprise, Availability group 1 primary, 1 readable secondary with synchronous commit.
I'm not intending for a user to connect to a linked server, or through any other server. The users connect directly to the database through SSMS (no other application) and I would like the AG Listener (or something around there) to be able to direct that user to a secondary node if one is available (as it only has read access there is no point in it accessing primary) without the user having to do anything, since they move around machines and will forget to add the application intent. Also I find adding that to the additional connection parameters does not always direct you to the secondary node.