4

I have read only routing setup and working fine. I have a SQL login that accesses the readable secondary via the listener using ApplicationIntent=ReadOnly. However I want to prevent the user from accessing the primary.

I have seen a lot of topics on this and they all seem to suggest disabling the login on the primary. When I do this connections to the listener with ApplicationIntent=ReadOnly fail with Login failed for user ''. Reason: The account is disabled.

I have ensured the accounts are the same SID.

Thanks for any help.

  • No, I checked that. I can connect to the secondary directly with the account – Dustin Laine Mar 14 at 18:25
  • Can you please be more specific on this point, "However I want to prevent the user from accessing the primary." as it will change my answer. – Sean Gallardy Mar 14 at 18:29
  • The user should not be able to connect to the primary replica, only secondary. – Dustin Laine Mar 14 at 18:34
5

I have a SQL login that accesses the readable secondary via the listener using ApplicationIntent=ReadOnly. However I want to prevent the user from accessing the primary.

More specifically:

The user should not be able to connect to the primary replica, only secondary.

In this case, it's not possible to do what you're wanting. You can't use read only routing with this as the first step in read only routing is to connect to the primary to check if the requirements to meet read only routing are correctly used and then get the metadata from the primary to understand where the new connection should take place.

You can, however, use something such as a network load balance appliance to dynamically update a cname or A record (AAAA if IPv6) to always point to a secondary. This would be specific to the load balancing software/hardware you choose to use. You could also write your own with a trivial amount of work.

  • I think you cleared this up, but please let me confirm. Reading the information I previously found there is obscurity in if a listener is used. In my scenario I can still connect to the secondary directly using the disabled login approach, but they would not benefit from the routing features of the AG. If I want to use the listener then the login needs to exist and be enabled on both. Does this sound right? – Dustin Laine Mar 14 at 19:01
  • @DustinLaine Correct (disabled login on the primary)! – Sean Gallardy Mar 14 at 19:54

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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