Auditoria local para coleta de dados de diagnóstico e uso do SQL Server (Programa de Aperfeiçoamento da Experiência do Usuário)

Aplica-se a:SQL Server – Somente Windows Instância Gerenciada de SQL do Azure

Introdução

O Microsoft SQL Server contém recursos habilitados para Internet que podem coletar e enviar à Microsoft informações sobre seu computador ou dispositivo. Isso é chamado de informações padrão do computador. O componente de auditoria local da Coleta Dados de Diagnóstico e Uso do SQL Server grava dados coletados pelo serviço em uma pasta designada, que representa os dados (logs) que serão enviados à Microsoft. A finalidade da auditoria local é permitir que os clientes vejam todos os dados que a Microsoft coleta com esse recurso, para fins de conformidade, regulatórios ou de validação de privacidade. 

Para o SQL Server 2016 CU2 e CU3, a auditoria Local é configurável no nível da instância no Mecanismo de Banco de Dados do SQL Server e o SSAS (Analysis Services). Para o SQL Server 2016 CU4, 2016 SP1 e versões mais recentes, a auditoria local também está habilitada para o SSIS (SQL Server Integration Services). Outros componentes do SQL Server que são instalados durante a instalação e as ferramentas do SQL Server que são baixadas ou instaladas após a instalação não têm o recurso de auditoria local para coleta de dados de diagnóstico e uso.

Comentários

• Não há suporte para remover ou desabilitar o serviço Programa de Aperfeiçoamento da Experiência do Usuário do SQL.
• Não há suporte para remover os recursos do Programa de Aperfeiçoamento da Experiência do Usuário do SQL do Grupo de Clusters.

É possível recusar o uso e a coleta de dados de diagnóstico do SQL Server, mas o serviço não pode ser removido ou desabilitado. Para recusar a coleta de dados, veja Ativando ou desativando a auditoria local

Pré-requisitos

A seguir estão os pré-requisitos para habilitar a auditoria local em cada instância do SQL Server:

1. A instância está corrigida para o SQL Server 2016 RTM CU2 ou posterior. Para o Integration Services, o patch da instância é aplicado ao SQL 2016 RTM CU4, ao SQL 2016 SP1 ou a versões mais recentes.

2. O usuário deve ser um administrador do sistema ou ter uma função com acesso para adicionar e modificar a Chave do Registro, criar pastas, gerenciar a segurança das pastas e parar/iniciar um Serviço do Windows. 

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

Antes de ativar a auditoria local, um administrador do sistema deve:

1. Conhecer o nome da instância do SQL Server e a conta de logon do serviço do Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server.

2. Configurar uma nova pasta para os arquivos de auditoria local.

3. Conceder permissões para a conta de logon do serviço do Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server.

4. Criar uma configuração de chave do Registro para configurar o diretório de destino da auditoria local.

Obter a conta de logon de serviço do Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server

Execute as seguintes etapas para obter a conta de logon do serviço do Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server

1. Inicie o console Serviços. Para fazer isso, selecione a tecla Windows + R no teclado para abrir a caixa de diálogo Executar. Em seguida, digite services.msc no campo de texto e selecione OK para iniciar o console Serviços.

2. Navegue até o serviço apropriado. Por exemplo, para o mecanismo de banco de dados, localize o Serviço do Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server(nome da instância). Para o Analysis Services, localize Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server Analysis Services(nome da instância). Para o Integration Services, localize o serviço do Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server Integration Services.

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

4. Selecione a guia Fazer logon. A conta de Logon está listada em Esta Conta.

Configurar uma nova pasta para os arquivos de auditoria local.

Crie uma nova pasta (diretório da auditoria local) em que a auditoria local gravará os logs. Por exemplo, o caminho completo para o Diretório da auditoria local para uma instância padrão do mecanismo de banco de dados seria: C:\SQLCEIPAudit\MSSQLSERVER\DB\. 

Conceder permissões para a conta de logon do serviço do Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server

1. No Explorador de Arquivos, navegue até o local em que a nova pasta está localizada.

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

3. Na guia Segurança, selecione Editar e gerenciar Permissão.

4. Selecione Adicionar e digite as credenciais do Serviço do Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server. Por exemplo, NT Service\SQLTELEMETRY.

5. Selecione Verificar Nomes para validar o nome fornecido e selecione OK.

6. Na caixa de diálogo Permissão, escolha a conta de logon ao Serviço do Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server e selecione Listar conteúdo da pasta, Ler e Gravar.

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

Criar uma configuração de chave do Registro para configurar o diretório de destino da auditoria local

1. Inicie o regedit.

2. Navegue até o caminho do CPE apropriado:

   Versão	Mecanismo de banco de dados – chave do Registro
   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

   Versão	Analysis Services – chave do Registro
   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

   Versão	Integration Services – chave do 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. Clique com o botão direito do mouse no caminho do CPE e escolha Novo. Selecione Valor de cadeia de caracteres.

4. Nomeie a nova chave do Registro UserRequestedLocalAuditDirectory.

Ativando ou desativando a auditoria local

Depois de concluir as etapas de pré-configuração, você pode ativar a auditoria local. Para fazer isso, use uma conta de administrador do sistema ou com uma função semelhante com acesso para modificar chaves de registro para ativar ou desativar a auditoria local seguindo as etapas abaixo.

1. Inicie o regedit. 

2. Navegue até o caminho do CPE apropriado.

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

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

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

5. Feche o regedit.

O Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server deve reconhecer a configuração da auditoria local imediatamente se o serviço já estiver em execução. Para iniciar o serviço do Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server, o administrador do sistema ou alguém que tenha acesso para iniciar ou parar Serviços do Windows pode seguir as etapas abaixo:

1. Inicie o console Serviços. Para fazer isso, selecione a tecla Windows + R no teclado para abrir a caixa de diálogo Executar. Em seguida, digite services.msc no campo de texto e selecione OK para iniciar o console Serviços.

2. Navegue até o serviço apropriado.

   • Para o Mecanismo de Banco de Dados, use Serviço do Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server (nome da instância).
   • Para o Analysis Services, use Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server Analysis Services (nome da instância).
   • Para o Integration Services,
     • Para o SQL 2016, use Serviço do Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server Integration Services 13.0.
     • Para o SQL 2017, use Serviço do Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server Integration Services 14.0.
   • No SQL 2019, use Serviço do Programa de Aperfeiçoamento da Experiência do Usuário do SQL Server Integration Services 15.0.

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

4. Verifique se o status do serviço é Executando.

A auditoria local produzirá um arquivo de log por dia. Os arquivos de log estarão na forma de <YYYY-MM-DD>.json. Por exemplo, 2016-07-12.json. Se houver um arquivo existente para o dia no diretório designado, a auditoria local acrescentará a ele. Caso contrário, criará um novo arquivo para o dia.

Manutenção

1. Para limitar o uso do espaço em disco pelos arquivos gravados pela auditoria local, configure uma política ou um trabalho regular para limpar o Diretório da auditoria local para remover arquivos antigos e desnecessários. 

2. Proteja o caminho do Diretório da auditoria local para que ele seja acessível apenas para as pessoas apropriadas. Observe que os arquivos de log contêm as informações descritas em Como configurar o SQL Server 2016 para enviar comentários à Microsoft. O acesso a este arquivo deve impedir que a maioria dos membros de sua organização o leiam. 

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

• Os arquivos de log da auditoria local estão em JSON, contendo um conjunto de objetos (linhas) que representam pontos de dados que são enviados de volta para a Microsoft em emitTime.
• Cada linha segue um esquema específico identificado por schemaVersion.
• Cada linha é uma saída de uma sessão de serviços SQLCEIP identificada como sessionID.
• As linhas são emitidas em sequência, identificadas por sequence.
• 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 a um tipo de rastreamento, identificado como traceName.
• queryIdentifiers são agrupados e sua versão é controlada em conjunto com querySetVersion.
• data contêm a saída da execução da consulta correspondente que levou queryTimeInTicks.
• queryIdentifiers para consultas T-SQL têm a definição da consulta T-SQL armazenada na consulta.

Definição e exemplos de pares nome/valor

As colunas listadas abaixo representam a ordem da saída de arquivos da auditoria local. Hash unidirecional com SHA 256 é usado para valores mantidos em anonimato para várias das colunas abaixo. 

Categorias de rastreamento

Atualmente, coletamos as seguintes categorias de rastreamento:

• SQLServerXeQueries: contém pontos de dados coletados por meio da sessão de Evento Estendido.
• SQLServerPeriodicQueries: contém pontos de dados coletados por meio de consultas periódicas executadas em uma instância do SQL Server.
• SQLServerPerDBPeriodicQueries: contém pontos de dados coletados por meio de consultas periódicas executadas em até 30 bancos de dados em uma instância do SQL Server.
• SQLServerOneSettingsException: contém mensagens de exceção relacionadas à atualização do esquema e/ou do conjunto de consultas.
• DigitalProductID: contém pontos de dados para agregar IDs de produtos digitais com hash (SHA-256) anônimos de instâncias do SQL Server.

Exemplos de arquivos da auditoria local

Abaixo, temos um trecho de uma saída de arquivo JSON da 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 os DBAs leem os arquivos de log da auditoria local? Esses arquivos de log são gravados no formato JSON. Cada linha será um objeto JSON que representa um trecho de dados de diagnóstico/uso carregado para a Microsoft. Os nomes dos campos devem ser autoexplicativos.

O que acontece se o DBA desabilitar a coleta de dados de diagnóstico e uso? Nenhum arquivo da auditoria local será gravado.

O que acontece se não houver conectividade com a internet/o computador estiver protegido pelo firewall? Os dados de diagnóstico e uso do SQL Server não serão enviados à Microsoft. Ele ainda tentará gravar os logs de auditoria local, se estiver configurado corretamente.

Como os DBAs desabilitam a auditoria local? Remova a entrada da chave do registro UserRequestedLocalAuditDirectory.

Quem pode ler os arquivos de log da auditoria local? Qualquer pessoa em sua organização que tiver acesso ao Diretório da auditoria local.

Como os DBAs gerenciam os arquivos de log gravados no diretório designado? Os DBAs precisarão gerenciar automaticamente a limpeza de arquivos no diretório para evitar o consumo de muito espaço em disco.

Há um cliente ou ferramenta que posso usar para ler esta saída JSON? A saída pode ser lida com o Bloco de notas, o Visual Studio ou qualquer leitor JSON de sua escolha. Você também pode ler o arquivo JSON e analisar os dados em uma instância do SQL Server, conforme ilustrado abaixo. Para obter mais detalhes sobre como ler o arquivo JSON no SQL Server, visite Importing JSON files into SQL Server using OPENROWSET (BULK) and OPENJSON (Transact-SQL)(Importar arquivos JSON para o 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'

Confira também

Auditoria local para coleta de dados de diagnóstico e uso do SSMS