Collect logs from Microsoft SQL Server

Microsoft SQL Server is a relational database management system (RDBMS) for corporate IT environments, supporting transaction processing, business intelligence, and analytics.

NXLog Agent can collect, parse, and enrich SQL Server error and audit logs, and forward them to your SIEM for centralized monitoring and analysis. In addition, it can read and write logs to an SQL Server database via ODBC.

Microsoft SQL Server error log

Microsoft SQL Server writes errors to a UTF-16LE-encoded file with records spanning multiple lines. When collecting these logs, it is best to normalize the encoding to UTF-8, as shown in the example below.

Example 1. Parsing multiline SQL Server error logs with NXLog Agent

This configuration collects Microsoft SQL Server error logs. It uses the Character Set Conversion extension to convert the input from UTF16-LE to UTF-8, and the Multiline Parser extension to process events spanning multiple lines. For demonstration purposes, it converts the events to JSON format using the JSON extension.

nxlog.conf

<Extension utf_16_to_8>
    Module           xm_charconv
    InputEncoding    UTF-16LE
</Extension>

<Extension multiline>
    Module           xm_multiline
    HeaderLine       /^\d+-\d+-\d+\ \d+:\d+:\d+.\d+\s/
</Extension>

<Extension json>
    Module           xm_json
</Extension>

<Input mssql_error>
    Module           im_file
    File             'C:\Program Files\Microsoft SQL Server\MSSQL17.MSSQLSERVER\MSSQL\Log\ERRORLOG'
    InputType        utf_16_to_8.convert, multiline (1)
    <Exec>
        if ($raw_event =~ /(\d+-\d+-\d+\ \d+:\d+:\d+.\d+)\s(\S+)\s+(.+)$/s) { (2)
            $EventTime = $1;
            $Source = $2;
            $Message = $3;
        }

        to_json();
    </Exec>
</Input>

1	Sets the InputType to first convert the input to UTF-8 and then process it with the xm_multiline instance.
2	Uses a regular expression to parse the record into fields.

Input sample

2026-04-18 02:56:14.84 Server      Command Line Startup Parameters:
	 -s "MSSQLSERVER"

Output sample

{
  "EventReceivedTime": "2026-04-18T02:56:15.555454-07:00",
  "SourceModuleName": "mssql_error",
  "SourceModuleType": "im_file",
  "Hostname": "WIN-MSSQL",
  "EventTime": "2026-04-18 02:56:14.84",
  "Source": "Server",
  "Message": "Command Line Startup Parameters:\r\n\t -s \"MSSQLSERVER\""
}

SQL Server error events do not contain a closing/footer line. Therefore, each log record is buffered until a new one is received.

Microsoft SQL Server audit log

SQL Server auditing records changes to databases and access groups. You can configure SQL Server to write audit events to a log file or Windows Event Log.

The following instructions require SQL Server Management Studio (SSMS) and a Microsoft SQL Server edition that supports advanced auditing. Consult the relevant documentation for your Microsoft SQL Server version and edition. You need the Fine Grained Auditing feature if using Microsoft SQL Server 2014. For newer versions, look for the Server audit and Database audit features.

See SQL Server Audit (Database Engine) in the Microsoft documentation for more information.

Configure SQL Server auditing

The first step to set up SQL auditing is to create a Server Audit object which defines the audit destination, i.e., a file or Windows Event Log. Then add a Server Audit Specification object to start auditing server events, such as logon attempts or principal changes, or a Database Audit Specification object to audit SQL statements.

Create a SQL Server Audit object

SQL Server 2025 has a known issue where audit events fail to write to the Windows Security log. If you are using SQL Server 2025, select File as the audit destination instead.

Open SQL Server Management Studio and connect to the relevant database server.
Expand Security, right-click on Audits, and select New Audit….
Enter a name for the audit object in the Create Audit dialog.
In the Audit destination drop-down list, choose Security log or File. Avoid using the Application log for security reasons.
If you chose File, enter a file path and configure log rotation.
Click OK to create the Server Audit object.
A red icon will be displayed next to the new object’s name, which indicates the object is inactive. Right-click on the audit object and select Enable audit. See Verify SQL Server audit logging below if you encounter any errors.

