Posts tagged sysadmin

Wstęp

Jakiś czas temu napisałem, dlaczego uważam, że logon trigger nie jest dobrym rozwiązaniem w sytuacji, gdy zależy nam na logowaniu udanych prób logowania do instancji SQL Servera członków roli sysadmin. Napisałem wówczas, że mechanizmem, który nadaje się do takiego zadania, są Extended Events. Ale okazało się, że Extended Events nie dysponują zdarzeniem, które takie logowanie by umożliwiło. W międzyczasie wpadłem na pomysł, by zastosować w takim przypadku events notifications (dostępne od SQL Server 2005).

Rozwiązanie

Zadanie: zapisywać do tabeli wszystkie udane próby logowania członków roli sysadmin.

Rozwiązanie:

-- (0) Wlaczamy Service Brokera, jezeli jest wylaczony
IF EXISTS (SELECT * FROM sys.databases WHERE name = N'msdb' AND is_broker_enabled = 0)
  ALTER DATABASE msdb SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE;
GO

-- (1) Wybieramy baze msdb, bo zawsze jest...
USE msdb;
GO

-- (2) Kasujemy obiekty, ktore zaraz utworzymy
IF EXISTS (SELECT * FROM sys.server_event_notifications WHERE name = N'AuditSysadminLoginNotification')
  DROP EVENT NOTIFICATION AuditSysadminLoginNotification ON SERVER;
GO
IF EXISTS (SELECT * FROM sys.services WHERE name = N'AuditSysadminLoginService')
  DROP SERVICE AuditSysadminLoginService;
GO
IF EXISTS (SELECT * FROM sys.service_queues WHERE name = N'AuditSysadminLoginQueue' AND [schema_id] = 1)
  DROP QUEUE dbo.AuditSysadminLoginQueue;
GO

