Регистрация соединения

Question

Регистрация соединения

Я хочу, чтобы SQL Server создал запись в файле журнала всех (успешных) входов / подключений к базе данных. Журнал должен содержать как минимум:

IP-адрес и порт подключающегося клиента
имя приложения клиента
имя пользователя
имя базы данных
время соединения или применимой транзакции

Похоже, что трассировка сервера, вероятно, может захватить всю эту информацию, но Microsoft говорит, что трассировки сервера устарели в пользу расширенных событий.

Поэтому я пытаюсь собрать эту информацию с помощью расширенных событий. Для своих тестов я использую SQL Server 2017 Developer Edition (работает в контейнере Docker в Windows 10) и SSMS v17.7. Я вошел как 'sa', когда я создаю или просматриваю расширенные события.

До сих пор я смог собрать большую часть этой информации с помощью расширенных событий. Проблема заключается в сборе IP-адреса клиента и порта. Я могу получить любую часть в изоляции, но не обе одновременно. XEvents, которые я использую, перечислены ниже. Дело в том, что connection_accept указан дважды не является ошибкой. На самом деле SQL Server имеет два разных события с одинаковым именем (!!!).

Login: нет опции (поле или действие) для сбора IP-адреса клиента или порта. По крайней мере, он предлагает client_hostname!
Logout: нет опции (поле или действие) для сбора IP-адреса клиента или порта. Это также захватывает client_hostname,
connection_accept:
- Собирает IP-адрес клиента, но он маскирует нижний октет (например, 192.168.1.XX)!!!
- Собирает клиентский порт! Хорошо!
- Не собирает session_id, поэтому его нельзя соотнести с Login или же Logout События.
- мой EVENT SESSION указывает коллекцию username, client_app_name а также client_hostname, но ни одно из этих полей / действий не присутствует в собранных данных.:(
connection_accept:
- Собирает клиентский порт, но не собирает клиентский IP!!!
- Имеет sesstion_id, поэтому, по крайней мере, его можно соотнести с Login а также Logout
- Примечание: я еще не поймал это конкретное событие в дикой природе, поэтому у меня нет дальнейших комментариев по этому поводу.

Ни одно из этих событий не предоставляет IP-адрес клиента, но я приму имя хоста клиента в качестве разумной замены. Однако получение номера порта является реальной проблемой. Номер порта находится только в connection_accept событие, и нет очевидного способа соотнести это с login событие, которое имеет имя хоста. Короче говоря, кажется, что Extended Events просто не может доставить это базовое клиентское соединение IP-портов. Я хочу верить, что я не прав, потому что это такие элементарные данные. Любая помощь или предложения о том, что я пропускаю, будет высоко ценится.

Расширенное событие DDL

Вот DDL для EVENT SESSION Я тестировал:

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

альтернативы

Помимо расширенных событий, "аудит безопасности" является еще одним механизмом, который не считается устаревшим. Так что я подумал, что тоже попробую:

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-адрес клиента", "идентификатор сеанса" и имя пользователя (а именно: "имя принципа сервера"). Тем не менее, нет способа соотнести это обратно с connection_accept XEvent. Таким образом, нет никакого способа извлечь две части информации, которую я хочу: IP-адрес клиента и номер порта.:(

бонус

Помимо сбора IP-адреса и порта клиента, я также заинтересован в сборе объема сетевого трафика SQL Server. Тем не менее, я еще не видел какой-либо способ сделать это либо. Советы приветствуются! Я вижу только смутно связанные посты.

Обновить

Я заметил глобальное действие (поле) под названием task_address кажется коррелирующим login, logout а также connection_accept События. Таким образом, теперь возможно найти IP и порт, комбинируя login:client_hostname а также connection_accept:port, Меня беспокоит только то, что я не могу найти документацию для task_address чтобы подтвердить мои наблюдения.

6

sql-server monitoring network-monitoring extended-events

Источник

user284758 05 июн '18 в 18:44

1 ответ

Другие вопросы по тегам sql-server monitoring network-monitoring extended-events

user2344394 29 окт '18 в 16:16 2018-10-29 16:16 · Answer 1 · 2018-10-29 16:16

Если вы можете использовать триггер в вашей среде, вот решение. Каждый раз, когда происходит "вход", в [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]
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