Create the Server Audit object via SQL by executing the CREATE SERVER AUDIT and ALTER SERVER AUDIT commands.

CREATE SERVER AUDIT myaudit
TO <SECURITY LOG|FILE>
WITH (QUEUE_DELAY=100, ON_FAILURE=CONTINUE);
ALTER SERVER AUDIT myaudit WITH (STATE=ON)

Create a SQL Server Audit Specification

Open SQL Server Management Studio and connect to the relevant database server.
Expand Security, right-click on Server Audit Specifications, and select New Server Audit Specification….
Choose the Server Audit object created earlier and select the actions you want to audit.
Click OK to create the Server Audit Specification object.
A red icon will be displayed next to the new object’s name, which indicates the object is inactive. Right-click on the audit object and select Enable Server Audit Specification.

Create the Server Audit Specification object via SQL by executing the CREATE SERVER AUDIT SPECIFICATION and ALTER SERVER AUDIT SPECIFICATION commands.

CREATE SERVER AUDIT SPECIFICATION srv_audit_spec
FOR SERVER AUDIT myaudit
  ADD (FAILED_LOGIN_GROUP)
ALTER SERVER AUDIT SPECIFICATION srv_audit_spec
FOR SERVER AUDIT myaudit
WITH (STATE=ON)

Create a SQL Server Database Audit Specification

Open SQL Server Management Studio and connect to the relevant database server.
Expand Databases > [database_name] > Security.
Right-click on Database Audit Specifications and select New Database Audit Specification….
Choose the Server Audit object created earlier and select the actions you want to audit.
Click OK to create the Database Audit Specification object.
A red icon will be displayed next to the new object’s name, which indicates the object is inactive. Right-click on the audit object and select Enable Database Audit Specification.

Create the Database Audit Specification object via SQL by executing the CREATE DATABASE AUDIT SPECIFICATION and ALTER DATABASE AUDIT SPECIFICATION commands.

CREATE DATABASE AUDIT SPECIFICATION mydb_audit_spec
FOR SERVER AUDIT myaudit
ADD (SELECT ON OBJECT::[Production].[Product] BY [Peter])
ALTER DATABASE AUDIT SPECIFICATION mydb_audit_spec
FOR SERVER AUDIT myaudit
  ADD (SELECT
       ON OBJECT::dbo.Table1
       by dbo)
  WITH (STATE = ON);

Verify SQL Server audit logging

Audit file

If you chose File as the audit destination, verify that SQL Server created the file and writes new logs when the audit criteria are met. If you do not see the file, check NTFS permissions and ensure the SQL Server user can write to the destination path.

Windows Event Log

Check the Security and Application logs for SQL Server auditing events. If the Security log doesn’t contain any SQL Server auditing events, check the Application log for event ID 33204, which indicates a failure to write to the Security log.

There are three key requirements for SQL Server to write events to the Security log:

The Audit object access security policy and Audit Application Generated advanced audit policy must be activated. These settings are usually controlled by the domain group policy, which overwrites the local policy settings.
The SQL Server service account must be able to write to the Security log. The LOCAL SERVICE and NETWORK SERVICE accounts have permission by default.
The SQL Server service account must be able to create an entry under the HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\EventLog\Security registry key. To grant the account permission:
1. Open Registry Editor (regedit.exe).
2. Navigate to HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Security.
3. Right-click on the key name and select Permissions…
4. Add the SQL Server service user and assign it Full Control.
5. Deselect, then reselect the Server Audit object so SQL Server attempts to recreate the MSSQLSERVER$AUDIT sub-key.
6. Once the key is created, you may remove the Full Control access permission since it is no longer required.

Collect SQL Server audit logs

Once you activate SQL Server audit, you can configure NXLog Agent to collect the logs. If SQL Server writes audit logs to the Security log, you must configure NXLog Agent to collect the logs from Windows Event Log. Otherwise, if it writes audit logs to a File, you must configure NXLog Agent to collect them via ODBC.

