Local audit for SQL Server usage and diagnostic data collection (CEIP)

Applies to: SQL Server - Windows only Azure SQL Managed Instance

Introduction

Microsoft SQL Server contains Internet-enabled features that can collect and send information about your computer or device. This is called standard computer information. The local audit component of SQL Server Usage and Diagnostic Data collection writes data collected by the service to a designated folder, representing the data (logs) that will be sent to Microsoft. The purpose of the local audit is to allow customers to see all data Microsoft collects with this feature, for compliance, regulatory or privacy validation reasons. 

For SQL Server 2016 CU2 and CU3, local audit is configurable at the instance level for SQL Server Database Engine and Analysis Services (SSAS). For SQL Server 2016 CU4, 2016 SP1, and later releases, local audit is also enabled for SQL Server Integration Services (SSIS). Other SQL Server components that get installed during Setup and SQL Server Tools that are downloaded or installed after Setup do not have local audit capability for usage and diagnostic data collection.

Remarks

• Removing or disabling the SQL CEIP service is not supported.
• Removing the SQL CEIP resources from the Cluster Group is not supported.

Opting out of SQL Server usage and diagnostic data collection is supported, but the service can't be removed or disabled. To opt out of the data collection, see Turning local audit on or off

Prerequisites

The following are prerequisites to enable local audit on each SQL Server instance:

1. The instance is patched to SQL Server 2016 RTM CU2 or later. For Integration Services, the instance is patched to SQL 2016 RTM CU4, SQL 2016 SP1, or later.

2. User must be a System Administrator or a role with access to add and modify Registry Key, create folders, manage folder security and stop/start a Windows Service. 

Pre-configuration steps prior to turning on local audit

Before turning on local audit, a system administrator needs to:

1. Know the SQL Server instance name and the SQL Server CEIP service logon account.

2. Configure a new folder for the local audit files.

3. Grant permissions to the SQL Server CEIP service logon account.

4. Create a registry key setting to configure local audit target directory.

Get the SQL Server CEIP service logon account

Do the following steps to get the SQL Server CEIP service logon account

1. Launch the Services console. To do this, select the Windows Key + R on your keyboard to open the Run dialog box. Next, type in services.msc in the text field and select OK to launch the Services console.

2. Navigate to the appropriate service. For example, for the database engine locate SQL Server CEIP service (Your-Instance-Name). For Analysis Services locate SQL Server Analysis Services CEIP (Your-Instance-Name). For Integration Services locate SQL Server Integration Services CEIP service.

3. Right-click on the service and choose Properties.

4. Select the Log On tab. The Logon account is in listed in This Account.

Configure a new folder for the local audit files.

Create a new folder (local audit directory) where the local audit will write the logs. For example, the complete path to the local audit Directory for a default instance of the database engine would be: C:\SQLCEIPAudit\MSSQLSERVER\DB\. 

Grant permissions to the SQL Server CEIP service logon account

1. In File Explorer, navigate to the location where the new folder is located.

2. Right-click the new folder and choose Properties.

3. On the Security tab, select Edit manage Permission.

4. Select Add and type the credentials of the SQL Server CEIP Service. For example NT Service\SQLTELEMETRY.

5. Select Check Names to validate the name you provided, then select OK.

6. On the Permission dialog box, choose the Log On account to SQL Server CEIP service and select List folder contents, Read and Write.

7. Select OK to apply the permission changes immediately.

Create a registry key setting to configure local audit target directory

1. Launch regedit.

2. Navigate to the appropriate CPE path:

   Version	Database engine - Registry key
   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

   Version	Analysis Services - Registry key
   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

   Version	Integration Services - Registry key
   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. Right-click the CPE path and choose New. Select String Value.

4. Name the new registry key UserRequestedLocalAuditDirectory.

Turning local audit on or off

After you have completed the preconfiguration steps, you can turn on local audit. To do this, use a System Administrator account or a similar role with access to modifying Registry Keys to turn local audit on or off by following the steps below.

1. Launch regedit. 

2. Navigate to the appropriate CPE path.

3. Right-click UserRequestedLocalAuditDirectory and select Modify.

4. To turn on local audit, type the local audit path, for example C:\SQLCEIPAudit\MSSQLSERVER\DB\.

   To turn off local audit, empty the value in UserRequestedLocalAuditDirectory.

