[SQL] DAC 應用

練習 LOGON Trigger 時搞烏龍，害自己沒有辦法登錄 SQL Server Instance，最後是利用 DAC 連進去刪除 LOGON Trigger 才搞定，這篇紀錄處理過程。

• 建立一個 LOGON Trigger 讓自己進不去 SQL Server Instance

CREATE TRIGGER Connection_Limit_Trigger
ON ALL SERVER
FOR LOGON
AS
    ROLLBACK -- 不管三七二十一，就是不准進來

• 故意連進去看看

• 使用 sqlcmd 來進行 DAC 連線，並刪除 LOGON Trigger

• 再次利用 SSMS 連進 SQL Server Instance，從 2 可以看見連進 WIN7-JengTing\SQL2012 內， 從 3 可以發現觸發程序內已經沒有 Connection_Limit_Trigger LOGON Trigger 了。

延伸閱讀
• [SQL] Logon Triggers
• [SQL] Dedicated Administrator Connection（DAC）

參考資料
• SQL Server: Disable Logon Trigger Using DAC to Resolve Login Problem
• SQL SERVER – sqlcmd – Using a Dedicated Administrator Connection to Kill Currently Running Query
• SQL Server: Disable Logon Trigger Using DAC to Resolve Login Problem

[SQL] Dedicated Administrator Connection（DAC）

SQL Server 正常（標準）連線失效時，可以利用 DAC 進行連線來執行診斷查詢和排解疑難問題。

各版本的 DAC 預設是開啟，但 Express 為節省資源，除非追蹤旗標 7806 啟動，否則無法使用 DAC。

• Express 未開啟追蹤旗標 7806 時，SSMS 要使用 DAC 連線時的錯誤訊息。

• SQL Server Configuration Manager => SQL Server 服務 => SQL Server instance 右鍵內容 => 啟動參數 Tag => 加入啟動參數 '-T 7806'。

• 設定追蹤旗標後必須重新啟動，才會產生作用。

[SQL] Logon Trigger

Logon Trigger 是透過 LOGON 事件觸發，可以追蹤登入活動或限制登入 SQL Server。

Logon Trigger 語法基本上跟 DDL Trigger 是一樣的，但 Logon Trigger 只有一個觸發事件，就是 LOGON 事件，同樣地擷取 Logon Trigger 的相關資訊，也必須使用 EVENTDATA()，但其 XML 內容有些許差異而已。

EVENTDATA() 函數

• ClientHost：來源用戶端之主機名稱。如果用戶端和伺服器名稱相同，這個值會是'<local_machine>'。 否則會是用戶端的 IP 位址。

限制登入 SQL Server
• 禁止 Client 端利用 SSMS 連入 SQL Server。
• 禁止特定帳戶（ServerName\LogonTest）、特定 IP （192.168.0.20）或特定時間連入（2000 之後） SQL Server。

CREATE TRIGGER Connection_Limit_Trigger
ON ALL SERVER 
FOR LOGON
AS
    BEGIN
      -- 禁止 SSMS 連入
      IF APP_NAME() LIKE '%Microsoft SQL Server Management Studio%'
          ROLLBACK
        
      -- 禁止特定帳號或特定 IP 連入
      DECLARE @data xml
      DECLARE @ClientHost varchar(50)
      DECLARE @LoginName varchar(50)
      DECLARE @PostTime datetime

      SET @data = EVENTDATA()
      SET @ClientHost = @data.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
      SET @LoginName = @data.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
      SET @PostTime = @data.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')

      -- 特定 IP
      IF @ClientHost = '192.168.0.20'
          ROLLBACK

      -- 特定帳號
      IF @LoginName = 'ServerName\LogonTest'
          ROLLBACK

      -- 特定時間
      IF DATEPART(hh,@PostTime) >= 20
          ROLLBACK

    END

• 2013 DBA 天團試題

延伸閱讀
• [SQL] DDLTrigger

MS 參考資料
• 登入觸發程序 => 內有範例：禁止單一帳戶建立三個以上的 session
• DDL Triggers and LOGON Triggers
• APP_NAME()

參考資料
• 停用(disable) IntelliSense；以使用「登入觸發程序(Logon Trigger)」，或是設定 SSMS 管理工具為例
• Logon trigger in SQL Server
• SQL SERVER – Interesting Observation of Logon Trigger On All Servers
• SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution
• SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’.
• Super SQL Server 討論

[SQL] DDL Trigger

DDL Trigger 可以用來追蹤與監控資料庫結構的變化，EX：紀錄誰何時對 Table schema 進行變化、新增登入者等，DDL 事件會觸發 DDL Trigger，DDL 事件則是指 CREATE、ALTER、DROP、GRANT、DENY、REVOKE 或 UPDATE STATISTICS 關鍵字開頭的 T-SQL 語法。

語法

CREATE TRIGGER trigger_name     -- DDL Trigger 名稱 
ON  { ALL SERVER | DATABASE }   -- 指定伺服器或資料庫層級
    [ WITH 
          ENCRYPTION ,          -- 是否加密
          EXECUTE AS User       -- 使用某 User 的權限來執行 DDL Trigger
    ]
FOR { event_type | event_group }-- 指定觸發的 DDL 事件或 DDL 群組事件
AS
    BEGIN

        -- 執行的 T-SQL 語法

    END

EVENTDATA()函數

DDL Trigger 執行時，使用 EVENTDATA() 擷取 DDL 相關資訊，回傳值為 XML，可以利用 XQuery 來解析其內容，內容可以儲存在 Table 中供後續分析追蹤或利用 DataBase Mail 發信通知相關人員。

• LoginName：登入 SQL Server 來執行 DDL 語法的帳號
• ObjectName：對哪個帳號進行加入、修改或刪除動，以上述圖型為例，是要新增 DDLTest 帳號