RDS for SQL Serverでサーバー監査(SQL Server Audit)を試してみた

Takuya Shibata

2022.01.13

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

しばたです。

RDS for SQL ServerではSQL Serverの機能(SQL Server Audit)を使った監査ログの取得がサポートされています。
本記事ではこの機能を試してみます。

SQL Server Auditの基本とRDSでのサポート

SQL Serverの監査機能自体はSQL Server 2008から提供されていた様です。

監査機能にはサーバー全体の操作などに対する「サーバーレベルの監査」と各データベース毎の操作に対する「データベースレベルの監査」の2つあり、「サーバーレベルの監査」はSQL Server 2012から全エディションで利用可能、「データベースレベルの監査」はSQL Server 2016 SP1から全エディションで利用可能になっています。 *1

そしてSQL Serverではサーバーおよび各データベースに対する監査ログをイベントログおよびバイナリ形式の専用ファイル(*.sqlaudit)に出力できます。

RDSにおけるSQL Server Auditサポート

RDS for SQL Serverではオプショングループを設定することでSQL Sever Auditを有効化し、バイナリ形式の監査ログファイルを指定のS3バケットへ転送することができます。

機能の詳細については以下のドキュメントをご覧ください。

本記事もこのドキュメントの内容をベースにしています。

試してみた

それでは早速試してみます。

0. 前提条件

今回は私の検証AWSアカウントの東京リージョンにRDS for SQL Server 2019 Standard EditionのRDSインスタンスを作ります。RDSの構築に際しVPC環境などは事前準備済みです。

また、手順の簡略化のため一部の作業をAWS CLI(Ver.2.4.9) on PowerShell 7.2.1で行います。

C:\> $PSVersionTable.PSVersion

Major  Minor  Patch  PreReleaseLabel BuildLabel
-----  -----  -----  --------------- ----------
7      2      1

C:\> aws --version
aws-cli/2.4.9 Python/3.8.8 Windows/10 exe/AMD64 prompt/off

1. S3バケットの作成

最初に監査ログの転送先となるS3バケットを用意します。

RDSと同じリージョンでPrivateなものであれば何でも構いません。
今回はrds-sqlserver-audit-logs-202201という名前のバケットをCLIで作っておきます。

# AWS CLIでS3バケット作成
$bucketName = 'rds-sqlserver-audit-logs-202201'
aws s3 mb "s3://$bucketName" --region ap-northeast-1
aws s3api put-public-access-block --bucket $bucketName `
    --public-access-block-configuration 'BlockPublicAcls=true,IgnorePublicAcls=true,BlockPublicPolicy=true,RestrictPublicBuckets=true'

2. IAM Roleの作成

次に監査ログをS3に転送するための権限となるIAM Roleを用意します。
ここで作ったロールはオプショングループの設定時に使用します。

権限の詳細は以下のドキュメントで確認していただきたいですが、基本的に前節で作ったS3バケットに対する各種アクセス権を与えてやる感じです。

今回はrds-sql-server-audit-roleと言う名前のロールをCLIから作ります。
(rds-sql-server-audit-policyというIAM Policyをアタッチする形にしています)

# S3バケット名指定
$bucketName = 'rds-sqlserver-audit-logs-202201'

# まずはIAM Policy rds-sql-server-audit-policy を作成
$policyName = 'rds-sql-server-audit-policy'
$policyDocument = @"
{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": "s3:ListAllMyBuckets",
            "Resource": "*"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket",
                "s3:GetBucketACL",
                "s3:GetBucketLocation"
            ],
            "Resource": "arn:aws:s3:::$bucketName"
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:PutObject",
                "s3:ListMultipartUploadParts",
                "s3:AbortMultipartUpload"
            ],
            "Resource": "arn:aws:s3:::$bucketName/*"
        }
    ]
}
"@ -replace '"','\"'
aws iam create-policy --policy-name $policyName --policy-document $policyDocument

# IAM Role rds-sql-server-audit-role を作成し、Policyをアタッチ
$roleName = 'rds-sql-server-audit-role'
$policyDocument = @'
{
    "Version": "2012-10-17",
    "Statement": [
      {
        "Sid": "",
        "Effect": "Allow",
        "Principal": {
          "Service": "rds.amazonaws.com"
        },
        "Action": "sts:AssumeRole"
      }
    ]
  }
'@ -replace '"','\"'
aws iam create-role --role-name $roleName --assume-role-policy-document $policyDocument
aws iam attach-role-policy --role-name $roleName `
  --policy-arn $(aws iam list-policies --query "Policies[?PolicyName=='$policyName'].Arn" --output text)