Example 2. Collecting SQL Server audit logs from Windows Event Log

This configuration uses the Event Log for Windows input module to collect events with ID 33205 from the Security log. It parses the $Message field with the Key-Value Pairs extension and, for demonstration, it converts events to JSON format using the JSON extension.

nxlog.conf

<Extension kvp>
    Module          xm_kvp
    KVDelimiter     :
    KVPDelimiter    \n
</Extension>

<Extension json>
    Module          xm_json
</Extension>

<Input mssql_audit>
    Module          im_msvistalog
    <QueryXML>
        <QueryList>
            <Query Id="0" Path="Security">
                <Select Path="Security">*[System[(EventID=33205)]]</Select>
            </Query>
        </QueryList>
    </QueryXML>
    <Exec>
        kvp->parse_kvp($Message);
        delete($Message);
        delete($Data);
        to_json();
    </Exec>
</Input>

The following is an SQL Server audit event for a failed login after NXLog Agent processed it.

Output sample

{
  "EventTime": "2026-04-18T09:44:48.108025-07:00",
  "Hostname": "WIN-MSSQL",
  "Keywords": "0x90000000000000",
  "LevelValue": 0,
  "EventType": "AUDIT_FAILURE",
  "SeverityValue": 4,
  "Severity": "ERROR",
  "EventID": 33205,
  "SourceName": "MSSQLSERVER",
  "ProviderGuid": null,
  "Version": 0,
  "TaskValue": 4,
  "OpcodeValue": 0,
  "RecordNumber": 1531,
  "ExecutionProcessID": 0,
  "ExecutionThreadID": 0,
  "Channel": "Security",
  "Category": "Logon",
  "Level": "Information",
  "EventReceivedTime": "2026-04-18T09:44:48.909811-07:00",
  "SourceModuleName": "mssql_audit",
  "SourceModuleType": "im_msvistalog",
  "Audit event": "audit_schema_version:1",
  "event_time": "2026-04-18 16:44:47.5263481",
  "sequence_number": 1,
  "action_id": "LGIF",
  "succeeded": "false",
  "is_column_permission": "false",
  "session_id": 0,
  "server_principal_id": 0,
  "database_principal_id": 0,
  "target_server_principal_id": 0,
  "target_database_principal_id": 0,
  "object_id": 0,
  "user_defined_event_id": 0,
  "transaction_id": 0,
  "class_type": "LX",
  "duration_milliseconds": 0,
  "response_rows": 0,
  "affected_rows": 0,
  "client_tls_version": 771,
  "database_transaction_id": 0,
  "ledger_start_sequence_number": 0,
  "is_local_secondary_replica": "false",
  "client_ip": "local machine",
  "permission_bitmask": 0,
  "sequence_group_id": "69A63D57-5DF7-4575-A796-ECE4411151A6",
  "session_server_principal_name": null,
  "server_principal_name": "test",
  "server_principal_sid": null,
  "database_principal_name": null,
  "target_server_principal_name": null,
  "target_server_principal_sid": null,
  "target_database_principal_name": null,
  "server_instance_name": "WIN-MSSQL",
  "database_name": null,
  "schema_name": null,
  "object_name": null,
  "statement": "Login failed for user 'test'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]",
  "additional_information": "<action_info xmlns=\"http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data\"><pooled_connection>0</pooled_connection><error>0x00004818</error><state>5</state><address>local machine</address></action_info>",
  "user_defined_information": null,
  "application_name": "SQL Server Management Studio",
  "connection_id": "2D55639B-F379-45AC-B91C-CED8FC04D353",
  "data_sensitivity_information": null,
  "host_name": "WIN-MSSQL",
  "session_context": null,
  "client_tls_version_name": "1.2",
  "external_policy_permissions_checked": null,
  "obo_middle_tier_app_id": "."
}

SQL Server stores the audit log in binary format and provides the sys.fn_get_audit_file function to retrieve log records. NXLog Agent can collect these audit logs via ODBC. For details on configuring ODBC, see Configure ODBC below.

Example 3. Collecting the SQL Server audit log file

