主页 / dba / 问题 / 286753
Accepted
Bergmeister
Asked: 2021-03-10 08:23:10 +0800 CST

扩展事件登录审核 - 减少登录噪音

  • 772

我们正在寻找具有可用过滤功能的最佳 sql 登录审计解决方案。

到目前为止，我们已经研究了服务器审计和分析器，但得出的结论是扩展事件看起来最有希望，除非有人可以向我们展示其他方式。

问题是，有什么好的方法可以过滤不必要的登录事件吗？例如，当我使用 SSMS 连接时，会立即添加大约 10 个登录事件，如果我将 SSMS 连接到实例，则会继续堆积。这是为什么？

这是我们当前的实现：

USE master;
GO
-- Create the Event Session
IF EXISTS
    (
        SELECT
                  *
            FROM  sys.server_event_sessions
            WHERE name = 'ServerLoginAudit'
    )
    DROP EVENT SESSION ServerLoginAudit ON SERVER;
GO
EXECUTE xp_create_subdir 'D:\audits\Sessions';
GO
CREATE EVENT SESSION ServerLoginAudit
    ON SERVER
    ADD EVENT sqlserver.login
        (SET
             collect_database_name = (1)
           , collect_options_text = (1)
         ACTION
             (
                 sqlserver.sql_text
               , sqlserver.nt_username
               , sqlserver.server_principal_name
               , sqlserver.client_hostname
               , package0.collect_system_time
               , package0.event_sequence
               , sqlserver.database_id
               , sqlserver.database_name
               , sqlserver.username
               , sqlserver.session_nt_username
               , sqlserver.client_app_name
               , sqlserver.session_id
               , sqlserver.context_info
               , sqlserver.client_connection_id
             )
        )

    ADD TARGET package0.event_file
        (SET filename = N'D:\audits\Sessions\ServerLoginAudit.xel', max_file_size = (20), max_rollover_files = (2))
    WITH
        (
            STARTUP_STATE = OFF
          , TRACK_CAUSALITY = ON
        );
/* start the session */
ALTER EVENT SESSION ServerLoginAudit ON SERVER STATE = START;
GO
sql-server audit

1 个回答

  1. Best Answer

    连接众多的原因

    这些是由 SSMS 引起的连接，主要为您提供对象浏览器的视觉结构。此外，当您使用 SSMS 时，您打开的每个新选项卡都是一个不同的会话（以及更多连接 - 请参阅SSMS 的多个连接）。IntelliSense 还会创建额外的连接，如果您删除我建议作为解决方法的查询上的谓词，您会看到。

    尝试在 CMD 提示符下使用这样的sqlcmd 实用程序连接：

    sqlcmd -A

    当我使用 SQLCMD 连接时，您提供的扩展事件会话中只报告了一个日志。

    解决方法

    我建议您过滤之后查询的数据，而不是过滤您收集的数据，这是一种方法：

    WITH
    event_data AS (
        SELECT CAST(event_data AS XML) AS event_data_xml
        FROM fn_xe_file_target_read_file('D:\audits\Sessions\ServerLoginAudit*.xel',NULL,NULL,NULL)
    )
    , event_data_fields AS (
        SELECT 
             DATEADD(ms, -DATEPART(ms, event_data_xml.value('(/event/@timestamp)[1]', 'datetime')), event_data_xml.value('(/event/@timestamp)[1]', 'datetime')) AS interval
            , event_data_xml.value('(/event/data[@name="database_name"]/value)[1]', 'varchar(250)') AS database_name
            , event_data_xml.value('(/event/action[@name="client_app_name"]/value)[1]', 'nvarchar(250)') AS client_app_name
            , event_data_xml.value('(/event/action[@name="client_hostname"]/value)[1]', 'nvarchar(250)') AS client_hostname
            , event_data_xml.value('(/event/action[@name="server_principal_name"]/value)[1]', 'nvarchar(250)') AS server_principal_name
            , event_data_xml.value('(/event/action[@name="nt_username"]/value)[1]', 'nvarchar(250)') AS nt_username
        FROM event_data
    )
SELECT 
      interval
    , database_name
    , client_app_name
    , client_hostname
    , server_principal_name
    , nt_username
FROM event_data_fields
WHERE 
    client_app_name NOT IN ('SQLServerCEIP', 'Microsoft SQL Server Management Studio - Transact-SQL IntelliSense')
GROUP BY
      interval
    , database_name
    , client_app_name
    , client_hostname
    , server_principal_name
    , nt_username;

    使用该方法，如果您一开始没有找到所需的信息，您可以减少谓词以显示收集的所有数据，并更准确地报告当时发生的所有连接。此外，如果您按应用程序名称过滤收集的数据，则会对您的审计造成破坏，因为您可以更改 SSMS 的应用程序名称并在不被注意的情况下登录您的服务器。

    • 1

相关问题