Локальный аудит для сбора данных об использовании и данных диагностики (CEIP) SQL Server

Локальный аудит для сбора данных об использовании и данных диагностики (CEIP) SQL Server

Microsoft SQL Server включает в себя функции, которые могут собирать и отправлять в Майкрософт сведения о компьютере или устройстве пользователя через Интернет. Эти сведения называются стандартными сведениями о компьютере. Компонент локального аудита для сбора данных об использовании и диагностических данных SQL Server записывает собранные службой данные в указанную папку. Эти данные (журналы) будут отправлены в корпорацию Майкрософт. Локальный аудит позволяет клиентам просматривать все данные, которые корпорация Майкрософт собирает с помощью этой функции для обеспечения соответствия, выполнения нормативных требований или соблюдения конфиденциальности.

Для SQL Server 2016 с накопительным пакетом обновления 2 и накопительным пакетом обновления 3 локальный аудит настраивается на уровне экземпляра для ядра СУБД SQL Server и служб Analysis Services (SSAS). Для SQL Server 2016 с накопительным пакетом обновления 4, SQL Server 2016 с пакетом обновления 1 (SP1) и более поздних выпусков локальный аудит также включен для служб SQL Server Integration Services (SSIS). Другие устанавливаемые компоненты SQL Server и средства SQL Server, которые скачиваются или устанавливаются позднее, не имеют функций локального аудита для сбора данных об использовании и диагностических данных.

Remarks

  • Удаление или отключение службы SQL CEIP не поддерживается.
  • Удаление ресурсов SQL CEIP из группы кластера не поддерживается.

Чтобы отказаться от сбора данных, см. сведения в разделе Отключение локального аудита

Предварительные требования

Ниже приведены предварительные требования для включения локального аудита на каждом экземпляре SQL Server:

В экземпляре установлены исправления для SQL Server 2016 RTM с накопительным пакетом обновления 2 или более поздней версии. Для служб Integration Services в экземпляре установлены исправления для SQL 2016 RTM с накопительным пакетом обновления 4, SQL 2016 с пакетом обновления 1 (SP1) или более поздних выпусков.

Пользователь должен быть системным администратором или использовать роль, позволяющую добавлять и изменять раздел реестра, создавать папки, управлять их безопасностью, а также останавливать и запускать службу Windows.

Шаги предварительной настройки перед включением локального аудита

Перед включением локального аудита системный администратор должен сделать следующее:

Получить имя экземпляра SQL Server, а также учетную запись для входа в службу CEIP для SQL Server.

Настройте новую папку для файлов локального аудита.

Предоставить разрешения для учетной записи входа в службу CEIP для SQL Server.

Создать раздел реестра для настройки целевого каталога локального аудита.

Получение учетной записи для входа в службу CEIP для SQL Server

Чтобы получить учетную запись для входа в службу CEIP для SQL Server, сделайте следующее:

Запустите консоль Службы. Для этого нажмите клавиши Windows+R, чтобы открыть диалоговое окно Выполнить. Введите в текстовом поле services.msc и нажмите кнопку ОК, чтобы запустить консоль Службы.

Перейдите к соответствующей службе. Например, для ядра СУБД найдите строку Служба CEIP для SQL Server (имя экземпляра) . Для служб Analysis Services найдите строку CEIP для служб SQL Server Analysis Services (имя экземпляра) . Для служб Integration Services найдите службу CEIP для служб SQL Server Integration Services.

Щелкните правой кнопкой службу и выберите Свойства.

Откройте вкладку Вход в систему. Учетная запись для входа приведена в поле Указанная учетная запись.

Настройте новую папку для файлов локального аудита.

Создайте папку (каталог локального аудита), в которую функция локального аудита будет записывать журналы. Например, полный путь к каталогу локального аудита для экземпляра ядра СУБД по умолчанию будет таким: C:\SQLCEIPAudit\MSSQLSERVER\DB\ .

Задайте путь к папке локального аудита за пределами пути установки SQL Server, чтобы избежать возможных проблем с SQL Server из-за включения функций аудита и применения соответствующих исправлений.

