SQL Server ユーザーグループ　＞　特集　＞　監視ツールの基本操作　第2回

　前回は、SQL Server のプロファイラを使ってトレースを開始する方法を説明しました。今回は、プロファイラを起動することなく、Transact-SQL（システムストアドプロシージャ）を使ってバックグラウンドでトレースを開始する方法について説明します。バックグラウンドでトレースを実行しておけば、不正侵入や不正操作を記録するための監査証跡（Audit Trail）目的として利用できるようになります。例えば、「ログインの失敗」を監視することで、辞書攻撃（ディクショナリアタック）による不正侵入を検知したり、「顧客マスタ」テーブルへの SELECT 文の実行履歴を記録することで、顧客情報の漏えいを監視するといった使い方ができます。

　トレースを Trsansact-SQL から実行するには、「sp_trace_xxx」で始まるいくつかのシステムストアドプロシージャを利用します。とはいっても、イチからすべてを作成する必要はなく、プロファイラを使えば、これらのシステムストアドプロシージャを実行するための“ひな型”（「トレーススクリプト」と呼ばれる）を自動生成してくれます。以降では、その手順を説明していきます。

手順1. プロファイラでトレースを開始する

　まずは、プロファイラを起動して、通常と同じようにトレースを開始します（プロファイラの使い方は、前回の記事を参考にしてください）。

図1：プロファイラを使ってトレースを開始

手順2. トレースを停止する

　ツールバーの「停止」ボタンをクリックして、開始したトレースをすぐに停止します。

手順3. トレーススクリプトを生成する

　図2のように「ファイル」メニューの「トレーススクリプト」→「SQL Server 2000」をクリックします。すると、「名前を付けて保存」ダイアログが表示されるので、“auto”など任意のファイル名を入力し、“C:”ドライブなどへ保存します。

図2：トレーススクリプトの生成

　次に、生成したトレーススクリプトの中身を確認してみましょう。クエリアナライザの「ファイル」メニューから「開く」をクリックし、トレーススクリプト（C:\auto.sql）を選択して開いてみましょう（図3、図4）。

図3：クエリアナライザからトレーススクリプトを開く

図4：トレーススクリプトの中身（自動生成されたもの）

　スクリプト内で「InsertFileNameHere」と記述された場所が、トレース結果の出力先となるトレースファイル名を指定する場所になります。これを “C:\Kekka” などと変更してみてください（拡張子「.trc」は省略することに注意してください。トレースが開始されると、自動的に .trc が付加されます）。

トレースを開始する

　ファイル名の変更後、残りは変更せずにツールバーの「クエリ実行」ボタンをクリックし、スクリプトを実行します。これでトレースが開始され、 SQL Server に対して実行された SQL がトレースファイル（C:\Kekka.trc）へ記録されるようになります。

　次に、クエリアナライザで「ファイル」メニューの「接続」をクリックし、新しい接続を作成します。新しい接続側で任意の SQL を実行し、その SQL をトレースファイルへ記録させます（図5）。

図5：新しい接続を作り、任意の SQL を実行

トレース情報を確認する

　次に、新しい接続側から「fn_trace_getinfo」というシステム関数を使って、現在実行中のトレース一覧を取得します（関数の前にコロン : を 2つ記述することに注意してください）。

SELECT * FROM :: fn_trace_getinfo(default)

図6：fn_trace_getinfo で現在実行中のトレース一覧を取得

　トレースごとに 5 行の結果が出力されますが、注目してほしいのはトレース ID（traceid）です。この ID は、1 からの連続番号が割り当てられるので、1つ目のトレースの場合は 1 になります。また、各プロパティ（property）の意味は Transact-SQL リファレンスの fn_trace_getinfo に記載されていますが、property=5 は「現在のトレース状態」を意味し、value=1 はトレースが実行中であることを意味しています。

トレースを停止する

　次にトレースを停止してみましょう。トレースを停止するには、「sp_trace_setstatus」というシステムストアドプロシージャを利用します。次のように実行します。

EXEC sp_trace_setstatus 1, 0 --トレースID=1 を停止(0)する
EXEC sp_trace_setstatus 1, 2 --トレースID=1 のトレース定義を削除(2)する

