利用 SQL Server Logon Triggers 限制資料庫連線條件
問題內容：
可否針對一個存在於「安全性 > 登入」的帳號，限制使其不能使用非 DB 主機的 SQL Server Management Studio 工具來連接此 SQL Server。對於這樣的問題，印象中似乎沒有辦法透過任何已提供的 UI 介面工具去設定，不過可以寫個 登入觸發程序 (Logon Triggers) 完成這樣的需求。
首先，來看 MSDN Libaray 對於登入觸發程序的說明：
登入觸發程序會引發預存程序來回應 LOGON 事件。當 SQL Server 執行個體建立使用者工作階段時，就會引發這個事件。登入觸發程序會在登入驗證階段結束之後，但在使用者工作階段實際建立之前引發。因此，從觸發程序內產生且一般會顯示給使用者的所有訊息，例如錯誤訊息和來自 PRINT 陳述式的訊息，都會轉至 SQL Server 錯誤記錄檔。如果驗證失敗，登入觸發程序就不會引發。 您可以使用登入觸發程序稽核和控制伺服器工作階段，例如追蹤登入活動、限制登入 SQL Server，或限制特定登入的工作階段數。這段說明應該很明白了吧？ 當連線到資料庫時會歷經：登入驗證 > 引發登入觸發程序 > 建立使用者工作階段 三個步驟，也因為這樣的先後順序，我們才有可能限制登入(或其他額外的處理)。此外，範例程式碼還揭露了一個關鍵點：sys.dm_exec_sessions 動態管理檢視，可用來取得已驗證的工作階段相關資訊，包括用戶端版本、用戶端程式名稱、用戶端登入時間、登入使用者、目前工作階段設定等…。直接下查詢來看看：
FROM sys.dm_exec_sessions
WHERE is_user_process = 1結果如下圖：
有 host_name、progra_name、login_name…資訊多到足夠我們做很多不同的篩選了 (關於各個欄位值的意義，請參閱 sys.dm_exec_sessions 動態管理檢視)。
現在來模擬操作環境，我的目標資料庫主機名稱是 XP64，現在要建立一個 Logon Trigger，使得 XP64 本機可以透過 SSMS 用 sa 帳號登入以外，其他電腦若要透過 SSMS (用 sa 帳號)來登入都會被拒絕。指令碼如下：
GO
-- 建立登入觸發程序，以回應 LOGON 事件。
CREATE TRIGGER deny_remote_ssms_trigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'sa' AND
(
SELECT COUNT(*)
FROM sys.dm_exec_sessions
WHERE is_user_process = 1
AND original_login_name = 'sa'
AND [host_name] != 'XP64'
AND [program_name] LIKE N'Microsoft SQL Server Management Studio%' -- 只限制由 SSMS 連過來
) > 0
ROLLBACK;
END;
GO執行完以上程式碼後，再度開啟 SSMS 試圖連接到 XP64，會跳出以下錯誤畫面：
並且會明確告知是因為觸發程序造成連線失敗。
本篇文章在此做個前導，分享給大家 (我個人覺得還蠻有實用價值的)，除了在重要資料庫限制內部成員透過 SSMS 擅自連線作業(*註)以外，目前資安事故頻傳，也許 Logon Triggers 也可以用在對於不同的登入連線作一些監控的動作，您覺得呢？如果您有不同意見或建議，歡迎您不吝提供給我，互相交流一下喔！
範例 T-SQL 檔：Logon_Trigger：Deny_Remote_Connection_via_SSMS.rar
*備註：
圖形化界面工具雖然好用，但這僅限於開發環境，在實際上線的資料庫環境當中，最好避免使用 Enterprise Manager 或 SSMS (SQL Server Management Studio) 這類的圖形化介面工具來存取資料庫，特別是增(INSERT)、刪(DELETE)、改(UPDATE)等異動，理由是這些工具在多數情況下使用了不必要的活動來達成變更 -- 例如：建立新資料表、複製資料、捨棄原資料表、更名新資料表(為原資料表名稱)。試想這樣的動作在正式環境的大型資料表執行 (例如只是新增一個欄位，或修改資料型別…)，會造成多大的負擔！因此限制 SSMS 擅自連線到正式資料庫，絕對有其必要。
參考資料：