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 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 was doing 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


Login using Windows Authentication and check audit table

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 (http://blog.SQLAuthority.com)

About these ads

36 thoughts on “SQL SERVER – Interesting Observation of Logon Trigger On All Servers

  1. 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).

  2. Pinal,

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

    Regards,

    Argenis

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

  4. 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.

  5. 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.

  6. 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.

  7. 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

  8. 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.

  9. 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

  10. Pingback: SQL SERVER – Interesting Observation of Logon Trigger On All Servers – Solution Journey to SQL Authority with Pinal Dave

  11. Pingback: SQL SERVER – Fix : Error : 17892 Logon failed for login due to trigger execution. Changed database context to ‘master’. Journey to SQL Authority with Pinal Dave

  12. 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

  13. 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

  14. 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.

  15. 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.

  16. 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.

  17. 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 )

  18. 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() = ”

  19. 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

  20. Pingback: SQL SERVER – Significance of Various Kinds of Triggers- Quiz – Puzzle – 2 of 31 « SQL Server Journey with SQL Authority

  21. hi pinal,
    i have a question regarding putting session time limit on users connecting to the database in sql server 2000, how do i implement it using logon triggers or with any other way?

  22. I ran your code, but now I can’t logon to SQL Server> I get the message ‘Cannot connect to Nick-PC\SQL.
    Additional Information:
    Login failed for login ‘Nick-PC\Nick’ due to trigger execution.
    Changed database context to master.
    Changed language setting to us_english. (Microsoft SQL Server, Error, 17892)

    How do I log into SQL Server to fix this?

    • In fact I can’t even log in as sa, because I get the same error. This is a disaster. I’m going to have to uninstall and reinstall SQL Server on my machine. Thanks for nothing PD.

  23. Hi Pinal,

    2 questions….

    1) Where is this trigger stored? On the serverlogonhistory table?

    2) I suspect I can put a “where clause” in the trigger create script, such as…

    WHERE System_User not in (‘sa’, ‘_SYSADMIN_’, ‘mydomain\myusername’)

    My goal is to capture only the logons for the application that runs on top of sql and not the logins by our service accounts, sqlreporting services, management studio connections, etc. I would also store the logon table not in a separate db but in the sql db associated to the application that we use.

    Please advise.

    Monica

  24. I run into similar issue actually I had a stored procedure that was called from within such similar trigger and my mail box (received it in my Gmail account) was filled up by that same message about security warning any help or findings pls

  25. Pingback: SQL SERVER – Weekly Series – Memory Lane – #031 | Journey to SQL Authority with Pinal Dave

  26. Hi Pinal,

    I’ve similar situation where I developed a trigger with database mail for a particular login is logging in, I received a hundreds of emails for each successful login.

    Thanks,

  27. Tried this on my test CRM system. Within minutes, I’d logged 35,000 rows! I thus add the code for dropping the trigger if you experience similar, in order to reduce panic:

    DROP TRIGGER tr_serverlogon
    ON ALL SERVER
    GO

    (if you don’t put in ‘ON ALL SERVER’ it will tell you that the trigger doesn’t exist. If you drop the table that the trigger fires the data to, you’ll end up locking everyone out of the server. EEK!).

    @Nick, I know it’s well over a year after – but the Dedicated Administrator Connection *might* have saved you http://technet.microsoft.com/en-us/library/ms178068%28v=sql.105%29.aspx

  28. i have faced the same issue. but not able to get the answer for this and for every time i am getting four entries and at that time i thought,we have four system databases and because of this we r getting the four entries.

  29. I have read both this post and its follow-up, and I am still confused as to what exactly causes a logon trigger to fire multiple times for what appears (on the surface, at least) to be the establishment of a single session.

    In my case, I want to record any sessions established by a specific account using SQL Server Management Studio. Here’s the relevant section of my logon trigger:

    DECLARE
    @LogonTriggerData xml,
    @EventTime datetime,
    @LoginName varchar(50),
    @HostName varchar(50),
    @LoginType varchar(50),
    @ApplicationName varchar(50)

    SET @LogonTriggerData = EventData()

    SET @EventTime = @LogonTriggerData.value(‘(/EVENT_INSTANCE/PostTime)[1]‘, ‘datetime’)
    SET @LoginName = @LogonTriggerData.value(‘(/EVENT_INSTANCE/LoginName)[1]‘, ‘varchar(50)’)
    SET @HostName = CONVERT(varchar(50), HOST_NAME())
    SET @LoginType = @LogonTriggerData.value(‘(/EVENT_INSTANCE/LoginType)[1]‘, ‘varchar(50)’)
    SET @ApplicationName = CONVERT(varchar(50), APP_NAME())

    IF @LoginName LIKE ‘%Test%’ AND @ApplicationName LIKE ‘Microsoft SQL Server Management Studio%’

    BEGIN

    INSERT INTO [SQLAdmin].[audit].[LogonAuditLog](
    [PostTime],
    [LoginName],
    [HostName],
    [LoginType],
    [ApplicationName])
    VALUES (
    @EventTime,
    @LoginName,
    @HostName,
    @LoginType,
    @ApplicationName)

    END

    During initial tests, I saw three rows written to my log table each time I connected using SSMS, but only one when using OSQL.

    On my machine, SQL Server Configuration Manager (2008 R2) lists the following services running in addition to the database engine itself: the Full-text Filter Daemon Launcher; SQL Agent; SSAS, SSIS; and SSRS. If the LOGON event fires once for each running service, why don’t I see six rows in the log table for each connection rather than three (from SSMS) or one (from OSQL)?

    I would understand it if, say, the LOGON event fired once for an SSMS query session, once for an Object Explorer session and once for an Intellisense session; however, I don’t have Intellisense enabled, and the APP_NAME() value recorded for each of the rows produced by an SSMS connection was ‘Microsoft SQL Server Management Studio – Query’ so the output from the EVENTDATA() function doesn’t provide a way to confirm that theory.

    In addition to this discussion, I have thus far found only one other mention of this phenomenon, and that was in another blog. Does anyone know whether there is any Microsoft documentation on the topic? I’d like to be able to code the trigger such that I can filter out the extraneous rows per SSMS session or at least be able to explain them satisfactorily.

  30. I am also facing the same issue of multiple entries with LOG on trigger , my requirement I just want single entry for successful login. I also want to record logoff entry as well can anyone give me script /solution for log off.

    Thank you.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s