Logon Trigger On HA

Question

Here is the setup: on MSSQL-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 (got trigger error). But when I try to connect to secondary replicas (synchronous and asynchronous), it does not work at all. (I can connect to asynchronous replica with that login from any IP) Do you have any ideas why this is happening? (The login will use the asynchronous replica to read) Here is the code:

CREATE TRIGGER [trg_LimitLogin]
ON ALL SERVER
WITH EXECUTE AS 'login with enough permission on server'
FOR LOGON
AS
BEGIN
    DECLARE @LoginName NVARCHAR(255)
    DECLARE @ClientIP NVARCHAR(255)
    -- Get the login name of the user attempting to connect
    SET @LoginName = ORIGINAL_LOGIN()
    -- Get the IP address of the client
    SET @ClientIP = (SELECT top 1 client_net_address
                     FROM sys.dm_exec_connections
                     WHERE session_id = @@SPID)
    -- Restrict the login for the user 'usr_tutunchian'
    IF  @LoginName NOT LIKE '%$'
       AND (@LoginName = 'That login'
AND @ClientIP != '1.1.1.1 (for posting code)')
BEGIN
        ROLLBACK
    END
    ELSE
    BEGIN
     RETURN
     END
END

score 0 · Answer 1 · answered Dec 10 '24 at 09:08

Do you have any ideas why this is happening?

have you deployed your logon trigger on 4 SQL Server instance (4 replicas)? You may want to check your 4 SQL Server instances as logon trigger is server scope. Make sure that the logon trigger is created and enabled on all 4 SQL Server instances.

I would not recommend using a logon trigger in a production environment with heavy load, as it can be difficult to pinpoint issues. I have experienced performance degradation when deploying a logon trigger for one user.

I suggest blocking the IP address at the network level. Check with your sysadmin or network admin to block the IP instead.

Logon Trigger On HA

1 Answers1