SQL Server の使用状況と診断データの収集のローカル監査 (CEIP)

適用対象: SQL Server - Windows のみ Azure SQL Managed Instance

はじめに

Microsoft SQL Server は、お客様のコンピューターまたはデバイスに関する情報を収集して送信するインターネット対応の機能を備えています。 これは標準的なコンピューター情報と呼ばれています。 SQL Server の使用状況と診断データの収集のローカル監査コンポーネントでは、サービスによって収集されたデータが保存先フォルダーに出力されます。このデータは、Microsoft に送信されるデータ (ログ) です。 ローカル監査の目的は、Microsoft がこの機能を使用して収集するすべてのデータをユーザーがコンプライアンス、法規制、またはプライバシーの検証目的で確認できるようにすることです。

SQL Server 2016 CU2 および CU3 の場合、ローカル監査は SQL Server Database Engine and Analysis Services (SSAS) のインスタンス レベルで構成できるようになりました。 SQL Server 2016 CU4、2016 SP1、およびそれ以降のリリースの場合、ローカル監査は SQL Server Integration Services (SSIS) に対しても有効になります。 セットアップ中にインストールされる他の SQL Server コンポーネントと、セットアップ後にダウンロードまたはインストールされる SQL Server ツールには、使用状況と診断データの収集用のローカル監査機能はありません。

解説

• SQL CEIP サービスの削除または無効化はサポートされていません。
• クラスター グループからの SQL CEIP リソースの削除はサポートされていません。

SQL Server の使用状況と診断データ収集のオプトアウトはサポートされていますが、サービスを削除したり無効にしたりすることはできません。 データ収集をオプト アウトするには、「ローカル監査の有効/無効を切り替える」を参照してください。

前提条件

各 SQL Server インスタンス上でローカル監査を有効にする前提条件は次のとおりです。

1. インスタンスには SQL Server 2016 RTM CU2 以降の修正プログラムが適用されている。 Integration Services の場合、インスタンスには SQL 2016 RTM CU4、SQL 2016 SP1、およびそれ以降の修正プログラムが適用されている。

2. ユーザーはシステム管理者であるか、レジストリ キーの追加および変更、フォルダーの作成、フォルダー セキュリティの管理、および Windows サービスの停止/開始を行うアクセス権を持つロールである必要があります。

ローカル監査を有効にする前の事前構成手順

ローカル監査を有効にする前に、システム管理者は次のことを行う必要があります。

1. SQL Server インスタンス名と SQL Server CEIP サービス ログオン アカウントを把握する。

2. ローカル監査ファイル用の新しいフォルダーを構成する。

3. SQL Server CEIP サービス ログオン アカウントにアクセス許可を付与する。

4. ローカル監査ターゲット ディレクトリを構成するレジストリ キー設定を作成する。

SQL Server CEIP サービス ログオン アカウントを取得する

次の手順で、SQL Server CEIP サービス ログオン アカウントを取得します

1. サービス コンソールを起動します。 これを行うには、キーボードで Windows キーを押しながら R キーを押し、[実行] ダイアログ ボックスを開きます。 次に、テキスト フィールドに「services.msc」と入力し、[OK] を選択してサービス コンソールを起動します。

2. 目的のサービスに移動します。 たとえば、データベース エンジンの場合、SQL Server CEIP サービス (Your-Instance-Name) を探します。 Analysis Services の場合、SQL Server Analysis Services CEIP (Your-Instance-Name) を検索します。 Integration Services の場合は、SQL Server Integration Services CEIP サービスを探します。

3. サービスを右クリックし、 [プロパティ] を選択します。

4. [ログオン] タブを選びます。ログオン アカウントが [このアカウント] に表示されます。

ローカル監査ファイル用の新しいフォルダーを構成する。

ローカル監査によってログが出力される新しいフォルダー (ローカル監査ディレクトリ) を作成します。 たとえば、データベース エンジンの既定インスタンスの Local Audit ディレクトリの完全なパスは、C:\SQLCEIPAudit\MSSQLSERVER\DB\ です。

SQL Server CEIP サービス ログオン アカウントにアクセス許可を付与する

