浮雲雅築: Audit

顯示具有 Audit 標籤的文章。顯示所有文章

2025年4月2日星期三

[研究]SQL Server 那些版本、等級支援 SQL Server Audit ?

[研究]SQL Server 那些版本、等級支援 SQL Server Audit ?

2025-04-02

ChatGPT 說在 SQL Server 中，「伺服器稽核（Server Audit）」功能只有在 Enterprise 版和特定的高階版本（如 Datacenter 或 Developer）才可用，而 Standard 版及更低版本（如 Express）不支援這個功能。敝人實際測試並非如此。

根據下面網址，至少 SQL Server 2016 有，但沒提到要 Enterprise 才支援
https://learn.microsoft.com/zh-tw/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-2016

3.2. SQL Server Audit Support in Different Editions and Versions
https://logbinder.helpspot.com/index.php?pg=kb.page&id=79
根據這篇，有更詳細說明

Edition \ Version	SQL Server 2008 and 2008 R2	SQL Server 2012 and 2014	SQL Server 2016* and 2017
Enterprise	Server- and database-level	Server- and database-level	Server- and database-level
Developer	Server- and database-level	Server- and database-level	Server- and database-level
Datacenter	Server- and database-level	N/A	N/A
Business Intelligence	None	Server-level	N/A
Standard	None	Server-level	Server- and database-level*
Web	None	Server-level	Server- and database-level*
Express	None	Server-level	Server- and database-level*

* Database-level auditing for Standard, Web and Express editions are available starting SQL Server 2016 SP1

********************************************************************************

以下是 SQL Server Audit 中伺服器層級（Server-Level）和資料庫層級（Database-Level）稽核的適用情境、優缺點，以及彼此無法取代的功能的比較：

層級	適用情境	優點	缺點	彼此無法取代的功能
伺服器層級	需要監控整個 SQL Server 執行個體的活動，例如登入事件、伺服器配置變更、資料庫的建立或刪除等。希望統一管理所有資料庫的稽核策略，確保一致性。	能夠集中監控整個伺服器的活動，提供全局視角。設定簡單，適用於需要統一監控的情境。	無法針對特定資料庫或物件進行細緻的稽核設定。可能會產生大量的稽核資料，增加儲存需求。	能夠監控整個伺服器的活動，例如登入和伺服器配置變更，這些是資料庫層級稽核無法覆蓋的。
資料庫層級	需要監控特定資料庫內的活動，例如對特定表格的 SELECT、INSERT、UPDATE、DELETE 操作。不同資料庫需要不同的稽核策略，以滿足各自的安全性和合規性要求。	提供更細緻的控制，能夠針對特定資料庫或物件設定稽核策略。有助於滿足特定的合規性要求，針對敏感資料進行監控。	需要在每個資料庫中單獨設定和管理稽核策略，增加管理複雜度。如果同時啟用伺服器層級和資料庫層級稽核，可能會導致重複記錄相同的事件，增加儲存成本。	能夠針對特定資料庫內的物件和操作進行細緻監控，這是伺服器層級稽核無法實現的。

********************************************************************************

(完)

2025年3月31日星期一

[研究]SQL Server 2019如何禁止sa或sysadmin群組中帳號遠端存取資料庫

[研究]SQL Server 2019如何禁止sa或sysadmin群組中帳號遠端存取資料庫

2025-03-31

限制 sa 帳戶的存取權限

連線到 SQL Server Management Studio (SSMS)

執行下列 SQL 語句來停用 sa 帳戶：

ALTER LOGIN sa DISABLE;

若仍需使用 sa，但只限本機存取，可以變更 sa 密碼並強制使用 Windows 身分驗證。

**********

建立登入觸發器 (Logon Trigger) 限制遠端 sysadmin 登入

建立觸發器來限制 sa 帳號若不是從某電腦登入，拒絕：

CREATE TRIGGER BlockRemoteSysadminLogin
ON ALL SERVER
FOR LOGON
AS
BEGIN
    IF ORIGINAL_LOGIN() IN ('sa') AND HOST_NAME() NOT IN ('本機電腦名稱')
    BEGIN
        ROLLBACK;
    END