　第 1 引数には fn_trace_getinfo で参照したトレース ID を指定し、第 2 引数には “0” と指定することでトレースの停止、“2” と指定することでトレース定義を削除することができます。詳しくは、Transact-SQL リファレンスの sp_trace_setstatus を参考にしてください。なお、トレース定義を削除しない場合は、第 2 引数に “1” と指定することでトレースを再開始することもできます。また、トレースを停止しない場合は、“SQL Server サービスが停止するまで” または “トレースファイルの最大サイズ（デフォルト 5MB）に達するまで” トレースは停止されません。

トレースファイルを開く

　次にトレースファイル（C:\Kekka.trc）をプロファイラで開いてみましょう。プロファイラを起動し、「ファイル」メニューの「開く」→「トレースファイル」をクリックして、C:\Kekka.trc を選択します。図5で実行した SQL が記録されていることを確認できると思います。

図7：プロファイラでトレースファイル（C:\Kekka.trc）を開く

　ここまでは、プロファイラで自動生成したトレーススクリプト（C:\auto.sql）を、トレースの出力先となるトレースファイル名だけを変更して使ってみましたが、以降ではこのスクリプトをカスタマイズして、トレースファイルの最大サイズを設定したり、ファイルのロールオーバーを有効にしたり、SQL Server の起動時のトレースを開始し、トレースを常時実行しておく方法を説明します。

トレーススクリプト（C:\auto.sql）の大枠

　プロファイラで生成されたトレーススクリプトの大枠は、次のようになります。

--トレース定義の作成
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5
exec @rc = sp_trace_create @TraceID output, 0, N'C:\Kekka', @maxfilesize, NULL
　　　　　　　：
--監視するイベントクラスの設定
exec sp_trace_setevent @TraceID, 10, 1, @on
exec sp_trace_setevent @TraceID, 10, 6, @on
　　　　　　　：
--フィルタの設定
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
　　　　　　　：
--トレースの開始
exec sp_trace_setstatus @TraceID, 1
　　　　　　　：

　4 つのシステムストアドプロシージャが使われますが、最後のトレースを開始するための sp_trace_setstatus は、トレースを停止するときに使ったものと同じストアドプロシージャです。

sp_trace_create ～トレース定義の作成～

　sp_trace_create は、トレース定義を作成するためのシステムストアドプロシージャです。構文は次のとおりです。

構文:
sp_trace_create @トレースID output, オプション, N'トレースファイル名', 最大ファイルサイズ, 終了時刻

　第 1 引数は、出力（OUTPUT）パラメータで、トレースID が返ります。第 2 引数は、後述するトレースファイルのロールオーバーを有効にするかどうかなどの設定を行ないます。第 3 引数は、すでに試したようにデフォルトは「InsertFileNameHere」と記述され、ここをトレースファイル名（.trc を省略）へ置換します。第 4 引数は、トレースファイルの最大サイズで、MB 単位で指定します。デフォルトは、@maxfilesize 変数が割り当てられ、1つ上に「set @maxfilesize=5」という記述があるので 5 MB に設定されます。第 5 引数は、トレースの終了時刻を指定しますが、終了時刻を指定したくない場合は NULL とします。sp_trace_create の詳細は、Transact-SQL リファレンスの sp_trace_create に記載されています。

　このように sp_trace_create は、図1の「全般」タブで設定できる項目とほぼ同等の効果があります。しかし、「全般」タブでトレースファイル名やトレースファイルの最大サイズ、ロールオーバーの有効化を設定しても、トレーススクリプトへは反映されないので注意してください。これらは手動でスクリプトを変更しなければなりません。なお、終了時刻だけは例外で、「全般」タブで設定したものがスクリプトへ反映されます。

sp_trace_setevent ～イベントクラスとデータ列の設定～

　sp_trace_setevent は、トレース対象としたいイベントクラスとデータ列を設定するためのシステムストアドプロシージャです。図1の「イベント」タブおよび「データ列」タブで設定したものが自動生成されます。

