Auditoría local para la recopilación de datos de uso y diagnóstico de SQL Server (CEIP)

Se aplica a: SQL Server: solo Windows Azure SQL Managed Instance

Introducción

Microsoft SQL Server contiene características habilitadas para Internet que pueden recopilar y enviar información sobre el equipo o dispositivo. Esto se denomina información estándar del equipo. El componente de auditoría local de recopilación de datos de uso y diagnóstico de SQL Server escribe los datos que recopila el servicio en una carpeta designada y que representa los datos (registros) que se enviarán a Microsoft. El propósito de la auditoría local es permitir que los clientes vean todos los datos que Microsoft recopila con esta característica, por motivos de cumplimiento, reglamentarios o por validación de privacidad.

A partir de SQL Server 2016 CU2 y CU3, la auditoría local se puede configurar en el nivel de instancia para Motor de base de datos de SQL Server y Analysis Services (SSAS). En SQL Server 2016 CU4, 2016 SP1 y versiones posteriores, la auditoría local también está habilitada para SQL Server Integration Services (SSIS). Otros componentes de SQL Server que se instalan durante la configuración y herramientas de SQL Server que se descargan o instalan después de la configuración no cuentan con la funcionalidad de auditoría local para la recopilación de datos de uso y diagnóstico.

Observaciones

• No se admite la eliminación o deshabilitación del servicio CEIP de SQL.
• No se admite la eliminación de los recursos de CEIP de SQL del Grupo de clúster.

Es posible deshabilitar la recopilación de datos de uso y diagnóstico de SQL Server, pero el servicio no se puede quitar ni deshabilitar. Para no participar en la recopilación de datos, vea Activar o desactivar la auditoría local.

Prerrequisitos

A continuación, aparecen los requisitos previos para habilitar la auditoría local en cada instancia de SQL Server:

1. La instancia aplica una revisión a SQL Server 2016 RTM CU2 o a una versión posterior. En el caso de Integration Services, la revisión de la instancia se aplica a SQL 2016 RTM CU4, SQL 2016 SP1 o versiones posteriores.

2. El usuario debe ser un administrador del sistema o debe tener un rol con acceso para agregar y modificar una clave del Registro, crear carpetas, administrar la seguridad de las carpetas y detener e iniciar un servicio de Windows.

Pasos de configuración previa antes de activar la auditoría local

Antes de activar la auditoría local, un administrador del sistema debe:

1. Saber el nombre de la instancia de SQL Server y la cuenta de inicio de sesión del servicio CEIP de SQL Server.

2. Configure una carpeta nueva para los archivos de auditoría local.

3. Conceda permisos a la cuenta de inicio de sesión del servicio CEIP de SQL Server.

4. Cree una configuración de clave del Registro para configurar el directorio de destino de auditoría local.

Obtención de la cuenta de inicio de sesión del servicio CEIP de SQL Server

Complete los pasos siguientes para obtener la cuenta de inicio de sesión del servicio CEIP de SQL Server

1. Abra la consola Servicios. Para ello, presione la tecla Windows + R en el teclado para abrir el cuadro de diálogo Ejecutar. Después, escriba services.msc en el campo de texto y seleccione Aceptar para abrir la consola Servicios.

2. Vaya al servicio correspondiente. Por ejemplo, para el motor de base de datos, ubique Servicio CEIP de SQL Server (Nombre-Instancia). En el caso de Analysis Services, busque CEIP de SQL Server Analysis Services (Nombre-Instancia). En el caso de Integration Services, busque Servicio CEIP para SQL Server Integration Services.

3. Haga clic con el botón derecho en el servicio y elija Propiedades.

4. Seleccione la pestaña Iniciar sesión. La cuenta de inicio de sesión aparece en Esta cuenta.

Configure una carpeta nueva para los archivos de auditoría local.

Cree una carpeta nueva (directorio de auditoría local) donde la uditoría local escribirá los registros. Por ejemplo, la ruta de acceso completa al directorio de auditoría local para una instancia predeterminada del motor de la base de datos sería la siguiente: C:\SQLCEIPAudit\MSSQLSERVER\DB\.

Concesión de permisos a la cuenta de inicio de sesión del servicio de CEIP de SQL Server

1. En Explorador de archivos, vaya a la ubicación donde se encuentra la carpeta nueva.

