前言
在前一篇文章中,介紹了伺服器稽核新增強的功能、稽核記錄失敗時的處理、設定最大換用檔案與最大檔案數目、認識稽核檔案的存取權、將SQL Server Audit的目標記錄檔案寫入到網路共用資料夾等主題。
在本次文章中,將繼續討論調整稽核記錄失敗時的處理、調整稽核檔案數目上限、認識對稽核記錄檔案的篩選以及認識使用者定義稽核群組等主題。
任務四:調整稽核記錄失敗時的處理
選擇:「繼續」模式
步驟 1. 在擔任網路共用伺服器上,關閉先前分享的資料夾。
步驟 2. 回到資料庫伺服器上,使用「物件總管」,建立、修改與刪除資料庫,或是執行先前的範例程式碼3。
因為事先關閉了網路分享資料夾,這將導致觸發的事件無法寫入到稽核記錄檔案內。但先前在建立「伺服器稽核」時,在「於稽核記錄失敗時」區域,預設所組態的是「繼續」。因此,系統仍是可以正常執行,但是在Windows事件檢視器上將可以觀察到以下的錯誤訊息,請參考下圖所示:
-- Windows事件檢視器的應用程式記錄
SQL Server Audit 無法寫入檔案 '\\OP1\Audit_Logs\稽核資料庫的異動_UNC_F378D9C3-4CD7-41E9-B37C-D483C62935FA.sqlaudit'。

圖10:檢視Windows事件檢視器的應用程式記錄,事件識別碼:33202
在SQL Server錯誤記錄檔上將可以觀察到以下的錯誤訊息,請參考下圖所示:
-- SQL Server錯誤記錄檔
訊息
Error: 33202, Severity: 17, State: 1.
訊息
SQL Server Audit could not write to file '\\OP1\Audit_Logs\稽核資料庫的異動_UNC_F378D9C3-4CD7-41E9-B37C-D483C62935FA.sqlaudit'.

圖11:檢視SQL Server錯誤記錄檔,錯誤識別碼:33202
或是,執行以下的範例程式碼來查詢「伺服器稽核」的運行狀態,並請參考下圖5所示:
-- 01_使用sys.dm_server_audit_status動態管理檢視,檢視「伺服器稽核」SQL Server Audit的狀態
SELECT audit_id N'稽核的識別碼', name N'稽核的名稱', status_desc N'伺服器稽核狀態',
status_time N'上一次狀態變更的時間戳記', audit_file_size N'稽核檔案大小(KB)',
audit_file_path N'稽核檔案目標的完整路徑名稱'
FROM sys.dm_server_audit_status;
GO
範例程式碼5:使用fn_get_audit_file檢視稽核檔案的內容

圖12:伺服器稽核的狀態是:RUNTIIME_FAILED
選擇:「失敗作業」模式
步驟 3. 在擔任網路共用伺服器上,再度分享先前的資料夾。
步驟 4. 回到資料庫伺服器上,使用「物件總管」,在「伺服器稽核」物件:稽核資料庫的異動_UNC上,先停用此「伺服器稽核」,在「於稽核記錄失敗」區域,設定為「失敗作業」,再重新啟用此「伺服器稽核」。
或是,執行以下的範例程式碼:
-- 01_停用此「伺服器稽核」:稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = OFF);
GO
-- 02_在「於稽核記錄失敗」參數,設定為「失敗作業」
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH( ON_FAILURE = FAIL_OPERATION)
GO
-- 03_啟用此「伺服器稽核」:稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = ON);
GO
範例程式碼6:變更伺服器稽核的屬性為:失敗作業
若是沒有先停用此「伺服器稽核」,就試圖去變更其屬性,將遭遇到以下的錯誤訊息,請參考下圖所示:
訊息 33071,層級 16,狀態 1,行 2
這個命令要求必須停用 稽核。請停用 稽核 後,再重新執行這個命令。

圖13:尚未停用伺服器稽核,是無法變更伺服器稽核的屬性
在本次實作練習中,若是沒有先啟用遠端的網路分享資料夾功能,導致無法存取稽核目標檔案,將遭遇到以下的錯誤訊息,請參考下圖所示:
訊息 33222,層級 16,狀態 1,行 1
稽核 '稽核資料庫的異動_UNC' 無法進行 start。
如需詳細資訊,請參閱 SQL Server 錯誤記錄檔。
您還可以查詢 sys.dm_os_ring_buffers,其中 ring_buffer_type = 'RING_BUFFER_XE_LOG'。

