none
Beim Anmelden Zugang loggen - geht das? RRS feed

  • Frage

  • Hi,

    ist es möglich, wenn ein Programm sich z.B. per SQL-Server-Authentifizierungs-User mit dem Server connected dieses Event abzugreifen und z.B. in einer Tabelle in der DB abzulegen?

    Gruß Hipp


    • Bearbeitet Hipp1010 Freitag, 8. Juni 2012 08:20
    Freitag, 8. Juni 2012 08:20

Antworten

  • Die Berechtigungen der Logins in einer Datenbank werden auch in dieser gespeichert, die musst Du nicht neu einrichten; das ist also völlig unkritisch. Nur die Zuordnung zu Serverrollen, sofern die den verwendet wurden, müsstest Du neu festlegen.

    Wenn Du mit SQL Login gearbeitet hast, erhalten die bei der Neuanlage andere SIDs und dadurch sind die vorhandenen Datenbank-Logins dann "verwaist" (orphaned users), da sie auf eine andere SID verweisen. Das kannst Du aber einfach per Script korrigieren lassen, siehe http://gallery.technet.microsoft.com/scriptcenter/Secure-Orphaned-User-f3aceae0

    Beim Rebuild wird die master Datenbank überschrieben, deswegen vorher weg kopieren, damit Du diese dann später als normale Datenbank anhängen kannst und so an Deine SPs wieder ran kommst, falls Du die Scripte nicht gespeichert hast.


    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

    • Als Antwort markiert Hipp1010 Freitag, 8. Juni 2012 19:40
    Freitag, 8. Juni 2012 16:46