END;

註：主機名稱請在 SSMS 用 SELECT HOST_NAME() 取得，而非用「命令提示字元」(cmd.exe) 用 hostname 指令取得，有可能不同。

建立觸發器來限制 sysadmin 群組若不是從某電腦登入，拒絕：

CREATE TRIGGER BlockRemoteSysadminLogin
ON ALL SERVER
FOR LOGON
AS
BEGIN
    IF IS_SRVROLEMEMBER('sysadmin', ORIGINAL_LOGIN()) = 1 
       AND HOST_NAME() NOT IN ('本機電腦名稱')
    BEGIN
        ROLLBACK;
    END
END;

注意，本機電腦名稱要用 hostname 的結果，不可用句點 (.) 代替，否則之後會完全不能進去

設定完成後，sysadmin 只能從本機登入。

********************************************************************************

【若不小心 "本機電腦名稱" 設定為 "." 】

T-SQL 的 "本機電腦名稱" 若設定為 "." 後，會被立刻強制登出 SSMS，無法再下任何 T-SQL 指令，如何解決。

使用 SQL Server 單人模式 (Single-User Mode)

C:\>net stop MSSQLSERVER
SQL Server (MSSQLSERVER) 服務正在停止.
SQL Server (MSSQLSERVER) 服務已經成功停止。
C:\>net start MSSQLSERVER /m
SQL Server (MSSQLSERVER) 服務正在啟動 .
SQL Server (MSSQLSERVER) 服務已經啟動成功。

C:\>sqlcmd -S localhost -E
1> DROP TRIGGER BlockRemoteSysadminLogin ON ALL SERVER;
2> GO
1> exit

C:\>net stop MSSQLSERVER
SQL Server (MSSQLSERVER) 服務正在停止.
SQL Server (MSSQLSERVER) 服務已經成功停止。

C:\>net start MSSQLSERVER
SQL Server (MSSQLSERVER) 服務正在啟動 .
SQL Server (MSSQLSERVER) 服務已經啟動成功。

C:\>

上面敝人實際測試過可行。

(完)

2025年3月28日星期五

[研究]SQL Server 2019 如何查詢資料庫帳號變更稽核？

[研究]SQL Server 2019 如何查詢資料庫帳號變更稽核？

2025-03-27

SQL Server 2019 如何查詢資料庫帳號變更稽核？

ChatGPT 說：

在 SQL Server 2019 中，可以使用 SQL Server Audit 或 Default Trace 來查詢資料庫帳號變更的稽核記錄，以下是幾種方法：

********************************************************************************

2025-04-02 補

註：ChatGPT 說在 SQL Server 中，「伺服器稽核（Server Audit）」功能只有在 Enterprise 版和特定的高階版本（如 Datacenter 或 Developer）才可用，而 Standard 版及更低版本（如 Express）不支援這個功能。敝人實際測試並非如此。根據下面網址，至少 SQL Server 2016 有，但沒提到要 Enterprise 才支援

https://learn.microsoft.com/zh-tw/sql/relational-databases/security/auditing/sql-server-audit-action-groups-and-actions?view=sql-server-2016

3.2. SQL Server Audit Support in Different Editions and Versions
https://logbinder.helpspot.com/index.php?pg=kb.page&id=79
根據這篇，有更詳細說明

Edition \ Version	SQL Server 2008 and 2008 R2	SQL Server 2012 and 2014	SQL Server 2016* and 2017
Enterprise	Server- and database-level	Server- and database-level	Server- and database-level
Developer	Server- and database-level	Server- and database-level	Server- and database-level
Datacenter	Server- and database-level	N/A	N/A
Business Intelligence	None	Server-level	N/A
Standard	None	Server-level	Server- and database-level*
Web	None	Server-level	Server- and database-level*
Express	None	Server-level	Server- and database-level*

* Database-level auditing for Standard, Web and Express editions are available starting SQL Server 2016 SP1.

********************************************************************************

方法 1：使用 SQL Server Audit（推薦方式）