2. Haga clic con el botón derecho en la carpeta nueva y elija Propiedades.

3. En la pestaña Seguridad, seleccione Editar permiso Administrar.

4. Seleccione Agregar y escriba las credenciales del servicio CEIP de SQL Server. Por ejemplo, NT Service\SQLTELEMETRY.

5. Seleccione Comprobar nombres para validar el nombre que se proporcionó y, después, seleccione Aceptar.

6. En el cuadro de diálogo Permiso, elija la cuenta de inicio de sesión en el servicio CEIP de SQL Server y seleccione Mostrar el contenido de la carpeta, Lectura y Escritura.

7. Seleccione Aceptar para aplicar de inmediato los cambios en los permisos.

Creación de una configuración de clave del Registro para configurar el directorio de destino de auditoría local

1. Inicie regedit.

2. Vaya a la ruta de CPE correspondiente:

   Versión	Motor de base de datos: clave del Registro
   2016	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.nombre-de-instancia\CPE
   2017	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.nombre-de-instancia\CPE
   2019	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15.nombre-de-instancia\CPE

   Versión	Analysis Services: clave del Registro
   2016	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS13.nombre-de-instancia\CPE
   2017	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS14.nombre-de-instancia\CPE
   2019	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS15.nombre-de-instancia\CPE

   Versión	Integration Services: clave del Registro
   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. Haga clic con el botón derecho en la ruta de CPE y elija Nuevo. Seleccione Valor de cadena.

4. Ponga un nombre a la nueva clave de registro UserRequestedLocalAuditDirectory.

Activación o desactivación de la auditoría local

Una vez que haya seguido los pasos de configuración previa, puede activar la auditoría local. Para ello, use una cuenta de administrador del sistema o un rol similar con acceso para modificar las claves del Registro a fin de activar o desactivar la auditoría local con los pasos siguientes.

1. Inicie regedit.

2. Vaya a la ruta de CPE correspondiente.

3. Haga clic con el botón derecho en UserRequestedLocalAuditDirectory y seleccione Modificar.

4. Para activar la auditoría local, escriba la ruta de acceso a la auditoría local; por ejemplo, C:\SQLCEIPAudit\MSSQLSERVER\DB\.

   Para desactivar la auditoría local, vacíe el valor de UserRequestedLocalAuditDirectory.

5. Cierre regedit.

CEIP de SQL Server debe reconocer inmediatamente la configuración de auditoría local si el servicio ya está en ejecución. Para iniciar el servicio CEIP de SQL Server, un administrador del sistema o alguna persona con acceso para iniciar o detener los servicios de Windows puede seguir estos pasos:

1. Abra la consola Servicios. Para ello, presione la tecla Windows + R en el teclado para abrir el cuadro de diálogo Ejecutar. Después, escriba services.msc en el campo de texto y seleccione Aceptar para abrir la consola Servicios.

2. Vaya al servicio correspondiente.

   • Para Motor de base de datos, use Servicio CEIP de SQL Server (Nombre-Instancia).
   • Para Analysis Services, use CEIP de SQL Server Analysis Services (Nombre-Instancia).
   • Para Integration Services,
     • Para SQL 2016, use Servicio CEIP para SQL Server Integration Services 13.0.
     • Para SQL 2017, use Servicio CEIP para SQL Server Integration Services 14.0.
   • Para SQL 2019, use Servicio CEIP para SQL Server Integration Services 15.0.

3. Haga clic con el botón derecho en el servicio y elija Reiniciar.

4. Compruebe que el estado del servicio sea En ejecución.

La auditoría local generará un archivo de registro al día. Los archivos de registro tendrán este formato: <YYYY-MM-DD>.json. Por ejemplo, 2016-07-12.json. Si ya hay un archivo correspondiente al día en cuestión en el directorio designado, la auditoría local lo anexará a este. En caso contrario, se creará un archivo nuevo correspondiente al día.

Mantenimiento

1. Para limitar el uso del espacio en disco que ocupan los archivos que escribe la auditoría local, configure una directiva o un trabajo periódico para limpiar el directorio de Auditoría local y quitar los archivos antiguos e innecesarios.

2. Proteja la ruta de acceso al directorio de la auditoría local para que solo las personas adecuadas puedan tener acceso a él. Tenga en cuenta que los archivos de registro contienen información según lo descrito en Cómo configurar SQL Server 2016 para enviar comentarios a Microsoft. El acceso a este archivo debe evitar que la mayoría de los miembros de su organización pueda leerlo.

