By Dustin Laine


2019-03-14 18:18:42 8 Comments

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.

1 comments

@Sean Gallardy 2019-03-14 18:39:24

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.

@Dustin Laine 2019-03-14 19:01:22

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?

@Sean Gallardy 2019-03-14 19:54:16

@DustinLaine Correct (disabled login on the primary)!

Related Questions

Sponsored Content

4 Answered Questions

[SOLVED] AlwaysON - Force users connection to use Readable Secondary Nodes

2 Answered Questions

[SOLVED] Load balancing reads SQL Server 2016 AG

1 Answered Questions

1 Answered Questions

[SOLVED] How to let particular logins to work on the secondary replica only?

1 Answered Questions

0 Answered Questions

Connection to secondary DB in AlwaysOn High Availability

2 Answered Questions

1 Answered Questions

[SOLVED] Availability Group: ReadIntent connection failing

Sponsored Content