SQL SERVER – Interesting Observation of Logon Trigger On All Servers

I was recently working on security auditing for one of my clients. In this project, there was a requirement that all successful logins in the servers should be recorded. The solution for this requirement is a breeze! Just create logon triggers. I created logon trigger on server to catch all successful windows authentication as well SQL authenticated solutions. When I was done with this project, I made an interesting observation of executing a logon trigger multiple times. It was absolutely unexpected for me! As I was logging only once, naturally, I was expecting the entry only once. However, it did it multiple times on different threads – indeed an eccentric phenomenon at first sight!

Let us first pore over our example.

Create database, table and logon trigger

/* Create Audit Database */
CREATE DATABASE AuditDb
GO
USE AuditDb
GO
/* Create Audit Table */
CREATE TABLE ServerLogonHistory
(SystemUser VARCHAR(512),
DBUser VARCHAR(512),
SPID INT,
LogonTime DATETIME)
GO
/* Create Logon Trigger */
CREATE TRIGGER Tr_ServerLogon
ON ALL SERVER FOR LOGON
AS
BEGIN
INSERT INTO AuditDb.dbo.ServerLogonHistory
SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
END
GO

Login using SQL Authentication and check audit table

SQL SERVER - Interesting Observation of Logon Trigger On All Servers logintr3

Login using Windows Authentication and check audit table

SQL SERVER - Interesting Observation of Logon Trigger On All Servers logintr1

The above example clearly demonstrates that there are multiple entries in the Audit table. Also, on close observation it is evident that there are multiple process IDs as well. Based on these two observations I can come to one conclusion. Similar actions like login to the server took place multiple times.

Question to readers:

Have you ever experienced this kind of situation? If yes, then have you received similar entries?

For those, who have not experienced this phenomenon yet, they can recreate this situation very quickly by running the above script, and then you all can post your observations and conclusions here.

I am very much interested in learning the cause that triggers multiple entries. Valid suggestions and explanations will be published on this blog with due credit.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, , ,
Previous Post
SQL SERVER – Find Hostname and Current Logged In User Name
Next Post
SQL SERVER – Fix : Error : SQLDUMPER library failed initialization. Your installation is either corrupt or has been tampered with. Please uninstall then re-run setup to correct to correct this problem. in a modal dialog with the title SQL Writer

Related Posts