This configuration collects SQL Server audit logs using the ODBC input module. To further enrich log records, the SQL query retrieves the corresponding action details from the sys.dm_audit_actions table. For demonstration purposes, it then converts the events to JSON format using the JSON extension.

nxlog.conf

<Extension json>
    Module              xm_json
</Extension>

<Input mssql_audit>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 18 for SQL Server}; Server=WIN-MSSQL; \
                        Encrypt=yes; TrustServerCertificate=yes; Database=TESTDB; \
                        Trusted_Connection=yes
    PollInterval        5 (1)
    IdType              timestamp
    SQL                 SELECT event_time AS 'id', f.*, a.name AS action_name \
                        FROM fn_get_audit_file('C:\Logs\Audit-*.sqlaudit', default, \
                        default) AS f \
                        INNER JOIN sys.dm_audit_actions AS a \
                        ON f.action_id = a.action_id \
                        WHERE event_time > ?
    <Exec>
        delete($id);
        rename_field($event_time, $EventTime);
        rename_field($host_name, $Hostname);
        to_json();
    </Exec>
</Input>

1	Checks for new records every 5 seconds.

The following is an SQL Server audit event for a failed login after NXLog Agent processed it.

Output sample

{
  "EventTime": "2026-04-18T08:11:50.456959-07:00",
  "sequence_number": 1,
  "action_id": "LGIF",
  "succeeded": false,
  "permission_bitmask": "00000000000000000000000000000000",
  "is_column_permission": false,
  "session_id": 0,
  "server_principal_id": 0,
  "database_principal_id": 0,
  "target_server_principal_id": 0,
  "target_database_principal_id": 0,
  "object_id": 0,
  "class_type": "LX",
  "session_server_principal_name": "",
  "server_principal_name": "test",
  "server_principal_sid": null,
  "database_principal_name": "",
  "target_server_principal_name": "",
  "target_server_principal_sid": null,
  "target_database_principal_name": "",
  "server_instance_name": "WIN-MSSQL",
  "database_name": "",
  "schema_name": "",
  "object_name": "",
  "statement": "Login failed for user 'test'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]",
  "additional_information": "<action_info xmlns=\"http://schemas.microsoft.com/sqlserver/2008/sqlaudit_data\"><pooled_connection>0</pooled_connection><error>0x00004818</error><state>5</state><address>local machine</address></action_info>",
  "file_name": "C:\\Logs\\Audit-MSSQL_258E1C43-EF86-4062-809E-E912EBE4D5E1_0_134210589876130000.sqlaudit",
  "audit_file_offset": 9728,
  "user_defined_event_id": 0,
  "user_defined_information": "",
  "audit_schema_version": 1,
  "sequence_group_id": "ca9bac57fcc5e143b2ee533efd720618",
  "transaction_id": 0,
  "client_ip": "local machine",
  "application_name": "SQL Server Management Studio",
  "duration_milliseconds": 0,
  "response_rows": 0,
  "affected_rows": 0,
  "connection_id": "1E76CA82-90CC-462B-8259-D4DF7258C8D0",
  "data_sensitivity_information": "",
  "Hostname": "WIN-MSSQL",
  "session_context": "",
  "client_tls_version": 771,
  "client_tls_version_name": "1.2",
  "database_transaction_id": 0,
  "ledger_start_sequence_number": 0,
  "external_policy_permissions_checked": "",
  "obo_middle_tier_app_id": "",
  "is_local_secondary_replica": false,
  "action_name": "LOGIN FAILED",
  "EventReceivedTime": "2026-04-18T01:11:52.690178-07:00",
  "SourceModuleName": "mssql_audit",
  "SourceModuleType": "im_odbc"
}

The action_id field in the events represents the ID of the logged operation. See the Microsoft documentation for more information on SQL Server Audit Records. The sys.dm_audit_actions view returns a list of audit actions, including the action_id field and a user-friendly action name. The configuration above joins audit log records with their corresponding action record. Executing the following query lists all possible actions.

SELECT DISTINCT action_id, name, class_desc, parent_class_desc
  FROM sys.dm_audit_actions

