在保密你的伺服器和資料，防備目前複雜的攻擊，SQL Server有你需要的一切。但在你能有效使用這些安全功能前，你需要了解你面對的威脅和一些基本的安全概念。這篇文章提供了基礎，是以你可以對SQL Server裡的安全功能充分利用，不用在面對特定威脅，不能保護你資料的功能上浪費時間。
SQL Server稽核
SQL Server裡的稽核指的是你可以在資料庫或伺服器執行個體裡監控事件。稽核日志包含你選擇捕獲的一系列事件，對于資料庫和伺服器對象，主體和操作，在伺服器上形成活動記錄。你可以捕獲發生的幾乎任何資料，同時包括成功和不成功的登入；讀取的，更新的，删除的資料；管理任務；還有更多。稽核可以深入資料庫和伺服器。
回顧追溯已經發生事件可能是一種很奇怪的方法。但設計日志常是你用來監控攻擊的第一個且最好的資源，尤其是當攻擊者隻探測資料庫還沒有成功通路資料或進行破壞。但入侵發生時，監管部門和媒體會下來對你，稽核日志就是可能會幫你找出發生了什麼，什麼資料被通路，和入侵的源頭。沒有日志，很可能你必須抓住攻擊者并審問他們才可以明确知道到底發生了什麼！
SQL Server有長期有提供支援稽核的功能，但直到SQL Server 2008，稽核才真正成為一個核心功能，有内建對象，T-SQL支援，在SSMS裡有使用者界面。微軟從這個初始版本學到很多教訓，在SQL Server 2012裡做出一些漂亮的增強。其中一個最大的改變是微軟在SQL Server的所有版本裡支援稽核，并不單單企業版。這就是說限制你可以在任何版本裡，指定在所有的資料庫裡建立稽核。但這個消息是混合的：隻在企業、開發和評估版本裡，可以進行單個資料庫監控。是以如果你測試或評估SQL Server，最終在生産裡使用企業版，确定你使用的版本有你需要的功能。
多年來推動的稽核的一部分是全世界各個政府對不同類型組織的法律要求。結果，現在SQL Server用來保護世界上一些最嚴格的監管組織，行業和資料。
SQL Server稽核對象
不管你使用T-SQL還是SSMS的使用者界面操作稽核，你會與三個對象打交道。
- 伺服器稽核：對于你的稽核，伺服器稽核對象是最進階别的容器；你經常為一個稽核使用這個對象。通常你會為特定的目的，建立伺服器稽核到組，包含一個或多個稽核規範，例如承諾，或為一系列的伺服器或資料庫對象。你會使用這個對象到特定的稽核名稱，那裡用來儲存稽核日志，限于日志，和在稽核失敗事件裡發生了什麼。你也可以定義篩選來給你在事件日志裡顆粒級别的控制。
- 伺服器稽核規格：使用這個對象在稽核日志裡來定義特定伺服器級别的事件來捕獲。規格與你剛才建立的具體伺服器稽核相關。這裡是你定義的地方，即你在想要記錄的對象上的事件。
- 資料庫稽核規格：這個對象和伺服器稽核規格類似，除了你會在單個資料庫裡用它來捕獲事件。它也和一個伺服器稽核對象關聯。
你會經常用到伺服器稽核對象，連同一個或其它對象，取決于在日志裡你想要捕獲的事件類型。如果你想要記錄的所有事件在單個資料庫，使用資料庫稽核規格對象。否則，如果事件跨越兩個或更多資料庫，或是伺服器級别的事件，使用伺服器稽核規格對象。
當你建立一個伺服器稽核時，你可以為稽核日志選擇三個位置之一：Windows應用程式日志，Windows安全日志，或檔案系統裡的一個檔案。對你寫入稽核資料的位置你要非常仔細，因為它很可能包含像社會保障号，信用卡号，工資，企業财務資料等敏感資訊。是以，你要使用未授權使用者不能通路的位置。這就是說Windows應用程式日志不是個好選擇，因為預設情況下，使用者不需要更高的許可來檢視它。但安全日志可以是個好選擇，因為通路需要管理者許可。檔案夾中的檔案也可以是個好選擇，因為你可以安全化檔案夾或它裡面的檔案使用Windows内建的安全系統。
建立一個伺服器稽核
你可以使用SSMS裡的圖形話界面工具或T-SQL語句建立SQL Server稽核。使用SSMS的稽核的圖形界面更加簡單，以為我們從這裡開始。打開SSMS連接配接到本地SQL Server執行個體。在對象浏覽器裡，切換到最高層的【安全性】節點——不是資料庫裡的【安全性】節點——展開節點，你會看到稽核。
提示：這裡我使用的是SQL Server 2012和AdventureWorks2012樣本資料庫。
你可以使用這個對話框來設定你建立的伺服器稽核對象的各種屬性：
- 稽核名稱：SQLServer預設以目前的日期和時間自動生成，如Audit-20160430-084903格式，你也可以設定為你需要的名稱。我設定為Sample Audit來更好的區分。
- 隊列延遲：接下來你可以用毫秒來設定隊列延遲，預設是1秒，這表示你願意等待系統寫入日志條目的最大時間是1秒。這個值是性能和完全之間的權衡：更短的時間限制，在災難故障前，更容易記錄關鍵事件，但會影響到伺服器性能。對于這伺服器稽核，我保持預設設定，意味着最壞的情況我會丢失一秒的條目。對于範例稽核，完全可以接受。
- 接下來的設定決定在稽核日志失敗時會發生什麼，當SQL Server不能寫入一條稽核日志條目時，可能因為沒有可用的磁盤空間。預設是【繼續】，在系統事件日志裡有會有一條錯誤，但潛在的操作還是繼續。當稽核至關重要，面對稽核失敗，不管操作還是伺服器執行都不應該繼續，可以使用【關閉伺服器】選項。這是一個嚴厲的操作——關閉SQL Server服務——但稽核至關重要時這個是必須的。【操作失敗】是在這2個極端之間，引起造成失敗的事務復原。這個設定我也保持預設。
- 對話框上剩下的選項與你選擇的審計目标有關。有3個選項：檔案，安全日志，和應用程式日志。如果你選擇了後2個系統事件日志選項，沒有可用的其它選項，剩下的選項隻針對【檔案】.
- 如果你選擇了【檔案】選項，你需要指定一個檔案路徑。對于這個例子，我選擇D:\Junk。你可以把稽核檔案放在你想要的任何地方，例如其它盤甚至在網絡硬碟上。
- 其它選項與存儲日志檔案有關，讓你在資料量和檔案輸上有限制。你可以指定【最大滾動更新檔案數】，也可以指定【最大檔案數】。使用【最大滾動更新檔案數】選項，一旦檔案夾裡檔案數達到最大數，SQL Server開始用最新的檔案覆寫最老的檔案。使用【最大檔案數】選項，一旦達到指定的檔案數，寫入稽核日志會失敗。如果你勾選了關聯的【無限制】選項，對于滾動更新數和檔案數沒有限制。你也可以指定【最大檔案大小】，也可以設定【無限制】選項。對話框上的最後一個選項，【保留磁盤空間】，告訴SQL Server預留磁盤空間到你指定的最大檔案大小，對于最大檔案大小，隻有在選擇了【無限制】的情況下才會有。是以對于指定硬碟上的空間量，你有大量的控制選項。
插圖11.1顯示了對于Sample Audit完整的建立稽核對話框資訊。
插圖11.1：在SSMS裡，使用建立稽核對話框建立一個伺服器稽核對象。
你也可以使用T-SQL代碼建立稽核。代碼11.1顯示了建立剛才一樣配置的稽核，使用CREATE SERVER AUDIT語句。我使用腳本按鈕生成了這個代碼，在建立稽核對話框上，我修改了我想要的配置後，我還修改了一些代碼。
USE master;
GO
CREATE SERVER AUDIT [Sample Audit]
TO FILE
( FILEPATH = N'D:\Junk'
,MAXSIZE = 0 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
);
GO 代碼11.1:你可以用來建立Sample Audit的T-SQL代碼
代碼11.2向你展示了如何建立一個伺服器稽核，寫入日志到系統應用程式事件日志，而不是檔案。沒有其它可以設定選項，是以語句比剛才的例子小很多。
CREATE SERVER AUDIT SQLServerAudit
TO APPLICATION_LOG
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE);
GO 代碼11.2：建立寫入系統應用程式日志的SQL Server 稽核的代碼
在建立這2個稽核後，可以看下對象浏覽器的【安全性】節點裡的【稽核】節點（你要重新整理下節點内容來檢視它們），如插圖11.2所示。看下，在放大鏡圖示上獨有一個向下的紅色箭頭。這表示稽核還沒啟用，建立後預設都沒啟用。你可以在對象浏覽器裡右擊稽核，在彈出菜單裡選擇【啟用稽核】來啟用它們，或者用代碼11.3使用ALTER SERVER AUDIT語句來修改稽核。一旦啟用審計，紅色向下箭頭會消失，在對象浏覽器裡你要重新整理下來才可以看到。如果你在SSMS裡和我們一起來操作，啟用它們吧，一會我們要用到。
插圖11.2：在對象浏覽器裡建立但沒啟用的伺服器稽核
1 ALTER SERVER AUDIT SQLServerAudit WITH (STATE = ON);
2 GO 代碼11.3：啟用SQLServerAudit稽核的代碼
建立一個伺服器稽核規範
一旦你建立了一個伺服器稽核，你可以定義你想要寫入的稽核時間。一個伺服器稽核基本上是稽核規範的容器。記住有2類稽核規範：伺服器和資料庫稽核規範。
這裡我建立的伺服器稽核規範會記錄所有SQL Server失敗的登陸。這個是伺服器層級操作，不是單個資料庫，是以我需要在伺服器層級建立一個規範，而不是單個資料庫層級。在SSMS裡使用圖形化使用者界面，在伺服器層級【安全性】節點裡，右擊【伺服器稽核規範】，從彈出菜單裡選擇【建立伺服器稽核規範】來打開【Create Server Audit Specification】對話框。
你可以使用預設生成的名稱，但我這裡命名為TestSQLServerAuditSpec。然後從【稽核】下拉清單裡選擇【Sample Audit】，這裡包含了SQL Server執行個體裡已存在的所有伺服器稽核，選擇【Sample Audit】意味着稽核日志項目會進入稽核裡定義的檔案系統。
然後你可以使用對話框裡【稽核操作類型】來定義你想要記錄的事件或事件組。可用操作類型很豐富，如插圖11.3所示。對于這個規範，選擇【FAILED_LOGIN_GROUP】類型。
插圖11.3：你可以選擇記錄的伺服器操作稽核類型很多
對于FAILED_LOGIN_GROUP操作類型剩下的列都停用了，因為對于這個類型沒有其它可用選項。但其它類型會讓你關聯多個伺服器對象。對話框如插圖11.4所示，但你點選【确定】的時候會建立規範。
插圖11.4：使用SSMS定義新的伺服器稽核規範
新定義的伺服器稽核規範會在對象浏覽器裡的【伺服器稽核規範】節點裡出現，預設是停用的。你可以右擊這個規範從彈出菜單裡選擇【啟用伺服器稽核規範】來啟用它。
你也可以使用代碼11.4來建立新的伺服器稽核規範。這個代碼使用WITH子句來接受用ON或OFF的STATE參數值，來啟用或停用規範。如果你忽略這個子句，狀态預設是OFF。
1 CREATE SERVER AUDIT SPECIFICATION TestSQLServerAuditSpec
2 FOR SERVER AUDIT [Sample Audit] ADD (FAILED_LOGIN_GROUP)
3 WITH (STATE = ON);
4 GO 代碼11.4：使用可選的WITH子句來建立新的立即生效伺服器稽核規範。
測試稽核，連接配接到SQL Server執行個體，嘗試用不正确的登陸來連接配接執行個體。你可以用另一個的SSMS執行個體，也可以在對象浏覽器的工具欄新開一個。
然後在對象浏覽器裡右擊【安全性】【稽核】下的【Sample Audit】，從彈出菜單裡選擇【檢視稽核日志】。你會看到如插圖11.5的裡清單。每行包含事件的一些資訊，你可以拉伸下窗體看到更多的欄目資訊。但當你點選一條記錄時，資料會顯示在窗體下方，更容易閱讀。遺憾的是，列的格式并不好，如你在圖中所示。
插圖11.5：對Sample Audit檢視它的稽核日志
注意伺服器稽核自動捕獲修改稽核日志的事件，這裡是我啟用伺服器稽核的事件。即在圖裡表顯示的第二行。
提示：
在它們捕獲并寫入事件前，你要啟用伺服器稽核和伺服器稽核規範。通常，你在建立它們的時候就會啟用它們。
建立一個資料庫稽核規範
建立一個資料庫稽核規範和建立伺服器稽核規範基本一樣。最大的差別是在日志裡你可以捕獲的事件範圍。資料庫稽核規範隻能在單個資料庫捕獲事件，例如通過T-SQL語句通路資料，修改資料庫對象的結構或許可，或執行存儲過程。其它最大的差別是你在資料庫對象裡建立規範，而不是在對象浏覽器裡的伺服器層級裡建立。
假設你想建立稽核記錄什麼時候，什麼使用者或程序在AdventureWorks2012資料庫裡對Person.Person表執行了SELECT語句。在資料庫裡，找到AdventureWorks2012資料庫，展開節點，在【安全性】【資料庫稽核規範】右擊，從彈出菜單裡選擇【建立資料庫稽核規範】，這會打開【建立資料庫稽核規範】對話框，修改自動生成的【名稱】為Person Data Access，【稽核】裡選擇Sample Audit，在操作裡選擇如插圖11.6的内容。
插圖11.6：建立資料庫稽核規範記錄在AdventureWorks2012資料庫裡什麼時候什麼使用者查詢了Person.Person表。
你可以設定主體名稱為任何資料庫使用者或角色，包括公共資料庫角色來包含通路資料庫的所有人。和伺服器稽核規範一樣，你可以增加多個操作。
一旦你建立了規範，記得啟用它。
同樣，你也可以使用T-SQL代碼來建立這個規範，有自動啟用的選項WITH子句。
1 USE AdventureWorks2014;
2 GO
3
4 CREATE DATABASE AUDIT SPECIFICATION [Person Data Access]
5 FOR SERVER AUDIT [Sample Audit]
6 ADD (SELECT ON OBJECT::Person.Person BY public)
7 WITH (STATE = ON);
8 GO 代碼11.5：建立資料庫稽核規範記錄Person.Person被查詢
接下來，你可以通過執行對表的SELECT操作測試稽核規範。一旦這樣做後，再次檢查下Sample Audit的稽核日志，如插圖11.7所示。如你所見，日志包含了事件的完整資訊，包括引起它的SQL語句。
插圖11.7：當查詢Person.Persion表時的稽核日志
注意在圖中的【其它資訊】是空的。這是因為生成日志條目的事件是直接的SELECT語句。但當SELECT語句在存儲過程裡或其它代碼子產品裡執行時，其它資訊會包含T-SQL堆棧資訊，以XML格式。這個用來識别查詢是語句還是代碼子產品實行非常有用。
-- Test it using a stored procedure
CREATE PROCEDURE tempPerson
AS
BEGIN
SET NOCOUNT ON;
SELECT BusinessEntityID, Title, FirstName, MiddleName, LastName, Suffix
FROM Person.Person
ORDER BY LastName, FirstName, MiddleName;
END
GO
EXEC dbo.tempPerson; 重新運作後，我們再次來看日志。
插圖11.8：當通過存儲過程運作SELECT語句，會包含T-SQL堆棧資訊
其它資訊包含XML片段，包含引起稽核日志條目的一些資訊，架構和存儲過程名。
寫自定義稽核資訊
對每類事件，你不限于把預設資訊寫入日志條目。你也可以建立使用者自定義稽核事件，它讓你寫入任何你想寫的東西。代碼11.6展示了這個方法。當稽核規範（不管伺服器還是資料庫）被停用時，你才可以修改它。是以代碼首先停用它，然後它添加使用USER_DEFINED_AUDIT_GROUP的TestSQLServerAuditSpec到稽核規範，并立即啟用它。最後一行代碼使用sp_audit_write系統存儲過程來寫入一些資訊到稽核日志。
USE master;
GO
ALTER SERVER AUDIT SPECIFICATION TestSQLServerAuditSpec
WITH (STATE = OFF);
GO
ALTER SERVER AUDIT SPECIFICATION TestSQLServerAuditSpec
ADD (USER_DEFINED_AUDIT_GROUP)
WITH (STATE = ON);
GO
-- Write some custom audit information
EXEC sp_audit_write 9999, 1, N'Something in SQL Server succeeded!' 代碼11.6：使用USER_DEFINED_AUDIT_GROUP操作類型來寫自定義稽核資訊到伺服器稽核規範
當然，你也可以用SSMS的可視化使用者界面來修改伺服器稽核規範。
如果你執行剛才的代碼并檢視日志，你會看到如插圖11.9的資訊。你還是會看到稽核事件的上下文，包括引起事件的T-SQL語句，但在底部，你會看到使用者定義的資訊。
插圖11.9：包含使用者自定義資訊的稽核日志條目
提示：如果USER_DEFINED_AUDIT_GROUP伺服器稽核規範停用的話，SQL Server會忽略sp_audit_write。
篩選稽核事件
SQL Server也包含篩選寫入稽核日志事件的能力，這個和擴充事件使用同樣的篩選機制。篩選給你顆粒度的控制，SQL Server寫入稽核日志的内容。但是SQL Server還是為你在規範裡定義的事件生成所有的日志條目，然後使用篩選來覺得是否寫入事件到日志，了解這個非常重要。是以你還是有大量的日志事件生成，即使這些條目不寫入日志。這就是說還是為特定的對象建立稽核事件，而不是篩選它們。
現在假設你想建立一個稽核，記錄特定類型的所有事件，除用特定登陸關聯之外。代碼11.7第一部分在AdventureWorks2012資料庫裡建立carol登入和登入的使用者。然後使用SUSER_ID方法獲得新使用者的主體ID（這裡是267）。然後第二部分建立一個伺服器稽核使用WHERE子句篩選主體ID不為267的事件，并啟用伺服器稽核。現在carol在資料庫裡有自由領域，在伺服器稽核事件裡是安全的。
CREATE LOGIN carol WITH PASSWORD = 'GEP2zYDt+5Cqw';
CREATE USER carol FOR LOGIN carol;
SELECT SUSER_ID('carol');
-- Part 2: Create the server audit
-- Change principal id from 307 based on SUSER_ID from previous statement
USE master;
GO
CREATE SERVER AUDIT FilterAudit
TO APPLICATION_LOG
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
)
WHERE server_principal_id <> 267;
ALTER SERVER AUDIT FilterAudit WITH (STATE = ON);
GO 代碼11.7：在AdventureWorks2012資料庫裡建立一個登入和使用者，然後建立除這個使用者外的伺服器稽核
你也可以隻用稽核屬性對話框篩選事件，在對象浏覽器裡右擊伺服器稽核，在彈出菜單裡選擇屬性。選擇【Filter】頁，如插圖11.10所示。注意，這裡沒有WHERE語句。
插圖11.10：使用稽核屬性對話框來添加篩選到伺服器稽核
小結
SQL Server的稽核功能，讓你為資料庫執行個體和資料庫範圍裡的各個不同僚件，記錄日志。你可以使用SSMS裡的使用者圖形界面或T-SQL代碼來建立不同稽核對象，有很多選項來完成，例如篩選記錄，寫自定義資訊到日志，和檢視T-SQL的堆棧資訊。稽核是SQL Server一個重要的安全功能，在你的資料庫裡幫你找出可疑的活動，這樣的話你可以消除掉你資料的安全漏洞。
原文連結
http://www.sqlservercentral.com/articles/Stairway+Series/130498/
注：此文章為
WoodyTu學習MS SQL技術，收集整理相關文檔撰寫，歡迎轉載，請在文章頁面明顯位置給出此文連結！
若您覺得這篇文章還不錯請點選下右下角的推薦，有了您的支援才能激發作者更大的寫作熱情，非常感謝！