Sample Header Ad - 728x90
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
Asked by Mandana (1 rep)
Dec 10, 2024, 06:58 AM
Last activity: Dec 10, 2024, 09:08 AM