SQL Databaseの監査ログに出力するログ種別を削減する

はじめに

Blob Storageに出力している監査ログが大量にあり目的を達成することが困難な状況にある。
監査ログを出力している目的はログイン成功/失敗の調査なので不要なSQL実行ログは削除する。
代わりに拡張イベントにSQL実行ログを出力することにする。

監査ログの削減

監査ログに出力する内容はアクショングループという概念で定義している。
Azure Blob Storageに出力すると規定のアクショングループは以下のとおりである。

• BATCH_COMPLETED_GROUP
• SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP
• FAILED_DATABASE_AUTHENTICATION_GROUP"

SQL実行ログはBATCH_COMPLETED_GROUPで定義されているので、BATCH_COMPLETED_GROUPを設定から除外する。

# BATCH_COMPLETED_GROUPを除外する
Set-AzSqlServerAuditing -ResourceGroupName RG-SQL -ServerName audittest2019 -AuditActionGroup "SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP"," FAILED_DATABASE_AUTHENTICATION_GROUP" -BlobStorage -State Enabled

#  変更後のBlobStorgeへの監査ログ設定を取得
Get-AzSqlServerAuditing -ResourceGroupName RG-SQL -ServerName audittest2019 -BlobStorage

管理ポータルからAzure Powershellは実行することができる。

BATCH_COMPLETED_GROUPが除外されたことがわかる。

Azure Blobに出力された監査ログはT-SQLで閲覧することができます。

-- Azure Blob から監査ログを読み込む
SELECT * FROM sys.fn_get_audit_file ('https://auditblob2019.blob.core.windows.net/sqldbauditlogs/audittest2019/AuditTest/SqlDbAuditing_ServerAudit_NoRetention/2019-04-15/12_',default,default);
GO

拡張イベントの設定

拡張イベントは以下のT-SQLを設定したいデータベースで実行すれば良い。
コードはsql_batch_completedをAzure Blobに出力する設定。

-- キーとcredentialを作成
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
BEGIN
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '0C34C960-6621-4682-A123-C7EA08E3FC46'
END
GO
CREATE
    DATABASE SCOPED
    CREDENTIAL [https://xevent2019.blob.core.windows.net/sqllogs]
    WITH
        IDENTITY = 'SHARED ACCESS SIGNATURE',
        SECRET = 'st=2017-04-15T12%3A56%3A00Z&se=2026-04-16T12%3A56%3A00Z&sp=rwdl&sv=2018-03-28&sr=c&sig=CGZWZ4ujOtZEtIM8JyXh%2F2fPOdge9qUUOc5DgP8t8rc%3D'
    ;
-- セッション作成
CREATE EVENT SESSION [test]
ON  DATABASE ADD EVENT sqlserver.sql_batch_completed(
        ACTION(package0.event_sequence
        , sqlserver.client_app_name
        , sqlserver.client_connection_id
        , sqlserver.client_hostname
        , sqlserver.client_pid
        , sqlserver.compile_plan_guid, sqlserver.context_info, sqlserver.database_id
        , sqlserver.database_name
        , sqlserver.execution_plan_guid
        , sqlserver.num_response_rows
        , sqlserver.plan_handle
        , sqlserver.query_hash
        , sqlserver.query_hash_signed
        , sqlserver.query_plan_hash
        , sqlserver.query_plan_hash_signed
        , sqlserver.request_id
        , sqlserver.session_id, sqlserver.sql_text
        , sqlserver.transaction_id
        , sqlserver.transaction_sequence
        , sqlserver.tsql_stack
        , sqlserver.username)
    ) ADD TARGET package0.event_file(
    SET
        filename = N'https://xevent2019.blob.core.windows.net/sqllogs/FileName.xel'
    ) WITH(
        MAX_MEMORY = 4096 KB,
        EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
        MAX_DISPATCH_LATENCY = 30 SECONDS,
        MAX_EVENT_SIZE = 0 KB,
        MEMORY_PARTITION_MODE = NONE,
        TRACK_CAUSALITY = OFF,
        STARTUP_STATE = OFF
    )
GO

-- セッション開始
ALTER EVENT SESSION [test] ON DATABASE STATE = START;
GO

そのほかに便利なT-SQLを残しておく

-- キー一覧
SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101

-- credential一覧
SELECT * FROM sys.database_scoped_credentials

-- セッション停止
ALTER EVENT SESSION [test] ON DATABASE STATE = STOP;
GO

-- セッション一覧
SELECT * from sys.database_event_sessions
GO

Azure Blobに拡張イベントが出力されたら、SSMSで閲覧することもできます。

参考サイト

監査ログ

docs.microsoft.com

拡張イベント

docs.microsoft.com