結果はこんな感じです。

3. DBオプショングループの作成

これで前準備が整ったのでRDS側の作業に取り掛かります。

RDSのマネジメントコンソールからオプショングループを作成します。
今回はsql-server-2019-audit-ogという名前で作成しました。

作成したオプショングループにオプションを追加します。

追加するオプションに「SQLSERVER_AUDIT」を選び、各種設定を埋めていきます。
「S3送信先」にS3バケットrds-sqlserver-audit-logs-202201を、「IAMロール」にrds-sql-server-audit-roleを指定します。
必要に応じてログ転送先に任意のプレフィックスを付けることが可能ですが今回は未指定のままとしました。

追加設定はデフォルトのまま(監査ログファイルのZip圧縮あり、RDSインスタンス内部に転送された後の監査ログを残さない)としました。

この設定でオプションを追加してやるとこんな感じになります。

4. RDSインスタンスの構築

あとはこのオプショングループを使ってRDSインスタンスを構築します。

オプショングループ以外の設定は任意で構いません。
今回はdb.m5.largeのSingle-AZ構成なインスタンスを作っています。

これでAWS側の準備は完了です。

5. データベース内部の設定

ここからは実際にSQL Serverに接続し「監査対象の指定」「SQL Serverとしての監査の開始」を行います。
特に「監査対象の指定」についてはユーザー自身でシステム要件に応じて監査対象を決めてやる必要がありますので事前に検討しておいてください。

以降の手順はオンプレ環境のSQL Serverで行うものと全く同じなのですが、RDS環境においては以下の点に対する考慮が必要となります。

  • インスタンスごとにサポートされるサーバー監査の最大数 50 を超えない。
  • データをバイナリファイルに書き込むように SQL Server に指示する。
  • サーバーの監査名のプリフィックスとして RDS_ を使用しない。
  • FILEPATH で、D:\rdsdbdata\SQLAudit を指定する。
  • MAXSIZE で、2 MB～50 MB の間のサイズを指定する。
  • MAX_ROLLOVER_FILES または MAX_FILES を設定しない。
  • 監査レコードの書き込みに失敗した場合に DB インスタンスをシャットダウンするように SQL Server を構成しない。

(監査の作成より引用)

5-1. サーバーレベルの監査を有効にする

最初にサーバーレベルの監査を有効にしてみます。

SQL Server Auditではどこに監査ログを出力するかなどを決める「サーバー監査オブジェクト」と監査対象を決める「サーバー監査仕様オブジェクト」をSQL(Transact-SQL)で作成・有効化する必要があります。

masterデータベースに接続しCREATE SERVER AUDIT文で任意のサーバー監査オブジェクトを作成します。
名前はRDS_はじまりで無ければなんでも良いですが、ログ出力先(FILEPATH)は必ずD:\rdsdbdata\SQLAuditにしてください。
また、監査レコードの書き込みに失敗した場合にインスタンスを止めない様にON_FAILURE = CONTINUEにすると良いでしょう。

-- masterデータベースに接続
USE [master]
GO

-- サーバー監査の作成
CREATE SERVER AUDIT [ServerAudit]
TO FILE 
(	FILEPATH = N'D:\rdsdbdata\SQLAudit',
	MAXSIZE = 10 MB,
	RESERVE_DISK_SPACE = OFF
) WITH (ON_FAILURE = CONTINUE)
GO

続けてCREATE SERVER AUDIT SPECIFICATION文でサーバー監査仕様を作成します。
監査対象の詳細は以下のドキュメントで確認のうえ必要なものを選んでください。

以下の例ではログインに関連するグループを適当に選んでいます。
そしてWITH (STATE = ON)でオブジェクト作成と同時に有効にしています。

