Snippet Name: Create_SQL_Logon_Trigger

Tags:

Created Date: 2025-06-16 13:12:43
Last Modified Date: 2025-05-31 10:31:43

Dependencies:

DBA Notes:

Sample Output:

Title: Create SQL Logon Trigger
Author: user
can you create a SQL Server logon trigger which tracks which port the connectio used to connect

Author: ChatGPT
Creating a SQL Server logon trigger to track the port used for each connection involves a combination of server-side configuration and a bit of T-SQL to capture the necessary details.

Here's a step-by-step guide to create such a trigger:

1. **Enable Event Notifications for Connection Events**: We'll use the `EVENTDATA()` function in the logon trigger to capture connection details.

2. **Create a Table to Log Connection Details**: This table will store the username, login time, and the port number.

3. **Create the Logon Trigger**: The trigger will extract the necessary information and insert it into the log table.

### Step 1: Create a Table for Logging Connection Details

```sql
CREATE DATABASE LogonTrackingDB;
GO

USE LogonTrackingDB;
GO

CREATE TABLE ConnectionLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    LoginName NVARCHAR(128),
    LoginTime DATETIME,
    ClientHostName NVARCHAR(128),
    ClientIP NVARCHAR(45),
    ClientPort INT
);
```

### Step 2: Create the Logon Trigger

Here's a trigger that logs connection details, including the port number:

```sql
USE master;
GO

CREATE TRIGGER LogonTrigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
    DECLARE @EventData XML
    SET @EventData = EVENTDATA()

    DECLARE @LoginName NVARCHAR(128)
    DECLARE @ClientHostName NVARCHAR(128)
    DECLARE @ClientIP NVARCHAR(45)
    DECLARE @ClientPort INT
    DECLARE @LoginTime DATETIME

    -- Extract the login name and login time
    SET @LoginName = @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)')
    SET @LoginTime = GETDATE()
    
    -- Extract the client host name and client IP
    SET @ClientHostName = HOST_NAME()
    SET @ClientIP = CONNECTIONPROPERTY('client_net_address')
    
    -- Extract the client port (this requires SQL Server 2012 SP1 CU2 or later)
    SET @ClientPort = CONNECTIONPROPERTY('local_tcp_port')

    -- Insert the captured details into the log table
    INSERT INTO LogonTrackingDB.dbo.ConnectionLog (LoginName, LoginTime, ClientHostName, ClientIP, ClientPort)
    VALUES (@LoginName, @LoginTime, @ClientHostName, @ClientIP, @ClientPort)
END
GO
```

### Explanation

1. **Create Table**: We first create a database `LogonTrackingDB` and a table `ConnectionLog` to store the connection details.

2. **Logon Trigger**: 
    - `EVENTDATA()` captures the event details in XML format.
    - Extract relevant information like `LoginName`, `ClientHostName`, `ClientIP`, and the port used (`ClientPort`).
    - Insert these details into the `ConnectionLog` table.

### Additional Notes

- **Permissions**: Ensure that the login executing the creation of the trigger has appropriate permissions (`CONTROL SERVER` or similar).
- **Connection Properties**: The `CONNECTIONPROPERTY('local_tcp_port')` function returns the local TCP port number that the SQL Server instance is listening on for the connection. This requires SQL Server 2012 SP1 CU2 or later.

This trigger will log every login event, capturing the port number used, which can then be queried from the `ConnectionLog` table for analysis.

Author: user
i wanted the server port, and not the client port

Author: ChatGPT
To log the server port rather than the client port, you should use the `CONNECTIONPROPERTY('local_tcp_port')` function within the logon trigger. This property returns the local TCP port number on which the SQL Server instance is listening.

Here's how you can modify the logon trigger to capture and log the server port:

### Step-by-Step Guide

1. **Create a Table for Logging Connection Details**: This table will store the username, login time, and the server port number.

2. **Create the Logon Trigger**: The trigger will extract the necessary information and insert it into the log table.

### Step 1: Create a Table for Logging Connection Details

```sql
CREATE DATABASE LogonTrackingDB;
GO

USE LogonTrackingDB;
GO

CREATE TABLE ConnectionLog (
    LogID INT IDENTITY(1,1) PRIMARY KEY,
    LoginName NVARCHAR(128),
    LoginTime DATETIME,
    ServerPort INT
);
```

### Step 2: Create the Logon Trigger

Here's a trigger that logs connection details, including the server port number:

```sql
USE master;
GO

CREATE TRIGGER LogonTrigger
ON ALL SERVER
FOR LOGON
AS
BEGIN
    DECLARE @EventData XML
    SET @EventData = EVENTDATA()

    DECLARE @LoginName NVARCHAR(128)
    DECLARE @LoginTime DATETIME
    DECLARE @ServerPort INT

    -- Extract the login name and login time
    SET @LoginName = @EventData.value('(/EVENT_INSTANCE/LoginName)[1]', 'NVARCHAR(128)')
    SET @LoginTime = GETDATE()
    
    -- Extract the server port (this requires SQL Server 2012 SP1 CU2 or later)
    SET @ServerPort = CONNECTIONPROPERTY('local_tcp_port')

    -- Insert the captured details into the log table
    INSERT INTO LogonTrackingDB.dbo.ConnectionLog (LoginName, LoginTime, ServerPort)
    VALUES (@LoginName, @LoginTime, @ServerPort)
END
GO
```

### Explanation

1. **Create Table**: We create a database `LogonTrackingDB` and a table `ConnectionLog` to store the connection details, specifically the login name, login time, and server port.

2. **Logon Trigger**: 
    - `EVENTDATA()` captures the event details in XML format.
    - Extract relevant information like `LoginName` and `LoginTime`.
    - Extract the server port using `CONNECTIONPROPERTY('local_tcp_port')`.
    - Insert these details into the `ConnectionLog` table.

### Additional Notes

- **Permissions**: Ensure that the login executing the creation of the trigger has appropriate permissions (`CONTROL SERVER` or similar).
- **Connection Properties**: The `CONNECTIONPROPERTY('local_tcp_port')` function returns the local TCP port number that the SQL Server instance is listening on for the connection. This requires SQL Server 2012 SP1 CU2 or later.

This setup will effectively log each login event along with the server port number used, which can then be queried from the `ConnectionLog` table for analysis.