圖14:若是無法存取稽核目標檔案,也是無法啟用此伺服器稽核,錯誤識別碼:33222
在SQL Server錯誤記錄檔上將可以觀察到以下的錯誤訊息,請參考下圖所示:
錯誤: 33206,嚴重性: 17,狀態: 1。
SQL Server Audit failed to create the audit file '\\OP1\Audit_Logs\稽核資料庫的異動_UNC_F0F90B89-9EFC-4593-A07F-6B6FA9CD65BE.sqlaudit'.
Make sure that the disk is not full and that the SQL service account has the required permissions to create and write to the file.

圖15:檢視SQL Server 錯誤記錄檔,無法建立稽核目標檔案,錯誤識別碼:33206
步驟 5. 使用「物件總管」,建立、修改與刪除資料庫,或是,執行先前的範例程式碼3。
步驟 6. 使用「物件總管」,檢視稽核記錄,或是,執行範例程式碼4來檢視稽核記錄。
步驟 7. 在擔任網路共用伺服器上,關閉先前分享的資料夾。
步驟 8. 回到資料庫伺服器上,使用「物件總管」,建立、修改與刪除資料庫,或是執行先前的範例程式碼3。
因為事先關閉了網路分享資料夾,這將導致觸發的事件無法寫入到稽核記錄檔案內。但先前在建立「伺服器稽核」時,在「於稽核記錄失敗時」區域,預設所組態的是「失敗作業」。因此,這將導致建立、修改與刪除資料庫等作業都會失敗,無法執行。
訊息 15247,層級 16,狀態 15,行 1
使用者沒有執行此動作的權限。

圖16:建立資料庫失敗,使用者沒有執行此動作的權限
在SQL Server錯誤記錄檔上將可以觀察到以下的錯誤訊息,請參考下圖所示:
錯誤: 33239,嚴重性: 16,狀態: 1。
An error occurred while auditing this operation. Fix the error in the audit and then retry this operation.

圖17:SQL Server錯誤記錄檔:無法存取稽核目標檔案,導致「失敗作業」,錯誤識別碼:33239
Windows事件檢視器上將可以觀察到以下的錯誤訊息,請參考下圖所示:
稽核此作業時發生錯誤。請修正核此中的錯誤,然後重試此作業。

圖18:Windows事件檢視器:無法存取稽核目標檔案,導致「失敗作業」,錯誤識別碼:33239
選擇:「關閉伺服器」模式
步驟 9. 在擔任網路共用伺服器上,再度分享先前的資料夾。
步驟10. 回到資料庫伺服器上,使用「物件總管」,在「伺服器稽核」:稽核資料庫的異動_UNC上,先停用此「伺服器稽核」,在「於稽核記錄失敗」區域,設定為「關閉伺服器」,再重新啟用此「伺服器稽核」。
或是,執行以下的範例程式碼:
-- 01_停用此「伺服器稽核」:稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = OFF);
GO
-- 02_在「於稽核記錄失敗」參數,設定為「關閉伺服器」
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH( ON_FAILURE = SHUTDOWN)
GO
-- 03_啟用此「伺服器稽核」:稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = ON);
GO
範例程式碼7:變更伺服器稽核的屬性為:關閉伺服器
步驟11. 使用「物件總管」,建立、修改與刪除資料庫,或是執行先前的範例程式碼3。
步驟12. 使用「物件總管」,檢視稽核記錄,或是,執行範例程式碼4來檢視稽核記錄。
步驟13. 在擔任網路共用伺服器上,關閉先前分享的資料夾。
步驟14. 回到資料庫伺服器上,使用「物件總管」,建立、修改與刪除資料庫,或是執行先前的範例程式碼3。
因為事先關閉了網路分享資料夾,這將導致觸發的事件無法寫入到稽核記錄檔案內。但先前在建立「伺服器稽核」時,在「於稽核記錄失敗時」區域,預設所組態的是「關閉伺服器」。因此,這將導致此執行個體自動關機,停止服務。在Windows事件檢視器上將可以觀察到以下的錯誤訊息,請參考下圖所示:
事件識別碼:33219
伺服器已停止,因為 SQL Server Audit '稽核資料庫的異動_UNC' 設定為於失敗時關閉。
如果要對此問題進行疑難排解,請在伺服器啟動時使用 -m 旗標 (單一使用者模式),以略過稽核所產生的關機。
--
事件識別碼:19019
MSSQLSERVER 服務非預期地結束.