Решение о разработки Рекомендация Наличие свободного места При умеренной рабочей нагрузке, когда используется около 10 баз данных, запланируйте около 2 МБ места на диске на базу данных на каждый экземпляр. Отдельные каталоги Создайте каталог для каждого экземпляра. Например, используйте C:\SQLCEIPAudit\MSSQLSERVER\DB\ для экземпляра SQL Server с именем MSSQLSERVER . Это упрощает управление файлами. Отдельные папки Используйте отдельную папку для каждой службы. Например, для определенного имени экземпляра используйте одну папку для ядра СУБД. Если экземпляр Analysis Services использует то же имя экземпляра, создайте для служб Analysis Services отдельную папку. Наличие экземпляров ядра СУБД и служб Analysis Services, настроенных для одной и той же папки, приведет к тому, что все данные локального аудита из обоих экземпляров будут записываться в один файл журнала. Предоставление разрешений для учетной записи для входа в службу CEIP для SQL Server Назначьте разрешения Просмотр содержимого папки, Чтение и Запись для доступа к учетной записи входа в службу CIEP для SQL Server. Предоставление разрешений для учетной записи для входа в службу CEIP для SQL Server

В проводнике, перейдите к расположению новой папки.

Правой кнопкой мыши щелкните папку и выберите пункт Свойства.

На вкладке Безопасность щелкните Изменить для управления разрешениями.

Выберите Добавить и введите учетные данные службы CEIP для SQL Server. Например, NT Service\SQLTELEMETRY .

Выберите Проверить имена, чтобы проверить указанное имя, а затем нажмите кнопку ОК.

В диалоговом окне Разрешение выберите учетную запись входа в службу CEIP для SQL Server и щелкните Просмотр содержимого папки, Чтение и Запись.

Нажмите кнопку ОК, чтобы немедленно применить изменения разрешений.

Создание раздела реестра для настройки целевого каталога локального аудита

Запустите программу regedit.

Перейдите по соответствующему пути CPE.

Щелкните путь CPE правой кнопкой мыши и выберите пункт Создать. Выберите Строковое значение.

Назовите новый раздел реестра " UserRequestedLocalAuditDirectory ".

Включение и отключение локального аудита

После завершения предварительной настройки вы можете включить локальный аудит. Для этого используйте учетную запись системного администратора или аналогичную роль с разрешением изменять разделы реестра для включения и отключения локального аудита, сделав следующее:

Запустите программу regedit.

Перейдите по соответствующему пути CPE.

Щелкните правой кнопкой мыши UserRequestedLocalAuditDirectory и выберите пункт Изменить.

Чтобы включить локальный аудит, введите соответствующий путь, например C:\SQLCEIPAudit\MSSQLSERVER\DB\ .

Чтобы отключить локальный аудит, задайте пустое значение для UserRequestedLocalAuditDirectory.

Закройте программу regedit.

Если служба CEIP для SQL Server запущена, она должна сразу же распознать настройку локального аудита. Чтобы запустить службу программы улучшения качества SQL Server, системному администратору или пользователю, имеющему права на запуск или остановку служб Windows, требуется выполнить указанные ниже действия.

Запустите консоль Службы. Для этого нажмите клавиши Windows+R, чтобы открыть диалоговое окно Выполнить. Введите в текстовом поле services.msc и нажмите кнопку ОК, чтобы запустить консоль Службы.

