[SQL][問題處理]是誰偷改登入帳號的密碼 ?
由於工作的關係，有些時候會遇到客戶的 SQL Server 主機有異常，需要協助去處理。一般來說，最讓我頭痛的不是 SQL Server 發生問題，而是遇到一些很奇葩的 MIS 人員，明明自己更改了設定卻又打死不承認。而最近遇到的一個狀況是，我們的系統會在 SQL Server 內建立一個登入者帳號，因此應用程式會使用該帳號登入資料庫去執行，但很特別的是在某個客戶端，時常發生該帳號的密碼被更改，造成系統無法登入，而客戶 MIS 又一值咬定他們沒有修改，是我們系統發生異常去更動的。因此前線的同事想找一個方法，來紀錄 SQL Server 登入帳號的變更。
基本上要來追查這個問題，就看您所使用的 SQL Server 版本，可以使用幾種不同的方法，其中最沒有限制的就是使用 SQL Trace，這個部份可以參考 MSDN 上的 sp_trace_setevent 的 Event 代號說明，可以看到透過 EventID 104 和 107 這兩組，就可以取得我們所需要的紀錄，因此我先利用 SQL Profile 匯出一個空的組態檔，再來做個修改。
1: -- Create a Queue
2: declare @rc int
3: declare @TraceID int
4: declare @maxfilesize bigint
5: set @maxfilesize = 5
6:
7: exec @rc = sp_trace_create @TraceID output, 2, N'D:\Log\ChangeLogin', @maxfilesize, NULL , 20
8: if (@rc != 0) goto error
9:
10: -- Client side File and Table cannot be scripted
11:
12: -- Set the events
13: declare @on bit
14: set @on = 1
15:
16: -- Audit AddLogin Event
17: EXEC sp_trace_setevent @TraceID, 104, 14, @on -- StartTime
18: EXEC sp_trace_setevent @TraceID, 104, 27, @on -- EventClass
19: EXEC sp_trace_setevent @TraceID, 104, 21, @on -- EventSubClass
20: EXEC sp_trace_setevent @TraceID, 104, 34, @on -- ObjectName
21: EXEC sp_trace_setevent @TraceID, 104, 38, @on -- RoleName
22: EXEC sp_trace_setevent @TraceID, 104, 39, @on -- TargetUserName
23: EXEC sp_trace_setevent @TraceID, 104, 42, @on -- TargetLoginName
24: EXEC sp_trace_setevent @TraceID, 104, 43, @on -- TargetLoginSid
25:
26: EXEC sp_trace_setevent @TraceID, 104, 7, @on -- NTDomainName
27: EXEC sp_trace_setevent @TraceID, 104, 23, @on -- Success
28: EXEC sp_trace_setevent @TraceID, 104, 8, @on -- HostName
29: EXEC sp_trace_setevent @TraceID, 104, 64, @on -- SessionLoginName
30: EXEC sp_trace_setevent @TraceID, 104, 9, @on -- ClientProcessID
31: EXEC sp_trace_setevent @TraceID, 104, 41, @on -- LoginSid
32: EXEC sp_trace_setevent @TraceID, 104, 10, @on -- ApplicationName
33: EXEC sp_trace_setevent @TraceID, 104, 26, @on -- ServerName
34:
35: EXEC sp_trace_setevent @TraceID, 104, 11, @on -- LoginName
36: EXEC sp_trace_setevent @TraceID, 104, 4, @on -- TransactionID
37: EXEC sp_trace_setevent @TraceID, 104, 12, @on -- SPID
38: EXEC sp_trace_setevent @TraceID, 104, 28, @on -- ObjectType
39: EXEC sp_trace_setevent @TraceID, 104, 6, @on -- NTUserName
40:
41: -- Audit Login Change Password Event
42: EXEC sp_trace_setevent @TraceID, 107, 14, @on -- StartTime
43: EXEC sp_trace_setevent @TraceID, 107, 27, @on -- EventClass
44: EXEC sp_trace_setevent @TraceID, 107, 21, @on -- EventSubClass
45: EXEC sp_trace_setevent @TraceID, 107, 34, @on -- ObjectName
46: EXEC sp_trace_setevent @TraceID, 107, 38, @on -- RoleName
47: EXEC sp_trace_setevent @TraceID, 107, 39, @on -- TargetUserName
48: EXEC sp_trace_setevent @TraceID, 107, 42, @on -- TargetLoginName
49: EXEC sp_trace_setevent @TraceID, 107, 43, @on -- TargetLoginSid
50:
51: EXEC sp_trace_setevent @TraceID, 107, 7, @on -- NTDomainName
52: EXEC sp_trace_setevent @TraceID, 107, 23, @on -- Success
53: EXEC sp_trace_setevent @TraceID, 107, 8, @on -- HostName
54: EXEC sp_trace_setevent @TraceID, 107, 64, @on -- SessionLoginName
55: EXEC sp_trace_setevent @TraceID, 107, 9, @on -- ClientProcessID
56: EXEC sp_trace_setevent @TraceID, 107, 41, @on -- LoginSid
57: EXEC sp_trace_setevent @TraceID, 107, 10, @on -- ApplicationName
58: EXEC sp_trace_setevent @TraceID, 107, 26, @on -- ServerName
59:
60: EXEC sp_trace_setevent @TraceID, 107, 11, @on -- LoginName
61: EXEC sp_trace_setevent @TraceID, 107, 4, @on -- TransactionID
62: EXEC sp_trace_setevent @TraceID, 107, 12, @on -- SPID
63: EXEC sp_trace_setevent @TraceID, 107, 28, @on -- ObjectType
64: EXEC sp_trace_setevent @TraceID, 107, 6, @on -- NTUserName
65:
66: -- Set the trace status to start
67: exec sp_trace_setstatus @TraceID, 1
68:
69: -- display trace id for future references
70: select TraceID=@TraceID
71: goto finish
72:
73: error:
74: select ErrorCode=@rc
75:
76: finish:
77: go
主要是在第 17 ~ 64 的設定，這裡寫的比較多一點，正常來說如果只要知道發生時間和修改人員，是可以不用到這麼多資訊，但這裡我就一倂把主要會用到的全部整裡出來，因此在每個設定後面，我都有說明是記錄哪些資訊，有需要的人可以按照你的狀況去作調整，不一定要記錄到全部。
因此當我們使用這個 SQL Trace 去執行之後，我們可以透過 SSMS 去建立帳號、修改密碼、刪除帳號，再來查看 SQL Trace 內是否有相關的資訊，因此我們使用以下的指令來查看
1: select
2: StartTime,HostName,ApplicationName, EventClass,EventSubClass,Success,TargetLoginName
3: from sys.fn_trace_gettable( 'D:\Log\ChangeLogin.trc',-1 )
4: where EventClass in ( 104, 107 )
可以看到結果如下：
可以參考 MSDN 內有篇 「Audit Login Change Password Event Class」的說明，EventClass = 104 EventSubClass=1 是建立帳號，EventClass = 107 EventSubClass=4 是密碼已重設，EventClass = 104 EventSubClass=2 是刪除帳號，因此看起來我們就可以透過 SQL Trace 來追蹤帳號的修改狀況了。但上述所提供的方式如果遇到關機，那可能就會失效了，因此這個部份可以參考另外一篇「設定長時間的 SQL Trace，避免因為關機和重新啟動造成中斷」，把這些設定寫成 Stored Procedure，再來配合「sp_procoption」讓開機就來執行，就不用擔心因為重開機導致沒有紀錄到的狀況了。
而在提供這個方式給同事的時候，有人問到說那 SQL Server 有一組 Default Trace ，是否在 Default Trace 內就有我們所要的資訊呢 ? 這個是一個蠻好玩的題目，關於這個部分我們可以用下面的指令來查看
1: SELECT DISTINCT eventid, name
2: FROM fn_trace_geteventinfo(1) a
3: JOIN sys.trace_events b ON a.eventid = b.trace_event_id
從下面的結果中可以看出來， Default Trace 並不會去紀錄所有的事件，他主要是有記錄以下的 34 個事件，其中我們上面有提到的 EventID=104 關於帳號的新增和刪除，這個在 Default Trace 內是有記錄，但是Default Trace 並沒有 EventID=107 關於密碼修改的紀錄
因此 Default trace 提供的資訊並不足夠我們來使用，因此還是要自己去作紀錄會是比較保險的做法。而就如同我們一開始所談到的，配合不同的 SQL Server 版本，除了 SQL Trace 之外，我們還可以使用 DDL Trigger 或者是 Server Audit 來做記錄，關於這兩個的做法，我們就留到下次再來做說明了。