1. エクスプローラーで、新しいフォルダーがある場所に移動します。

2. 新しいフォルダーを右クリックし、 [プロパティ]をクリックします。

3. [セキュリティ] タブの [編集] を選択し、権限を管理します。

4. [追加] を選択し、SQL Server CEIP サービスの資格情報を入力します。 たとえば、「 NT Service\SQLTELEMETRY 」のように指定します。

5. [名前の確認] を選択して入力した名前を検証し、[OK] を選択します。

6. [権限] ダイアログ ボックスで、SQL Server CEIP サービスのログオン アカウントを選択し、[フォルダーの内容の一覧表示]、[読み取り]、[書き込み] を選択します。

7. [OK] を選択すると、権限の変更が直ちに適用されます。

ローカル監査ターゲット ディレクトリを構成するレジストリ キー設定を作成する

1. regedit を起動します。

2. 目的の CPE パスに移動します。

   Version	データベース エンジン - レジストリ キー
   2016	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.Your-Instance-Name\CPE
   2017	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.Your-Instance-Name\CPE
   2019	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.Your-Instance-Name\CPE

   バージョン	Analysis Services - レジストリ キー
   2016	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS13.Your-Instance-Name\CPE
   2017	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS14.Your-Instance-Name\CPE
   2019	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS15.Your-Instance-Name\CPE

   バージョン	Integration Services - レジストリ キー
   2016	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\130
   2017	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\140
   2019	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\150

3. CPE パスを右クリックし、[新規] を選択します。 [文字列値] を選択します。

4. 新しいレジストリ キーに UserRequestedLocalAuditDirectoryと名前を付けます。

ローカル監査の有効/無効を切り替える

事前構成手順を完了したら、ローカル監査を有効にすることができます。 有効にするには、システム管理者アカウント、またはレジストリ キーを変更するアクセス権を持つ同様のロールを使用し、次の手順に従ってローカル監査の有効/無効を切り替えます。

1. regeditを起動します。

2. 目的の CPE パスに移動します。

3. [UserRequestedLocalAuditDirectory] を右クリックし、[変更] を選択します。

4. ローカル監査を有効にするには、ローカル監査のパスを入力します (例: C:\SQLCEIPAudit\MSSQLSERVER\DB\)。

   ローカル監査を無効にするには、[UserRequestedLocalAuditDirectory] の値を空にします。

5. regeditを閉じます。

SQL Server CEIP が既に実行中の場合、ローカル監査設定は直ちに認識されます。 SQL Server CEIP サービスを開始するには、システム管理者、または Windows サービスを開始または停止するアクセス権を持つユーザーが、次の手順を実行します。

1. サービス コンソールを起動します。 これを行うには、キーボードで Windows キーを押しながら R キーを押し、[実行] ダイアログ ボックスを開きます。 次に、テキスト フィールドに「services.msc」と入力し、[OK] を選択してサービス コンソールを起動します。

2. 目的のサービスに移動します。

   • データベース エンジンの場合、「SQL Server CEIP サービス (Your-Instance-Name)」を使用します。
   • Analysis Services の場合、「SQL Server Analysis Services CEIP (Your-Instance-Name)」を使用します。
   • Integration Services の場合、
     • SQL 2016 の場合、「SQL Server Integration Services CEIP サービス 13.0」を使用します。
     • SQL 2017 の場合、「SQL Server Integration Services CEIP サービス 14.0」を使用します。
   • SQL 2019 の場合、"SQL Server Integration Services CEIP サービス 15.0" を使用します。

3. サービスを右クリックし、[再起動] を選択します。

4. サービスの状態が "実行中" であることを確認します。

ローカル監査では、1 日に 1 つのログ ファイルが生成されます。 ログ ファイルは <YYYY-MM-DD>.jsonの形式です。 たとえば、 2016-07-12.jsonというファイル名です。 保存先ディレクトリにその日のファイルが既にある場合、ローカル監査のログはそのファイルに付加されます。 既存のファイルがない場合、その日の新しいファイルが作成されます。

メンテナンス

