Can't login to SQL Server due to active trigger

Question

I'm playing around with triggers on our SQL Server. After dropping some logon triggers to allow login of some users from specific IP's only. Didn't get it to work.

Actually I do some auditing with a logon trigger.

When I try to login from grafana (was one of the users mentioned above) I get the message that it can't logon due to active triggers.

Here is the trigger:

ALTER TRIGGER [LogonAuditTrigger] /* Creates trigger for logons */
ON ALL SERVER 
FOR LOGON
AS

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()

INSERT INTO [LogonAudit].[dbo].[LogonAuditing]
(
SessionId,
LogonTime,
HostName,
ProgramName,
LoginName,
ClientHost
)
SELECT
@@spid,
@EventTime,
@HostName,
@AppName,
@LoginName,
@ClientHost

END

This is the error message:

Error logging in with the username 'grafana' due to trigger execution

Disabling the trigger let Grafana login to the SQL Server, but I expect to get it work with activated trigger to log the logins.

Answer 1

It's possible you can start by changing LoginName in the audit table to allow NULL, since this may not be available the way you're pulling from the XML in the case of SQL authentication logins. (And in fact I don't think you necessarily need the heavy EventData() XML at all.)

Next, you need to make sure your audit table supports the data that you might try to insert - login names, program names, and host names can all be up to nvarchar(128), so that's how your columns and variable declarations should be declared.

Though, I don't think it needs both ClientHost and HostName since I don't know when those could be different.

Then hopefully you can log any error messages somewhere else.

ALTER TRIGGER [LogonAuditTrigger]
ON ALL SERVER 
FOR LOGON
AS
BEGIN
  DECLARE @EventTime  datetime      = getutcdate(),
          @LoginName  nvarchar(128) = ORIGINAL_LOGIN(),
          @HostName   nvarchar(128) = HOST_NAME(),
          @AppName    nvarchar(128) = APP_NAME();

  BEGIN TRY
    INSERT LogonAudit.dbo.LogonAuditing
    (
      SessionId,
      LogonTime,
      HostName,
      ProgramName,
      LoginName
    )
    SELECT
      @@spid,
      @EventTime,
      @HostName,
      @AppName,
      @LoginName;
  END TRY
  BEGIN CATCH
    INSERT dbo.LoggingTable ... stuff including ERROR_MESSAGE() ...
    THROW();
  END CATCH
END

The only other problem would be if the grafana user doesn't have the ability to write to the audit table (or the logging table). Try to insert a row manually using EXECUTE AS.