構文:
sp_trace_setevent トレースID, イベント番号, 列番号, オン(1) or オフ(0)

　例えば、SQL:BatchCompleted イベントクラス（イベント番号=12）に対して、TextData 列（列番号=1）と Duration 列（列番号=13）を記録するように設定している場合は、次のようにスクリプトが生成されます。

set @on = 1
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 13, @on

　イベント番号や列番号の詳細は、Transact-SQL リファレンスの sp_trace_setevent に記載されています。

sp_trace_setfilter ～フィルタの設定～

　sp_trace_setfilter は、フィルタを設定するためのシステムストアドプロシージャです。図1の「フィルタ」タブで設定したものが自動生成されます。

構文:
sp_trace_setfilter トレースID, 列番号, 論理演算子, 比較演算子, 値

　例えば、データベースID（列番号=3）が 6 のものだけをフィルタしたい場合は、次のようにスクリプトが生成されます。

set @intfilter = 6
exec sp_trace_setfilter @TraceID, 3, 1, 0, @intfilter

　列番号や論理演算子、比較演算子の詳細は Transact-SQL リファレンスの sp_trace_setfilter に記載されています。

　ここからは、SQL Server サービスの起動時にトレースを開始し、SQL Server が稼動している間、常時トレースを実行しておく方法を説明します。

手順1. ファイルのロールオーバーを有効にする

　前述したように、デフォルトではトレースファイルの最大サイズ（sp_trace_create の第 4 引数）に達すると、トレースが停止してしまいます。これではトレースを常時起動しておくことができません。トレースを常時起動するには、ファイルのロールオーバーを有効にするようにします。これは sp_trace_create の第 2 引数を “2” に設定します。

exec @rc = sp_trace_create @TraceID output, 2, N'C:\Kekka', @maxfilesize, NULL

　ロールオーバーを有効にすれば、C:\Kekka.trc が最大サイズに達したときに 2つ目のファイル C:\Kekka_1.trc が作成され、2 つ目が最大サイズに達したときは 3つ目のファイル C:\Kekka_2.trc が作成されるようになります。なお、トレース情報のファイルへの書き込みが発生するのは、最大サイズに達したときで、それまではメモリ内のバッファへ書き込まれています（バッファが最大サイズに達するとファイルへ書き出し）。

手順2. ファイル名に日付と時刻を埋め込むようにする

　sp_trace_create は、第 3 引数で指定するトレースファイル名のファイルがすでに存在していると、エラーになります。したがって、SQL Server の起動時にトレースを開始するには、開始ごとにファイル名を動的に変更しなければなりません。また、ファイル名は、トレース開始時の日付と時刻を入れるようにしておけば、後から管理がしやすくなるので、日付／時刻を入れると良いでしょう。トレースファイル名に日付／時刻を埋め込むには、例えば “2005年4月3 日 18時08分46秒” なら「C:\20050403180846」というファイル名にするには、次のように記述します。

DECLARE @d1 char(8), @d2 char(6), @fName sysname
SELECT @d1 = CONVERT(char(8),GETDATE(),112)
SELECT @d2 = REPLACE( CONVERT(char(8),GETDATE(),108), ':', '')
SELECT @fName = N'C:\' + @d1 + @d2
　　：
--@fNameをsp_trace_createの第3引数に指定
exec @rc = sp_trace_create @TraceID output, 2, @fName, @maxfilesize, NULL

　CONVERT 関数を使って、日付と時刻を文字列へ変換していますが、第 3 引数に指定した “112” や “108” については、Transact-SQL リファレンスの CAST と CONVERT を参考にしてください。“112” は yymmdd 形式、“108” は hh:mm:ss 形式の結果を返します（間のコロン : は REPLACE 関数で取り除いています）。

手順3. トレーススクリプトのストアドプロシージャ化

　次にトレーススクリプトをストアドプロシージャ化します。とはいっても、この作業はトレーススクリプトの先頭に “CREATE PROC xxx AS ” を追加するだけで完了です（xxx の部分はストアドプロシージャ名になるので、“trcStart” など任意の名前を指定します）。

