代码之家 › 专栏 › 技术社区 › Martin

由于活动触发器,无法登录到SQL Server

triggers sql-server

Martin · 技术社区 · 2 年前

我在SQL Server上玩触发器。删除一些登录触发器后,只允许某些用户从特定IP登录。没有起作用。

事实上,我使用登录触发器进行一些审核。

当我尝试从grafana(是上面提到的用户之一)登录时,我收到消息,由于活动触发器,它无法登录。

触发器如下:

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

这是错误消息:

由于触发器执行,使用用户名“grafana”登录时出错

禁用触发器可以让Grafana登录到SQL Server,但我希望使用激活的触发器来记录登录。

1 回复 | 直到 2 年前

Aaron Bertrand 2 年前

你可以从改变开始 LoginName 在审核表中 NULL ,因为在SQL身份验证登录的情况下,这可能不适用于从XML中提取的方式。(事实上,我不认为你一定需要重 EventData() 完全是XML。)

接下来,您需要确保审核表支持您可能尝试插入的数据——登录名、程序名和主机名都可以达到 nvarchar(128) ,所以这就是应该声明列和变量声明的方式。

不过,我不认为两者都需要 ClientHost 和 HostName 因为我不知道什么时候会有所不同。

然后希望您可以在其他地方记录任何错误消息。

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

唯一的其他问题是 grafana 用户没有能力写入审核表(或日志记录表)。尝试使用手动插入行 EXECUTE AS 。