Login/Register
Connection Logging
Asked Answered
sql-servermonitoringnetwork-monitoringextended-events
V

1

6

I want SQL Server to create a log file recording of all (successful) logins/connections to the database. The log should contain minimally:

  • IP address and port of the connecting client
  • application name of the client
  • username
  • database name
  • time of connection or applicable transaction

It looks like a server trace likely can capture all this information, but Microsoft says that server traces are deprecated in favor of Extended Events.

So I am attempting to gather this information with Extended Events. For my tests I'm using SQL Server 2017 Developer Edition (running in a Docker container on Windows 10) and SSMS v17.7. I'm logged in as 'sa' when I create or view the extended events.

So far I've been able to collect most of this information with Extended Events. The problem is collecting the client IP and port. I can get either part in isolation, but not both simultaneously. The XEvents I'm using are listed below. The fact that connection_accept is listed twice is not a mistake. SQL Server actually has two different events with the exact same name(!!!).

  • Login: no option (field or action) to collect client IP or port. At least it does offer client_hostname!
  • Logout: no option (field or action) to collect client IP or port. This also captures client_hostname.
  • connection_accept:
    • Collects client IP, but it masks the lowest octet (e.g. 192.168.1.XX)!!!
    • Collects client port! Good!
    • Does not collect session_id, so it cannot be correlated with Login or Logout events.
    • My EVENT SESSION specifies collection of username, client_app_name and client_hostname, but none of these fields/actions are present in the collected data. :(
  • connection_accept:
    • Collects client port, but does not collect client IP!!!
    • Has sesstion_id, so at least it can be correlated with Login and Logout
    • Note: I've not yet caught this particular event in the wild, so I have no further comments about it.

None of these events provide the IP address of the client, but I will accept the client hostname as a reasonable substitute. However, attaining the port number is a real problem. The port number is only found in the connection_accept event, and there is no evident way to correlate that to the login event which has the hostname. In short, it seems that Extended Events simply is unable to deliver this basic client IP-and-port pairing. I want to believe I'm wrong, because it is such elementary data. Any help or suggestions of what I'm overlooking would be much appreciated.

Extended Event DDL

Here is the DDL for the EVENT SESSION I've been testing:

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

Alternatives

Aside from Extended Events, "security audits" is another mechanism that is not being deprecated. So I thought I'd also give this a try:

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

Among the fields this provided were "client app", "client IP", "Session ID" and username (viz. "server principle name"). However, there is no way to correlate this back to the connection_accept XEvent. So there is no way to extract the two pieces of information I want: client IP and port number. :(

Bonus

Aside from collecting client IP and port, I'm also interested in collecting SQL Server network traffic volume. However, I've yet to see any way to do that either. Tips appreciated! I'm only seeing vaguely related posts.

Update

I've noticed a global action (field) called task_address that appears to correlate login, logout and connection_accept events. Thus it may be possible now to find the IP and port by combining login:client_hostname and connection_accept:port. My only concern is that I can't find documentation for task_address to validate my observations.

Venery answered 5/6, 2018 at 18:44 Comment(3)
It may be worth asking this in dba.stackexchange.com, it's a better fit for that group. – Flaxman
@Flaxman I've seen a half-dozen or so dba's on dba.stackexchange step in to comment on this question (others have asked it), and the recurring feedback is "I've not worked with Extended Events." So I thought I'd take the question to SO. Besides, the gap between development and operations continues to close! – Venery
4 years later, did you get any further with this? – Horten
F
2

If you can utilize a trigger in your environment here is a solution. Every time there is a "log in", a row will be inserted to [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
Fransis answered 29/10, 2018 at 16:16 Comment(2)
I don't think this achieves the goal. The client_net_address property is good, but it needs to be matched with a client_net_port...which does not exist. The local_tcp_port is the server side port, which I don't need (I already know that it is 1433). – Venery
@BrentArias the value from that property is going to be the only valid port you can log on with to the SQL Server Instance. Is you requirement that you need to know if someone tried to connect to an incorrect port like yoursqlserver,5555 instead of yoursqlserver,1433? (Assuming 1433 is your actual port #) – Fransis

© 2022 - 2024 — McMap. All rights reserved.