Sql-server – Prevent read access on primary AG replica, but allow it on the readable secondary

availability-groupsread-only-databasesql serversql-server-2016

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.

Best Answer

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).

Related Question