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