Гру 1 2020
Активируем аудит на SQL Server

Задача: Настроить мониторинг изменения учетной записи

Login: sa на SQL Server's

У меня на работе довольно странная ситуация в плане коммуникации между Руководителем IT отдела, Программистом 1С и системными администраторами. Говорить что делается и что изменяется, как то не принято. А вот писать письма если что-то не так — так это вполне нормальная практика. Вот на выходных 11 апреля, программист 1С не мог якобы зайти под учетной записью Sa в базе. А все потому что пароль на учетку был изменен. Да и спрашивается зачем ему заходить под ней если у него есть своя. Тут еще смешнее, вопреки всем правилам работы с информационными системами, тут работают в домене под доменной учетной записью Administrator , а под SQL с применением учетной записи sa. Я конечно отучаю их и вот чтобы не было вопросов кто изменил пароль я хочу настроить аудит. Ниже мой практический опыт настройки.

В заметке задействована конфигурация

  • OS: Windows Server 2012 R2 Std
  • SQL: SW_DVD9_SQL_Svr_Enterprise_Edtn_2012w_SP4_64Bit_English_MLF_X21-43470.iso

Мне нужно на SQL Server включить мониторинг кто изменяет пароль на учетную запись Login: Sa

Шаг №1: На текущем сервере srv-backup создаю каталог куда будут записываться логи работы аудита:

Win + X - Command Prompt (Admin)

1C:\Windows\system32>if not exist c:\Log mkdir c:\Log

Шаг №2: Создаю для SQL базы уровень аудита:

Win + нажимаю на кружок со стрелочкой вниз — затем в листинге всех установленных программ нахожу оснастку SQL Server Management Studio и через правый клик мышью на ней выбираю из меню «Pin to Star«. Теперь при нажатии на кнопку Win у меня в главном меню иконка доступа в SQL Server Management Studio, нажимаю на нее, запускается SQL Server Management Studio. Подключаюсь:

  • Server type: Database Engine
  • Server name: SRV-SERVER
  • Authentication: Windows Authentication

и нажимаю Connect

Затем разворачиваю SRV-SERVER (SQL Server 11.0.7001 - SRV-SERVER\ekzorchik) — Security — и через правый клик мышью на Audits выбираю New Audit, запускаем мастер

  • Audit name: AuditDB
  • Queue delay (in milleseconds): 1000
  • On Audit Log Failure: Continue
  • Audit destination: File
  • File path: указываю путь до каталога C:\Log (его создал в "Шаг №1")
  • Audit File Maximum Limit: Maximum rollover files: Unlimited
  • Maximum file size: 10 MB

после нажимаю OK текущего окна «Create Audit»

Шаг №3: Связываю профили аудита с настройками аудита

Далее передвигаюсь на

Databases — моя база testdb - Security — и через правый клик мышью на «Database Audit Specifications» выбираю меню «New Database Audit Specification...»

  • Name: DatabaseTestDBAudit
  • Audit: выбираю созданный профиль аудита, т.е. AuditDB (это из Шаг №2)

Actions: предопределяю профили из списка

  • DATABASE_PRINCIPAL_CHANGE_GROUP
  • SCHEMA_OBJECT_CHANGE_GROUP

Должно получиться вот так:

Предопределяю профили аудита из списка для базы данных

после нажимаю OK текущего окна «Create Database Audit Specification»

Шаг №4: Теперь активирую созданный аудит из «Шаг №2»

Security - Audits — и через правый клик мышью на AuditDB активирую «Enable Audit» — «Close»

Шаг №5: Теперь активирую связанный профиль с профилем аудита из «Шаг №3»

Databases - testdb - Security - Database Audit Specifications — и через правый клик мышью на DatabaseTestDBAudit активирую "Enable Database Audit Specification" - "Close"

Шаг №6: Проверяю работоспособность настроенного аудита:

Создаю нового пользователя сервиса базы данных

Security - Logins - New Login... -

(General)

  • Login name: alektest
  • SQL Server Authentication
  • Password: Aa1234567
  • Confirm password: Aa1234567
  • Default database: testdb
  • Default language: English

(User mapping)

  • Users mapped to this login: отмечаю галочкой базу testdb и права
Создаю SQL учетную запись и даю ей права

после нажимаю OK текущего окна «Login - New»

Разлогиниваюсь и авторизуюсь в сервисе базы данных с использованием

  • Server type: Database Engine
  • Server name: SRV-SERVER
  • Authentication: SQL Server Authentication
  • Login: alektest
  • Password: Aa1234567

и нажимаю Connect

Разлогиниваюсь

Авторизуюсь снова под ekzorchik (Windows Authentication) и удаляю созданного пользователя

Security - Logins — и через правый клик на учетке alektest выбираю Delete - OK - OK, но увы пока учетная запись соединена через User Mapping с базой ее удалить нельзя:

Удалить SQL учетную запись нельзя, т.к. она связана с базой

В свойствах учетной записи alektest снимаю галочку с Users mapped to this login с базы testdb и возвращаюсь к удаление учетной записи из сервиса базы данных.

Проверяю лог событий создания и удаления пользователя:

Security - Audits — через правый клик мышью на AuditDB выбираю View Audit Logs вижу фиксируемые мною действия, как создание учетной запись, присвоение прав и удаление:

Подробный лог создания sql учетной записи

Подробно достаточно.

Также если посмотреть что содержится в каталоге C:\Log

В Каталоге C:\Log есть запись аудита

Шаг №7: Точно такой же вывод можно получить если сформировать запрос «New Query»

123select event_time, action_id,server_principal_name,database_principal_name,database_name,object_name,statement from sys.fn_get_audit_file (‘C:\Log\*.sqlaudit’,default,default) order by event_time DESC
Простой SQL запрос из анализа файла на предмет что залогировано

Все выше это показательный пример того, как можно мониторить действия выполняемые с базой.

Шаг №8: Создаю серверный аудит SQL Server:

Security - Server Audit Specifications - New Server Audit Specification...

  • Name: ServerAudit
  • Audit: AuditDB

Audit Action Type:

  • SUCCESSFUL_LOGIN_GROUP
  • FAILED_LOGIN_GROUP
  • LOGOUT_GROUP

и нажимаю OK окна «Create Server Audit Specification»

Шаг №9: Активирую серверный аудит SQL Server из «Шаг №8«:

Security - Server Audit Specifications и через правый клик на ServerAudit выбираю «Enable Server Audit Specification» — «Close»

Шаг №10: Проверяю, как работает аудит входа и выхода к примеру. Для этого завершаю текущее подключение и через n-ое количество времени подключаюсь снова, к примеру через

  • Server type: Database Engine
  • Server name: SRV-SERVER
  • Authentication: SQL Server Authentication
  • Login: sa
  • Password: 712mbddr@

и нажимаю Connect

Security - Audits — и через правый клик на AuditDB выбираю View Audit Logs

Аудит входов и выходов в SQL Management Studio

И да теперь у меня есть логирование входов и выходов, также значит и все остального, что предопределено в «Шаг №8«.

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

На этом у меня всё, с уважением автор блога Олло Александр aka ekzorchik.

a.boyko 0 Коментарі