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

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

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

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

      Reply
  • 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_’, ‘mydomainmyusername’)

    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

    Reply
  • 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

    Reply
  • 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,

    Reply
  • 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 https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms178068(v=sql.105)

    Reply
  • This is excellent but after executing this trigger.we r unable to log in at sql server 2008 R2.

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

    Reply
  • Is your login being used for SQL Server Agent

    Reply
  • Bob Christensen
    March 29, 2014 6:04 pm

    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.

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

    Reply
  • Hi,
    Did anyone ever figure out how to get just one entry in the logging table from a trigger?

    Reply
  • I have recently encountered this. I am creating a LOGON trigger and setting the max session count to (let’s say 10). As soon as I log into SSMS, I have 3. I tried disabling Intellisense but I still get 3 sessions upon first logging in SSMS. I still haven’t seen a legitmate reason why multiple session IDs are created.

    Reply
  • Hi,

    We had a requirement at one of our client site to audit logins connecting from SSMS . There were group of logins created on the instance which were used by multiple users. It was difficult to track and identify the users as they were using a common login.
    The only possibility seen here is to record using IP address of the client machine.

    In my case, I record Login name,Login Time and most important Client IP Address established by a specific account using SQL Server Management Studio.

    –Step 1
    USE [AudtiDB]
    GO
    –Create Database
    Use [master]
    go
    Create database AuditDB
    go

    –Step 2
    Use [AudtiDB]
    go
    –Create Audit table
    CREATE TABLE [dbo].[AudtiLoginTbl](
    [Host_Name] [varchar](255) NULL,
    [Login_Name] [varchar](255) NULL,
    [login_time] [datetime] NULL,
    [Client_Address] [varchar](48) NULL,
    [Server_Address] [varchar](48) NULL,
    [Application_Name] [varchar](255) NULL
    ) ON [PRIMARY]

    GO

    –Step 3
    –Create Logon trigger
    Use [master]
    go
    CREATE TRIGGER [AudtiLoginTrgr]
    ON ALL SERVER FOR LOGON
    AS
    BEGIN
    INSERT top (1) INTO [AudtiDB].[dbo].[AudtiLoginTbl]
    –the auditing snippet below works fine in a
    –login trigger,
    –database trigger
    –or any stored procedure.
    SELECT
    SP.hostname,
    SP.LogiName,
    –FORMAT ( login_time, ‘yyyy/MM/dd hh:mm’,’en-US’ ),
    sp.login_time,
    EC.Client_Net_Address,
    EC.local_net_address,
    App_Name()
    From sys.sysprocesses SP inner join sys.dm_exec_connections EC
    on ec.session_id = sp.spid where
    APP_NAME() = ‘Microsoft SQL Server Management Studio – Query’
    and ec.session_id = @@SPID
    –and DB_Name(sp.dbid) ‘master’
    –and DB_Name(sp.dbid) ‘msdb’
    END
    GO

    ENABLE TRIGGER [AudtiLoginTrgr] ON ALL SERVER
    GO

    The above trigger seems to be working fine with minimal logging in the table.You can further customized this trigger base on your requirement.

    Note: These trigger will record only when query has been executed from the query management..

    Cheers!!!

    Reply
  • trigger is working fine but when i try to login with user having dbowner rights it is not allowing me to log in but able to log in with sa privileges..
    Strange behavior!!

    Reply
  • Solved and working fine…

    –Step 1 –Create Database
    Use [master]
    go
    Create database [AuditDB]
    go

    –Step 2 –Create Audit table
    Use [AuditDB]
    go
    CREATE TABLE [dbo].[AudtiLoginTbl](
    [Host_Name] [varchar](255) NULL,
    [Login_Name] [varchar](255) NULL,
    [login_time] [datetime] NULL,
    [Client_Address] [varchar](48) NULL,
    [Server_Address] [varchar](48) NULL,
    [Application_Name] [varchar](255) NULL
    ) ON [PRIMARY]
    GO

    –Grant following permission to all the logins who does not have sa priveleges
    use [master]
    go
    GRANT VIEW SERVER STATE TO TestUser2
    go
    Use [AuditDB]
    go
    Grant select on [dbo].[AudtiLoginTbl] to TestUser2
    go
    Grant Insert on [dbo].[AudtiLoginTbl] to TestUser2
    go

    –Step 3
    –Create Logon trigger
    Use [master]
    go
    CREATE TRIGGER [AudtiLoginTrgr]
    ON ALL SERVER FOR LOGON
    AS
    BEGIN
    INSERT top (1) INTO [AuditDB].[dbo].[AudtiLoginTbl]
    –the auditing snippet below works fine in a
    –login trigger,
    –database trigger
    –or any stored procedure.
    SELECT
    SP.hostname,
    SP.LogiName,
    –FORMAT ( login_time, ‘yyyy/MM/dd hh:mm’,’en-US’ ),
    sp.login_time,
    EC.Client_Net_Address,
    EC.local_net_address,
    App_Name()
    From sys.sysprocesses SP inner join sys.dm_exec_connections EC
    on ec.session_id = sp.spid where
    APP_NAME() = ‘Microsoft SQL Server Management Studio – Query’
    and ec.session_id = @@SPID
    –and DB_Name(sp.dbid) ‘master’
    –and DB_Name(sp.dbid) ‘msdb’
    END
    GO
    ENABLE TRIGGER [AudtiLoginTrgr] ON ALL SERVER
    GO
    –The above trigger seems to be working fine with minimal logging in the table.You can further customized this trigger base on your requirement.
    –Note: These trigger will record only when query has been executed from the query management..

    Reply
  • Hi Pinal,
    My auditors are giving me a hard time because of this. I am tired of explaining this to them. Is there a way that I stop the audit collecting this info? In my case, I actually see a login connection success in my Error log file.

    Reply
  • How to disable Windows Authentication Login to particular Database?

    Reply
    • By not giving permission? Authentication mode is a server level property. Have you looked at contained users?

      Reply
  • Stephane St-Louis
    April 6, 2017 8:50 pm

    I know I’m years later… I stumbled upon this post while looking up something else, related to logon triggers.

    My answer to your question: I do expect multiple connections on the server when the user is connecting with Management studio. SSMS will open at least 1 connection for the object browser, plus 1 connection for each query window the user will open.

    Using logon triggers is dangerous. As several users noted, if the slightest error occurs during the trigger execution (ex: the logging user does not have (and SHOUT NOT) write permissions on the audit table, the trigger will fail, the transaction will be rolled back and connection will be refused, potentially locking everybody out of the server, including sysadmins. (as noted by other users, the Dedicated Admin Connection is the solution)

    That’s when someone shouts out “just put your code within a TRY/CATCH block to swallow up the error”. Unfortunately, and surprisingly, this does NOT work. Any trivial error, even with a TRY/CATCH block will set the XACT_STATE to -1 (uncommitable transaction). Upon exiting the trigger, the transaction will be rolled back and connection refused.

    You can spend a lot of time and effort trying to validate all possible causes for errors to occur, but it’s unrealistic to think that you foretell every possible error. That’s why I can’t figure out why “by design” Microsoft engineers would have decided that it would be such an indispensable feature for SQL Server to prevent managing unhandled errors in a logon trigger, where errors are so critical. Therefore, it must be a BUG… But then… I can’t figure out why this bug would never have been fixed. I can’t be the only one on earth trying to handle errors in logon triggers, that’s impossible. In fact, I’ve googled it up and found users complaining about this for several years back. Sadly, it seems to affect *ALL* versions of SQL Server do date…

    I just opened a request for support at Microsoft regarding error handling in triggers. I’m waiting for an answer.

    Reply
    • I think a logon trigger is too dangerous to use unless you design it to always allow SYSADMINS to connect and log their accesses in the sql log so that there can’t be a failure to log which can block the connection. Instead of try catch blocks just use IF statements to give a different behavior depending on whether the login has SYSADMIN role or not. Logon triggers are not for the faint of heart. All the advice about staying away from them is not unfounded. If you get a good design it will work though (and not lock you out). If you always make sure that a sysadmin has an connected window in SSMS, you can always disable the trigger. It is new connections that are a problem.

      Reply
  • I am sure this has been suggested before but to avoid a logon trigger locking up all users is because
    a) the table is not created in master database or
    b) the user has no write access to the table

    These can be easily overcome by
    a) either creating the audit table in the master db (not recommended) or use the full qualified
    name for the audit table e.g. database.schema.tablename
    b) once the audit table is created, grant insert rights to public GRANT INSERT ON AuditTable
    TO public;

    I have done this and it works fine but I am struggling with the multiple lines in the table

    Reply

Leave a Reply

Menu