Auditoria local para utilização do SQL Server e recolha de dados de diagnóstico (CEIP)

Aplica-se a:SQL Server no Windows Azure SQL Managed Instance

Introdução

O Microsoft SQL Server contém funcionalidades com acesso à Internet que podem recolher e enviar informações sobre o seu computador ou dispositivo. Isto chama-se informação informática padrão. O componente de auditoria local da recolha de Dados de Utilização e Diagnóstico do SQL Server escreve os dados recolhidos pelo serviço numa pasta designada, representando os dados (logs) que serão enviados à Microsoft. O objetivo da auditoria local é permitir que os clientes vejam todos os dados recolhidos pela Microsoft com esta funcionalidade, por razões de conformidade, regulamentação ou validação de privacidade.

Para SQL Server 2016 CU2 e CU3, a auditoria local é configurável ao nível da instância para o SQL Server Database Engine and Analysis Services (SSAS). Para SQL Server 2016 CU4, 2016 SP1 e versões posteriores, a auditoria local também está ativada para SQL Server Integration Services (SSIS). Outros componentes do SQL Server que são instalados durante a Configuração e as Ferramentas SQL Server que são descarregadas ou instaladas após a Configuração não têm capacidade de auditoria local para utilização e recolha de dados de diagnóstico.

Observações

• Remover ou desativar o serviço SQL CEIP não é suportado.
• A remoção dos recursos SQL CEIP do Grupo de Cluster não é suportada.

A opção de não usar SQL Server e a recolha de dados de diagnóstico é suportada, mas o serviço não pode ser removido ou desativado. Para optar por não participar na recolha de dados, consulte Ativar ou desativar a auditoria local

Pré-requisitos

Seguem-se os pré-requisitos para permitir a auditoria local em cada instância do SQL Server:

1. A instância está atualizada para SQL Server 2016 RTM CU2 ou posterior. Para Serviços de Integração, a instância está atualizada para SQL 2016 RTM CU4, SQL 2016 SP1 ou versões posteriores.

2. O utilizador deve ser um Administrador de Sistema ou um cargo com acesso para adicionar e modificar a Chave do Registo, criar pastas, gerir a segurança das pastas e parar/iniciar um Serviço Windows.

Passos de pré-configuração antes de ativar a auditoria local

Antes de ativar a auditoria local, um administrador de sistemas precisa:

1. Saiba o nome da instância do SQL Server e a conta de login do serviço CEIP do SQL Server.

2. Configura uma nova pasta para os ficheiros de auditoria local.

3. Conceda permissões à conta de login do serviço CEIP do SQL Server.

4. Crie uma definição de chave de registo para configurar o diretório de alvo de auditoria local.

Obtenha a conta de login do serviço CEIP do SQL Server

Faça os seguintes passos para obter a conta de login do serviço CEIP do SQL Server

1. Inicia a consola de Serviços . Para isso, selecione a tecla Windows + R no teclado para abrir a caixa de diálogo Executar . De seguida, escreva services.msc no campo de texto e selecione OK para iniciar a consola Services .

2. Navegue para o serviço adequado. Por exemplo, para o motor de base de dados, localize SQL Server CEIP service(Nome-da-Sua-Instância). Para Serviços de Análise, localize SQL Server Analysis Services CEIP(Your-Instance-Name). Para os Serviços de Integração, localize o serviço CEIP dos Serviços de Integração do SQL Server.

3. Clique com o botão direito no serviço e escolha Propriedades.

4. Selecione a aba Início de Sessão. A conta de início de sessão está listada em Esta Conta.

Configura uma nova pasta para os ficheiros de auditoria local.

Crie uma nova pasta (diretório de auditoria local) onde a auditoria local irá escrever os registos. Por exemplo, o caminho completo para o Diretório de Auditoria local para uma instância padrão do motor de base de dados seria: C:\SQLCEIPAudit\MSSQLSERVER\DB\.

Conceder permissões à conta de login do serviço CEIP do SQL Server

1. No Explorador de Ficheiros, navegue até à localização onde está a nova pasta.

2. Clique com o botão direito na nova pasta e escolha Propriedades.

