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)