-- (3) Tworzymy obiekty: kolejke, usluge i powiadomienie o zdarzeniu
CREATE QUEUE dbo.AuditSysadminLoginQueue WITH STATUS = OFF;
GO
CREATE SERVICE AuditSysadminLoginService
ON QUEUE AuditSysadminLoginQueue
(
[http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
);
GO
CREATE EVENT NOTIFICATION AuditSysadminLoginNotification
ON SERVER
FOR AUDIT_LOGIN
TO SERVICE 'AuditSysadminLoginService', 'current database';
GO

-- (4) Tworzymy tabele na potrzeby logowania
IF OBJECT_ID(N'dbo.SysadminLogins', N'U') IS NOT NULL
  DROP TABLE dbo.SysadminLogins;
GO
CREATE TABLE dbo.SysadminLogins (
  LoginID int NOT NULL IDENTITY(1,1) PRIMARY KEY,
  LoginName sysname NOT NULL,
  ApplicationName nvarchar(256) NULL,
  HostName nvarchar(128),
  LoginDate datetime NOT NULL DEFAULT(GETDATE())
);
GO

-- (5) Tworzymy procedure do logowania
IF  OBJECT_ID(N'dbo.usp_LogSysadminLogin', N'P') IS NOT NULL
  DROP PROCEDURE dbo.usp_LogSysadminLogin
GO
CREATE PROC dbo.usp_LogSysadminLogin
AS
DECLARE
  @ErrorMessage nvarchar(4000),
  @EventData xml,
  @LoginName sysname,
  @ApplicationName nvarchar(256),
  @HostName nvarchar(128),
  @ConversationHandle uniqueidentifier;
WHILE (1=1)
BEGIN
  BEGIN TRAN;
  BEGIN TRY;
    RECEIVE TOP (1)
      @EventData = CAST(message_body AS xml),
      @ConversationHandle = [conversation_handle]
    FROM dbo.AuditSysadminLoginQueue;
    IF @@ROWCOUNT = 0
    BEGIN
      IF @@TRANCOUNT > 0
      BEGIN
          ROLLBACK;
      END;
      BREAK;
    END;
    SELECT
      @LoginName = @EventData.value('(/EVENT_INSTANCE/LoginName/text())[1]', 'sysname'),
      @ApplicationName = @EventData.value('(/EVENT_INSTANCE/ApplicationName/text())[1]', 'nvarchar(256)'),
      @HostName = @EventData.value('(/EVENT_INSTANCE/HostName/text())[1]', 'nvarchar(128)');
    IF IS_SRVROLEMEMBER(N'sysadmin', @LoginName) = 1 BEGIN
      INSERT INTO dbo.SysadminLogins (LoginName, ApplicationName, HostName)
      SELECT @LoginName, @ApplicationName, @HostName;
    END;
    IF @@TRANCOUNT > 0
      COMMIT;
  END TRY
  BEGIN CATCH;
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK;
        END CONVERSATION @ConversationHandle;
        BREAK;
    END;
  END CATCH;
END;
GO

-- (6) Przypinamy procedure do kolejki i wlaczamy kolejke
ALTER QUEUE dbo.AuditSysadminLoginQueue
  WITH STATUS = ON,
  RETENTION = OFF,
  ACTIVATION (
    STATUS = ON ,
    PROCEDURE_NAME = msdb.dbo.usp_LogSysadminLogin,
    MAX_QUEUE_READERS = 2,
    EXECUTE AS OWNER
  );
GO

-- (7) Testujemy, czy cos sie zalogowalo
SELECT * FROM msdb.dbo.SysadminLogins;

Komentarz do kodu:

• jako bazę docelową, w której zapisuję informacje o zdarzeniach, wybieram bazę msdb (zawsze jest na serwerze, domyślnie ma włączonego Service Brokera),
• w kroku (0) włączam na bazie msdb Service Brokera, jeżeli jeszcze nie jest on włączony,
• w kroku (2) dbam o to, by obiekty, które zamierzam stworzyć, nie istniały na serwerze i w bazie msdb,
• w kroku (3) tworzę obiekty niezbędne do asynchronicznego przechwytywania zdarzeń: kolejkę, usługę oraz event notification (na poziomie serwera, przechwytuje zdarzenia AUDIT_LOGIN – logowania do instancji SQL Servera),
• w kroku (4) tworzę tabelę, do której będą zrzucane informacje o logowaniu sysadminów (login, nazwa użytej aplikacji klienckiej, nazwa maszyny klienckiej oraz data i czas logowania),
• w kroku (5) tworzę procedurę składowaną, która zostanie użyta do wybrania informacji z tego, co przechwyci event notification i zwróci za pomocą funkcji EVENTDATA, wybrane informacje zostaną zapisane do tabeli utworzonej w kroku (4), do wychwycenia loginów będących członkami roli sysadmin używam funkcji IS_SRVROLEMEMBER,
• w kroku (6) przypinam procedurę z kroku (5) do kolejki z kroku (3) i włączam kolejkę, od tej pory zdarzenia klasy AUDIT_LOGIN są już przechwytywane, a stosowne informacje zapisywane w tabeli z kroku (4),
• w kroku (7) prezentuję zapytanie, dzięki któremu możesz sprawdzać, czy jakieś zdarzenie zostało już zalogowane w tabeli z kroku (4).

Przykładowe informacje o zdarzeniach logowania sysadminów:

Podsumowanie

Zastosowanie event notification ma kilka zalet: nie ingeruje w proces logowania (nie ma takiej możliwości, by sysadmin nie zalogował się z powodu błędu w procesie logowania zdarzeń, jak to mogło mieć miejsce w przypadku zastosowania logon triggera), asynchroniczność zapewnia niezłą skalowalność i działanie mechanizmu nawet w przypadku dużego natężenia zdarzeń, ewentualne informacje o błędach są do znalezienia w errorlogu instancji SQL Servera (np. błędy wynikające z wykrycia “zatrutej wiadomości” – poison message) lub w widoku systemowym sys.transmission_queue (w kolumnie transmission_status znajdziesz informację, dlaczego komunikat o zdarzeniu utknął w czasie przesyłania). Co ciekawe, wydaje mi się, że można znaleźć naprawdę sporo kategorii zdarzeń, dla przechwytywania których event notifications nadają się idealnie (m.in. DEACLOCK_GRAPH i BLOCKED_PROCESS_REPORT). Używając event notifications możesz zbudować całkiem niezły framework do monitorowania SQL Servera. Polecam zabawę tym mechanizmem.