1. ローカル監査から出力されるファイルのディスク使用量を制限するには、ローカル監査ディレクトリをクリーンアップして古い不要なファイルを削除するポリシーまたは定期ジョブを設定します。

2. ローカル監査ディレクトリ パスには適切なユーザーのみがアクセスできるようにセキュリティで保護します。 ログ ファイルには、フィードバックを Microsoft に送信するように SQL Server 2016 を構成する方法に関するページで説明されている情報が含まれています。 このファイルに対するアクセス権では、組織のほとんどのメンバーに読み取りを禁止することが推奨されます。

ローカル監査出力データ構造のデータ辞書

• ローカル監査ログ ファイルは JSON 形式です。emitTime で Microsoft に送信されるデータ ポイントを表すオブジェクト (行) のセットが含まれます。
• 各行は、 schemaVersionに指定された特定のスキーマに従います。
• 各行は、 sessionIDに指定された SQLCEIP サービス セッションの出力です。
• 行は、 sequenceに指定された順に出力されます。
• 各データ ポイント行には、queryIdentifier の出力が含まれています。この出力は、traceName に指定されたトレースの種類に関連する T-SQL クエリ、XE セッション、またはメッセージの可能性があります。
• queryIdentifiers はグループ化され、 querySetVersionを使用してバージョン管理されます。
• data には、queryTimeInTicks を使用した対応するクエリ実行の出力が含まれます。
• T-SQL クエリのqueryIdentifiers によって、クエリに T-SQL クエリ定義が格納されます。

名前/値ペアの定義と例

以下の列は、ローカル監査ファイル出力の順を表しています。 以下の複数列の匿名化された値に対して、SHA 256 による一方向のハッシュが使用されています。

トレース カテゴリ

現在、次のトレース カテゴリを収集します。

• SQLServerXeQueries: 拡張イベント セッションで収集されたデータ ポイントが含まれます。
• SQLServerPeriodicQueries: SQL Server インスタンスで実行される定期的なクエリで収集されたデータ ポイントが含まれます。
• SQLServerPerDBPeriodicQueries: SQL Server インスタンスの最大 30 データベースに対して実行される定期的なクエリで収集されたデータ ポイントが含まれます。
• SQLServerOneSettingsException: スキーマやクエリ セットの更新に関連する例外メッセージが含まれます。
• DigitalProductID: SQL Server インスタンスの匿名化された (SHA-256) ハッシュ デジタル プロダクト ID を集約するデータ ポイントが含まれます。

ローカル監査ファイルの例

次にローカル監査の JSON ファイル出力の例を示します。

