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!

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.

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

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.

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.

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.

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

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 :

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

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.

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

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.

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:

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.

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.

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.