Collect logs from an SQL Server database

NXLog Agent can read logs from an SQL Server database using the ODBC input module. This module requires an ODBC client to connect to the database. See Configure ODBC for more information.

To use the ODBC input module, set the ConnectionString directive to define the database connection settings. You also need to provide an SQL SELECT statement specifying how to query the data. The statement must return an incremental id field and include a WHERE clause that references the same field to filter the result.

The module uses the id field to track the position of the last read logs. In a simple scenario, the id is an auto-incrementing integer, but several other data types are also supported. See the IdType directive. If the table does not contain an id field, you can use an ALIAS in the SELECT statement. The following examples demonstrate the use of the int, datetime2, datetimeoffset, datetime, and timestamp data types.

Example 4. Reading SQL Server database records with an int ID type

This configuration uses the ODBC input module to collect logs from the dbo.test table and converts the data to JSON format. The table uses an auto-incrementing integer ID field. Therefore, the configuration uses the default IdType value.

Sample table

CREATE TABLE dbo.test (
    RecordID int IDENTITY(1,1) NOT NULL,
    EventTime datetime2 NOT NULL,
    Message varchar(100) NOT NULL,
)

INSERT INTO dbo.test (EventTime, Message)
VALUES ('2026-04-18T10:00:00', 'This is a test message');
GO

nxlog.conf

<Extension json>
    Module              xm_json
</Extension>

<Input mssql>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 18 for SQL Server}; Server=WIN-MSSQL; \
                        Encrypt=yes; TrustServerCertificate=yes; Database=TESTDB; \
                        Trusted_Connection=yes
    SQL                 SELECT RecordID AS id, * FROM dbo.test WHERE RecordID > ?
    <Exec>
        delete($id);
        to_json();
    </Exec>
</Input>

Output sample

{
  "RecordID": 1,
  "EventTime": "2026-04-18T10:00:00.000000-07:00",
  "Message": "This is a test message",
  "EventReceivedTime": "2026-04-18T10:00:54.313071-07:00",
  "SourceModuleName": "mssql",
  "SourceModuleType": "im_odbc",
  "Hostname": "WIN-MSSQL"
}

Example 5. Reading SQL Server database records with a datetime2 ID type

Sample table

CREATE TABLE dbo.test (
    EventTime datetime2 NOT NULL,
    Message varchar(100) NOT NULL,
)

INSERT INTO dbo.test (EventTime, Message)
VALUES ('2026-04-18T10:00:00', 'This is a test message');
GO

nxlog.conf

<Extension json>
    Module              xm_json
</Extension>

<Input mssql>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 18 for SQL Server}; Server=WIN-MSSQL; \
                        Encrypt=yes; TrustServerCertificate=yes; Database=TESTDB; \
                        Trusted_Connection=yes
    IdType              timestamp
    SQL                 SELECT EventTime AS id, * FROM dbo.test WHERE EventTime > ?
    <Exec>
        delete($id);
        to_json();
    </Exec>
</Input>

Output sample

{
  "EventTime": "2026-04-18T10:00:00.000000-07:00",
  "Message": "This is a test message",
  "EventReceivedTime": "2026-04-18T10:00:47.739121-07:00",
  "SourceModuleName": "mssql",
  "SourceModuleType": "im_odbc",
  "Hostname": "WIN-MSSQL"
}

Example 6. Reading SQL Server database records with a datetimeoffset ID type

This configuration uses the ODBC input module to collect logs from the dbo.test table and converts the data to JSON format. The table does not contain a dedicated ID field, so the configuration uses the EventTime field as the ID and sets the IdType directive to timestamp. However, the SQL Server datetimeoffset type stores timestamps with a time zone offset, which the ODBC module does not support by default. Therefore, the configuration uses the CAST() SQL Server function to convert the EventTime to datetime2.

Casting event times to datetime2 removes the time zone offset and treats the value as local time. This means that events with the same local time but different offsets, such as 2026-04-18T10:00:00+02:00 and 2026-04-18T10:00:00-02:00, become identical after casting. As a result, NXLog Agent treats them as duplicate records and skips the second event.