圖19:Windows事件檢視器:SQL Server執行個體已經被非預期地結束
在圖19中,此執行個體已經被強迫關機,停止服務,你之後也無法啟動此執行個體。若沒有修復讓其能夠存取稽核目標檔案,那就只能使用-m旗標(單一使用者模式)方式來啟動執行個體,修改「伺服器稽核」的屬性。
步驟15. 在擔任網路共用伺服器上,再度分享先前的資料夾。
步驟16. 重新啟動此執行個體。
步驟17. 檢視SQL Server錯誤記錄檔將可以觀察到以下的錯誤訊息,請參考下圖所示:
The server was stopped because SQL Server Audit '稽核資料庫的異動_UNC' is configured to shut down on failure.
To troubleshoot this issue, use the -m flag (Single User Mode) to bypass Audit-generated shutdowns when the server is starting.

圖20:SQL Server錯誤記錄檔:因為伺服器稽核失敗,執行個體也被關閉
任務五:調整稽核檔案數目上限
步驟 1. 在擔任網路共用伺服器上,先將分享資料夾內稽核目標檔案,除了目前正在使用的無法刪除外,其餘的請全部都刪除掉。
步驟 2. 回到資料庫伺服器上,使用「物件總管」,在「伺服器稽核」:稽核資料庫的異動_UNC上,先停用此「伺服器稽核」。設定此「伺服器稽核」以下的屬性:
- 在「於稽核記錄失敗」區域,設定為「失敗作業」。
- 在「稽核檔案數目上限」區域,點選:「最大檔案數目」,在「檔案數目」方塊,輸入:2。
- 再重新啟用此稽核。或是,執行以下的範例程式碼。請參考下圖所示:
-- 01_停用此「伺服器稽核」:稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = OFF);
GO
-- 02_在「於稽核記錄失敗」參數,設定為「失敗作業」
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH( ON_FAILURE = FAIL_OPERATION)
GO
-- 03_設定「最大檔案數目」的數量為:2
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
TO FILE (MAX_FILES = 2)
GO
-- 04_啟用此「伺服器稽核」:稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = ON);
GO
範例程式碼8:變更伺服器稽核的屬性:稽核檔案數目上限、失敗作業

圖21:設定稽核檔案數目上限、於稽核記錄失敗時的組態
步驟 2. 使用「物件總管」,在「稽核」物件:稽核資料庫的異動_UNC上,先停用此「伺服器稽核」,在啟用此「伺服器稽核」,前述的動作,請重複兩次。或是,執行以下的範例程式碼:
-- 01_停用此「伺服器稽核」:稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = OFF);
GO
-- 02_啟用此「伺服器稽核」:稽核資料庫的異動_UNC
USE [master]
GO
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = ON);
GO
範例程式碼9:對伺服器稽核先停用後再度啟用
應該會遭遇到啟用「伺服器稽核」失敗的錯誤訊息,請參考圖14與15,以及下圖所示。
訊息 33222,層級 16,狀態 1,行 1
稽核 '稽核資料庫的異動_UNC' 無法進行 start。如需詳細資訊,請參閱 SQL Server 錯誤記錄檔。
您還可以查詢 sys.dm_os_ring_buffers,其中 ring_buffer_type = 'RING_BUFFER_XE_LOG'。