45 Comments. Leave new

  • Very strange.

    I was playing around with this and observed some more weird behavior. I was logged in SSMS using windows auth. I ran the script you mentioned above(created the DB,table and trigger). Then, I disconnected the connection and reconnected after 10 secs. Now, when I (select *) the ServerLogonHistory table, the first entry is related to the disconnection(based on the timestamp). The SystemUser was “NT AUTHORITY\SYSTEM”. There were 4 other entries related to the successful login(again based on the timestamp). One of them has “NT AUTHORITY\SYSTEM” as its SystemUser and the others as “servername\username”. The SPIDs for both the “NT AUTHORITY\SYSTEM” entries are the same(53).

    Reply
  • Greg Mattias
    May 27, 2009 1:02 pm

    Dude,

    same here, I am finding very strange behaviour any help.

    GRM

    Reply
  • I never thought about it or checked it before…I’m off now to see if I can experience the same thing.

    Reply
  • Argenis Fernandez
    May 27, 2009 5:54 pm

    Pinal,

    Maybe Intellisense is to blame for the additional logons…you can extend the logon trigger to include APP_NAME()

    Regards,

    Argenis

    Reply
    • Argenis, I came across this same thing. I actually disabled intellisense in the registry and still got 3 sessions upon first logging into SSMS.

      Reply
  • What you are probably seeing is the connections to Object Explorer, Intellisense, and your query window. These all have their own connections.

    Reply
  • Jeremy Lowell
    May 27, 2009 7:53 pm

    Pinal,

    I’ve done this before in a different manner. I created a trace (capture login events), dump to file, load to table, cleanup. Similar behavior; but it’s also expected as there are numerous logins via SSMS, VS, etc… due to the different contexts of the app. I.E. you will authenticate seperatley for server explorer than a query window etc…

    In my experience, the auditors loved having this information even though it was overkill in my opinion.

    Reply
  • I add APP_NAME() and it shows me connecting to Management Studio and also when I opened a query window, as a result, multiple entries added to my table. Thanks for the challenge, very interesting.

    Reply
  • Another update.

    I deleted all the data from the ServerLogonHistory table and was doing some other stuff(non-sql). When I logged back into SSMS, I saw a lot entries with timestamps of the time when I was working on something else or infact doing nothing. This is clearly not an issue related to connections to Object Explorer, Intellisense, and query window as Simon mention. Anybody wants to chip in. I am stumped.

    Reply
  • reena trikha
    May 27, 2009 8:40 pm

    Dear Mr. Dave
    I tried the above mentioned code in my Sql Server @005. But I am getting this error
    logon is an invalid event type

    I need to know the reason
    Thanks
    Reena

    Reply
  • Sriki
    If you have the SQL agent running it will show up in the table as well. If reporting services is running it will show up a ton of times in the table too. Any services and applications you have running on the machine or remote apps and services that use your SQL Server instance will all show up. I find Reporting Services to be the biggest though, generally logging in every minute – and therefore adding an another entry into the table. Even if you refresh an open table in Management studio, that counts as another login and adds another record to the table. Jobs that run add records, as well as clean up tasks. You’d be surprised how much activity is going on behind the scenes when you are doing nothing with SQL Server.

    Reply
  • Hi Simon,

    Thanks for info. Pardon my ignorance. I now disabled SSIS/SSRS/SSAS/SQL Agent and as far as I know there is no app using my instance of SQL. But, I still see multiple entries in the table(even for the time when I am not logged into SSMS. Am I missing anything else??

    Sriki

    Reply
  • Greg hilsheimer
    January 20, 2010 10:05 pm

    Re:multiple trigger events
    We are getting a logon trigger event fired on a .Net connection with pooling on for a simple query.

    If I do not do a query it only fires once, pooling is working
    but if execute an sql statement on connection for example:

    “SELECT 1” an perform a .Net Execute scalar the trigger is fired.

    The spids are always the same.

    If take query out and open an close connection with same connection string, event is only fired once

    Reply
  • This is excellent, but it doesnt log failed logon attempts how can I log failed logon’s

    eg

    INSERT INTO AuditDb.dbo.ServerLogonHistory
    SELECT SYSTEM_USER, HOST_NAME(),USER, @@SPID, GETDATE(), APP_NAME(), DB_NAME()
    END

    Reply
  • This is excellent, but it doesnt log failed logon attempts how can I log failed logon’s

    The login trigger only fires after successful authentication to the server. For failed logins, you still have to rely on SQL Server auditing.

    Reply
  • I copied the code and pasted it in the SQL2005 but i got this error message

    Msg 1084, Level 15, State 1, Procedure Tr_ServerLogon, Line 3
    ‘LOGON’ is an invalid event type.

    Reply
  • Hii,

    In the similar way..

    Can any one please let me know the process that how can we create trigger so that it grab the job failure logs when the job fails and that error log details will sends to mail via SMTP.

    I am using SQL 2000.

    Please provide some URL’s for this task is much helpfull for me.

    Thanks in Advance.
    Kareem.

    Reply
  • Hey Men,

    Thx for the Help us

    All work Ok

    Greating from Lima . Peru

    Reply
  • Hi,

    I´ve been trying your logon trigger on SQL2K5. It seems to work when I log on with Windows Authentication but logon fails with SQL Server Authentication…. for all users except sa.

    I see that you tested sq auth for sa, have you also tried for a normal user? :/

    ( I get Error: 17892 )

    Reply
  • Ritesh Kumar
    July 8, 2011 6:22 pm

    Hi,
    I was facing same problem.
    When I have inserted ORIGINAL_DB_NAME() in ServerLogonHistory table I observed in my cases It is happening due to other applications like report sever etc. is also accessing the sql server. When I stopped those services which solved my problem. Logon trigger fires when new session is established (For example when we open a new query page due to its execute USE YourDb) .
    To stop such entry we can write :

    SELECT SYSTEM_USER,USER,@@SPID,GETDATE() WHERE ORIGINAL_DB_NAME() = ”

    Reply
  • The same error also comes even if the database has not been dropped. This comes when you login with Windows authetication and occurs if the user does not have permission over AuditDB database to execute the trigger. Solution for this is to create Trigger with Execute as sa clause
    i.e.
    CREATE TRIGGER Tr_ServerLogon
    ON ALL SERVER WITH Execute As ‘sa’ FOR LOGON
    AS
    BEGIN
    INSERT INTO AuditDb.dbo.ServerLogonHistory
    SELECT SYSTEM_USER,USER,@@SPID,GETDATE()
    END
    GO

    Reply

Leave a Reply

Menu