SQL Server Primary Login Restrictions

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.

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.

StackExchange Database Administrators Q#232174, answer score: 5