Du kommst hier net rein – Logon Trigger

Trigger sind seitens Performance vermutlich die schlechteste Wahl. Worüber wir allerdings hier sprechen hat nichts mit den üblichen Insert, Update und Delete Trigger zu tun.  Wir wollen aktive Login Versuche überwachen und evtl darauf reagieren.

Das einzig Gemeinsame mit den Üblichen DML Triggern ist also das Reagieren auf Ereignisse – den Logins.

Ein Frage der Zeit

Wissenswert ist hier, dass Logon Trigger erst nach der Authentifizierung stattfinden, aber bevor eine Benutzersitzung zugewiesen wird. Daher werden auch alle Fehlermeldung, die evtl durch den Trigger ausgelöst werden in das SQL Server Fehlerprotokoll geschrieben Ist das Login grundsätzlich falsch (falsches Kennwort oder ähnliches), dann wird der Logon Trigger gar nicht erst ausgelöst.

Zweckdienliche Hinweise erbeten

Insofern läßt sich der Zweck der Logon Trigger gut vorstellen. So könnte man beispielsweise die Uhrzeiten abfragen und je nachdem eine Login zulassen oder eben auch das Login verwerfen (Rollback). Oder etwa pro User maximal 3 Sessions oder grundsätzlich eine maximale Anzahl an Sessions generell zulassen. Der Phantasie sind hier keine Grenzen gesetzt.

Beachte allerdings hierbei, dass der Trigger im Namen des User ausgeführt wird. Insofern muss, falls  die Anzahl der Sessions ausgelesen werden sollte, das Recht für View State für den User vorhanden sein oder alternativ läßt man den Trigger unter einem bestimmten Useraccount ausführen.  (execute as ‘’)

Maximal 30 Session, außer für den Administrator

 1  Create Trigger MaxSessions
 2      ON ALL SERVER  WITH EXECUTE AS 'FBIAdministrator'
 3      FOR LOGON
 4    AS
 5    BEGIN
 6     If ((select count(*) from sys.dm_exec_connections 
 7    ALTER TRIGGER trgMaxSessions
 8                where session_id>50) > 30
 9         AND
10         (ORIGINAL_LOGIN() != 'FBIAdministrator'))
11     ROLLBACK;
12   END;

Nur  3 Sessions pro User , außer für den Administrator

 1  ALTER TRIGGER trgMaxSessions
 2  ON ALL SERVER  WITH EXECUTE AS 'FBIAdministrator'
 3  FOR LOGON
 4  AS
 5  BEGIN
 6   If ((select count(*) from sys.dm_exec_sessions where original_login_name= ORIGINAL_LOGIN()) > 3
 7    AND
 8    (ORIGINAL_LOGIN() != 'FBIAdministrator'))
 9   ROLLBACK;
10  END;

Für bestimmte Anwendungen Zeiten vorgeben

 1  create TRIGGER BusinessTime
 2  ON ALL SERVER  WITH EXECUTE AS 'FBIAdministrator'
 3  FOR LOGON
 4  AS
 5  BEGIN
 6   IF (substring(convert(varchar(50),Current_timestamp),12,23)) 
 7            not between ' 06:00PM' and ' 06:00AM'
 8    AND
 9      (app_name() like  '%Report%')
10   ROLLBACK;
11  END;

Protokollierung der Logins

 1  Create TRIGGER LoginMonitor
 2  ON ALL SERVER  WITH EXECUTE AS 'FBIAdministrator'
 3  FOR LOGON
 4  AS
 5  BEGIN
 6   INSERT INTO DWMontor..Logininfos (Orgname, Datum, Info)
 7         SELECT ORIGINAL_LOGIN(), GETDATE(),
 8         EVENTDATA().value('(/EVENT_INSTANCE/ClientHost)[1]','NVARCHAR(128)')
 9  END;
10  
11 --Tabelle für Loginprotokollierung
12 
13 CREATE TABLE [dbo].[LoginInfos](
14   [id] [int] IDENTITY(1,1) NOT NULL,
15   [Orgname] [varchar](50) NULL,
16   [Datum] [datetime] NULL,
17   [Info] [varchar](100) NULL
18  )
19 

Wie bereits gesagt. Der Fantasie sind keine grenzen gesetzt, ob noch eine Emailbenachrichtigung zusenden lassen oder was auch immer. Aüßerst praktisch!

Viel Spaß damit!