--masterデータベースへ移動
USE master
go
--ストアドプロシージャの作成
CREATE PROC trcStart
AS
　<< トレーススクリプトをココへ貼り付け >>

　このようにストアドプロシージャにすることで、SQL Server サービスの起動時に自動実行させることができるようになります。

手順4. ストアドを SQL Server の起動時に自動実行させるようにする

　任意のストアドプロシージャは、SQL Server サービスの起動時に自動実行させることができます。この機能を利用すれば、手順 3 で作成したストアドプロシージャ（トレーススクリプト）を自動実行し、SQL Server の稼働中はトレースを常時実行させておくことができるようになります。

　自動実行を設定するには、「sp_procoption」というシステムストアドプロシージャを使って、次のように実行します。

sp_procoption 'trcStart', 'startup', 'on'

　第 1 引数にストアドプロシージャ名、第 2 引数に “startup”、第 3 引数に “on” を指定すると、そのストアドプロシージャが SQL Server サービスの起動時に自動実行されるようになります。なお、この操作は sp_procoption を実行するかわりに Enterprise Manager で該当ストアドプロシージャをダブルクリックし、「SQL Server 開始時に実行」チェックボックスをチェックしても同じ効果があります。

図8：SQL Server の起動時にストアドプロシージャを自動実行させる設定

　以上でトレースを常時実行できるようになります。トレースを停止したい場合は、SQL Server サービスを停止すれば、自動的に停止します。また明示的に停止したい場合は、前述の sp_trace_setstatus システムストアドプロシージャを使用すれば OK です。

　トレースを常時実行させるということは、その分 SQL Server へ負荷がかかるということにも注意しなければなりません（パフォーマンスとセキュリティのトレードオフがあります）。したがって、トレース対象とするイベントクラスを少なくしたり、フィルタを設定したり、必要な情報のみをトレースするようにし、パフォーマンスへの影響を少なくするとよいでしょう。イベントクラスやフィルタの設定は、図1 の「イベント」タブおよび「フィルタ」タブから行なえます（これらのタブで設定したものはトレース? スクリプトへ反映されます）。

　また、PASSJ 理事／セキュリティボードリーダーの河端さんの連載「セキュリティ TIPS」では、特定のオブジェクトのみをトレースするスクリプトも提供されているので、参考になると思います。

　　オブジェクトの使用を監視する by 河端さん
　　http://blogs.sqlpassj.org/yoshihirokawabata/articles/5720.aspx

　SQL Server では、米国国防総省が定める C2 レベルのセキュリティ基準を満たすための機能も用意されています。これは、「セキュリティ監査」カテゴリにあるイベントクラスによって提供されています。

図9：C2 レベルセキュリティ基準を満たすためのイベントクラス群

　例えば、“Audit Login Failed” でログインの失敗、“Audit Object Permission Event” でオブジェクト権限（SELECT／INSERT／UPDATE／DELETE／EXEC など）の使用が成功／失敗したときをトレースできるようになります。これらの詳細は、河端さんのセキュリティ TIPS などが参考になると思います。

　河端さんのセキュリティ TIPS 「プロファイラで監視しよう」
　 http://blogs.sqlpassj.org/yoshihirokawabata/articles/4835.aspx

　 PASSJ 掲示板「Re: 監査証跡（Audit Trail）の方法」
　 http://www.sqlpassj.org/bbs/bbs_disp.aspx?forum_id=1&current_page=1&disp_mode=2
&detail_mode=1&message_id=2265

C2 Audit Mode

　SQL Server には環境設定パラメータに “C2 Audit Mode” というオプションもあります。このモードを有効にすると、SQL Server の起動時に自動的にトレースを開始し、図8 の「セキュリティ監査」カテゴリにあるイベントクラスをすべてトレース対象としてくれます。しかし、前述したように、トレースに含めるイベントクラスが多ければ多いほど、トレースの負荷がかかることになるので、このモードを利用する場合はパフォーマンスへの影響に注意するようにしましょう。C2 Audit Mode の設定方法については、私のブログが参考になると思います。

　C2 Audit Mode の使い方
　 http://blogs.sqlpassj.org/matu_tak/archive/2004/10/31/5364.aspx