SQL Server 登錄審計實戰：Logon Trigger + BCP 記錄登入時間，含 DAC 緊急救援指南

需求

記錄所有 SQL Server 登入帳號與時間，防範竄改 sa 密碼等安全事件。原生審計僅記錄失敗登入，需自建全量審計。

方案架構

1. Server-scoped Logon Trigger：捕獲 LOGON、ALTER_LOGIN 事件。
2. EXECUTE AS 'sa'：提權寫入文件（需啟用 xp_cmdshell）。
3. BCP 追加寫入文本日誌：ECHO login_time >> D:\Logondata.txt。

完整部署腳本

-- 1. 清理舊觸發器
USE master;
IF EXISTS (SELECT * FROM sys.server_triggers WHERE name = 'trg_logon_audit')
    DROP TRIGGER trg_logon_audit ON ALL SERVER;
GO

-- 2. 確保日誌文件存在（首次手動建立或用腳本）
-- 3. 啟用 xp_cmdshell（若未啟用）
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;
GO

-- 4. 建立觸發器
CREATE TRIGGER trg_logon_audit
ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON, ALTER_LOGIN
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @cmd NVARCHAR(4000) = N'ECHO ' 
        + ORIGINAL_LOGIN() + NCHAR(9) 
        + CONVERT(NVARCHAR(30), GETDATE(), 121) 
        + N' >> D:\Logondata.txt';

    DECLARE @tbl TABLE (out_line NVARCHAR(4000));
    INSERT @tbl EXEC master..xp_cmdshell @cmd;
END;
GO

日誌樣本

NT AUTHORITY\SYSTEM	2024-05-18 10:12:04.140
JOE\Administrator	2024-05-18 10:12:31.753
sa	                2024-05-18 10:15:39.930

兩大已知限制與對策

緊急救援：觸發器把自己鎖死怎麼辦？

若觸發器邏輯錯誤導致所有登入失敗（含 sa），啟用 專用管理員連接 (DAC)：

-- 命令列
sqlcmd -S admin:localhost -U sa -P  -Q "DROP TRIGGER trg_logon_audit ON ALL SERVER;"
-- 或 SSMS 連線時在伺服器名輸入 admin:localhost

DAC 保留專用排程器與連線配額，觸發器不攔截 DAC 連線，是最後一道防線。

生產級替代方案對比

最佳實踐清單

1. 優先啟用 SQL Server Audit 記錄 SERVER_PRINCIPAL_CHANGE_GROUP、FAILED_LOGIN_GROUP、SUCCESSFUL_LOGIN_GROUP。
2. 若必須自建 Trigger，務必在 BEGIN 後立即 SET NOCOUNT ON、TRY...CATCH 包裹 xp_cmdshell、避免觸發器內做耗時操作。
3. 日誌文件定期輪轉（如每日一檔），避免單檔過大導致寫入卡頓。
4. 嚴格管控 sa 密碼與 xp_cmdshell 權限，原則最小化。

審計不僅是合規勾選，更是事後溯源的「黑盒子」。