Logon Trigger On HA
0
votes
1
answer
68
views
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
Last activity: Dec 10, 2024, 09:08 AM