gpt4 book ai didi

sql-server - 连接日志

转载 作者:行者123 更新时间:2023-12-03 18:22:22 26 4
gpt4 key购买 nike

我希望 SQL Server 创建一个记录所有(成功)登录/连接到数据库的日志文件。日志应至少包含:

  • 连接客户端的 IP 地址和端口
  • 客户端的应用名称
  • 用户名
  • 数据库名称
  • 连接或适用事务的时间

  • 它看起来像 a server trace likely can capture all this information ,但微软表示不推荐使用服务器跟踪以支持扩展事件。

    所以我试图用扩展事件收集这些信息。对于我的测试,我使用 SQL Server 2017 Developer Edition(在 Windows 10 上的 Docker 容器中运行)和 SSMS v17.7。创建或查看扩展事件时,我以“sa”身份登录。

    到目前为止,我已经能够使用扩展事件收集大部分信息。问题是收集客户端 IP 和端口。我可以单独获得任一部分,但不能同时获得。下面列出了我正在使用的 XEvent。 connection_accept 被列出两次的事实并非错误。 SQL Server 实际上有两个完全相同名称的不同事件(!!!)。
  • Login :没有选项(字段或操作)来收集客户端 IP 或端口。至少它提供 client_hostname !
  • Logout :没有选项(字段或操作)来收集客户端 IP 或端口。这也捕获 client_hostname
  • connection_accept :
  • 收集客户端 IP,但它会屏蔽最低的八位字节(例如 192.168.1.XX)!!!
  • 收集客户端端口!好的!
  • 不收集 session_id,因此无法与 LoginLogout 事件关联。
  • 我的 EVENT SESSION 指定了 usernameclient_app_nameclient_hostname 的集合,但收集的数据中不存在这些字段/操作。 :(
  • connection_accept :
  • 收集客户端端口,但不收集客户端IP!!!
  • 有 sesstion_id,所以至少可以和 LoginLogout
  • 相关联
  • 注意: 我还没有在野外捕捉到这个特定的事件,所以我没有进一步的评论。

  • 这些事件都没有提供客户端的 IP 地址,但我将接受客户端主机名作为合理的替代。然而,获得端口号是一个真正的问题。端口号仅在 connection_accept 事件中找到,并且没有明显的方法将其与具有主机名的 login 事件相关联。简而言之,扩展事件似乎根本无法提供这种基本的客户端 IP 和端口配对。我想相信我是错的,因为它是这样的基本数据。对我所忽视的任何帮助或建议将不胜感激。

    扩展事件 DDL

    这是我一直在测试的 EVENT SESSION 的 DDL:
    CREATE EVENT SESSION [Connections] ON SERVER 
    ADD EVENT SQLSatellite.connection_accept(
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.context_info,sqlserver.database_name,sqlserver.nt_username,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.transaction_id,sqlserver.username)
    WHERE ([sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'SQLServerCEIP'))),
    ADD EVENT sqlserver.connection_accept(
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.context_info,sqlserver.database_name,sqlserver.nt_username,sqlserver.request_id,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.transaction_id,sqlserver.username)
    WHERE ([sqlserver].[not_equal_i_sql_unicode_string]([sqlserver].[client_app_name],N'SQLServerCEIP'))),
    ADD EVENT sqlserver.connectivity_ring_buffer_recorded(
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.transaction_id,sqlserver.username)
    WHERE ([sqlserver].[client_app_name]<>N'SQLServerCEIP')),
    ADD EVENT sqlserver.login(SET collect_options_text=(1)
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.transaction_id,sqlserver.username)
    WHERE ([sqlserver].[client_app_name]<>N'SQLServerCEIP')),
    ADD EVENT sqlserver.logout(
    ACTION(sqlserver.client_app_name,sqlserver.client_connection_id,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.server_instance_name,sqlserver.server_principal_name,sqlserver.session_id,sqlserver.session_nt_username,sqlserver.transaction_id,sqlserver.username)
    WHERE ([sqlserver].[client_app_name]<>N'SQLServerCEIP'))
    ADD TARGET package0.event_file(SET filename=N'c:\xevents\connections')
    WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=ON,STARTUP_STATE=OFF)
    GO

    备择方案

    除了扩展事件, "security audits" is another mechanism 没有被弃用。所以我想我也试试这个:
    USE [master]
    GO

    CREATE SERVER AUDIT [AuditTest]
    TO FILE
    ( FILEPATH = N'C:\xevents\'
    ,MAXSIZE = 2 MB
    ,MAX_FILES = 5
    ,RESERVE_DISK_SPACE = OFF
    )
    WITH
    ( QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
    )

    CREATE SERVER AUDIT SPECIFICATION [Connections]
    FOR SERVER AUDIT [AuditTest]
    ADD (SUCCESSFUL_LOGIN_GROUP),
    ADD (LOGOUT_GROUP)

    GO

    提供的字段包括“客户端应用程序”、“客户端 IP”、“ session ID”和用户名(即“服务器主体名称”)。但是,无法将其与 connection_accept XEvent 相关联。所以没有办法提取出我想要的两条信息:客户端IP和端口号。 :(

    奖金

    除了收集客户端 IP 和端口,我还对 collecting SQL Server network traffic volume 感兴趣。但是,我还没有看到任何方法可以做到这一点。提示赞赏!我只看到 vaguely related posts

    更新

    我注意到一个名为 task_address 的全局操作(字段)似乎与 loginlogoutconnection_accept 事件相关联。因此,现在可以通过组合 login:client_hostnameconnection_accept:port 来找到 IP 和端口。我唯一担心的是我找不到 task_address 的文档来验证我的观察结果。

    最佳答案

    如果您可以在您的环境中使用触发器,这里是一个解决方案。每次“登录”时,都会在[master].[dbo].[TRACETABLE]中插入一行。

    CREATE TABLE [master].[dbo].[TRACETABLE] ( 
    [EVENTDATE] DATETIME NOT NULL,
    [DBNAME] NVARCHAR(128) NULL,
    [CURRENTUSER] NVARCHAR(128) NULL,
    [HOSTNAME] NVARCHAR(128) NULL,
    [APPLICATIONNAME] NVARCHAR(128) NULL,
    [PROCEDURENAME] NVARCHAR(128) NULL,
    [USERID] SMALLINT NULL,
    [USERNAME] NVARCHAR(128) NULL,
    [SUSERID] INT NULL,
    [SUSERNAME] NVARCHAR(128) NULL,
    [IS_SERVERADMIN_SYSADMIN] INT NULL,
    [IS_DB_OWNER] INT NULL,
    [IS_DDL_ADMIN] INT NULL,
    [IS_DB_DATAREADER] INT NULL,
    [ORIGINAL_LOGIN] NVARCHAR(4000) NULL,
    [NET_TRANSPORT] SQL_VARIANT NULL,
    [PROTOCOL_TYPE] SQL_VARIANT NULL,
    [AUTH_SCHEME] SQL_VARIANT NULL,
    [LOCAL_NET_ADDRESS] SQL_VARIANT NULL,
    [LOCAL_TCP_PORT] SQL_VARIANT NULL,
    [CLIENT_NET_ADDRESS] SQL_VARIANT NULL,
    [PHYSICAL_NET_TRANSPORT] SQL_VARIANT NULL)

    GO
    GRANT INSERT ON [master].[dbo].[TRACETABLE] TO PUBLIC
    GRANT SELECT on [master].[sys].[dm_exec_connections] TO PUBLIC
    GO
    CREATE TRIGGER Logon_Trigger_Track_IP
    ON ALL SERVER FOR LOGON
    AS
    BEGIN
    INSERT INTO [master].[dbo].[TRACETABLE]
    --the auditing snippet below works fine in a
    --login trigger,
    --database trigger
    --or any stored procedure.
    SELECT
    getdate() AS EventDate,
    DB_NAME() AS DBName,
    CURRENT_USER AS CurrentUser,
    HOST_NAME() AS HostName,
    APP_NAME() AS ApplicationName,
    OBJECT_NAME(@@PROCID) AS ProcedureName,
    USER_ID() AS Userid,
    USER_NAME() AS UserName,
    SUSER_ID() AS sUserid,
    SUSER_SNAME() AS sUserName,
    IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
    IS_MEMBER('db_owner') AS [Is_DB_owner],
    IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
    IS_MEMBER('db_datareader') AS [Is_DB_Datareader],
    ORIGINAL_LOGIN() AS [ORIGINAL_LOGIN],
    ConnectionProperty('net_transport') AS 'net_transport',
    ConnectionProperty('protocol_type') AS 'protocol_type',
    ConnectionProperty('auth_scheme') AS 'auth_scheme',
    ConnectionProperty('local_net_address') AS 'local_net_address',
    ConnectionProperty('local_tcp_port') AS 'local_tcp_port',
    ConnectionProperty('client_net_address') AS 'client_net_address',
    ConnectionProperty('physical_net_transport') AS 'physical_net_transport'

    END
    GO
    ENABLE TRIGGER [Logon_Trigger_Track_IP] ON ALL SERVER

    关于sql-server - 连接日志,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/50706981/

    26 4 0
    Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
    广告合作:1813099741@qq.com 6ren.com