[
  {
    "instanceUniqueId": "888770C4D5A8C6729F76F33D472B28883AE518C92E1999888B171A085059FD",
    "isSSEIInstance": "0",
    "schemaVersion": "5",
    "emitTime": "2018-05-04T15:27:59.7031518Z",
    "sessionId": "c3cd1b56-ab61-462f-8363-8881779aa223",
    "correlationId": 0,
    "sequence": 18,
    "clientVersion": "14.0.3025.34 ((SQLServer2017-CU6).180410-0033)",
    "isInternalMachine": "1",
    "operatingSystem": "Microsoft Windows 10 Enterprise",
    "querySetVersion": "14.0.3025.34",
    "traceName": "SQLServerPeriodicQueries",
    "queryIdentifier": "SQLServerProperties.002",
    "data": [
      {
        "Collation": "SQL_Latin1_General_CP1_CI_AS",
        "SqlFTinstalled": "0",
        "SqlIntSec": "1",
        "IsSingleUser": "0",
        "SqlFilestreamMode": "2",
        "SqlPbInstalled": "1",
        "SqlPbNodeRole": "Head",
        "SqlVersionMajor": "14",
        "SqlVersionMinor": "0",
        "SqlVersionBuild": "3025",
        "ProductBuildType": "",
        "ProductLevel": "RTM",
        "ProductUpdateLevel": "CU6",
        "ProductUpdateReference": "KB4101464",
        "ProductRevision": "34",
        "SQLEditionId": "1872460670",
        "IsClustered": "0",
        "IsHadrEnabled": "0",
        "SqlAdvAInstalled": "1",
        "PacketReceived": "422",
        "Version": "Microsoft SQL Server 2017 (RTM-CU6) (KB4101464) - 14.0.3025.34 (X64) \n\tApr  9 2018 18:00:41 \n\tCopyright (C) 2017 Microsoft Corporation\n\tEnterprise Edition: Core-based Licensing (64-bit) on Windows 10 Enterprise 10.0 <X64> (Build 16299: )\n"
      }
    ],
    "query": "SELECT\n      SERVERPROPERTY('Collation') AS [Collation],\n      SERVERPROPERTY('IsFullTextInstalled') AS [SqlFTinstalled],\n      SERVERPROPERTY('IsIntegratedSecurityOnly') AS [SqlIntSec],\n      SERVERPROPERTY('IsSingleUser') AS [IsSingleUser],\n      SERVERPROPERTY ('FileStreamEffectiveLevel') AS [SqlFilestreamMode],\n      SERVERPROPERTY('IsPolyBaseInstalled') AS [SqlPbInstalled],\n      SERVERPROPERTY('PolyBaseRole') AS [SqlPbNodeRole],\n      SERVERPROPERTY('ProductMajorVersion') AS [SqlVersionMajor],\n      SERVERPROPERTY('ProductMinorVersion') AS [SqlVersionMinor],\n      SERVERPROPERTY('ProductBuild') AS [SqlVersionBuild],\n      SERVERPROPERTY('ProductBuildType') AS ProductBuildType,\n      SERVERPROPERTY('ProductLevel') AS ProductLevel,\n      SERVERPROPERTY('ProductUpdateLevel') AS ProductUpdateLevel,\n      SERVERPROPERTY('ProductUpdateReference') AS ProductUpdateReference,\n      RIGHT(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(30)),CHARINDEX('.', REVERSE(CAST(SERVERPROPERTY('ProductVersion') AS NVARCHAR(30)))) - 1) AS ProductRevision,\n      SERVERPROPERTY('EditionID') AS SQLEditionId,\n      SERVERPROPERTY('IsClustered') AS IsClustered,\n      SERVERPROPERTY('IsHadrEnabled') AS IsHadrEnabled,\n      SERVERPROPERTY('IsAdvancedAnalyticsInstalled') AS [SqlAdvAInstalled],\n      @@PACK_RECEIVED AS PacketReceived,\n      @@VERSION AS Version",
    "queryTimeInTicks": 0
  },
  {
    "instanceUniqueId": "8884F770C4D5A8C6729F76F33D472B28883AE518C92E1999888B171A085059FD",
    "isSSEIInstance": "0",
    "schemaVersion": "5",
    "emitTime": "2018-05-04T15:28:00.9025999Z",
    "sessionId": "c3cd1b56-ab61-462f-8363-8881779aa223",
    "correlationId": 0,
    "sequence": 23,
    "clientVersion": "14.0.3025.34 ((SQLServer2017-CU6).180410-0033)",
    "isInternalMachine": "1",
    "operatingSystem": "Microsoft Windows 10 Enterprise",
    "querySetVersion": "14.0.3025.34",
    "traceName": "SQLServerPeriodicQueries",
    "queryIdentifier": "OsSysInfo.003",
    "data": [
      {
        "LogicalCPUCount": "8",
        "HyperthreadRatio": "8",
        "PhysicalMemoryMB": "32710.902343",
        "SQLServerStartTime": "05/04/2018 08:22:30",
        "AffinityTypeDesc": "AUTO",
        "VirtualMachineType": "0",
        "SocketCount": "1",
        "CoresPerSocket": "4",
        "NumaNodeCount": "1",
        "ContainerType": "0",
        "ContainerDescription": "NONE"
      }
    ],
    "query": "SELECT\n      cpu_count AS LogicalCPUCount,\n      hyperthread_ratio AS HyperthreadRatio,\n      physical_memory_kb/1024.0 AS PhysicalMemoryMB,\n      sqlserver_start_time AS SQLServerStartTime,\n      affinity_type_desc AS AffinityTypeDesc,\n      virtual_machine_type AS VirtualMachineType,\n      socket_count as SocketCount,\n      cores_per_socket as CoresPerSocket,\n      numa_node_count as NumaNodeCount,\n      container_type as ContainerType,\n      container_type_desc as ContainerDescription\n      FROM sys.dm_os_sys_info WITH(nolock)",
    "queryTimeInTicks": 0
  }
]