Diccionario de datos de la estructura de datos de salida de la auditoría local

• Los archivos de registro de auditoría local tienen el formato JSON y contienen un conjunto de objetos (filas) que representan puntos de datos que se envían de vuelta a Microsoft en emitTime.
• Cada fila sigue un esquema específico que se identifica por schemaVersion.
• Cada fila es una salida de una sesión de servicio de SQLCEIP que se identifica como sessionID.
• Las filas se emiten en una secuencia que se identifica por sequence.
• Cada fila de punto de datos contiene la salida de un queryIdentifier, que puede ser una consulta T-SQL, una sesión de XE o un mensaje relacionado con un tipo de seguimiento, identificado como traceName.
• Los valoresqueryIdentifiers están agrupados y tienen versiones junto con querySetVersion.
• Los valoresdata contienen la salida de la ejecución de consulta correspondiente, que usó queryTimeInTicks.
• Los valoresqueryIdentifiers para consultas T-SQL tienen la definición de consulta T-SQL almacenada en la consulta.

Definición y ejemplos de pares nombre-valor

Las columnas siguientes representan el orden de la salida de archivo de auditoría local. Se usa un hash unidireccional con SHA 256 para anonimizar los valores para varias de las columnas siguientes.

Categorías de seguimiento

Actualmente recopilamos las siguientes categorías de seguimiento:

• SQLServerXeQueries: contiene puntos de datos recopilados a través de una sesión de eventos extendidos.
• SQLServerPeriodicQueries: contiene puntos de datos recopilados a través de consultas periódicas ejecutadas en una instancia de SQL Server.
• SQLServerPerDBPeriodicQueries: contiene puntos de datos recopilados a través de consultas periódicas ejecutadas en hasta 30 bases de datos en una instancia de SQL Server.
• SQLServerOneSettingsException: contiene mensajes de excepción relacionados con la actualización de un esquema o un conjunto de consultas.
• DigitalProductID: contiene puntos de datos para agregar el id. de producto digital con hash anónimo (SHA-256) de instancias de SQL Server.

Ejemplos de archivos de auditoría local

A continuación, aparece un fragmento de una salida de archivo JSON de auditoría local.

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

Preguntas más frecuentes

¿De qué manera los administradores de bases de datos leen los archivos de registro de auditoría local? Estos archivos de registro están escritos en formato JSON. Cada línea será un objeto JSON que representa una parte de los datos de uso o diagnóstico que se cargaron en Microsoft. Los nombres de los campos deben ser claros.

¿Qué ocurre si el administrador de base de datos deshabilita la recopilación de datos de uso y diagnóstico? No se escribirá ningún archivo de auditoría local.

¿Qué ocurre si no hay conectividad a Internet o si la máquina está detrás de un firewall? No se enviarán datos de uso y diagnóstico de SQL Server a Microsoft. De todos modos, se intentarían escribir los registros de Auditoría local si la configuración es correcta.

¿De qué manera los administradores de bases de datos deshabilitan la auditoría local? Deshabilitan la entrada de la clave del Registro UserRequestedLocalAuditDirectory.

¿Quiénes pueden leer los archivos de registro de auditoría local? Cualquier persona de la organización que cuente con acceso al directorio de auditoría local.

¿De qué manera los administradores de bases de datos administran los archivos de registro escritos en el directorio designado? Los administradores de bases de datos deberán administrar automáticamente la limpieza de los archivos del directorio para evitar que se use demasiado espacio en disco.

¿Existe algún cliente o herramienta que se pueda usar para leer esta salida JSON? Es posible leer la entrada con el Bloc de notas, Visual Studio o cualquier lector de JSON de su preferencia. Como alternativa, puede leer el archivo JSON y analizar los datos en una instancia de SQL Server como se muestra a continuación. Para más detalles sobre cómo leer un archivo JSON en SQL Server, visite Importing JSON files into SQL Server using OPENROWSET (BULK) and OPENJSON (Transact-SQL)(Importación de archivos JSON a SQL Server con OPENROWSET [BULK] y 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'

Consulte también

Auditoría local para la recopilación de datos de uso y diagnóstico de SSMS