圖22:啟用伺服器稽核卻失敗:超過最大檔案數目
在圖22中,我們在「伺服器稽核」上,在「稽核檔案數目上限」區域,特別設定其「最大檔案數目」為:2。
再多次的先停用此「伺服器稽核」與啟用此「伺服器稽核」。因為每次啟用「伺服器稽核」,系統就會自動產生一份新的稽核目標檔案,如此作法,就會超過當初在稽核檔案數目上所設定的上限值,導致無法啟用此「伺服器稽核」。
步驟 3. 使用「物件總管」,先停用後刪除此「伺服器稽核」以及其「伺服器稽核規格」。或是,執行以下的範例程式碼:
-- 01_停用以及刪除「伺服器稽核」:稽核資料庫的異動_UNC
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'稽核資料庫的異動_UNC')
BEGIN
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
WITH (STATE = OFF)
DROP SERVER AUDIT [稽核資料庫的異動_UNC]
END
GO
-- 02_停用以及刪除「伺服器稽核規格」:監視每當發生建立、改變或卸除任何資料庫的事件
IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'監視每當發生建立、改變或卸除任何資料庫的事件')
BEGIN
ALTER SERVER AUDIT SPECIFICATION [監視每當發生建立、改變或卸除任何資料庫的事件]
WITH (STATE = OFF)
DROP SERVER AUDIT SPECIFICATION [監視每當發生建立、改變或卸除任何資料庫的事件]
END
GO
範例程式碼10:對伺服器稽核與伺服器稽核規格分別執行:停用及刪除
實作練習二:認識對稽核記錄檔案的篩選
準備工作
請事先在本機伺服器上建立以下的資料夾:
- C:\myAdmin\Device。
- C:\myAdmin\Audit_Logs。
任務一:建立伺服器稽核與加入WHERE子句
步驟 1. 使用SSMS管理工具,建立「伺服器稽核」,在「建立稽核」視窗,設定以下的屬性:
- 「伺服器稽核」命名為:稽核備份或還原資料庫作業。
- 點選左邊窗格的「篩選」,右邊窗格,輸入WHERE子句:database_name = 'Northwind'。
請參考下圖所示,或是,執行以下的範例程式碼:

圖23:在伺服器稽核上,設定篩選條件式
在上圖23中,若要篩選稽核記錄的內容,能夠使用的資料行名稱是英文名稱,可以藉由執行sys.fn_get_audit_file系統預存函數來查詢可以使用哪些資料行的名稱。
-- 01_建立「伺服器稽核」:稽核備份或還原資料庫作業,WHERE子句篩選式:Northwind
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'稽核備份或還原資料庫作業')
BEGIN
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WITH (STATE = OFF);
DROP SERVER AUDIT [稽核備份或還原資料庫作業]
END
GO
CREATE SERVER AUDIT [稽核備份或還原資料庫作業]
TO FILE
( FILEPATH = N'C:\myAdmin\Audit_Logs'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF )
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE )
WHERE database_name = 'Northwind' -- WHERE子句篩選式:Northwind
GO
-- 02_啟用此「伺服器稽核」:稽核備份或還原資料庫作業
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WITH (STATE = ON);
GO
-- 03_使用sys.dm_server_audit_status動態管理檢視,檢視「伺服器稽核」SQL Server Audit的狀態
SELECT audit_id N'稽核的識別碼', name N'稽核的名稱', status_desc N'伺服器稽核狀態',
status_time N'上一次狀態變更的時間戳記', audit_file_size N'稽核檔案大小(KB)',
audit_file_path N'稽核檔案目標的完整路徑名稱',*
FROM sys.dm_server_audit_status;
GO
-- 04_使用sys.server_audits目錄檢視,檢視「伺服器稽核」物件的目前狀態,包含:WHERE子句篩選式
SELECT name N'稽核', type_desc N'稽核類型', on_failure_desc N'寫入動作失敗時',
predicate N'述詞運算式', is_state_enabled N'啟用', queue_delay N'寫入磁碟前等候時間(毫秒)',
create_date N'建立日期時間', modify_date '修改日期時間'
FROM sys.server_audits
GO
範例程式碼11:建立伺服器稽核:稽核備份或還原作業

