Prevent read access on primary AG replica, but allow it on the readable secondary

Question

We have a unique situation where we want to allow users to query a readable secondary replica of a database with SSMS for adhoc reporting, but not allow them to ever read from the primary replica. We have setup read-only routing to accomplish this. This is also all on SQL 2016.

My initial thought was to create the login on both the primary and secondary replicas and grant read access to the database in question. Then we'd DENY CONNECT or disable the login on the current primary replica. In SSMS, the users could then connect to the Listener with ApplicationIntent=ReadOnly and be routed to the secondary replica without ever touching the primary.

We'd setup a simple job on both the primary and secondary replica servers with basic logic: IF current server = primary then disable login; if current server = secondary then enable login.

The problem is that I'm getting login failures when connecting to the listener with readonly intent when the login is disabled on the primary server. When I re-enable the login on the primary replica, it works just fine and the connection is properly routed to the readable secondary.

I setup a trace on the primary server and sure enough, I can see the login connect and run some system type queries in both master and msdb on the primary replica - even though I'm connecting with ApplicationIntent=ReadOnly in SSMS. I'm not sure if this is something that SSMS does behind the scenes or if it is the default behavior of a login going through the read-only routing process.

Here are the queries I captured with the quick profiler trace on the primary:

--msdb query

    select 
        case 
            when object_id('dbo.sysdac_instances') is not null then 1             
        else
            0 
        end

--master query

SELECT
dtb.name AS [Name],
dtb.database_id AS [ID],
CAST(has_dbaccess(dtb.name) AS bit) AS [IsAccessible]
FROM
master.sys.databases AS dtb
ORDER BY
[Name] ASC

Has anyone had to deal with this situation before? It seems we basically need to allow a login connect permission on the primary replica while denying it read access to the database in the AG on the primary, but give that login permission to read the database on the readable secondary replica.

The other alternative is to create a DNS entry that points directly to the secondary replica, but we can't guarantee that replica will ALWAYS be the secondary as a failover could happen.

Answer 1

It seems we basically need to allow a login connect permission on the primary replica [...]

That's exactly right. The login being used to connect has to be able to connect to the primary.

When you using ApplicationIntent=ReadOnly in a connection string that's pointed at an AG listener, the driver initially connects to the primary instance. This is so that SQL Server can review the list of replicas and see if there are any available, online readable replicas to send you to. This is documented here:

Read-Only Application Intent and Read-Only Routing

The primary database of the availability group processes the incoming read-only routing request and attempts to locate an online, read-only replica that is joined to the primary replica and is configured for read-only routing. The client receives back connection information from the primary replica server and connects to the identified read-only replica.

There's also a good summary of the process laid out by Top Microsoft Man Sean Gallardy on the PFE blog here:

Finding Which Connections Have Been Read Only Routed

1. We create a connection through the driver
2. The driver connects to SQL Server through the listener and passes the relevant information
3. SQL Server notices that we have read only routing setup and checks the configuration
4. SQL server reports back to the driver that we have a read only secondary that can be used
5. SQL Server sends the new secondary information to the driver
6. The driver creates a new connection to the secondary with the given information

Regarding the other item you noted in the question:

[...] while denying it read access to the database in the AG on the primary, but give that login permission to read the database on the readable secondary replica.

Database level permissions are going to be replicated across the AG - so if you remove the user's read-access on the primary, they will lose read-access on the secondary as well.

I'm a little confused about the goal of such an endeavor, since the data should, in theory, be the same (or close to the same). Maybe you just don't want certain logins running queries on the primary at all for performance reasons. Expressing Read-Only intent and connecting to the listener is really the most straightforward approach to solving this.

Other options would involve separate hardware, or custom coding in your app that deals with connecting directly to the right replica (and retry logic if a failover has occurred, and scheduled jobs on each replica to disable / enable the logins in the event of a failover, etc).

Answer 2

A couple of approaches you can try.

1. Connect the users directly to the secondary without using the AG Listener. A fancy variant of this uses an additional cluster IP endpoint with preferred owner on the secondary node. This would allow the IP endpoint to fail over to the primary if the secondary was down.

2. Do not grant database-level SELECT permissions the read-only users, and grant the following server-level permissions on the secondary: CONNECT ANY DATABASE and SELECT ALL USER SECURABLES.

Answer 3

I know this is a bit of an old thread, but we actually have exactly the same requirements - Allow users to connect with read only intent and prevent them from falling back to the primary.

The reason is simple - Our databases are complex with lots of little queries constantly running against them from our production systems and our users are typically running heavy report queries requiring a lot of joins and subsequent locks. We cannot, under any circumstances, have users run these reports against the primary replicas due to the impact on production systems. It is admittedly extremely unlikely for them to fallback to the primary, but it is possible for it to occur and that is what we wish to prevent.

It is also possible for users to avoid the listener and connect directly to the primary replica, which we also obviously need to prevent.

I think for now the best solution is to enable RCSI on the databases (we already pay the cost for row versioning due to the synchronous secondary), so if a fallback from a reporting query does hit the primary, it at least won't block or deadlock against production queries. It's not ideal though, as we still have the resource cost (temp db, memory, cpu, etc).

If anyone has any other ideas, I'd love to hear them.

Answer 4

One suggestion is your plan as outlined but combined with the Resource Governor to keep some of the other resource costs under control.

Alternatively, suppose it is not too disruptive to the application. In that case, you could use a login trigger to prevent the report from running or have a job that runs frequently that kills the SPID associated with the report (you could alleviate your boss by making the job threshold-based).

Also, a login trigger would work but be conscious of the risk of locking yourself out of the primary. Review thoroughly and test, test, test, so you don't find yourself trying to remember how to startup in minimal mode as an administrator at 3 AM.

We geeked out a little brainstorming over here, and there's no way to avoid configuration changes running on the primary that we know of.

I would also consider stepping outside of SQL server for this one and looking at application load balancers. F5 if there is a budget, HAProxy has worked fine for me in the past using the open-source community edition.

Answer 5

We have this same requirement. I'm curious if the use of an on logon trigger would work?

Create a way to identify which uses are "Read-Only" users: Grant them a specific role for "Read-Only"

During the on-logon event if the user is in this table and you're on the primary replica rollback.

As above test very well before trying in production.