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.