Today while poking around the AlwaysOn Availability Groups feature I came across the above error when testing the read-only access routing configuration. That configuration allows connections with declared ReadOnly intent to be routed to one of the read-only secondary replicas.
The configuration looked like this:
I was testing connectivity from Management Studio to open a new Database Engine Query window (not the Object Explorer) and was using the following options:
However when I tried to USE any of the databases in the AG, it would disallow me. When I checked @@SERVERNAME it actually reported the instance name of the PRIMARY replica! So clearly the routing was not working.
Then it dawned on me that the default database for this login being master, it was causing an issue with the routing. On changing the ‘Connect to database’ to XYZ (the name of the AG database) it was routing me correctly to the read-only secondary.
Of course, this is only ONE reason for the above error. Other possibilities include the fact that your AG configuration is incorrect and does not allow read-only secondary in any case.
Hope this helps you!