擷取登錄失敗警報特定於錯誤的密碼嘗試

我無法找出僅擷取那些失敗的登錄的方法，這些登錄因錯誤而失敗：

使用者 X 登錄失敗。密碼不匹配…

是的，我可以從 SQL Server 錯誤日誌和事件查看器中讀取該資訊，但是由於空間限制，我們沒有保留頻繁的錯誤日誌，因此問題出在某些伺服器上。

此外，我在這裡試圖實現的是找到試圖使用錯誤密碼連接並隨後將帳戶鎖定的罪魁禍首主機或應用程序。因此，一段時間後，當我們嘗試查看錯誤日誌消息已滿並鎖定時，但基本密碼不匹配錯誤日誌被截斷。

有沒有辦法只擷取/跟踪和保存與那些登錄的錯誤密碼嘗試相關的資訊並擷取所需的詳細資訊？

您應該能夠設置擴展事件並使用通用error_reported event或process_login_finish（我不確定process_login_finishSQL Server 2014 中是否可用）。

Ivan Stankovic 在 2014 年撰寫的Using Extended Events to review SQL Server failed logins中有一篇文章詳細介紹了幾個不同的選項。

這個錯誤：

使用者 X 登錄失敗。密碼不匹配…

是一個錯誤事件，其消息文本對應於sys.messages表中的消息 id: 18456。

select * from sys.messages where language_id=1033 and message_id=18456

現在，所有各種與事件相關的資訊都儲存在 SQL Server 的預設跟踪中，並且您的要求是僅擷取由於密碼不匹配而導致登錄失敗類型的事件，正如我之前所說，這只不過是消息 id 18456，您可以使用以下查詢獲取此資訊：

SELECT *
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), 
( SELECT TOP 1 f.[value]
 FROM    sys.fn_trace_getinfo(NULL) f
 WHERE   f.property = 2
  )), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE Error=18456 

上面的查詢將給出 18456 類型的所有登錄失敗，因此要消除重複記錄，請使用以下不同的東西：

SELECT distinct(cast(TextData as nvarchar(max))) 
FROM    sys.fn_trace_gettable(CONVERT(VARCHAR(150), 
( SELECT TOP 1 f.[value]
 FROM    sys.fn_trace_getinfo(NULL) f
 WHERE   f.property = 2
  )), DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE Error=18456  

現在您已經獲得了您想要擷取的內容，因此創建一個作業以每隔幾分鐘將此輸出保存在某個AuditDatabase的某個AuditTable中。

筆記

閱讀下面的小概述，了解為什麼保存從預設跟踪查詢的數據很重要（提示：預設跟踪不會永遠保存相同的數據）

從Feodor Georgiev收集預設跟踪中的資訊：

預設跟踪是系統定義的跟踪，由 5 個文件組成，每個文件 20 MB，位於 SQL Server 目錄中。這些文件用作記錄大量事件的循環緩衝區。此循環緩衝區中的資訊將在一段時間後刪除，因為最舊的文件在重新啟動時被刪除，或者噹噹前文件達到 20Mb 大小時。在繁忙的 OLTP 系統中，這可能只是幾分鐘的事情。收集了很多事件，在繁忙的伺服器上，5 個文件，每個 20 MB，將非常快速地滾動。

引用自：https://dba.stackexchange.com/questions/193575