Alle Antworten

  • Hallo Hipp1010,

    ja, geht mit einem Logon-Trigger.

    Siehe auch EventData.


    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

    Freitag, 8. Juni 2012 08:47
  • Ja, du kannst einen Logon-Trigger anlegen.

    Nimm den Hinweis mit dem Verhindern von Anmeldungen ernst. D.h. teste ordentlich, auch Dinge wie Backup, SSIS-Pakete und SQL Agent Jobs.

    Freitag, 8. Juni 2012 08:59
    Moderator
  • D.h. teste ordentlich

    Das ganz vorallem; denn wenn im Trigger ein Fehler auftritt, kann sich womöglich keiner mehr anmelden, auch der Admin auf normalem Wege nicht mehr, um den Trigger wieder zu löschen. Dann kann nur noch der SysAdmin sich per DAC anmelden, denn nur dabei werden Login Trigger nicht gefeuert.

    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


    Freitag, 8. Juni 2012 09:13
  • Danke.

    Habe mir einen Trigger erstellt. Doch sehe ich ihn nirgends im Objekt-Explorer. Unter der DB bei Datenbank-Trigger scheint er nicht auf. Liegt das an der "Express-Version"? Und wenn ich mich 2x beim SQL-Server-Management-Studio mit dem gleichen SQL-User anmelde geht das auch. Oder muss das eine echte Daten-Connection sein?

    Gruß Hipp

    Freitag, 8. Juni 2012 09:40
  • Hallo Hipp,

    ein Logon-Trigger wird auf Server Ebene erstellt, nicht in einer speziellen Datenbank.

    In SSMS findest Du ihn in "Serverobjekte" => "Trigger".


    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

    Freitag, 8. Juni 2012 09:47
  • Danke. Aber warum greift der nicht beim doppelten Login im Sql-Server-Manager? Müsste er nicht bei einem "ROLLBACK" beim doppeleten Anmelden irgend einen Fehler bringen?

    Gruß Hipp

    Freitag, 8. Juni 2012 11:06
  • Hallo Hipp,

    ich kenne Deinen Trigger Code nicht, von daher weiß ich nicht was er machen sollte und warum er es nicht macht.

    Ist dass das Beispiel aus http://msdn.microsoft.com/de-de/library/bb326598.aspx ? Der macht ein Rollback ab der 4ten Anmeldung.


    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

    Freitag, 8. Juni 2012 12:03
  • Ja das ist das Beispiel und ich habe > 1 gemacht. Aber nix mit Rollback.

    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    CREATE TRIGGER [tr_login_connection_limit] ON ALL SERVER WITH EXECUTE AS 'my_default' FOR LOGON AS BEGIN IF ORIGINAL_LOGIN()= 'my_default' AND (SELECT COUNT(*) FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND original_login_name = 'my_default') > 1 ROLLBACK; END; GO SET ANSI_NULLS OFF GO SET QUOTED_IDENTIFIER OFF GO ENABLE TRIGGER [tr_login_connection_limit] ON ALL SERVER GO

    Und das sind die Anmeldungen aktuell:

    NT Service\ReportServer$SQLFOCUS
    AXXXXXX\_master_xxx
    AXXXXXX\_master_xxx
    NT Service\ReportServer$SQLFOCUS
    AXXXXXX\_master_xxx
    my_default
    my_default
    AXXXXXX\_master_xxx
    
    Gruß Hipp

    Freitag, 8. Juni 2012 13:28
  • Und wenn Du Dich nun mit "my_default" ein drittes mal anmeldest?

    Zum Zeitpunkt des ersten Logon Vorganges dürfte noch keine Session vorhanden sein, beim zweiten Logon ist nur die erste Session da und es wird auf > 1 geprüft; für mein Verständnis dürfte erst beim 3ten Logon Versuch einen Rollback geben.


    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

    Freitag, 8. Juni 2012 13:38
  • Ja, das habe ich auch schon versucht. Aber ebenfalls nix.

    Dann habe ich einen zweiten Trigegr geschrieben, der abfragt ob eine Anmeldung für den sich Anzumeldeten bereits vorliegt. Habe aber das auf > 1 abgefragt. Doch wie ich gerade bemerkt habe, meldet der Admin sich wohl mindesten 2x pro Session an. Und nun habe ich die Bescherung. Ich komme nicht mehr hinein. Was tun?

    Gruß Hipp

    Freitag, 8. Juni 2012 13:44
  • Du meinst, wenn man SSMS startet? Ja, da werden kurzfristig 2 Sessions verwendet, eine für den Objekt-Explorer und eine fürs eigentliche Abfragefenster.

    Schalte zunächst einmal den Objekt-Explorer ab; ich meine, das wird zuerst gestartet. Dann kannst Du ein Abfragefenster öffnen.

    Ansonsten geht es wie schon erwähnt nur noch über eine DAC (Dedicated Admin Connection), siehe http://msdn.microsoft.com/de-de/library/ms189595(v=sql.105).aspx


    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


    Freitag, 8. Juni 2012 13:49
  • Habe das Objekt-Explorer Fenster zugemacht. Klicke auf neue Abfrage und bekomme das Anmelde-Fenster. Login schlägt fehl mit

    Verbindung mit Server herstellen
    
    Es kann keine Verbindung mit 'AXXXXXXXX\SQLSERVER' hergestellt werden.
    
    ZUSÄTZLICHE INFORMATIONEN:
    
    Fehler beim Anmelden für den Anmeldenamen ' AXXXXXXXX \Administrator' wegen Triggerausführung.
    Der Datenbankkontext wurde in 'master' geändert.
    Die Spracheneinstellung wurde auf Deutsch geändert. (Microsoft SQL Server, Fehler: 17892)
    
    Hilfe erhalten Sie durch Klicken auf: http://go.microsoft.com/....

    Ich hoffe ich habe jetzt nicht alles vernichtet :-(

    Gruß Hipp


    • Bearbeitet Hipp1010 Freitag, 8. Juni 2012 14:00
    Freitag, 8. Juni 2012 14:00
  • Hi,

    Ich hoffe ich habe jetzt nicht alles vernichtet :-(

    nuja, viel mehr als dich mehrfach warnen, konnten Olaf und Stefan auch nicht machen.

    Das ganz vorallem; denn wenn im Trigger ein Fehler auftritt, kann sich womöglich keiner mehr anmelden, auch der Admin auf normalem Wege nicht mehr, um den Trigger wieder zu löschen. Dann kann nur noch der SysAdmin sich per DAC anmelden, denn nur dabei werden Login Trigger nicht gefeuert.

    Du solltest den Trigger daher mal wieder rausnehmen und ggfs. mit einer separaten Instanz für die Tests arbeiten. Dort kannst Du dann eine dauerhafte Verbindung als sa offen halten um ggfs. reagieren zu können, wenn doch mal wieder was schiefläuft.


    Gruß, Stefan
    Microsoft MVP - Visual Developer ASP/ASP.NET
    http://www.asp-solutions.de/ - Consulting, Development
    http://www.aspnetzone.de/ - ASP.NET Zone, die ASP.NET Community

    Freitag, 8. Juni 2012 14:12
    Moderator
  • Wie nehme ich den Trigger wieder raus, wenn ichmich überhaupt nicht anmelden kann?

    Gruß Hipp


    • Bearbeitet Hipp1010 Freitag, 8. Juni 2012 14:16
    Freitag, 8. Juni 2012 14:16
  • Hallo Hipp,

    wie ich schon schrieb, über eine DAC = Dedicated Admin Connection, siehe http://msdn.microsoft.com/de-de/library/ms189595(v=sql.105).aspx

    Bei einer DAC wird der Logon Trigger nicht gefeuert, die geht immer. Im Standard ist aber eine Remote DAC disabled, Du musst es also auf dem Rechner machen, auf dem der SQL Server läuft.


    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

    Freitag, 8. Juni 2012 14:20
  • Und noch einen Tipp für die Zukunft: Der Prozess-Liste nach, arbeitest Du bisher alleine auf dem SQL Server, deshalb kannst Du mal folgendes machen:

    Nachdem der Logon-Trigger entfernt ist und Du wieder eine saubere "master" Datenbank hast (dort wird der Logon Trigger gespeichert), dann fahr mal den SQL Server Dienst runter (bitte wirklich nur, wenn Du alleine auf dem SQL Server bist) und kopiere dann die master Datenbank (Dateien master.mdf + mastlog.ld) weg; ja wirklich kopieren und kein Datenbank-Backup machen. Danach kannst Du den Dienst wieder starten.

    Wenn das nächste mal was mit dem Logon-Trigger schief geht, beendest Du den Dienst wieder und kopierst Die Kopie der master wieder ins Data Verzeichnis. Dienst wieder starten und alles ist heile-heile Welt.

    Das mache ich bei unseren Produktivsystemen auch regelmäßig mal, wenn wir Wartungsfenster haben. Ein Desaster-Recovery bei einer defekten master DB ist zwar problemlos per Rebuild + anschließendem Restore machbar, aber Zeit- und dadurch Nervenraubend.


    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

    Freitag, 8. Juni 2012 14:27
  • Hallo Olaf,

    danke. Doch habe ich mit dem sqlcmd - Teil Probleme. Irgendwie komme ich mit den Internet-Beschreibungen nicht klar. Habe versucht mit

    sqlcmd /User ... / Pass ... /Db ... mich auf der db einzuloggen, doch bekomme ich den Fehler

    Logging into 'master' using account 'AXXXXXXXX\Administrator'...
    ERR: Pre-> [IM002] [Microsoft][ODBC Driver Manager] Der Datenquellenname wurde nicht gefunden, und es wurde kein Standardtreiber angegeben
    ERR: (Disconnected) Unable to Connect (5) to 'master'
    ERR: Pre-> [08003] [Microsoft][ODBC Driver Manager] Die Verbindung ist nicht geöffnet
    ERR: (Disconnected) Unable to Disconnect

    Bin langsam am verzweifeln...

    Gruß Hipp

    Freitag, 8. Juni 2012 14:37
  • Hallo Hipp,

    Wie kommst Du auf Slash, die Parameter werden per Bindstrich angegeben.

    Ich erstelle mir für so was immer ein Batch Datei, das tippen in der Command Box ist nicht so prall; der Aufruf sieht dann so aus:

    "%ProgramFiles%\Microsoft SQL Server\100\Tools\Binn\SqlCmd.exe" -A -S (local)\SQL105DEV

    -A steht für die Admin Connection, -S zum Angeben des Servernamen (hier benannte Instanz), im Standard wird Win Auth verwendet, soll es über einen SQL Account gehen, musst Du mit -U und -P angeben. Wichtig: Groß-/Kleinschreibung der Parameternamen beachten, -u ist was anderes als -U.

    Siehe SqlCmd: http://msdn.microsoft.com/de-de/library/ms162773.aspx 


    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

    Freitag, 8. Juni 2012 14:49
  • Da habe ich mir was eingefange.

    Fehler :

    Sqlcmd: Fehler: Microsoft SQL Server Native Client 11.0 : SQL Server-Netzwerksch
    nittstellen: Fehler beim Anfordern des Ports für die dedizierte Administratorver
    bindung (Dedicated Administrator Connection, DAC). Vergewissern Sie sich, dass S
    QL Browser ausgeführt wird, oder überprüfen Sie das Fehlerprotokoll auf die P.
    Sqlcmd: Fehler: Microsoft SQL Server Native Client 11.0 : Anmeldungstimeout abge
    laufen.
    Sqlcmd: Fehler: Microsoft SQL Server Native Client 11.0 : Netzwerkbezogener oder
     instanzspezifischer Fehler beim Herstellen einer Verbindung mit SQL Server. Der
     Server wurde nicht gefunden, oder auf ihn kann nicht zugegriffen werden. Überpr
    üfen Sie, ob der Instanzname richtig ist und ob SQL Server Remoteverbindungen zu
    lässt. Weitere Informationen erhalten Sie in der SQL Server-Onlinedokumentation.

    Egal was ich als Instanz angebe, geht nicht.

    D:\downloads>"C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SqlCmd.exe"
    Sqlcmd: Fehler: Microsoft SQL Server Native Client 11.0 : Fehler beim Anmelden f
    ür den Anmeldenamen 'AXXXXXXXX\Administrator' wegen Triggerausführung..

    Vermutlich muss ich den Server neu installieren.

    Gruß Hipp

    Freitag, 8. Juni 2012 15:07
  • Dann ohne den Parameter -A für die DAC; eine einzige normale Connection kannst Du ja auch mit dem vorhandenen Trigger öffnen, oder?

    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

    Freitag, 8. Juni 2012 15:12
  • Leider nein. Alle werden abgewiesen wegen Trigger-Ausführung. Ich vermute ich habe im Trigger einen mächtigen BUG.

    Ich hoffe ich habe die SPs alle als *,sql abgelegt, sonst wird extrem blöd. Den wenn die alle weg wären, dann gute Nacht. Die liegen nämlich alle in der master...

    Trotzdem hoffe ich noch darauf, dass mir jemand helfen kann.

    Gruß Hipp

    Freitag, 8. Juni 2012 15:26
  • Ist das TCP/IP Protokoll aktiviert, das wird für die DAC benötigt? Wenn nicht (sicher), SQL Server Konfiguration Tool aufrufen, => SQL Server Netzwerkkonfiguration und dort TCP aktivieren; dann noch einmal mit -A für DAC versuchen.

    Falls Du mit SqlCmd nicht klar kommst: Vorgehensweise: Verwenden der dedizierten Administratorverbindung zu SQL Server Management Studio

    Wenn alle Stricke reissen: Vorgehensweise: Wiederherstellen der master-Datenbank von SQL Server 2005

    Vorher die master Datenbank kopieren (siehe vorherigen Post), die kannst Du später als "normale" Datenbank an die SQL Instanz anhängen und alle erstellte Objekte skripten lassen.


    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

    Freitag, 8. Juni 2012 15:38
  • Wenn ich mir eine neue master erstelle und dann die user die ich beisher angelegt habe dort neu hinterlege, pass die auch mit all den Berechtigungen zu denen, die bereits den weiteren Datenbanken zugeordnet sind?

    Und soll ich die alte master wegkopieren, damit das Setup sie nicht findet und neu erstellen kann?

    Gruß Hipp


    • Bearbeitet Hipp1010 Freitag, 8. Juni 2012 16:36
    Freitag, 8. Juni 2012 16:34
  • Die Berechtigungen der Logins in einer Datenbank werden auch in dieser gespeichert, die musst Du nicht neu einrichten; das ist also völlig unkritisch. Nur die Zuordnung zu Serverrollen, sofern die den verwendet wurden, müsstest Du neu festlegen.

    Wenn Du mit SQL Login gearbeitet hast, erhalten die bei der Neuanlage andere SIDs und dadurch sind die vorhandenen Datenbank-Logins dann "verwaist" (orphaned users), da sie auf eine andere SID verweisen. Das kannst Du aber einfach per Script korrigieren lassen, siehe http://gallery.technet.microsoft.com/scriptcenter/Secure-Orphaned-User-f3aceae0

    Beim Rebuild wird die master Datenbank überschrieben, deswegen vorher weg kopieren, damit Du diese dann später als normale Datenbank anhängen kannst und so an Deine SPs wieder ran kommst, falls Du die Scripte nicht gespeichert hast.


    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

    • Als Antwort markiert Hipp1010 Freitag, 8. Juni 2012 19:40
    Freitag, 8. Juni 2012 16:46
  • Scripte sind alle da :-) Ich habe gesehen, dass die master überschrieben wurde (neuer Änderungs-TS), aber der Login danach scheitert immer noch an der Trigger-Meldung. Ichhabe den Server gestoppt und die master umbenannt. Mal sehen obs jetzt geht.

    Gruß Hipp

    Freitag, 8. Juni 2012 16:52