alwayson Readonly environment SQL Login routing secondary while preventing access primary

Question

we have SQL Server 2016 R3 , always on two nodes, read only routing is configured. We need to have SQL login and test connection to re routing to secondary site using sqlcmd and looks working. so question here is we need to prevent accidental connection to primary and allowing access to secondary. I deny access to SQL login then stop access secondary as well.

connect primary

USE [master]

GO

DENY CONNECT SQL TO [SQLloginusr]

GO

sqlcmd -U SQLloginusr -P SQLloginusr -S groupaglistener1 -d proddb -K ReadOnly

Sqlcmd: Error: Microsoft ODBC Driver 13 for SQL Server : Login failed for user 'SQLloginusr'..

all working when enable the user from primary site.

can any one advice how to achieve this please

Answer 1

Hello

After creating the login and granting the permissions you could disable the login in the primary

ALTER LOGIN [SQLloginusr] DISABLE

GO

Regards

Javier

Answer 2

One option to explore is to have a logon trigger. But I don't know if the logon trigger fires on the primary when you connect with Application Intent=ReadOnly, so you would have to test. Then again, maybe eventdata() includes information about application intent.

(I have a lab AG here at home, but the read-only routing seems to be broken at the moment.)