Here is the setup: on SQL server 2022, we have a contained availability group with 4 replicas: 3 synchronous, 1 asynchronous, and a listener. We created a logon trigger to limit access to a specific login from only one IP address. The trigger works well on the listener and primary replica. But when I try to connect to other replicas (synchronous and asynchronous), it does not work at all. Do you have any ideas why this is happening? (The login will use the asynchronous replica to read)
I got IP address and login name from these queries:
SELECT ORIGINAL_LOGIN();
<code>SELECT client_net_address FROM sys.dm_exec_connectionsWHERE session_id = @@SPID
</code>
<code>SELECT client_net_address FROM sys.dm_exec_connectionsWHERE session_id = @@SPID
</code>
SELECT client_net_address FROM sys.dm_exec_connectionsWHERE session_id = @@SPID