圖24:檢視有設定WHERE子句篩選式的伺服器稽核
任務二:建立伺服器稽核規格
步驟 1. 使用「物件總管」,建立「伺服器稽核規格」,在「建立伺服器稽核規格」視窗,輸入以下的參數:
- 將「伺服器稽核規格」命名為:監視每當執行備份或是還原資料庫的事件。
- 在「稽核動作群組類型」部分,選擇:BACKUP_RESTORE_GROUP。
在伺服器層級的稽核動作群組上,使用的是:BACKUP_RESTORE_GROUP,這是指每當發出執行備份或是還原資料庫命令時,就會引發這個事件。
或是,執行以下的範例程式碼:
-- 01_建立「伺服器稽核規格」:監視每當執行備份或是還原資料庫的事件
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'監視每當執行備份或是還原資料庫的事件')
BEGIN
ALTER SERVER AUDIT SPECIFICATION [監視每當執行備份或是還原資料庫的事件]
WITH (STATE = OFF);
DROP SERVER AUDIT SPECIFICATION [監視每當執行備份或是還原資料庫的事件]
END
GO
CREATE SERVER AUDIT SPECIFICATION [監視每當執行備份或是還原資料庫的事件]
FOR SERVER AUDIT [稽核備份或還原資料庫作業]
ADD (BACKUP_RESTORE_GROUP)
GO
-- 02_啟用此「伺服器稽核規格」:監視每當執行備份或是還原資料庫的事件
ALTER SERVER AUDIT SPECIFICATION [監視每當執行備份或是還原資料庫的事件]
WITH (STATE = ON);
GO
-- 03_檢視「伺服器稽核規格」物件
SELECT name N'伺服器稽核規格', is_state_enabled N'是否已啟用',
create_date N'建立日期時間', modify_date N'修改日期時間'
FROM sys.server_audit_specifications
GO
範例程式碼12:建立與啟用伺服器稽核規格:監視每當執行備份或是還原資料庫的事件
任務三:測試備份資料庫與檢視稽核記錄
步驟 1. 使用SSMS管理工具,對pubs、Northwind、master以及msdb等資料庫執行備份作業。或是,執行以下的範例程式碼:
-- 備份資料庫:pubs、Northwind、master以及msdb
USE master
GO
BACKUP DATABASE [pubs]
TO DISK = N'C:\myAdmin\Device\pubs.bak'
WITH FORMAT, NAME = N'pubs-完整 資料庫 備份', COMPRESSION
GO
BACKUP DATABASE [Northwind]
TO DISK = N'C:\myAdmin\Device\Northwind.bak'
WITH FORMAT, NAME = N'Northwind-完整 資料庫 備份', COMPRESSION
GO
BACKUP DATABASE [master]
TO DISK = N'C:\myAdmin\Device\master.bak'
WITH FORMAT, NAME = N'master-完整 資料庫 備份', COMPRESSION
GO
BACKUP DATABASE [msdb]
TO DISK = N'C:\myAdmin\Device\msdb.bak'
WITH FORMAT, NAME = N'msdb-完整 資料庫 備份', COMPRESSION
GO
範例程式碼13:備份資料庫pubs、Northwind、master以及msdb
步驟 2. 再重複執行步驟1的動作。
步驟 3. 檢查此稽核的稽核記錄,應該僅能看到Northwind資料庫的備份稽核記錄,卻無法看到其他資料庫的備份稽核記錄,這是因為我們先前在「伺服器稽核」上加入了WHERE字句僅篩選了此資料庫。
步驟 4. 先停用「伺服器稽核」:稽核備份或還原資料庫作業,修改此「伺服器稽核」,設定以下的屬性:
- 點選左邊窗格的「篩選」,右邊窗格,輸入WHERE子句:database_name = 'Northwind' OR database_name = 'pubs'。請參考下圖所示:

圖25:在伺服器稽核上,修改篩選條件式
完成後,再度啟用此「伺服器稽核」。或是,執行以下的範例程式碼:
-- 01_停用此「伺服器稽核」:稽核備份或還原資料庫作業
USE master
GO
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WITH (STATE = OFF);
GO
-- 02_修改WHERE子句篩選式,包含:Northwind與pubs資料庫
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WHERE database_name = 'Northwind' OR database_name = 'pubs'
GO
-- 03_啟用此「伺服器稽核」:稽核備份或還原資料庫作業
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WITH (STATE = ON);
GO
範例程式碼14:修改伺服器稽核的WHERE子句篩選式,包含:Northwind與pubs資料庫
在範例程式碼14中,經過測試好像不能使用IN邏輯運算子。
步驟 5. 再重複執行步驟1的動作。檢查此稽核的稽核記錄,已經可以看到Northwind以及pubs資料庫的備份稽核記錄,卻無法看到其他資料庫的備份稽核記錄,這是因為我們先前在「伺服器稽核」上修改了WHERE字句,加入篩選了這兩個資料庫。請參考下圖所示:

圖26:檢視篩選過的稽核記錄
步驟 6. 先停用「伺服器稽核」:稽核備份或還原資料庫作業,移除「篩選」窗格的內容,再度啟用此「伺服器稽核」。或是,執行以下的範例程式碼:
-- 01_停用此「伺服器稽核」:稽核備份或還原資料庫作業
USE master
GO
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WITH (STATE = OFF);
GO
-- 02_移除WHERE子句篩選式
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
REMOVE WHERE;
GO
-- 03_啟用此「伺服器稽核」:稽核備份或還原資料庫作業
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WITH (STATE = ON);
GO
範例程式碼15:移除WHERE子句篩選式
步驟 7. 再重複執行步驟1的動作。檢查此稽核的稽核記錄。應該可以看記錄了每個資料庫的備份稽核記錄。
步驟 8. 停用以及刪除「伺服器稽核」與「伺服器稽核規格」。或是,執行以下的範例程式碼:
-- 01_停用以及刪除「伺服器稽核」:稽核備份或還原資料庫作業
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'稽核備份或還原資料庫作業')
BEGIN
ALTER SERVER AUDIT [稽核備份或還原資料庫作業]
WITH (STATE = OFF)
DROP SERVER AUDIT [稽核備份或還原資料庫作業]
END
GO
-- 02_停用以及刪除「伺服器稽核規格」:監視每當發生建立、改變或卸除任何資料庫的事件
IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'監視每當執行備份或是還原資料庫的事件')
BEGIN
ALTER SERVER AUDIT SPECIFICATION [監視每當執行備份或是還原資料庫的事件]
WITH (STATE = OFF)
DROP SERVER AUDIT SPECIFICATION [監視每當執行備份或是還原資料庫的事件]
END
GO
範例程式碼16:停用及刪除:對伺服器稽核與伺服器稽核規格分別執行
實作練習三:認識使用者定義稽核群組
在SQL Server 2012版本上新增加了「使用者定義稽核群組」,讓你可以自行在指定的層級上設定所需的稽核群組,所包含了兩個層級,分別是:「伺服器稽核規格」或是「資料庫稽核規格」。
在設計好「使用者定義稽核群組」後,再利用sp_audit_write系統預存程序於指定的情況時執行,此系統預存程序可以將使用者所定義的稽核事件傳遞觸發到此「使用者定義稽核群組」上。
由於是讓使用者可以設計所需的稽核事件,傳送的資料也都可以自訂。所以,在稽核目標記錄上,額外提供了T-SQL堆疊(Stack) 框架資訊以及與動作相關聯的T-SQL堆疊資訊,這是以XML格式顯示,一樣可以使用sys.fn_get_audit_file系統預存函數來查詢。
任務一:建立伺服器稽核
步驟 1. 使用SSMS管理工具,建立「伺服器稽核」,在「建立稽核」視窗,設定以下的屬性:
或是,執行以下的範例程式碼:
-- 01_建立「伺服器稽核」:稽核使用者定義稽核群組
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'稽核使用者定義稽核群組')
BEGIN
ALTER SERVER AUDIT [稽核使用者定義稽核群組]
WITH (STATE = OFF);
DROP SERVER AUDIT [稽核使用者定義稽核群組]
END
GO
CREATE SERVER AUDIT [稽核使用者定義稽核群組]
TO FILE
( FILEPATH = N'C:\myAdmin\Audit_Logs'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF )
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE )
GO
-- 02_啟用此「伺服器稽核」:稽核使用者定義稽核群組
ALTER SERVER AUDIT [稽核使用者定義稽核群組]
WITH (STATE = ON);
GO
-- 03_使用sys.dm_server_audit_status動態管理檢視,檢視「伺服器稽核」SQL Server Audit的狀態
SELECT audit_id N'稽核的識別碼', name N'稽核的名稱', status_desc N'伺服器稽核狀態',
status_time N'上一次狀態變更的時間戳記', audit_file_size N'稽核檔案大小(KB)',
audit_file_path N'稽核檔案目標的完整路徑名稱',*
FROM sys.dm_server_audit_status;
GO
-- 04_使用sys.server_audits目錄檢視,檢視「伺服器稽核」物件的目前狀態,包含:WHERE子句篩選式
SELECT name N'稽核', type_desc N'稽核類型', on_failure_desc N'寫入動作失敗時',
predicate N'述詞運算式', is_state_enabled N'啟用', queue_delay N'寫入磁碟前等候時間(毫秒)',
create_date N'建立日期時間', modify_date '修改日期時間'
FROM sys.server_audits
GO
範例程式碼17:建立與啟用伺服器稽核:稽核使用者定義稽核群組
任務二:建立伺服器稽核規格
步驟 1. 使用「物件總管」,建立「伺服器稽核規格」,在「建立伺服器稽核規格」視窗,輸入以下的參數:
- 將「伺服器稽核規格」命名為:監視sp_audit_write所引發的事件。
- 在「稽核動作群組類型」部分,選擇:USER_DEFINED_AUDIT_GROUP。
在伺服器層級的稽核動作群組上,使用的是:USER_DEFINED_AUDIT_GROUP,這是指每當執行sp_audit_write系統預存程序時,就會引發這個事件。請參考下圖所示:

圖27:在「稽核動作群組類型」部分,選擇:USER_DEFINED_AUDIT_GROUP
或是,執行以下的範例程式碼:
-- 01_建立「伺服器稽核規格」:監視sp_audit_write所引發的事件
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'監視sp_audit_write所引發的事件')
BEGIN
ALTER SERVER AUDIT SPECIFICATION [監視sp_audit_write所引發的事件]
WITH (STATE = OFF);
DROP SERVER AUDIT SPECIFICATION [監視sp_audit_write所引發的事件]
END
GO
CREATE SERVER AUDIT SPECIFICATION [監視sp_audit_write所引發的事件]
FOR SERVER AUDIT [稽核使用者定義稽核群組]
ADD (USER_DEFINED_AUDIT_GROUP)
GO
-- 02_啟用此「伺服器稽核規格」:監視sp_audit_write所引發的事件
ALTER SERVER AUDIT SPECIFICATION [監視sp_audit_write所引發的事件]
WITH (STATE = ON);
GO
-- 03_檢視「伺服器稽核規格」物件
SELECT name N'伺服器稽核規格', is_state_enabled N'是否已啟用',
create_date N'建立日期時間', modify_date N'修改日期時間'
FROM sys.server_audit_specifications
GO
範例程式碼18:建立與啟用伺服器稽核規格:監視sp_audit_write所引發的事件
任務三:測試觸發使用者定義稽核群組與檢視稽核記錄
步驟 1. 執行以下的範例程式碼來建立巢狀式呼叫的使用者預存程序:
-- 01_建立使用者預存程序:nest1
USE tempdb
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[nest1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[nest1]
GO
CREATE PROCEDURE nest1
AS
DECLARE @nestlevel nvarchar(19)
SET @nestlevel =N'存取被監控的物件,巢狀層級:'+CAST(@@NESTLEVEL AS nvarchar(10))
SELECT @nestlevel
EXEC sp_audit_write
@user_defined_event_id = 11, @succeeded = 0 , @user_defined_information = @nestlevel;
GO
-- 02_建立使用者預存程序:nest2
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[nest2]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[nest2]
GO
CREATE PROCEDURE nest2
AS
DECLARE @nestlevel nvarchar(19)
SET @nestlevel =N'存取被監控的物件,巢狀層級:'+CAST(@@NESTLEVEL AS nvarchar(10))
SELECT @nestlevel
EXEC sp_audit_write
@user_defined_event_id = 12, @succeeded = 0 , @user_defined_information = @nestlevel;
EXEC nest1
GO
範例程式碼19:建立巢狀式呼叫使用者預存程序
在範例程式碼19中,我們建立了兩個使用者預存程序:nest1與nest2。在nest2使用者預存程序內,會再執行nest2使用者預存程序,形成所謂的巢狀式呼叫使用者預存程序。
在nest1與nest2使用者預存程序中,都呼叫執行了sp_audit_write系統預存程序,以下是其使用的參數之說明:
- @user_defined_event_id:由使用者定義並且記錄在稽核記錄之 user_defined_event_id 資料行中的參數,其使用的資料類型是smallint。
- @succeeded:由使用者傳遞的參數,用於指出事件是否成功。這會顯示在稽核記錄的succeeded資料行中,其使用的資料類型是bit。
- @user_defined_information:由使用者自行定義所要記錄的訊息。這會顯示在稽核記錄的user_defined_information資料行中,其使用的資料類型是nvarchar(4000)。
此系統預存程序也有傳回碼值可以使用,0表示成功,1表示失敗,若發生輸入參數錯誤,或是無法寫入目標稽核記錄都會造成失敗。
在此範例程式中,我們在@user_defined_information參數內,搭配使用了@@NESTLEVEL組態函數,來傳回本機伺服器中執行目前預存程序的巢狀層級(最初值是0)。
步驟 2. 執行以下的範例程式碼來執行先前建立的使用者預存程序,請參考下圖來檢視其執行結果:
-- 01_執行巢狀式呼叫預存程序:nest2
USE tempdb
GO
EXEC nest2
GO
-- 02_直接執行系統預存程序:sp_audit_write
EXEC sp_audit_write
@user_defined_event_id = 88 , @succeeded = 0 ,
@user_defined_information = N'直接執行系統預存程序:sp_audit_write' ;
GO
範例程式碼20:執行巢狀式呼叫使用者預存程序

圖28:檢視使用者預存程序的執行結果
在圖28中,可以觀察到由@@NESTLEVEL組態函數,所傳回本機伺服器中執行目前預存程序的巢狀層級。
步驟 3. 檢視「伺服器稽核」的稽核目標記錄,或是,執行以下的範例程式碼,請參考下圖所示:
-- 01_使用fn_get_audit_file系統安全函數來檢視稽核檔案的內容
-- 移轉為伺服器所在地時區,增加資料行:additional_information、user_defined_event_id、user_defined_information
SELECT SWITCHOFFSET (CAST(event_time AS datetimeoffset), datepart(TZoffset,sysdatetimeoffset())) N'引發時的日期時間(伺服器所在地時區)',
server_principal_name N'登入帳戶', database_principal_name N'資料庫使用者',
database_name N'資料庫', object_name N'物件名稱', statement N'TSQL 陳述式',
additional_information N'其他資訊', user_defined_event_id N'使用者定義事件識別碼',
user_defined_information N'使用者定義資訊'
FROM sys.fn_get_audit_file ('C:\myAdmin\Audit_Logs\*',default,default)
ORDER BY 1 DESC;
GO
範例程式碼21:使用fn_get_audit_file系統安全函數查詢使用者定義稽核事件

圖29:使用「物件總管」,檢視稽核記錄
圖30:使用fn_get_audit_file系統安全函數,檢視稽核記錄
以下是執行nest2使用者預存程序後,在稽核目標記錄內所產生的資料:
其他資訊:<tsql_stack><frame nest_level = '2' database_name = 'tempdb' schema_name = 'dbo' object_name = 'nest2'/></tsql_stack>
使用者定義事件識別碼:12
使用者定義資訊:存取被監控的物件,巢狀層級:1
以下是nest2使用者預存程序呼叫nest1使用者預存程序後,在稽核目標記錄內所產生的資料:
其他資訊:<tsql_stack><frame nest_level = '3' database_name = 'tempdb' schema_name = 'dbo' object_name = 'nest1'/></tsql_stack>
使用者定義事件識別碼:11
使用者定義資訊:存取被監控的物件,巢狀層級:2
以下是直接執行sp_audit_write系統預存程序後,在稽核目標記錄內所產生的資料:
其他資訊:
使用者定義事件識別碼:88
使用者定義資訊:直接執行系統預存程序:sp_audit_write
在additional_informatio(其他資訊)資料行部分,其輸出的資料格式為XML,請參考如下:
<tsql_stack><frame nest_level = '%u' database_name = '%.*s' schema_name = '%.*s' object_name = '%.*s' /></tsql_stack>
其中Frame nest_level表示框架的目前巢狀層級。而模組名稱,會以三部分格式表示(database_name、schema_name以及object_name)。如果輸出的模組名稱包含了無效的XML字元,系統在剖析時會自動將這些字元逸出為格式:_xHHHH_,其中HHHH 代表字元的四位數十六進位UCS-2碼。無效的XML字元,例如有:'<'、'>'、'/' 和 '_x'等。
步驟 4. 使用「物件總管」,停用以及刪除「伺服器稽核」以及「伺服器稽核規格」,或是,執行以下的範例程式碼:
-- 01_停用以及刪除「伺服器稽核」:稽核使用者定義稽核群組
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N'稽核使用者定義稽核群組')
BEGIN
ALTER SERVER AUDIT [稽核使用者定義稽核群組]
WITH (STATE = OFF);
DROP SERVER AUDIT [稽核使用者定義稽核群組]
END
GO
-- 02_停用以及刪除「伺服器稽核規格」:監視sp_audit_write所引發的事件
USE [master]
GO
IF EXISTS (SELECT * FROM sys.server_audit_specifications WHERE name = N'監視sp_audit_write所引發的事件')
BEGIN
ALTER SERVER AUDIT SPECIFICATION [監視sp_audit_write所引發的事件]
WITH (STATE = OFF);
DROP SERVER AUDIT SPECIFICATION [監視sp_audit_write所引發的事件]
END
GO
範例程式碼22:停用以及刪除伺服器稽核與伺服器稽核規格
結語
在本期文章中,介紹了調整稽核記錄失敗時的處理、調整稽核檔案數目上限、認識對稽核記錄檔案的篩選以及認識使用者定義稽核群組等主題。