Sample table

CREATE TABLE dbo.test (
    EventTime datetimeoffset NOT NULL,
    Message varchar(100) NOT NULL,
)

INSERT INTO dbo.test (EventTime, Message)
VALUES ('2026-04-18T10:00:00+02:00', 'This is a test message');
GO

nxlog.conf

<Extension json>
    Module              xm_json
</Extension>

<Input mssql>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 18 for SQL Server}; Server=WIN-MSSQL; \
                        Encrypt=yes; TrustServerCertificate=yes; Database=TESTDB; \
                        Trusted_Connection=yes
    IdType              timestamp
    SQL                 SELECT CAST(EventTime AS datetime2) AS id, Message FROM dbo.test \
                        WHERE CAST(EventTime AS datetime2) > ?
    <Exec>
        rename_field($id, $EventTime);
        to_json();
    </Exec>
</Input>

Output sample

{
  "EventTime": "2026-04-18T10:00:00.000000+02:00",
  "Message": "This is a test message",
  "EventReceivedTime": "2026-04-18T10:00:02.605019+02:00",
  "SourceModuleName": "mssql",
  "SourceModuleType": "im_odbc",
  "Hostname": "WIN-MSSQL"
}

Example 7. Reading SQL Server database records with a datetime ID type

The SQL Server datetime type is deprecated, and due to a change in the internal representation of datetime values, some timestamps, such as the example below, cannot be parsed correctly. Therefore, the configuration uses the CAST() SQL Server function in the WHERE clause to explicitly convert the value to datetime. Without it, SQL Server may return the same records repeatedly until a newer event is recorded.

Sample table

CREATE TABLE dbo.test (
    EventTime datetime NOT NULL,
    Message varchar(100) NOT NULL,
)

INSERT INTO dbo.test (EventTime, Message)
VALUES ('2026-04-18T10:00:00.333', 'This is a test message');
GO

nxlog.conf

<Extension json>
    Module              xm_json
</Extension>

<Input mssql>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 18 for SQL Server}; Server=WIN-MSSQL; \
                        Encrypt=yes; TrustServerCertificate=yes; Database=TESTDB; \
                        Trusted_Connection=yes
    IdType              timestamp
    SQL                 SELECT EventTime AS id, * FROM dbo.test \
                        WHERE EventTime > CAST(? as datetime)
    <Exec>
        delete($id);
        to_json();
    </Exec>
</Input>

Output sample

{
  "EventTime": "2026-04-18T10:00:00.333000-07:00",
  "Message": "This is a test message",
  "EventReceivedTime": "2026-04-18T10:00:02.212818-07:00",
  "SourceModuleName": "mssql",
  "SourceModuleType": "im_odbc",
  "Hostname": "WIN-MSSQL"
}

Example 8. Reading SQL Server database records with a rowversion ID type

This configuration uses the ODBC input module to collect logs from the dbo.test table and converts the data to JSON format. The table uses a rowversion ID field. Therefore, the configuration uses the default IdType value.

Sample table

CREATE TABLE dbo.test (
    RowNumber rowversion NOT NULL,
    Message varchar(100) NOT NULL,
)

INSERT INTO dbo.test (Message)
VALUES ('This is a test message');
GO

nxlog.conf

<Extension json>
    Module              xm_json
</Extension>

<Input mssql>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 18 for SQL Server}; Server=WIN-MSSQL; \
                        Encrypt=yes; TrustServerCertificate=yes; Database=TESTDB; \
                        Trusted_Connection=yes
    SQL                 SELECT RowNumber AS id, * FROM dbo.test \
                        WHERE CONVERT(integer, RowNumber) > ?
    <Exec>
        delete($id);
        to_json();
    </Exec>
</Input>

Output sample

{
  "RowNumber": "0000000000001b59",
  "Message": "This is a test message",
  "EventReceivedTime": "2026-04-18T10:21:32.031549-07:00",
  "SourceModuleName": "mssql",
  "SourceModuleType": "im_odbc",
  "Hostname": "WIN-MSSQL"
}

Convert SQL Server data types

