Logon Trigger verhindert Login

Question

Hi,

habe erst einmal einen neuen einfachen Versuch gestartet für einen LOGON-Trigger:

ALTER TRIGGER connection_limit_trigger
ON ALL SERVER WITH EXECUTE AS 'test_default'
FOR LOGON
AS
BEGIN
    DECLARE @dummy int
    IF ORIGINAL_LOGIN()= 'test_default'
    BEGIN
        SET @dummy = 1
    END
END

Doch selbst dieses "NICHTS"-tuende Etwas verhindert mir bereits das Einloggen über den user "test_default".

Hier kommt genau das zum tragen, womit ich mir vorgestern den gesamten Server zugenagelt habe, weil ich "WTH EXECUTE AS ..." weggelassen habe.

Warum komme ich mit den User nicht mehr hinein?

Gruß Hipp

Answer 1

Hallo Hipp,

bei mir funktioniert der Trigger soweit, ohne Schaden auzurichten. Der SQL Login kann sich weiterhin anmelden.

Gibt es evtl. noch einen weiteren Logon Trigger? Konntest Du Dich mit dem User anmelden, bevor Du den Trigger angelegt hattest?

Und wozu soll das ganze mit dem Logon Trigger überhaupt gut sein / welchen Zweck soll das erfüllen?

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 2

Wenn ich den Trigger wegnehme, dann geht es. Hängt es evtl. an der EXPRESS-Version? Kann ich mir aber nicht vorstellen. Die Idee war ursprünglich, dass ein User sich nicht mehrmals anmelden kann. Doch jetzt gibt es eine Grundsatz-Diskussion, ob wir für jeden User, der sich bei unserer Applikation anmeldet, einen eigenen User im Server einrichten. Zur Diskussion stehen ein Standard-User mit beschränkten rechten, und jeder LOGIN erfolgt damit. User werden dann bei INSERT oder UPDATE im Datensatzmit DateTime abgelegt. Auch kam die Idee auf, ein Client-IP-Logging hier zu etablieren...

Muss der User noch irgendeine Berechtigung erhalten, damit der Trigger ausgeführt wird?

Was meinst Du zu dem Ganzen?

Gruß Hipp

Answer 3

Ich habe es auch mit einer Express Edition ausprobiert (zerschiesse mir doch nicht meinen richtigen Server) und für den Test habe ich mir auch nur einen SQL Login angelegt, der sich nur anmelden darf, sonst aber keine Rechte hat.

Warum sollte sich ein User nicht mehrmals anmelden dürfen. Wir stellen alles per Citrix bereit und egal wo ich gerade in der Firma bin, bekomme ich meinen Desktop; selbst wenn ich in einer Ausstelle bin. Meinen "Hauptarbeitsplatzrechner" lasse ich meistens laufen und melde ich auch von nichts ab; bei Deiner App könnte ich mich dann also nirgends anders mal anmelden, um z.B. kurz mal was zu zeigen.

Es ist durchaus gängig, das der Datenbank-Zugriff über einen festen SQL Login erfolgt und die Berechtigungen über die Applikation validiert werden und nicht über den SQL Server. Das geht gut, solange der Zugriff ausschließlich über die Appliaktion erfolgt. Sollen irgendwann die User z.B. für Auswertungen in Excel Zugriff auf den SQL Server erhalten, müssten doch wieder (zusätzliche) Zugriffsrechte im SQL Server eingerichtet werden; oder der Zugriff darf dann auch wieder nur über WebServices erfolgen, der die Appliaktionsrechte prüft.

P.S.: Ein SysAdmin kann den Logon-Trigger jederzeit wieder entfernen, es stellt also nicht wirklich ein Absicherung da.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 4

Danke Olaf. Wir haben uns heute zusammengesetzt und machen es so wie in Deinem 3. Abschnitt beschrieben. Die Applikationen sind recht übersichtlich und folgen fest definierten Prozessen für ganz wenig User. Wer die Applikation bedienen darf (max 3 Leute), darf darin alles, denn es sind Produktions-Prozesse.

Aber die Frage die sich mir stellt, ist die, warum versagt mir der Sql-Server den Zugang bei unserem Test-User für den Trigger, den ich bereits gepostet habe. Der Trigger tut ja im Grunde genommen nichts. Also muss es irgendwo eine Einstellung geben, die dem User versagt, sich anzumelden, wenn so ein Server-Trigger aktiv ist. Vor allen Dingen auch für den Admin, denn bei der Definition wie ich sie das erste Mal irrtümlich hatte ohne

WITH EXECUTE AS 'test_default'

was dann bedeutet, für jeden der sich anmelden will, auch der Admin. Dmait habe ich mir nämlich vor ein paar Tagen den kompletten Server zugenagelt und es war überhaupt kein Einloggen möglich.

Was anderes außer irgend eine Security fällt mir dazu nicht ein. Ich habe im Internet leider bisher nichts gefunden. Ich hoffe irgend jemand wiß etwas, weil wir das eigentlich haben wollen.

Gruß Thomas

Answer 5

wie ich sie das erste Mal irrtümlich hatte ohne

WITH EXECUTE AS 'test_default'

Hallo Thomas,

und genau diese Inpersonate ist, was mich etwas irritiert / stört, auch an dem Beispiel in der MSDN.

Eigentlich macht es keinen Sinn, den Logon Trigger unter einem anderen Context auszuführen, vor allem dann, wenn der auch noch minder-previligiert ist. Denn so eigentlich benötigt man für ein Inpersonate auch das Recht dazu

GRANT IMPERSONATE ON USER::[UserA] TO [UserB]

Ich habe auch einen Logon Trigger, der die Anmeldungen zur späteren Abrechnung protokolliert und der sieht so aus:

CREATE TRIGGER [wir_TRG_Logon]
ON ALL SERVER
FOR LOGON
AS
BEGIN
    BEGIN TRY
        EXECUTE dbo.wir_SP_WriteLogon;
    END TRY
    BEGIN CATCH
    END CATCH
END;

Es wird also nur eine Stored Procedure aufgerufen, die die Protokollierung vornimmt; alles in einem Try/Catch gekapselt, falls Fehler in der SP auftreten. Die DB Rolle "public" hat das Recht, die SP auszuführen, obwohl das eigentlich in dem Fall nicht nötig wäre.

Und das alles ohne ein EXECUTE AS; funktioniert problemlos, egal wie viel oder wenig Rechte der User hat.

---

Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog Xing

Answer 6

Hier liegt vermutlich der Hase im Pfeffer begraben. Das Recht eine SP auszuführen. Ich werde es noch einmal auspropieren.

Merci.

Gruß Hipp