Перейдите к соответствующей службе.

  • Для ядра СУБД используйте Служба CEIP для SQL Server (имя экземпляра).
  • Для служб Analysis Services используйте CEIP для служб SQL Server Analysis Services (имя экземпляра).
  • Для служб Integration Services:
    • Для SQL 2016 используйте службу CEIP 13.0 для служб SQL Server Integration Services.
    • Для SQL 2017 используйте службу CEIP 14.0 для служб SQL Server Integration Services.

    Щелкните правой кнопкой службу и выберите "Перезапуск".

    Убедитесь в том, что служба находится в состоянии Выполняется.

    Функция локального аудита создает один файл журнала в день. Файлы журнала имеют форму <YYYY-MM-DD>.json . Например, 2016-07-12.json. Если в назначенном каталоге уже создан файл за этот день, функция локального аудита добавит в него данные. В противном случае она создает новый файл за этот день.

    После включения локального аудита на создание файла журнала может потребоваться до пяти минут.

    Обслуживание

    Чтобы ограничить использование дискового пространства файлами, записываемыми функцией локального аудита, настройте политику или регулярное задание очистки каталога локального аудита для удаления старых и ненужных файлов.

    Защитите путь к каталогу локального аудита, чтобы он был доступен только соответствующим пользователям. Обратите внимание, что файлы журнала содержат сведения, описанные в статье Настройка SQL Server 2016 для отправки отзывов в корпорацию Майкрософт. Доступ для чтения этого файла должен быть запрещен большинству сотрудников вашей организации.

    Словарь данных для структуры выходных данных локального аудита

    • Файлы журнала локального аудита имеют формат JSON и содержат набор объектов (строк), представляющих точки данных, которые отправляются в корпорацию Майкрософт во время emitTime.
    • Каждая строка соответствует определенной схеме, определенной значением schemaVersion.
    • Каждая строка является выходным значением сеанса службы SQLCEIP, указанного в sessionID.
    • Строки выводятся в определенной последовательности, заданной значением sequence.
    • Каждая строка точки данных содержит выходные данные queryIdentifier, которые могут быть запросом T-SQL, сеансом XE или сообщением, связанным с типом трассировки, который обозначается значением traceName.
    • Группирование и контроль версий для queryIdentifiers осуществляются вместе с querySetVersion.
    • data содержит выходные данные соответствующего выполнения запроса, занявшего время queryTimeInTicks.
    • queryIdentifiers для запросов T-SQL имеют определение запроса T-SQL, хранящееся в запросе.
    Определение пары "имя — значение" и примеры

    Указанные ниже столбцы представляют порядок выходных данных файлов для локального аудита. Односторонний хэш-алгоритм SHA 256 используется для анонимизации значений в указанных ниже столбцах.

    name Описание Примеры значений instanceUniqueID Анонимизированный идентификатор экземпляра 888770C4D5A8C6729F76F33D472B28883AE518C92E1999888B171A085059FD schemaVersion Версия схемы SQLCEIP 3 emitTime Время вывода точки данных в формате UTC 2016-09-08T17:20:22.1124269Z sessionID Идентификатор для обслуживания службы SQLCEIP 89decf9a-ad11-485c-94a7-fefb3a02ed86 correlationId Заполнитель для дополнительного идентификатора 0 sequence Порядковый номер точек данных, отправленных в рамках сеанса 15 clientVersion Версия экземпляра SQL Server 13.0.2161.3 ((SQL16_RTM_QFE-CU).160907-1223) operatingSystem Версия операционной системы, где установлен экземпляр SQL Server Microsoft Windows Server 2012 R2 Datacenter querySetVersion Версия группы определений запросов 1.0.0.0 traceName Категории трассировки: (SQLServerXeQueries, SQLServerPeriodicQueries, SQLServerOneSettingsException) SQLServerPeriodicQueries queryIdentifier Идентификатор запроса SQLServerProperties.002 . Выходные сведения, собранные для queryIdentifier в виде вывода запроса T-SQL, сеанса XE или приложения [], query Если это применимо, определение запроса T-SQL, связанное с queryIdentifier, который создает данные. Этот компонент не отправляется службой CEIP SQL Server. Он включен в локальный аудит только для справки. SELECT\n SERVERPROPERTY(\u0027Collation\u0027) AS [Collation],\n SERVERPROPERTY(\u0027IsFullTextInstalled\u0027) AS [SqlFTinstalled],\n SERVERPROPERTY(\u0027IsIntegratedSecurityOnly\u0027) AS [SqlIntSec],\n SERVERPROPERTY(\u0027IsSingleUser\u0027) AS [IsSingleUser],\n SERVERPROPERTY (\u0027FileStreamEffectiveLevel\u0027) AS [SqlFilestreamMode],\n SERVERPROPERTY(\u0027IsPolyBaseInstalled\u0027) AS [SqlPbInstalled],\n SERVERPROPERTY(\u0027PolyBaseRole\u0027) AS [SqlPbNodeRole],\n SERVERPROPERTY(\u0027ProductMajorVersion\u0027) AS [SqlVersionMajor],\n SERVERPROPERTY(\u0027ProductMinorVersion\u0027) AS [SqlVersionMinor],\n SERVERPROPERTY(\u0027ProductBuild\u0027) AS [SqlVersionBuild],\n SERVERPROPERTY(\u0027ProductBuildType\u0027) AS ProductBuildType,\n SERVERPROPERTY(\u0027ProductLevel\u0027) AS ProductLevel,\n SERVERPROPERTY(\u0027ProductUpdateLevel\u0027) AS ProductUpdateLevel,\n SERVERPROPERTY(\u0027ProductUpdateReference\u0027) AS ProductUpdateReference,\n RIGHT(CAST(SERVERPROPERTY(\u0027ProductVersion\u0027) AS NVARCHAR(30)),CHARINDEX(\u0027.\u0027, REVERSE(CAST(SERVERPROPERTY(\u0027ProductVersion\u0027) AS NVARCHAR(30)))) - 1) AS ProductRevision,\n SERVERPROPERTY(\u0027EditionID\u0027) AS SQLEditionId,\n SERVERPROPERTY(\u0027IsClustered\u0027) AS IsClustered,\n SERVERPROPERTY(\u0027IsHadrEnabled\u0027) AS IsHadrEnabled,\n SERVERPROPERTY(\u0027IsAdvancedAnalyticsInstalled\u0027) AS [SqlAdvAInstalled],\n @@PACK_RECEIVED AS PacketReceived,\n @@VERSION AS Version queryTimeInTicks Время, затрачиваемое на выполнение запроса в следующей категории трассировки: (SQLServerXeQueries, SQLServerPeriodicQueries) 0

    Категории трассировки

    Сейчас мы собираем следующие категории трассировки.

    • SQLServerXeQueries: содержит точки данных, собранные в рамках сеанса расширенных событий.
    • SQLServerPeriodicQueries: содержит точки данных, собранные периодическими запросами, выполняемыми в экземпляре SQL Server.
    • SQLServerPerDBPeriodicQueries: содержит точки данных, собранные периодическими запросами, выполняемыми для баз данных (до 30) в экземпляре SQL Server.
    • SQLServerOneSettingsException: содержит сообщения об исключениях, связанные с обновлением схемы и/или набора запроса.
    • DigitalProductID: содержит точки данных для статистической обработки обезличенного (SHA-256) хэшированного идентификатора цифрового продукта для экземпляров SQL Server.
    Примеры файла локального аудита

    Ниже приведен отрывок выходных данных файла JSON для локального аудита.

    Часто задаваемые вопросы

    Какие данные содержатся в файлах журнала локального аудита? Эти файлы журналов записываются в формате JSON. Каждая строка соответствует объекту JSON, который представляет фрагмент данных об использовании или диагностических данных, отправляемых корпорации Майкрософт. Имена полей должны быть понятными и очевидными.

    Что произойдет, если администратор базы данных отключит сбор данных об использовании и диагностических данных? Запись файла локального аудита не будет выполняться.

    Что произойдет, если нет подключения к Интернету или компьютер защищен брандмауэром? Данные об использовании и диагностические данные SQL Server не будут отправляться в корпорацию Майкрософт. При правильной настройке функция по-прежнему будет пытаться записать журналы локального аудита.

    Как администраторы баз данных отключают локальный аудит? Удаляя запись раздела реестра UserRequestedLocalAuditDirectory.

    Кто имеет доступ к файлам журнала локального аудита? Любой пользователь в вашей организации с доступом к каталогу локального аудита.

    Как администраторы баз данных управляют файлами журналов, записываемыми в указанный каталог? Администраторам баз данных необходимо самостоятельно следить за очисткой файлов в каталоге, чтобы избежать использования слишком большого объема места на диске.

    Существует ли клиент или инструмент, который можно использовать для чтения выходных данных JSON? Выходные данные можно прочитать в Блокноте, Visual Studio или любом подходящем средстве чтения JSON. Кроме того, прочитать файл JSON и проанализировать данные можно в экземпляре SQL Server, как показано ниже. Дополнительные сведения чтении файла JSON в SQL Server см. в разделе Импорт файлов JSON в SQL Server с помощью функции OPENROWSET (BULK) и OPENJSON (Transact-SQL).