3. No separador Segurança, selecione Editar para gerir Permissões.

4. Selecione Adicionar e escreva as credenciais do Serviço CEIP do SQL Server. Por exemplo, NT Service\SQLTELEMETRY.

5. Selecione Verificar Nomes para validar o nome que forneceu, depois selecione OK.

6. Na caixa de diálogo Permissões, escolha a conta de iniciar sessão no serviço CEIP do SQL Server e selecione Listar conteúdos da pasta, Ler e Escrever.

7. Selecione OK para aplicar as alterações de permissões imediatamente.

Criar uma definição de chave de registo para configurar o diretório de alvo de auditoria local

1. Abra o regedit.

2. Navegue pelo caminho de CPE apropriado:

   Versão	Motor de base de dados - Chave de registo
   2016	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.O seu-Nome-de-Instância\CPE
   2017	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14. O Vosso-Nome-de-Instância\CPE
   2019	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL15. O Vosso-Nome-de-Instância\CPE

   Versão	Serviços de Análise - Chave do Registo
   2016	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS13.NomeDaInstância\CPE
   2017	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS14. O Vosso-Nome-de-Instância\CPE
   2019	HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSAS15. O Vosso-Nome-de-Instância\CPE

   Versão	Serviços de Integração - Chave do Registo
   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. Clique com o botão direito no caminho CPE e escolha Novo. Selecione Valor de String.

4. Nomeie a nova chave UserRequestedLocalAuditDirectorydo registo.

Ativar ou desativar a auditoria local

Depois de completares os passos de pré-configuração, podes ativar a auditoria local. Para isso, utilize uma conta de Administrador de Sistema ou um papel semelhante com acesso para modificar as Chaves do Registo para ativar ou desativar a auditoria local, seguindo os passos abaixo.

1. regedit.

2. Navegue até ao caminho de CPE apropriado.

3. Clique com o botão direito em UserRequestedLocalAuditDirectory e selecione Modificar.

4. Para ativar a auditoria local, escreva o caminho de auditoria local, por exemplo C:\SQLCEIPAudit\MSSQLSERVER\DB\.

   Para desativar a auditoria local, esvazie o valor em UserRequestedLocalAuditDirectory.

5. Fechar registo.

O CEIP do SQL Server deve reconhecer imediatamente a definição de auditoria local se o serviço já estiver a funcionar. Para iniciar o Serviço CEIP do SQL Server, um Administrador de Sistema ou alguém que tenha acesso para iniciar ou parar Serviços Windows pode seguir os passos abaixo:

1. Inicia a consola de Serviços . Para isso, selecione a tecla Windows + R no teclado para abrir a caixa de diálogo Executar . De seguida, escreva services.msc no campo de texto e selecione OK para iniciar a consola Services .

2. Navegue para o serviço adequado.

   • Para o Motor de Base de Dados, utilize o serviço CEIP do SQL Server (Seu-Nome-de-Instância).
   • Para Serviços de Análise, utilize SQL Server Analysis Services CEIP (Seu-Nome-de-Instância).
   • Para Serviços de Integração,
     • Para SQL 2016, utilize o serviço CEIP 13.0 dos Serviços de Integração SQL Server.
     • Para SQL 2017, utilize o serviço CEIP 14.0 dos Serviços de Integração do SQL Server.
   • Para SQL 2019, utilize o serviço CEIP 15.0 dos Serviços de Integração do SQL Server.

3. Clique com o botão direito no serviço e escolha Reiniciar.

4. Verifique se o estado do serviço está em Execução.

A auditoria local irá produzir um ficheiro de registo por dia. Os ficheiros de registo terão a forma de <YYYY-MM-DD>.json. Por exemplo, 2016-07-12.json. Se existir um ficheiro existente para o dia no diretório designado, a auditoria local será anexada a ele. Caso contrário, criará um novo ficheiro para o dia.

Maintenance

1. Para limitar o uso de espaço em disco pelos ficheiros escritos por auditoria local, estabeleça uma política ou um trabalho regular para limpar o diretório local de auditoria e remover ficheiros antigos e desnecessários.

