はじめに
やりたいこと
Azure Virtual Machines 上の SQL Serverの監査ログ(Audit)の保存先をストレージアカウントに設定したい。
躓いたところ
PaaSのAzure SQL DatabaseやAzure SQL Managed Instanceではストレージアカウントを直接指定ができたので、SQL Server on Azure VMでも設定しようとしたらできなかった。
Azure SQL Databaseの設定と確認方法は以下を参照
原因
SQL Serverのネットワークドライブへのアクセス権限不足
結論
SQL Server on Azure VMでは監査ログの保存先をストレージアカウントに設定できるが、ストレージアカウントのファイル共有を使用する必要がある。
ただしVM上にファイル共有のストレージアカウントを単純にマウントしただけでは保存先に指定ができない。
xp_cmdshellを使用して一時的にSQL Serverの権限を強化して、マウントしたディレクトリを保存先に設定する。
xp_cmdshellについてはこちら
概要詳細
Azure SQL Managed InstanceやAzure SQL DatabaseではSQL Server監査ログ(SQL Server Audit)をAzureストレージアカウント上のBLOBを保存先として設定がSSMSやポータルから指定できるのですが、Azure Virtual Machines 上の SQL ServerではBLOBへの保存設定は標準ではサポートされていないとのことです。
代替手段としてストレージアカウントのファイル共有を利用し、VMにマウントすることでSQL Server監査ログの保存先をAzureストレージアカウントに指定が可能です。
しかしVM上にストレージアカウントをマウントしただけでは、SQL Serverにマウントしたディレクトリを指定することができなかったため手順を記載します。
事前準備
ストレージアカウントの作成とファイル共有を作成し、対象のVMに対してAzure ファイル共有をマウントしておく必要があります。
手順
ここではSSMSを利用した手順を記載します。
手順概要
- SSMS にて対象の Azure Virtual Machines 上の SQL Server インスタンスに接続
- [新しいクエリ] でエディタを起動
- xp_cmdshellを実行しSQL Serverのサービスアカウントの権限を付与します。
- ファイルメニューの[オブジェクトエクスプローラー] > [監査] > [新しい監査] もしくは作成済みの監査を開く
- [全般]から[監査の出力先]でマウントしたディレクトリのパスを指定する
- xp_cmdshellを再度実行し、設定を元に戻す
1,SSMS にて対象の Azure Virtual Machines 上の SQL Server インスタンスに接続
2,[新しいクエリ] でエディタを起動
3,xp_cmdshellを実行しSQL Serverのサービスアカウントの権限を付与します。
以下のクエリを実行します。
EXECUTE sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
-- 'xp_cmdshell' の機能を有効にします
EXECUTE sp_configure 'xp_cmdshell', 1;
GO
RECONFIGURE;
GO
実行後、以下のクエリを実行します。
xp_cmdshell 'net use Z: \\[ファイル共有名.file.core.windows.net]\[ファイル共有名] /u:localhost\[ファイル共有ユーザー名] [ファイル共有パスワード]'
上記クエリが成功すると以下のような結果が表示されます。
[ファイル共有ユーザー名]、[ファイル共有パスワード]については以下のファイル共有の接続から確認ができます。
4,ファイルメニューの[オブジェクトエクスプローラー] > [監査] > [新しい監査] もしくは作成済みの監査を開く
5,[全般]から[監査の出力先]でマウントしたディレクトリのパスを指定する
6,xp_cmdshellを再度実行し、設定を元に戻す
以下のクエリを実行して、設定を戻します。
EXECUTE sp_configure 'xp_cmdshell', 0;
GO
RECONFIGURE;
GO
補足
上記手順の「3,xp_cmdshellを実行しSQL Serverのサービスアカウントの権限を付与します。」を実行せずにマウントしたディレクトリを指定すると以下のようなエラーが発生し失敗します。
TITLEMicrosoft SQL Server Management Studio
監査 'Audit-20230208-191705' の作成に失敗しました。 (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
Transact-SQL ステートメントまたはバッチの実行中に例外が発生しました。 (Microsoft.SqlServer.ConnectionInfo)
監査ログ ファイルのパスが無効です。 (Microsoft SQL Server、エラー: 33072)
ヘルプを表示するには https://docs.microsoft.com/sql/relational-databases/errors-events/mssqlserver-33072-database-engine-error をクリック