The ODBC input module does not support all SQL Server data types. Attempting to read an unsupported data type results in an unsupported odbc type error in the NXLog Agent logs. To work around this, use the SQL Server CAST() function to convert the field to a supported data type.

Example 9. Converting the SQL Server datetimeoffset type

This configuration uses the ODBC input module to collect logs from the dbo.test table and converts the data to JSON format. The table contains a datetimeoffset field, which is a timestamp with a time zone offset. The configuration splits this field into two distinct fields, storing the timestamp in $EventTime and the time zone offset in minutes in $TZOffset.

Sample table

CREATE TABLE dbo.test (
    RecordID int IDENTITY(1,1) NOT NULL,
    LogTime datetimeoffset NOT NULL,
    Message varchar(100) NOT NULL,
)

INSERT INTO dbo.test (LogTime, Message)
VALUES ('2026-04-18T10:00:00+02:00', 'This is a test message');
GO

nxlog.conf

<Extension json>
    Module              xm_json
</Extension>

<Input mssql>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 18 for SQL Server}; Server=WIN-MSSQL; \
                        Encrypt=yes; TrustServerCertificate=yes; Database=TESTDB; \
                        Trusted_Connection=yes
    IdType              integer
    SQL                 SELECT RecordID AS id, \
                               CAST(LogTime AS datetime2) AS EventTime, \
                               DATEPART(tz, LogTime) AS TZOffset, \
                               Message \
                        FROM dbo.test WHERE RecordID > ?
    <Exec>
        rename_field($id, $RecordID);
        to_json();
    </Exec>
</Input>

Event fields

{
  "RecordID": 1,
  "EventTime": "2026-04-18T10:00:00.000000+02:00",
  "TZOffset": 120,
  "Message": "This is a test message",
  "EventReceivedTime": "2026-04-18T10:02:40.524819+02:00",
  "SourceModuleName": "mssql",
  "SourceModuleType": "im_odbc",
  "Hostname": "WIN-MSSQL"
}

Write logs to an SQL Server database

NXLog Agent can write logs to an SQL Server database using the ODBC output module. This module requires an ODBC client to connect to the database. See Configure ODBC below for more information.

To use the ODBC output module, set the ConnectionString directive to define the database connection settings. You also need to provide an SQL INSERT statement specifying how to write the data.

Example 10. Writing logs to an SQL Server database

This configuration inserts records into the dbo.test table in the TESTDB database. The $EventTime and $Message fields in the event record match the EventTime and Message fields in the table.

Sample table

CREATE TABLE dbo.test (
    RecordID int IDENTITY(1,1) NOT NULL,
    EventTime datetime2 NOT NULL,
    Message varchar(100) NOT NULL,
)

nxlog.conf

<Output mssql>
    Module              om_odbc
    ConnectionString    Driver={ODBC Driver 18 for SQL Server}; Server=WIN-MSSQL; \
                        Encrypt=yes; TrustServerCertificate=yes; Database=TESTDB; \
                        Trusted_Connection=yes
    SQL                 "INSERT INTO dbo.test (EventTime, Message) VALUES (?,?)", \
                        $EventTime, $Message
</Output>

Configure ODBC

To connect to an SQL Server database, install an ODBC client on the host running NXLog Agent and use the ConnectionString directive to specify the ODBC connection string. The following instructions cover setting up a DSN-less ODBC connection from a Windows or Linux host. Because the connection string carries all connection parameters, you do not need to configure an ODBC DSN.

To use a DSN instead, refer to ODBC Data Source Administrator DSN options for Windows, the unixODBC documentation for Linux, and the steps below.

SQL Server database connections use either Windows Authentication (trusted connection) or SQL Server Authentication. For details, see Choose an Authentication Mode in the Microsoft documentation.

To verify your ODBC driver setup, you can test the connection using the ODBC Data Source Administrator on Windows or isql on Linux.

When using SQL Server Authentication, the NXLog Agent configuration must include the username and password in the connection string. This applies to both direct and DSN connections. Therefore, securing the configuration file with appropriate access permissions is important. You can also fetch the connection string from another file using the include directive, or from a script using include_stdout.

