顯示具有 日誌 標籤的文章。 顯示所有文章
顯示具有 日誌 標籤的文章。 顯示所有文章

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 \ VersionSQL Server 2008 and 2008 R2SQL Server 2012 and 2014SQL Server 2016* and 2017
EnterpriseServer- and database-levelServer- and database-levelServer- and database-level
DeveloperServer- and database-levelServer- and database-levelServer- and database-level
DatacenterServer- and database-levelN/AN/A
Business IntelligenceNoneServer-levelN/A
StandardNoneServer-levelServer- and database-level*
WebNoneServer-levelServer- and database-level*
ExpressNoneServer-levelServer- 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年6月24日 星期四

[研究]資通系統防護基準-保留日誌至少六個月

[研究]資通系統防護基準-保留日誌至少六個月

2021-06-24

資通安全責任等級分級辦法(110-08-23版)
https://law.moj.gov.tw/LawClass/LawAll.aspx?pcode=A0030304
附表十++資通系統防護基準.PDF
構面：事件日誌與可歸責性
措施內容：記錄事件
控制措施：一、訂定日誌之記錄時間週期及留存政策，並保留日誌至少六個月。

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

Windows Event Log

好像沒有直接設定保留多久的方法，只能改「事件檢視器」的「最大記錄檔大小」。




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

Windows 2016、2019、2022 IIS 10.0 - IIS Log

預設沒有大小限制、天數限制，直到硬碟空間用盡。




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

(完)

張貼者： 於 沒有留言:
標籤： , , ,
訂閱： 文章 (Atom)