-- サーバー監査仕様の作成 + 有効化
-- ※監査対象は自分で決める必要がある。本記事の例は極めて雑に決めている。
CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpec]
FOR SERVER AUDIT [ServerAudit]
    ADD (LOGIN_CHANGE_PASSWORD_GROUP),
    ADD (FAILED_LOGIN_GROUP),
    ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE = ON)
GO

最後に以下のSQLを実行することで監査を開始します。

-- 監査開始 (監査の有効化)
ALTER SERVER AUDIT [ServerAudit]
WITH (STATE = ON)
GO

監査設定はSSMSからも可能です。
SSMSで監査設定をした場合以下の様な感じになります。

(監査が無効の場合はアイコンの種類が異なる)

5-2. データベースレベルの監査を有効にする

データベースレベルの監査を有効にする場合、「サーバー監査オブジェクト」と「データベース監査仕様オブジェクト」を作成します。
「サーバー監査オブジェクト」の作成方法については前節で解説しているので割愛します。

監査対象のデータベースに接続しCREATE DATABASE AUDIT SPECIFICATION文で任意のデータベース監査仕様オブジェクトを作成し有効にします。

監査対象の詳細は以下のドキュメントで確認のうえ必要なものを選んでください。

以下の例ではデータベース自体の変更に関連するグループを適当に選んでいます。
そしてサーバー監査仕様オブジェクト同様にWITH (STATE = ON)でオブジェクト作成と同時に有効にします。

-- 前提条件としてサーバー監査を作成していること

-- 監査対象のデータベースに接続 : 事前に mydb データベースを作成済み
USE [mydb]
GO

-- データベース監査仕様の作成 + 有効化
-- ※監査対象は自分で決める必要がある。本記事の例は極めて雑に決めている。
CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpec]
FOR SERVER AUDIT [ServerAudit]
    ADD (DATABASE_CHANGE_GROUP),
    ADD (DATABASE_OBJECT_CHANGE_GROUP),
    ADD (FAILED_DATABASE_AUTHENTICATION_GROUP)
WITH (STATE = ON)
GO

-- データベース監査の開始はサーバー監査が開始されていればOK

サーバー監査が有効になっていればデータベース監査も有効になります。
SSMSから確認するとこんな感じになります。

5-3. 監査ログの確認

監査ログはRDSインスタンス内部のD:\rdsdbdata\SQLAudit配下に保存され定期的にS3に転送されます。
インスタンス内部にある監査ログはmsdb.dbo.rds_fn_get_audit_file関数 *2で参照できます。

-- RDS専用の msdb.dbo.rds_fn_get_audit_file 関数を使う
-- 使い方は sys.fn_get_audit_file と同じ
SELECT * 
  FROM msdb.dbo.rds_fn_get_audit_file('D:\rdsdbdata\SQLAudit\*.sqlaudit', default, default)

ちなみにオプショングループで監査ログの保持設定を有効にしている場合はログがD:\rdsdbdata\SQLAudit\transmitted\移動されるので関数のファイル保存先指定も適宜変更してください。

最後にS3に転送された結果はこんな感じになります。

補足 : 参考資料

SQL Server Auditの設定方法については以下の記事がとても分かりやすいので参考にしてください。

最後に

以上となります。

事前準備が少し面倒ですが無事RDSでSQL Server Auditが利用できました。
くどい様ですが監査対象は自分で決める必要がありますのでシステム要件に応じて設定してください。

脚注

  1. 古いバージョンではEnterprise Edition専用機能として提供されていました ↩
  2. 通常のSQL Serverであれば sys.fn_get_audit_file を使いますがRDSだとこの関数は権限不足で利用できません ↩
スキルアップ|初心者向け|書評|デベキャン|AWS認定|スキルアップ|デベキャンだより|勉強会|コミュニティ

関連記事

[アップデート] RDS for SQL Server でも Performance Insights 上で実行計画を確認できるようになりました

いわさ

2024.04.24

CDC で RDS for SQL Server のデータを Snowflake に同期してみた #Fivetran

安原朋紀

2024.04.05

[アップデート] Amazon RDS for SQL Server のリードレプリカ機能がシングル AZ 構成でも利用出来るようになりました

いわさ

2024.04.04

Fivetran Teleport Sync で RDS for SQL Server のデータを Snowflake に同期してみた #Fivetran

安原朋紀

2024.03.04