ODBC driver for SQL Server

Visit Download ODBC Driver for SQL Server in the Microsoft documentation to download the appropriate ODBC driver for your platform. See Install the Microsoft ODBC driver for SQL Server (Linux) for detailed Linux installation instructions.

Example 11. Using ODBC driver 18 for SQL Server with Windows Authentication

This configuration uses ODBC Driver 18 for SQL Server to connect to a Microsoft SQL Server database. It specifies the Trusted_Connection=yes parameter in the connection string to use Windows Authentication. This uses the NXLog Agent service user for authentication, so you must ensure the account has permission to access the database.

nxlog.conf

<Input mssql>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 18 for SQL Server}; Server=WIN-MSSQL; \
                        Encrypt=yes; TrustServerCertificate=yes; Database=TESTDB; \
                        Trusted_Connection=yes
    IdType              integer
    SQL                 SELECT RecordID AS id, * FROM dbo.test WHERE RecordID > ?
</Input>

Example 12. Using ODBC driver 18 for SQL Server with SQL Server Authentication

This configuration uses ODBC Driver 18 for SQL Server to connect to a Microsoft SQL Server database. It specifies the UID and PWD parameters in the connection string to use SQL Server Authentication.

nxlog.conf

<Input mssql>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 18 for SQL Server}; Server=WIN-MSSQL; \
                        Encrypt=yes; TrustServerCertificate=yes; Database=TESTDB; \
                        UID=dbuser; PWD=secret
    IdType              integer
    SQL                 SELECT RecordID AS id, * FROM dbo.test WHERE RecordID > ?
</Input>

FreeTDS

You can use the FreeTDS driver on Linux hosts. Execute the following commands to install it.

SQL Server 2025 uses TLS 1.3 by default for all new installations, and FreeTDS does not support TLS 1.3. Connections from FreeTDS to SQL Server 2025 will fail unless TLS 1.3 is deactivated on the server side. If you are connecting to SQL Server 2025, use the Microsoft ODBC Driver 18 for SQL Server instead.

RHEL 8, 9, and 10

$ sudo dnf install epel-release
$ sudo dnf install freetds
$ sudo odbcinst -i -d -r <<EOF
[FreeTDS]
Description = TDS driver (Sybase/MS SQL)
Driver = libtdsodbc.so.0
Setup = libtdsS.so
EOF

On a Red Hat subscription (as opposed to CentOS Stream, Rocky Linux, or AlmaLinux), enabling EPEL also requires activating the CodeReady Linux Builder (CRB) repository before installing epel-release. See the EPEL documentation for instructions.

Debian 11, 12, and 13

$ sudo apt-get install tdsodbc unixodbc
$ sudo dpkg-reconfigure tdsodbc

See the FreeTDS User Guide for more information.

Example 13. Using the FreeTDS driver with SQL Server Authentication

This configuration uses the FreeTDS driver to connect to a Microsoft SQL Server database. It specifies the UID and PWD parameters in the connection string to use SQL Server Authentication.

nxlog.conf

<Input mssql>
    Module              im_odbc
    ConnectionString    Driver={FreeTDS}; Server=WIN-MSSQL; Port=1433; \
                        UID=dbuser; PWD=secret; Database=TESTDB
    IdType              integer
    SQL                 SELECT RecordID AS id, * FROM dbo.test WHERE RecordID > ?
</Input>

Disclaimer

While we endeavor to keep the information in our guides up to date and correct, NXLog makes no representations or warranties of any kind, express or implied about the completeness, accuracy, reliability, suitability, or availability of the content represented here. We update our screenshots and instructions on a best-effort basis.

NXLog does not guarantee that any scripts provided in our guides are error-free, secure, or suitable for any specific environment. Use of the scripts is at your own risk. In no event shall NXLog be liable for any damages or losses arising from using these scripts.

The accurateness of the content was tested and proved to be working in our lab environment at the time of the last revision with the following software versions:

Microsoft SQL Server 2025
Microsoft SQL Server 2022

Last revision: 18 April 2026