2. Assegure o percurso do Diretório de Auditoria Local para que só seja acessível às pessoas apropriadas. Note que os ficheiros de registo contêm informações conforme descrito em Como configurar o SQL Server 2016 para enviar feedback à Microsoft. O acesso a este ficheiro deve impedir que a maioria dos membros da sua organização o leia.

Dicionário de dados da estrutura de dados de saída de auditoria local

• Os ficheiros de registo de auditoria local estão em JSON, contendo um conjunto de objetos (linhas) que representam pontos de dados que são enviados de volta à Microsoft no emitTime.
• Cada linha segue um esquema específico identificado por schemaVersion.
• Cada linha é uma saída de uma sessão de serviço SQLCEIP identificada como sessionID.
• As filas são emitidas em sequência, identificadas por sequência.
• Cada linha de ponto de dados contém a saída de um queryIdentifier, que pode ser uma consulta T-SQL, uma sessão XE ou uma mensagem relacionada com um tipo de traço, identificada como traceName.
• QueryIdentifiers são agrupados e versionados juntamente com querySetVersion.
• os dados contêm a saída da execução correspondente da consulta, que demorou queryTimeInTicks.
• Identificadores de consulta para consultas T-SQL têm a definição de consulta T-SQL armazenada na consulta.

Definição e exemplos de pares nome/valor

As colunas listadas abaixo representam a ordem de saída do ficheiro de auditoria local. O hash unidirecional com SHA 256 é usado para anonimizar valores de várias das colunas abaixo.

Categorias de rastreamento

Atualmente, recolhemos as seguintes categorias de traços:

• SQLServerXeQueries: contém pontos de dados recolhidos através da sessão de Evento Estendido.
• SQLServerPeriodicQueries: contém pontos de dados recolhidos através de consultas periódicas executadas numa instância do SQL Server.
• SQLServerPerDBPeriodicQueries: contém pontos de dados recolhidos através de consultas periódicas executadas para até 30 bases de dados numa instância SQL Server.
• SQLServerOneSettingsException: contém mensagens de exceção relacionadas com a atualização do esquema e/ou conjunto de consultas.
• DigitalProductID: contém pontos de dados para agregar ID de produto digital anonimizado (hash SHA-256) de instâncias do SQL Server.

Exemplos de ficheiros de auditoria locais

Abaixo está um excerto de um ficheiro JSON de uma auditoria 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
  }
]

Perguntas frequentes

Como é que os DBAs leem os ficheiros de registo de auditoria local? Estes ficheiros de registo são escritos em formato JSON. Cada linha será um objeto JSON que representa um dado de utilização/diagnóstico carregado para a Microsoft. Os nomes dos campos devem ser autoexplicativos.

O que acontece se o DBA desativar a recolha de dados de utilização e diagnóstico? Não será escrito nenhum ficheiro de auditoria local.

O que acontece se não houver ligação à internet ou se a máquina estiver atrás do firewall? O uso e os dados de diagnóstico do SQL Server não serão enviados à Microsoft. Ainda assim, tentará escrever os registos de auditoria locais se estiver configurado corretamente.

Como é que os DBAs desativam a auditoria local? Remover a entrada da chave de registo UserRequestedLocalAuditDirectory.

Quem consegue ler os ficheiros de registo da auditoria local? Qualquer pessoa da sua organização que tenha acesso ao Diretório de Auditoria local.

Como é que os DBAs gerem os ficheiros de registo escritos no diretório designado? Os DBAs terão de gerir por si próprios a limpeza dos ficheiros no diretório para evitar consumir demasiado espaço em disco.

Existe algum cliente ou ferramenta que eu possa usar para ler esta saída JSON? A saída pode ser lida com Notepad, Visual Studio ou qualquer leitor JSON à sua escolha. Em alternativa, pode ler o ficheiro JSON e analisar os dados numa instância SQL Server, como ilustrado abaixo. Mais detalhes sobre como ler ficheiros JSON no SQL Server, por favor visite Importar ficheiros JSON para SQL Server usando OPENROWSET (BULK) e 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 também

Auditoria local para utilização do SSMS e recolha de dados de diagnóstico