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
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 (https://blog.sqlauthority.com)
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).
Dude,
same here, I am finding very strange behaviour any help.
GRM
I never thought about it or checked it before…I’m off now to see if I can experience the same thing.
Pinal,
Maybe Intellisense is to blame for the additional logons…you can extend the logon trigger to include APP_NAME()
Regards,
Argenis
Argenis, I came across this same thing. I actually disabled intellisense in the registry and still got 3 sessions upon first logging into SSMS.
What you are probably seeing is the connections to Object Explorer, Intellisense, and your query window. These all have their own connections.
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.
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.
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.
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
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.
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
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
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
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.
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.
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.
Hey Men,
Thx for the Help us
All work Ok
Greating from Lima . Peru
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 )
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() = ”
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