SQL Server Audit 提供較完整的稽核功能，可以記錄登入帳號新增、刪除、變更密碼、授權變更等操作。

1. 建立 Server Audit（伺服器級稽核）

首先，建立稽核物件，將稽核記錄存放於檔案中：

請先手動建立C:\AuditLogs\ 目錄

下圖。ChatGPT 原本給的指令如下，就算是 SQL Server 2019 Enterprise 也無法執行成功

CREATE SERVER AUDIT LoginAudit
TO FILE (FILEPATH = 'C:\SQLAudit\', MAXSIZE = 10MB, ROLLOVER = ON);
ALTER SERVER AUDIT LoginAudit WITH (STATE = ON);

敝人後來改成下面

CREATE SERVER AUDIT Audit_LoginChanges
TO FILE ( FILEPATH = 'C:\AuditLogs\', MAXSIZE = 10MB)　　
WITH ( QUEUE_DELAY = 1000, ON_FAILURE = CONTINUE );

(下圖)ChatGPT 說 SQL Server Audit 要 Enterprise 版才支援，但敝人實際測試下面指令 SQL Server 2019 Standard 也可以正常執行

2. 建立 Server Audit Specification（伺服器層級稽核規格）

設定要稽核的事件：

CREATE SERVER AUDIT SPECIFICATION Audit_Login_Changes_Spec
FOR SERVER AUDIT Audit_LoginChanges
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
WITH (STATE = ON);

(下圖)SQL Server 2019 Enterprise 和SQL Server 2019 Standard 都可以執行

其中 SERVER_PRINCIPAL_CHANGE_GROUP 會記錄登入帳號變更（如 CREATE LOGIN、ALTER LOGIN、DROP LOGIN 等）。

3. 啟動 Audit

ALTER SERVER AUDIT Audit_LoginChanges WITH (STATE = ON);

(下圖)SQL Server 2019 Enterprise 和SQL Server 2019 Standard 都可以執行

4. 查詢 Audit 記錄

當帳號變更後，可以用以下 SQL 查詢：

SELECT event_time, action_id, succeeded, session_server_principal_name, server_principal_name, statement
FROM sys.fn_get_audit_file('C:\AuditLogs\*.sqlaudit', DEFAULT, DEFAULT)
ORDER BY event_time DESC;

(下圖)SQL Server 2019 Enterprise 和SQL Server 2019 Standard 都可以執行

其中

event_time：事件時間
action_id：動作類型（如 LGCR 表示 CREATE LOGIN，LGMD 表示 ALTER LOGIN）
succeeded：是否成功（1 = 成功，0 = 失敗）
session_server_principal_name：執行變更的帳號
server_principal_name：被變更的帳號
statement：執行的 SQL 指令

5. 刪除建立的東西

--步驟 1：停用 SERVER AUDIT SPECIFICATION
ALTER SERVER AUDIT SPECIFICATION Audit_Login_Changes_Spec WITH (STATE = OFF);

--步驟 2：刪除 SERVER AUDIT SPECIFICATION
DROP SERVER AUDIT SPECIFICATION Audit_Login_Changes_Spec;

--步驟 3：停用 SERVER AUDIT
ALTER SERVER AUDIT Audit_LoginChanges WITH (STATE = OFF);

--步驟 4：刪除 SERVER AUDIT
DROP SERVER AUDIT Audit_LoginChanges;

********************************************************************************

方法 2：使用 Default Trace（預設追蹤）

SQL Server 預設啟用了 Default Trace，可以用來查詢帳號變更。

查詢 Default Trace

SELECT 
    te.name AS EventName,
    t.DatabaseName,
    t.StartTime,
    t.ObjectName,
    t.ObjectType,
    t.EventClass,
    t.EventSubClass,
    t.TextData
FROM sys.fn_trace_gettable(
    (SELECT REVERSE(SUBSTRING(REVERSE(path), CHARINDEX('\', REVERSE(path)), LEN(path))) + 'log.trc' 
     FROM sys.traces WHERE is_default = 1), DEFAULT) t
JOIN sys.trace_events te ON t.EventClass = te.trace_event_id
WHERE t.EventClass IN (104, 105, 108) -- 104: Create Login, 105: Drop Login, 108: Alter Login
ORDER BY t.StartTime DESC;

其中

104（Create Login）：建立登入帳號
105（Drop Login）：刪除登入帳號
108（Alter Login）：修改登入帳號
Default Trace 只保留短期記錄，如果需要長期記錄，建議使用 SQL Server Audit。

(下圖) 新增一個帳號後測試

********************************************************************************

方法 3：查詢 SQL Server Error Log（部分變更可查）

部分帳號變更（如 CREATE LOGIN 或 ALTER LOGIN）會記錄到 SQL Server Error Log。

請參考

[研究]SQL Server 2019 資料庫帳號登出登入稽核？
https://shaurong.blogspot.com/2025/03/sql-server-2019.html

這方法適用於 SQL Server 已啟用 LOGIN_CHANGE_PASSWORD 記錄的情況，但不如 SQL Server Audit 詳細。

********************************************************************************

總結

方法	優點	缺點
SQL Server Audit（推薦）	最完整，支援長期記錄，可用 GUI 或 SQL 查詢	需設定 Audit
Default Trace	內建功能，不需額外設定	只能查詢最近變更
SQL Server Error Log	不需設定，適用於密碼變更查詢	記錄不完整

如需完整監控帳號變更，建議使用 SQL Server Audit，可確保符合稽核規範（如 GDPR、ISO 27001），並能長期儲存記錄。

(完)

2021年1月14日星期四

[研究]SQL Server資料庫稽核紀錄 (Database Audit Log)

[研究]SQL Server資料庫稽核紀錄 (DB Audit Log)

2021-01-14

因為某些緣故，臨時被要求查SQL Server 2019是否有提供這些稽核記錄，若無，如何設定、如何查詢，緊急查了一下，記錄下來。

一、資料庫的帳號變動(新增、刪除、修改)相關紀錄

之前被要求的是網站帳號 (或機敏資料表) 變動記錄，這次是資料庫帳號。

可用下面方法查詢

select * FROM sys.server_principals

或

SELECT *
FROM sys.server_principals AS pr   
JOIN sys.server_permissions AS pe   
    ON pe.grantee_principal_id = pr.principal_id;

(下圖) 查詢結果 (Click 圖片可看 100% 尺寸圖)

********************************************************************************

二、資料庫的帳號登出/登入行為相關紀錄

參考

[研究] SQL Server 2019 Audit 資料庫稽核 - 登入成功或失敗紀錄檢視

https://shaurong.blogspot.com/2020/06/sql-server-2019-audit.html

********************************************************************************

三、資料庫結構新增、刪除、修改等行為相關紀錄

sys. 追蹤 (Transact-sql) - SQL Server | Microsoft Docs

https://docs.microsoft.com/zh-tw/sql/relational-databases/system-catalog-views/sys-traces-transact-sql?view=sql-server-ver15

SELECT * FROM sys.traces ;

(上圖)max_files 顯示有5份logs

查詢 Log 中資訊

select * from

  fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\log_14.trc',5)
  where textdata is not null 
  ORDER BY StartTime DESC;

查詢有關 TestDB 的資訊

WITH ObjectTypeMap(Value,ObjectType) AS
(
    SELECT * FROM 
        ( Values ( 8259, 'Check Constraint' ),(8272,'Stored Procedure'),(8277, 'Table'),(8278,'View'),
                 (16964, 'Database'),(17235,'Schema' )
        ) as TypeMap(Value,ObjectType)
)
SELECT e.name,f.DatabaseName,m.ObjectType, f.ObjectName,f.ApplicationName, f.HostName,f.NTUserName,f.StartTime 
  FROM fn_trace_gettable('C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\log_14.trc',5) f
  JOIN sys.trace_events e ON f.EventClass = e.trace_event_id AND f.EventClass in (46,47,164) and f.EventSubClass = 0
  JOIN ObjectTypeMap m ON f.ObjectType = m.Value
  WHERE DatabaseName = 'TestDB'  
  ORDER BY f.StartTime DESC,f.EventSequence DESC