5. Close regedit.

SQL Server CEIP should recognize the local audit setting immediately if the service is already running. To start the SQL Server CEIP Service, a System Administrator or someone who has access to start or stop Windows Services can follow the steps below:

1. Launch the Services console. To do this, select the Windows Key + R on your keyboard to open the Run dialog box. Next, type in services.msc in the text field and select OK to launch the Services console.

2. Navigate to the appropriate service.

   • For Database Engine, use SQL Server CEIP service (Your-Instance-Name).
   • For Analysis Services, use SQL Server Analysis Services CEIP (Your-Instance-Name).
   • For Integration Services,
     • For SQL 2016, use SQL Server Integration Services CEIP service 13.0.
     • For SQL 2017, use SQL Server Integration Services CEIP service 14.0.
   • For SQL 2019, use SQL Server Integration Services CEIP service 15.0.

3. Right-click on the service and choose Restart.

4. Verify that the status of the service is Running.

Local audit will produce one log file per day. The log files will be in a form of <YYYY-MM-DD>.json. For example, 2016-07-12.json. If there is an existing file for the day in the designated directory, local audit will append to it. Otherwise, it will create a new file for the day.

Maintenance

1. To limit disk space usage by the files written by local audit, set up a policy or a regular job to clean up the local audit Directory to remove older, unneeded files. 

2. Secure the local audit Directory path so that it is only accessible by the appropriate people. Note that the log files contain information as outlined in How to configure SQL Server 2016 to send feedback to Microsoft. Access to this file should prevent most members of your organization from reading it. 

Data dictionary of local audit output data structure

• Local audit log files are in JSON, containing a set of objects (rows) representing data points that are sent back to Microsoft at emitTime.
• Each row follows a specific schema identified by schemaVersion.
• Each row is an output of a SQLCEIP service session identified as sessionID.
• Rows are emitted in sequence, identified by sequence.
• Each data point row contains the output of a queryIdentifier, which can be a T-SQL query, an XE session or a message related to a type of trace, identified as traceName.
• queryIdentifiers are grouped and versioned together with querySetVersion.
• data contains the output of the corresponding query execution, which took queryTimeInTicks.
• queryIdentifiers for T-SQL queries have the T-SQL query definition stored in query.

Name/value pairs definition and examples

The columns listed below represent the order of the local audit file output. One-way hash with SHA 256 is used to anonymize values for a number of the columns below. 

Trace categories

Currently we collect the following trace categories:

• SQLServerXeQueries: contains data points collected through Extended Event session.
• SQLServerPeriodicQueries: contains data points collected through periodic queries executed in a SQL Server instance.
• SQLServerPerDBPeriodicQueries: contains data points collected through periodic queries executed to up to 30 databases in a SQL Server instance.
• SQLServerOneSettingsException: contains exception messages related to updating schema and/or query set.
• DigitalProductID: contains data points for aggregating anonymized (SHA-256) hashed digital product ID of SQL Server instances.

Local audit file examples

Below is an excerpt of a JSON file output of local audit.

[
  {
    "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
  }
]

Frequently asked questions

How do DBAs read the local audit log files? These log files are written in JSON format. Each line will be a JSON object representing a piece of usage / diagnostic data uploaded to Microsoft. The fields names should be self-explanatory.

What happens if the DBA disables usage and diagnostic data collection? No local audit file will be written.

What happens if there is not internet connectivity/machine is behind the firewall? SQL Server usage and diagnostic data will not be sent to Microsoft. It will still try to write the local audit logs if configured correctly.

How do DBAs disable local audit? Remove the UserRequestedLocalAuditDirectory registry key entry.

Who can read the local audit log files? Anyone in your organization that has access to the local audit Directory.

How do DBAs manage the log files written to the designated directory? DBAs will need to self-manage the clean-up of the files in the directory to avoid consuming too much disk space.

Is there a client or tool that I can use to read this JSON output? The output can be read with Notepad, Visual Studio, or any JSON reader of your choice. Alternatively, you can read the JSON file and analyze the data in an SQL Server instance as illustrated below. More details on how to read JSON file in SQL Server, please visit Importing JSON files into SQL Server using OPENROWSET (BULK) and OPENJSON (Transact-SQL).

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'

See also

Local audit for SSMS usage and diagnostic data collection