よく寄せられる質問

DBA でローカル監査ログ ファイルを読み取るにはどうすればよいですか? Local Audit ログ ファイルは JSON 形式で出力されます。 各行は、Microsoft にアップロードされた 1 件の使用状況/診断データを表す JSON オブジェクトになります。 フィールドには、わかりやすい名前を付けることをお勧めします。

DBA が使用状況と診断データの収集を無効にするとどうなりますか? ローカル監査ファイルが出力されなくなります。

インターネット接続がない場合やコンピューターがファイアウォールの背後にある場合はどうなりますか? SQL Server の使用状況と診断データは Microsoft に送信されなくなります。 構成が正しければ、Local Audit ログの出力は試行されます。

DBA がローカル監査を無効にするにはどうすればよいですか? UserRequestedLocalAuditDirectory レジストリ キー エントリを削除します。

ローカル監査ログ ファイルを読み取ることができるのはだれですか? 組織内で、ローカル監査ディレクトリにアクセス権を持つ全員です。

指定したディレクトリに出力されたログ ファイルを DBA で管理するにはどうすればよいですか? ディスク領域を消費しすぎないように、ディレクトリ内にあるファイルのクリーンアップを DBA で自動管理する必要があります。

この JSON 出力を読み取るために使用できるクライアントまたはツールはありますか? 出力は、メモ帳、Visual Studio、JSON リーダーなど任意のツールで読み取ることができます。 または、次に示すように、JSON ファイルを読み取り、SQL Server インスタンス内のデータを分析することもできます。 SQL Server で JSON ファイルを読み取る方法の詳細については、「 Importing JSON files into SQL Server using OPENROWSET (BULK) and OPENJSON (Transact-SQL)」(OPENROWSET (BULK) と OPENJSON (Transact-SQL) を使用して JSON ファイルを SQL Server にインポートする) を参照してください。

DECLARE @JSONFile AS VARCHAR(MAX)

-- Read the JSON file into variable 
SELECT @JSONFile = BulkColumn 
FROM OPENROWSET (BULK 'C:\SQLCEIPAudit\MSSQLSERVER\2016-09-08.json', SINGLE_CLOB) MyFile 

-- Check if the JSON file has been read properly and if it's in a JSON format
SELECT 
	@JSONFile LocalAuditOutput, 
	ISJSON(@JSONFile) IsFileInJSONFormat

-- Get the query identifier, query and the data (output of the query)	
SELECT 
	sequence,
	queryIdentifier,
	query,
	data
FROM OPENJSON(@JSONFile) 
	WITH (sessionId VARCHAR(64)
		 ,sequence INT
		 ,queryIdentifier VARCHAR(128)
		 ,query VARCHAR(MAX)
		 ,data NVARCHAR(MAX) AS JSON)
-- Get specific details about the output of "DatabaseProperties.001" query	
SELECT 
	QueryIdentifier,
	DatabaseID,
	CompatibilityLevel,
	IsQueryStoreOn
FROM OPENJSON(@JSONFile) 
	WITH (sessionId VARCHAR(64)
		 ,sequence INT
		 ,queryIdentifier VARCHAR(128)
		 ,query VARCHAR(MAX)
		 ,data NVARCHAR(MAX) AS JSON) 
	CROSS APPLY OPENJSON(data) 
		WITH (	 DatabaseID varchar(128) '$.database_id'
				,CompatibilityLevel varchar(128) '$.compatibility_level'
				,IsQueryStoreOn varchar(128) '$.QS'
			 )
WHERE queryIdentifier = 'DatabaseProperties.001'

関連項目

SSMS の使用状況および診断データの収集のローカル監査