SQL Server 登入觸發器實作 IP 限制與登入稽核五大場景

登入觸發器優勢

SQL Server 無類似 MySQL mysql.user.Host 的 IP 白名單機制，登入觸發器 (LOGON TRIGGER) 可在驗證通過、會話建立前攔截，實現精細存取控制。

五大實戰場景

1. 限制特定登入 (sa) 僅允許本機/指定 IP。
2. 限制伺服器角色 (sysadmin) 登入來源。
3. 時間窗口控制：如僅允許上班時間登入。
4. 多對多 登入-IP 映射表驅動：維護白名單表，觸發器查表判斷。
5. CIDR 網段比對：支援 192.168.1.* 格式 (需自行解析)。

核心函數

• ORIGINAL_LOGIN()：取得原始登入名稱 (不受 EXECUTE AS 影響)。
• EVENTDATA()：回傳 XML，.value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)') 取得客戶端 IP。
• ROLLBACK：終止登入，回傳錯誤 17892「登入失敗，觸發器回滾」。

範例：限制 test 帳號僅允許本機、192.168.1.50、192.168.1.120

CREATE TRIGGER tr_connection_limit ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON AS
BEGIN
    IF ORIGINAL_LOGIN() = 'test'
       AND EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]', 'NVARCHAR(15)') 
           NOT IN ('', '192.168.1.50', '192.168.1.120')
        ROLLBACK;
END;

驗證查詢

SELECT s.session_id, s.login_time, s.host_name, s.original_login_name, c.client_net_address
FROM sys.dm_exec_sessions s
JOIN sys.dm_exec_connections c ON s.session_id = c.session_id;

風險與防護

• 觸發器錯誤會導致所有登入失敗 (含 sa)，必須在觸發器內 TRY...CATCH 並寫入錯誤日誌。
• 建立專用 DAC 連線腳本備份，緊急時透過 DAC 停用觸發器：DISABLE TRIGGER tr_connection_limit ON ALL SERVER;