Friday, July 29, 2016

Using a SQL Server Logon Trigger

One of my customers has an instance where many people log in as sa. We're planning to change the password and rename the sa login, but we first need to know who is using it, and for what. To do this, I've put together an sa Logon Trigger. While I typically avoid triggers, I believe this one is very manageable because it is specific to only one login, and it is only collecting details from each login attempt.

First I create a LogonAudit table to collect the logon details.

     USE DBA;
     CREATE TABLE LogonAudit
     (
      LogonTime datetime,
      HostName varchar(50),
      ProgramName varchar(500),
      LoginName varchar(50),
      OriginalLoginName varchar(50),
      ClientHost varchar(50)
     ) ON [DBA_Data]
    GO

Now for the trigger.

CREATE TRIGGER tr_LogonTrigger
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
/* 
Used with 'sa logon audit', to determine who is logging in with sa, when and from where.
Auth:  Me
Date:  7/28/2016  
*/
BEGIN
    DECLARE
@LogonTriggerData xml,
       @EventTime datetime,
       @LoginName varchar(50),
       @ClientHost varchar(50),
       @LoginType varchar(50),
       @HostName varchar(50),
       @AppName varchar(500)

  SET @LogonTriggerData = eventdata()
  SET @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
  SET @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
  SET @ClientHost = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
  SET @HostName = HOST_NAME()
  SET @AppName = APP_NAME()

  --- Add condition to log only succesful sa logins
  IF((ORIGINAL_LOGIN() = 'sa'))
  INSERT INTO DBA..LogonAudit (
       LogonTime,HostName,ProgramName,LoginName,OriginalLoginName,ClientHost )
  SELECT
       @EventTime,
       @HostName,
       @AppName,
       @LoginName,
       ORIGINAL_LOGIN(),
       @ClientHost
END

GO


How to test it?  Easy. Launch SSMS and open a query window.  Look at the lower right, you should see you're logged with your domain login.  Right click the query screen, choose the 'Connection' option, and then 'Change Connection...'.  Here you will change the authentication to 'SQL Server Authentication', then input the sa login and password and hit 'Connect'.  That's it!  That single action was a logon attempt by the sa login, and it should have triggered an insert into your LogonAudit table.  

This is what I captured in my local instance:


















But wait.  We logged in one time, and we have more than one entry recorded in our LogonAudit table.  Why?  This happens because there are multiple SQL Server Services running in parallel.  If you were to go in and stop all SQL Server Services except for SQL Server (MSSQLSERVER), then you would only capture one entry in the table for each login attempt.

Maybe you want to disable it for some reason?  Rather than deleting it, you can just DISABLE/ENABLE with these statements:

     -- DISABLE TRIGGER
     DISABLE TRIGGER tr_LogonTrigger ON ALL SERVER
     -- RE-ENABLE TRIGGER
     ENABLE TRIGGER tr_LogonTrigger ON ALL SERVER

See more details here regarding the SQL Server Logon Trigger:
    https://msdn.microsoft.com/en-us/library/bb326598(v=sql.110).aspx


4 comments:

  1. Thanks for your post, in my experience logon triggers can become really expensive to the point of creating serious performance issues depending on the number of logon events happening.

    A small improvement you can do in this trigger is first of all validate

    IF((ORIGINAL_LOGIN() = 'sa'))

    that way you can exit the trigger without further action in 99.9% of the cases. (unless you have everyone using 'sa', of course)

    Processing the xml and then asking would be a waste of resources.

    ReplyDelete
  2. Great script, but instead of logging it to a table we put it into the SQL logs. This change meant I would not need to manage another db on my servers; by using "exec xp_logevent" we achieve the same as a table and it seems to work quite well for our means and tracks the users that need to be tracked.

    ReplyDelete
    Replies
    1. Adam, I'm curious to know how you'd change the code so that this would show up in the logs. I'm still novice with TSQL code.

      Delete
    2. concatenate all the information into an @Message variable and replace the Insert Into and following Select section with the xp_logevent call:
      exec xp_logevent 1000001, @Message, 'Informational'

      Delete