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

Трассировка на стороне сервера

Один из способов отслеживания изменений паролей – использование трассировки на стороне сервера. SQL Server предоставляет событие “Audit Login Change Password Event”, которое специально предназначено для захвата событий изменения паролей. Вы можете создать трассировку с помощью следующего кода:

DECLARE @TraceID INT, @MaxFileSize BIGINT;
SET @MaxFileSize = 5;
EXEC sp_trace_create 
    @TraceID OUTPUT, 
    2, 
    N'C:\Traces\PasswordChangeTrace', -- убедитесь, что измените это!
    @MaxFileSize,
    10;
EXEC sp_trace_setevent @TraceID,107, 1,  1;
EXEC sp_trace_setevent @TraceID,107, 11, 1;
EXEC sp_trace_setevent @TraceID,107, 8,  1;
EXEC sp_trace_setevent @TraceID,107, 12, 1;
EXEC sp_trace_setevent @TraceID,107, 14, 1;
EXEC sp_trace_setevent @TraceID,107, 40, 1;
EXEC sp_trace_setevent @TraceID,107, 42, 1;
EXEC sp_trace_setstatus @TraceID, 1;
SELECT @TraceID;

После запуска трассировки вы можете запросить захваченные события с использованием TraceID:

DECLARE @path NVARCHAR(255);
SELECT @path = [path]
FROM  sys.traces
WHERE id = <traceID из вышеуказанного кода>;
SELECT 
  LoginName  = TargetLoginName, 
  EventCount = COUNT(*), 
  FirstEvent = MIN(StartTime), 
  LastEvent  = MAX(StartTime)
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass = 107 -- на случай, если вы добавили другие события
GROUP BY TargetLoginName;

Уведомления о событиях

Другой способ отслеживания изменений паролей – использование уведомлений о событиях. Уведомления о событиях – это легкие асинхронные сообщения, отправляемые через службу Service Broker, которые могут использоваться для выполнения различных действий в ответ на определенное событие. В данном случае мы будем записывать изменения паролей в таблицу. Сначала создайте следующую таблицу в базе данных msdb:

USE [msdb];
GO
CREATE TABLE dbo.PasswordChangeLog
(
    LoginName  SYSNAME,
    EventTime  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Затем настройте очередь и уведомление для обработки событий:

CREATE QUEUE PasswordChangeQueue;
GO
CREATE SERVICE PasswordChangeService ON QUEUE PasswordChangeQueue
  ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO
CREATE EVENT NOTIFICATION PasswordChangeNotification
    ON SERVER WITH FAN_IN
    FOR AUDIT_LOGIN_CHANGE_PASSWORD_EVENT
    TO SERVICE 'PasswordChangeService', 'current database';
GO

Создайте хранимую процедуру для записи событий в таблицу:

CREATE PROCEDURE dbo.LogPasswordChange
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @message_body XML;
    WHILE (1 = 1)
    BEGIN
       WAITFOR 
       ( 
         RECEIVE TOP(1) @message_body = message_body
         FROM dbo.PasswordChangeQueue
       ), TIMEOUT 1000;
       IF (@@ROWCOUNT = 1)
       BEGIN
        INSERT dbo.PasswordChangeLog(LoginName) 
          SELECT @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'sysname');
       END
    END
END
GO

Наконец, измените очередь, чтобы вызывать хранимую процедуру в ответ на событие:

ALTER QUEUE PasswordChangeQueue
WITH ACTIVATION
(
   STATUS = ON,
   PROCEDURE_NAME = dbo.LogPasswordChange,
   MAX_QUEUE_READERS = 1,
   EXECUTE AS OWNER
);
GO

Аудит сервера

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

USE [master];
GO
CREATE SERVER AUDIT ChangePasswordAudit
  TO FILE (FILEPATH = 'C:\Audits\', MAXSIZE = 5MB, MAX_ROLLOVER_FILES = 10)
  WITH (ON_FAILURE = CONTINUE);
ALTER SERVER AUDIT ChangePasswordAudit
  WITH (STATE = ON);
CREATE SERVER AUDIT SPECIFICATION ChangePasswordAuditSpecification
  FOR SERVER AUDIT ChangePasswordAudit
  ADD (LOGIN_CHANGE_PASSWORD_GROUP)
  WITH (STATE = ON);
GO

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

DECLARE @folder VARCHAR(255);
SELECT @folder = log_file_path + '*' 
  FROM sys.server_file_audits 
  WHERE name = 'ChangePasswordAudit';
SELECT 
  LoginName  = target_server_principal_name, 
  EventCount = COUNT(*),
  FirstEvent = MIN(event_time), 
  LastEvent  = MAX(event_time)
FROM sys.fn_get_audit_file(@folder, DEFAULT, DEFAULT)
WHERE action_id IN ('PWR', 'PWC') -- PWR = ALTER LOGIN / SSMS, PWC = sp_password
GROUP BY target_server_principal_name;

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

Заключение

Отслеживание изменений паролей в SQL Server является важным для обеспечения безопасности и соответствия требованиям. В этой статье мы рассмотрели три различных метода отслеживания изменений паролей: трассировка на стороне сервера, уведомления о событиях и аудит SQL Server. Каждый метод имеет свои преимущества и может быть относительно легко реализован. Реализуя одно из этих решений, вы можете эффективно отслеживать и аудитировать изменения паролей в вашей среде SQL Server.