前言
隨著企業開始正視資訊安全的重要性，存取資料的稽核紀錄將變得重要。在SQL Server 2008版本開始導入一套全新的稽核系統：SQL Server Audit，讓管理人員不但可以精確地紀錄所需要之稽核資訊，而且利用「擴充事件(Extended Event)」來監視系統。
所謂的「擴充事件」，是一種耗用資源少的輕量型效能監視系統，這也是在SQL Server 2008版本開始提供。如今在SQL Server 2012版本，增加了更多彈性與自訂的功能。
伺服器稽核新增強的功能
以下整理了SQL Server 2012版本在「伺服器稽核(SQL Server Audit)」上新增強的功能：
- 所有SQL Server版本都支援「伺服器層級稽核(Server Level Audit)」。但是「資料庫層級稽核(Database Level Audit)」，則限於Enterprise、Developer以及Evaluation版本。
- 強化對稽核檔案記錄寫入失敗的處理，並且提供了繼續、關閉伺服器以及失敗作業等選項。
- 新增加對稽核記錄檔的篩選。例如，在CREATE SERVER AUDIT可以搭配使用WHERE條件式，篩選符合條件式的資料才能存放到稽核記錄檔上。
- 在先前版本中，稽核記錄可能具有不定數目的記錄檔，或在預先定義的數目之後換用。現在，已經導入了新的選項，可設定稽核檔案的數目上限而不換用，讓客戶能夠控制所收集的稽核資訊數量，而不會遺失稽核記錄。
- 新增「稽核動作群組」：USER_DEFINED_AUDIT_GROUP。它讓你可以自定使用者所需的稽核事件。使用系統預存程序：sp_audit_write，則可以將使用者定義稽核事件加入至 USER_DEFINED_AUDIT_GROUP。
- 提供了T-SQL堆疊(Stack) 框架資訊與動作相關聯的T-SQL堆疊資訊，這是以XML格式顯示。可以利用系統預存函數「sys.fn_get_audit_file」來查詢。
- 支援監視「自主資料庫」的資料庫使用者。
稽核記錄失敗時的處理
舉例來說：如果寫入的目標目錄是位於遠端共用上，若是發生網路中斷事件，「伺服器稽核」可以設定為能夠於恢復網路連接後，繼續執行服務。此外，也導入發生寫入記錄目標失敗時，讓此作業失敗的新功能。
有關於強化稽核檔案寫入失敗的處理，以下提供更進一步的說明：
「繼續(CONTINUE)」
若發生稽核記錄寫入失敗時，系統不會保留稽核記錄。「伺服器稽核」是會繼續嘗試記錄事件，而且之後，如果失敗狀況已解決，就會恢復稽核作業。
使用此選項可能會違反安全性原則，因為可能會有遺漏未稽核到的活動。這項功能，適用於資料庫系統的運行作業比維持完整稽核更重要時，請使用此選項。
「關閉伺服器(SHUTDOWN)」
這是在SQL Server 2008版本就已經提供的功能。若是發生稽核記錄寫入失敗時，系統將會強制伺服器關閉。發出此內容的登入必須具有SHUTDOWN權限。
如果登入的帳戶沒有此權限，這個功能將會失敗，而且會引發錯誤訊息，不會發生稽核的事件。這項功能，適用於當稽核失敗可能危害系統的安全性或完整性時，請使用此選項。
「失敗作業(FAIL OPERATION)」
如果資料庫動作導致發生稽核的事件，但卻發生稽核記錄寫入失敗時，這些動作就會失敗。也就是說，稽核事件的動作無法繼續進行，也不會發生稽核的事件。
稽核會繼續嘗試記錄事件，而且如果失敗狀況已解決，就會恢復稽核。這項功能，適用於當維持完整稽核是比資料庫系統的運行作業更重要時，請使用此選項。
這是以交易的方式來進行，若是因故發生無法寫入稽核記錄的事件，系統將會回復此交易。但是，這只包含有使用「稽核」組態的對象，若有未受到稽核的對象，則不受到交易回復的影響，仍將繼續執行。
舉例來說：以客戶、訂單兩個資料表為例，因為客戶資料表包含了機密資料，你僅在客戶資料表上設定了「稽核」。此選項能夠以交易的方式回復客戶資料表，但是因為沒有在訂單資料表上設定「稽核」，所以，仍是可以對訂單資料表繼續作業。
圖1：建立「伺服器稽核」與設定存放稽核結果的目的地，比較SQL Server 2008與2012版本
設定最大換用檔案與最大檔案數目
請參考以下表格的整理：
| 選項 | 說明 |
| 最大換用檔案(Maximum rollover files) | 此選項用於設定除了目前的檔案外，還要保留多少份的檔案數量之上限。對「檔案大小上限」所輸入的參數值必須要是整數，預設是無限制，系統會自動填入：2147483647。 每當「稽核」重新啟動(例如：執行個體重新啟動或是對「稽核」設定停用後再度啟用)，或者是，檔案數量已經達到「檔案大小上限」所設定的空間時，系統就會評估此參數。在評估「最大換用檔案」時，如果檔案的數量已經超過其所設定值時，系統會自動刪除最舊版本的檔案。 如果將「最大換用檔案」設定為0，則每次在評估「最大換用檔案」的設定時，系統都會建立新的檔案。每次評估「最大換用檔案」的設定值時，系統只會自動刪除一個檔案。 |
| 最大檔案數目(MAX FILES) | 在SQL Server 2012版本上新增加了「最大檔案數目」選項，可以指定可建立的最大稽核檔案之數目。若是達到此限制，系統不會換用最初的第一個檔案。 因此，當達到MAX_FILES選項限制時，任何會被觸發進而產生稽核事件的動作，都會遭遇到失敗並接收到錯誤訊息。 |
表格1：使用參數：最大換用檔案以及最大檔案數目
由表格1來看，在設定「最大換用檔案」後，搭配使用「最大檔案數目」選項，可以避免發生被人惡意洗稽核記錄的情形。
認識稽核檔案的存取權
這要提醒你的是：系統會將「稽核」的結果傳送到事先所定義的「目標」內存放，若將「目標」組態為二進位檔案，這是使用SQL Server服務啟動帳戶的身分來寫入與讀取。因此，請確認SQL Server服務啟動帳戶對於目標的檔案位置有足夠的讀取和寫入權限。若是權限不足，在建立「伺服器稽核」時，就會遭遇到以下的錯誤訊息，以及下圖所示：
訊息 33072，層級 16，狀態 1，行 2
稽核記錄檔路徑無效。
![02_稽核記錄檔案路徑無效 [1]](http://blogs.uuu.com.tw/Articles/image.axd?picture=02_%E7%A8%BD%E6%A0%B8%E8%A8%98%E9%8C%84%E6%AA%94%E6%A1%88%E8%B7%AF%E5%BE%91%E7%84%A1%E6%95%88%20%5B1%5D_thumb.png "02_稽核記錄檔案路徑無效 [1]")
圖2：SQL Server服務啟動帳戶對於稽核記錄檔路徑沒有存取權限
若要將SQL Server Audit的目標記錄檔案直接寫入到網路共用資料夾，需要進行以下的設定：
- SQL Server服務的服務啟動帳戶，需要使用指定的登入帳戶。
- 此登入帳戶需要對於遠端的網路共用資料夾，具備寫入與讀取的權限。
- 請使用「通用命名慣例(Universal Naming Convention，UNC)」命名格式來存取。
實作練習一：將SQL Server Audit的目標記錄檔案寫入到網路共用資料夾
在本次練習將一併介紹SQL Server Audit新增強的功能。
準備工作
1. 準備兩台伺服器：一台安裝SQL Server的資料庫伺服器，一台伺服器提供網路共用。在本次實作練習環境中，使用OPM擔任資料庫伺服器，使用OP1擔任網路共用伺服器。
2. 建立共用的登入帳戶，請擇一執行即可：
- 若在網域環境，請建立一個網域登入帳戶：advop，密碼為：P@ssw0rd。
- 若非網域環境，替代方案是可以在兩台伺服器上，建立相同名稱、密碼的本機登入帳戶：advop，密碼為：P@ssw0rd。
3. 將此登入帳戶：advop，加入到以下本機Administrators群組內。
4. 將此網域登入帳戶：advop，加入到SQL Server執行個體的伺服器角色：sysadmin內。
5. 在擔任網路共用的伺服器上，建立資料夾：C:\myAdmin\Audit_Logs。並設定此網域登入帳戶：advop對此資料夾的「共用使用權限」為「完全控制」。
6. 設定使用此advop網域登入帳戶來當做SQL Server服務啟動帳戶。
任務一：建立SQL Server Audit伺服器稽核，將目標記錄檔案寫入到遠端的網路共用資料內
步驟 1. 使用SSMS管理工具，連線到目標伺服器，使用「物件總管」，展開指定的伺服器「安全性」\「稽核」節點。
步驟 2. 在「稽核」節點上，滑鼠右鍵選擇「新增稽核」。
步驟 3. 在「建立稽核」視窗填入以下的參數：
- 在「稽核名稱」方塊輸入：稽核資料庫的異動_UNC。
- 在「佇列延遲(以毫秒為單位)」方塊輸入：1000。
- 在「於稽核記錄失敗」方塊點選：「繼續」。
- 在「稽核目的地」方塊選擇：「檔案」。
- 在「檔案路徑」方塊輸入：\\OP1\Audit_Logs。
- 點選「確定」，完成設定。
請參考下圖所示：
![03_組態伺服器稽核 [1]](http://blogs.uuu.com.tw/Articles/image.axd?picture=03_%E7%B5%84%E6%85%8B%E4%BC%BA%E6%9C%8D%E5%99%A8%E7%A8%BD%E6%A0%B8%20%5B1%5D_thumb.png "03_組態伺服器稽核 [1]")
圖3：建立SQL Server Audit「伺服器稽核」
步驟 4. 在「稽核」物件：稽核資料庫的異動_UNC上，滑鼠右鍵，選擇「啟用稽核」。
步驟 5. 在「啟用稽核」視窗，點選「關閉」。
或是，使用以下的範例程式碼來建立與啟用SQL Server Audit「伺服器稽核」。請參考下圖所示：
-- 01_建立「伺服器稽核」，將目標記錄檔案寫入到遠端的網路共用資料內
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
CREATE SERVER AUDIT [稽核資料庫的異動_UNC]
TO FILE
( FILEPATH = N'\\OP1\Audit_Logs'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
GO
-- 02_啟用此「伺服器稽核」：稽核資料庫的異動_UNC
ALTER SERVER AUDIT [稽核資料庫的異動_UNC]
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目錄檢視，檢視「伺服器稽核」物件的目前狀態
SELECT name N'稽核',type_desc N'稽核類型', on_failure_desc N'寫入動作失敗時',
is_state_enabled N'啟用',queue_delay N'寫入磁碟前等候時間(毫秒)',
create_date N'建立日期時間', modify_date '修改日期時間'
FROM sys.server_audits
GO
範例程式碼1：建立與啟用伺服器稽核，將目標記錄檔案寫入到遠端的網路共用資料內
![04_檢視已經建立的「伺服器稽核」 [1]](http://blogs.uuu.com.tw/Articles/image.axd?picture=04_%E6%AA%A2%E8%A6%96%E5%B7%B2%E7%B6%93%E5%BB%BA%E7%AB%8B%E7%9A%84%E3%80%8C%E4%BC%BA%E6%9C%8D%E5%99%A8%E7%A8%BD%E6%A0%B8%E3%80%8D%20%5B1%5D_thumb.png "04_檢視已經建立的「伺服器稽核」 [1]")
圖4：檢視已經建立的「伺服器稽核」
任務二：建立伺服器稽核規格
在伺服器層級的稽核動作群組上，使用的是：DATABASE_CHANGE_GROUP，這是指每當建立、改變或卸除任何資料庫時，就會引發這個事件。
步驟 1. 使用「物件總管」，展開指定的伺服器「安全性」\「伺服器稽核規格」節點。
步驟 2. 在「伺服器稽核規格」節點，滑鼠右鍵選擇「新增伺服器稽核規格」。
步驟 3. 在「建立伺服器稽核規格」視窗，輸入以下的參數：
- 在「名稱」方塊輸入：監視每當發生建立、改變或卸除任何資料庫的事件。
- 在「稽核」方塊選取：稽核資料庫的異動_UNC。
- 在「稽核動作類型」方塊選擇：DATABASE_CHANGE_GROUP。
- 點選「確定」完成設定。
請參考下圖所示：
![05_建立伺服器稽核規格 [1]](http://blogs.uuu.com.tw/Articles/image.axd?picture=05_%E5%BB%BA%E7%AB%8B%E4%BC%BA%E6%9C%8D%E5%99%A8%E7%A8%BD%E6%A0%B8%E8%A6%8F%E6%A0%BC%20%5B1%5D_thumb.png "05_建立伺服器稽核規格 [1]")
圖5：建立伺服器稽核規格
步驟 4. 在「伺服器稽核規格」物件：當建立、改變或卸除伺服器主體，滑鼠右鍵選擇「啟用伺服器稽核規格」。
步驟 5. 在「啟用伺服器稽核規格」視窗，點選「關閉」。請參考下圖所示：
圖6：檢視已經建立的伺服器稽核以及對應的伺服器稽核規格
或是，使用以下的範例程式碼來建立與啟用伺服器稽核規格。請參考下圖所示：
-- 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 [稽核資料庫的異動_UNC]
ADD (DATABASE_CHANGE_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
範例程式碼2：建立與啟用伺服器稽核規格
![07_檢視已經建立的伺服器稽核規格 [1]](http://blogs.uuu.com.tw/Articles/image.axd?picture=07_%E6%AA%A2%E8%A6%96%E5%B7%B2%E7%B6%93%E5%BB%BA%E7%AB%8B%E7%9A%84%E4%BC%BA%E6%9C%8D%E5%99%A8%E7%A8%BD%E6%A0%B8%E8%A6%8F%E6%A0%BC%20%5B1%5D_thumb.png "07_檢視已經建立的伺服器稽核規格 [1]")
圖7：檢視已經建立的伺服器稽核規格
任務三：建立、修改與刪除資料庫與檢視稽核記錄
步驟 1. 使用「物件總管」，建立、修改與刪除資料庫，或是執行以下的範例程式碼：
-- 01_建立資料庫：DB_Audit02
USE master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N'DB_Audit02')
DROP DATABASE [DB_Audit02]
GO
CREATE DATABASE DB_Audit02
GO
-- 02_啟用資料庫屬性：AUTO_SHRINK
ALTER DATABASE [DB_Audit02]
SET AUTO_SHRINK ON WITH NO_WAIT
GO
-- 03_刪除此資料庫：DB_Audit02
DROP DATABASE DB_Audit02
GO
範例程式碼3：建立、修改與刪除資料庫
步驟 2. 在「物件總管」，展開指定的伺服器「安全性」\「稽核」節點。
步驟 3. 在「稽核」物件：稽核資料庫的異動_UNC上，滑鼠右鍵選擇「檢視稽核紀錄」。這裡面記錄了詳盡的資訊。
![08_使用記錄檔檢視器來檢視稽核記錄 [1]](http://blogs.uuu.com.tw/Articles/image.axd?picture=08_%E4%BD%BF%E7%94%A8%E8%A8%98%E9%8C%84%E6%AA%94%E6%AA%A2%E8%A6%96%E5%99%A8%E4%BE%86%E6%AA%A2%E8%A6%96%E7%A8%BD%E6%A0%B8%E8%A8%98%E9%8C%84%20%5B1%5D_thumb.png "08_使用記錄檔檢視器來檢視稽核記錄 [1]")
圖8：使用記錄檔檢視器來檢視稽核記錄
步驟 4. 點選「關閉」，完成檢視稽核目標記錄。
或是，使用以下的範例程式碼來檢視稽核目標記錄：
-- 01_使用fn_get_audit_file系統安全函數來檢視稽核檔案的內容
-- 請留意：event_time資料行存放的是格林威治標準時間(GMT)。
USE master
GO
SELECT *
FROM sys.fn_get_audit_file ('\\OP1\Audit_Logs\\*',default,default)
ORDER BY 1 DESC;
GO
-- 02_移轉為伺服器所在地時區
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 陳述式'
FROM sys.fn_get_audit_file ('\\OP1\Audit_Logs\\*',default,default)
ORDER BY 1 DESC;
GO
範例程式碼4：使用fn_get_audit_file系統安全函數來檢視稽核檔案的內容
![09_使用fn_get_audit_file系統安全函數來檢視稽核檔案的內容[1]](http://blogs.uuu.com.tw/Articles/image.axd?picture=09_%E4%BD%BF%E7%94%A8fn_get_audit_file%E7%B3%BB%E7%B5%B1%E5%AE%89%E5%85%A8%E5%87%BD%E6%95%B8%E4%BE%86%E6%AA%A2%E8%A6%96%E7%A8%BD%E6%A0%B8%E6%AA%94%E6%A1%88%E7%9A%84%E5%85%A7%E5%AE%B9%5B1%5D_thumb.png "09_使用fn_get_audit_file系統安全函數來檢視稽核檔案的內容[1]")
圖9：使用fn_get_audit_file系統安全函數來檢視稽核檔案的內容
結語
在本期文章中，介紹了伺服器稽核新增強的功能、稽核記錄失敗時的處理、設定最大換用檔案與最大檔案數目、認識稽核檔案的存取權、將SQL Server Audit的目標記錄檔案寫入到網路共用資料夾等主題。
訂閱