NXLog User Guide

74. Microsoft SQL Server

NXLog can be integrated with SQL Server in several ways. The server error log file can be read and parsed. SQL Server Auditing can be configured for a database and the logs collected. It is also possible to read logs from or write logs to databases hosted by SQL Server. The last section provides some additional information about setting up ODBC for connecting to a database.

74.1. Error Log

Microsoft SQL Server writes its error logs to a UTF-16LE encoded file using a line-based format. Log messages may span multiple lines. It is recommended to normalize the encoding to UTF-8 as shown in the examples below.

Example 330. Reading From the SQL Server Error Log

This example uses the xm_charconv LineReader input reader to convert the input to UTF-8 encoding. Events spanning multiple lines are joined and each event is parsed into $EventTime, $Source, and $Message fields.

nxlog.conf


  1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

  <Extension charconv>
    Module      xm_charconv
    LineReader  UTF-16LE
</Extension>

define ERRORLOG_EVENT /(?x)^(\xEF\xBB\xBF)? \
                       (?<EventTime>\d+-\d+-\d+\ \d+:\d+:\d+.\d+) \
                       \ (?<Source>\S+)\s+(?<Message>.+)$/s
<Input mssql_errorlog>
    Module      im_file
    File        'C:\Program Files\Microsoft SQL Server\' + \
                'MSSQL14.MSSQLSERVER\MSSQL\Log\ERRORLOG'
    InputType   charconv
    <Exec>
        # Attempt to match regular expression
        if $raw_event =~ %ERRORLOG_EVENT%
        {
            # Check if previous lines were saved
            if defined(get_var('saved'))
            {
                $tmp = $raw_event;
                $raw_event = get_var('saved');
                set_var('saved', $tmp);
                delete($tmp);
                # Process and send previous event
                $raw_event =~ %ERRORLOG_EVENT%;
                $EventTime = parsedate($EventTime);
            }
            # Save this line to module variable until the next event
            else
            {
                set_var('saved', $raw_event);
                drop();
            }
        }
        # Save this line to module variable until the next event
        else
        {
            set_var('saved', get_var('saved') + "\n" + $raw_event);
            drop();
        }
    </Exec>
</Input>

Note	Because there is no closing/footer line for the events, a log message is kept in the buffers, and not forwarded, until a new log message is read.

Example 331. Reading From the SQL Server Error Log (NXLog Community Edition)

This example uses the xm_charconv module convert() function to convert the character set to UTF-8. For log messages that span multiple lines, an event is created for each line. Variables are used to retain the same $EventTime and $Source values for subsequent events in this case.

nxlog.conf


  1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

  <Extension _charconv>
    Module  xm_charconv
</Extension>

<Input mssql_errorlog>
    Module      im_file
    File        'C:\Program Files\Microsoft SQL Server\' + \
                'MSSQL14.MSSQLSERVER\MSSQL\Log\ERRORLOG'
    <Exec>
        # Convert character encoding
        $raw_event = convert($raw_event, 'UTF-16LE', 'UTF-8');
        # Discard empty lines
        if $raw_event == '' drop();
        # Attempt to match regular expression
        else if $raw_event =~ /(?x)^(?<EventTime>\d+-\d+-\d+\ \d+:\d+:\d+.\d+)
                              \ (?<Source>\S+)\s+(?<Message>.+)$/s
        {
            # Convert $EventTime field to datetime type
            $EventTime = parsedate($EventTime);
            # Save $EventTime and $Source; may be needed for next event
            set_var('last_EventTime', $EventTime);
            set_var('last_Source', $Source);
        }
        # If regular expression does not match, this is a multi-line event
        else
        {
            # Use the entire line for the $Message field
            $Message = $raw_event;
            # Check if fields were save from the previous event
            if defined(get_var('last_EventTime'))
            {
                # Use $EventTime and $Source from previous event
                $EventTime = get_var('last_EventTime');
                $Source = get_var('last_Source');
            }
            else
                # Use received timestamp for $EventTime; $Source is unknown
                $EventTime = $EventReceivedTime;
        }
    </Exec>
</Input>

74.2. Audit Log

Microsoft SQL Server 2008 introduced a new feature that provided a much needed solution for security oriented customers: SQL Server Auditing. With this feature, the server records all changes to the database and access groups. These logs are stored in a proprietary format file or in the Application/Security EventLog data.

While in earlier versions these logs had to be generated by SQL Trace or a custom monitoring process, it is now possible to start recording audit logs with a few clicks in Management Studio or a relatively simple SQL script.

The following instructions require a Microsoft SQL Server with auditing support and the Microsoft SQL Management Studio. Consult the relevant documentation below to determine whether "Fine Grained Auditing" is available for your SQL Server version and edition.

For more information, see SQL Server Audit (Database Engine) on Microsoft Docs.

74.2.1. Configuring SQL Server for Auditing

To set up SQL auditing, create a Server Audit object that describes the target for audit data (a binary file or EventLog channel). Then add either a Server Audit Specification object or a Database Audit Specification object (or both) so SQL Server can start producing meaningful data into the defined Server Audit object. Generally, to log SQL statements set up a Database Audit Specification object. To trace server events, such as log-on attempts or server principle changes, define a Server Audit Specification.

74.2.1.1. Creating a Server Audit Object

GUI

In Management Studio, after connecting to the database instance:

Click on the plus (+) next to Security.
Right-click on Audits and select New Audit. The Create Audit dialog box appears. Choose a name for the audit object.
In the Audit destination drop-down list, choose Security log or File (for security reasons, Application log is not recommended as a target). For File, enter a file path and configure log rotation.
Click OK. The Server Audit object is created. Note the red arrow next to the newly created object’s name indicating this is a disabled object. To enable it, right click on the audit object and select Enable audit (in case of an error, see Checking SQL Audit Generation below).

SQL script

To instead create the Server Audit object via SQL, run the CREATE SERVER AUDIT and ALTER SERVER AUDIT commands. For example:

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

74.2.1.2. Creating a Server Audit Specification

GUI

In Management Studio, after connecting to the database instance:

Click on the plus (+) next to Security.
Right-click on Server Audit Specifications and select New Audit. The Create Audit dialog box appears.
Choose a Server Audit object (the one defined earlier) and select the actions to be reported.
Click OK. The Server Audit Specification object is created. Note the red arrow next to the newly created object’s name indicating this is a disabled object. To enable it, right click on the audit object and select Enable audit.

SQL script

Alternatively, use the CREATE SERVER AUDIT SPECIFICATION and ALTER SERVER AUDIT SPECIFICATION commands. For example:

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)

74.2.1.3. Creating a Database Audit Specification

GUI

In Management Studio, after connecting to the database instance:

Click on the plus (+) next to Databases.
Click on the plus (+) next to the database to be audited, then click on the plus (+) next to Security under the database.
Right-click on Database Audit Specifications and select New Audit. The Create Audit dialog box appears.
Choose a Server Audit object (the one defined earlier) and select the actions to be reported.
Click OK. The Database Audit Specification object is created.

SQL script

Alternatively, use the CREATE DATABASE AUDIT SPECIFICATION and ALTER DATABASE AUDIT SPECIFICATION commands. For example:

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);

74.2.2. Checking SQL Audit Generation

Audit file

If File was chosen as the audit target, check if the file is created and grows when the audit criteria are met. Other than incorrect NTFS permissions, there should not be any issue with this type of log target.

EventLog

Check the Security and Application EventLogs to see if SQL Auditing is working properly. If there are no related events in the Security log (though it was set as the destination), check the Application log too. Look for event ID 33204 in the Application log indicating SQL Server’s failure to write to the Security log.

This is a registry related permission error: the account running the SQL server instance is unable to create an entry under HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Security and fails with ID 33204.

This error can be fixed as follows.

Run regedit.
Grant Full Control permission for the account running the SQL server instance (for example, Network Service or a named account) to HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Security.
Disable, then re-enable the Server Audit; this creates a sub-key, MSSQLSERVER$AUDIT.
Optionally, remove the Full Control permission that was just added. This permission is no longer required now that the sub-key has been created.

74.2.3. Configuring Collection of SQL Audit Logs

After a working SQL audit is in place, NXLog can be configured to read the logs from the Server Audit object. If it is configured with a Security log destination, the events can be read from the EventLog. If it is configured with a File target, the events can be queried via ODBC.

74.2.3.1. Reading From Windows EventLog

The im_msvistalog module can be used to read events from the Security log.

Example 332. Reading Audit Events From the EventLog

In this example, events with ID 33205 are retrieved and some additional fields are parsed from $Message.

Sample Event

2011-11-11 11:00:00 sql2008-ent AUDIT_SUCCESS 33205 Audit event: event_time:2011-11-11 11:00:00.0000000
sequence_number:1
action_id:SL
succeeded:true
permission_bitmask:1
is_column_permission:true
session_id:57
server_principal_id:264
database_principal_id:1
target_server_principal_id:0
target_database_principal_id:0
object_id:2105058535
class_type:U
session_server_principal_name:SQL2008-ENT\myuser
server_principal_name:SQL2008-ENT\myuser
server_principal_sid:0105000000000002120000001aaaaaabbbbcccccddddeeeeffffffff
database_principal_name:dbo
target_server_principal_name:
target_server_principal_sid:
target_database_principal_name:
server_instance_name:SQL2008-ENT
database_name:logindb
schema_name:dbo
object_name:users
statement:select username nev from dbo.users;
additional_information:

nxlog.conf


  1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

  <Input in>
    Module im_msvistalog
    <QueryXML>
        <QueryList>
            <Query Id="0" Path="Security">
                <Select Path="Security">*[System[(EventID=33205)]]</Select>
            </Query>
        </QueryList>
    </QueryXML>
    <Exec>
        if $Message =~ /action_id:(.*)/ $ActionId = $1;
        if $Message =~ /session_server_principal_name:(.*)/ $SessionSPN = $1;
        if $Message =~ /database_principal_name:(.*)/ $DBPrincipal = $1;
        if $Message =~ /server_instance_name:(.*)/ $ServerInstance = $1;
        if $Message =~ /database_name:(.*)/ $DBName = $1;
        if $Message =~ /schema_name:dbo(.*)/ $SchemaName = $1;
        if $Message =~ /object_name:(.*)/ $ObjectName = $1;
        if $Message =~ /statement:(.*)/ $Statement = $1;
    </Exec>
</Input>

74.2.3.2. Reading From the Audit File

The audit file is stored in a binary format and is read with the sys.fn_get_audit_file function. NXLog can be configured to collect the audit logs via ODBC with the im_odbc module. For more information about ODBC (and the ConnectionString directive), see the Setting up ODBC section.

Example 333. Reading Audit Events From SQL Audit File

The configuration below uses the im_odbc module to collect audit logs via ODBC. A corresponding name for the action_id is included via a lookup performed on the sys.dm_audit_actions table (see Translating the action_id Field below for more information).

Note	This configuration has been tested with SQL Server 2017.

nxlog.conf


  1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

  <Input in>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 17 for SQL Server}; Server=MSSQL-HOST; \
                        Trusted_Connection=yes; DATABASE=TESTDB_doc81;
    PollInterval        5
    IdType  timestamp
    SQL     SELECT event_time AS 'id', f.*, a.name AS action_name \
              FROM fn_get_audit_file('C:\audit_log\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);
    </Exec>
</Input>

74.2.3.3. Translating the action_id Field

The action_id field in the received events contains the ID of the logged operation (see SQL Server Audit Records on Microsoft Docs). The sys.dm_audit_actions view returns a row for every audit action that can be reported, including a related action_id field and a human-readable action name. The Reading Audit Events From SQL Audit File example above includes the action name field for each audit event. To get a complete list of audit actions and associated details, execute this query and save the result for further reference.

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

74.3. Reading Logs From a Database

NXLog provides the im_odbc module for reading logs from a database via ODBC. For more information about ODBC (and the ConnectionString directive), see the Setting up ODBC section.

The SQL directive requires a SELECT statement for collecting logs. An id field must be returned, and must be used to limit the results of the SELECT statement. Also, some data types may need special handling in order to be used with NXLog. Continue to the following sections for more details.

74.3.1. Configuring the ID

The id field is used to track the position while collecting logs. It allows the im_odbc module to repeatedly poll for new log records without collecting records more than once. In a simple scenario, the id is an auto-increment integer field in a table, but several other data types are supported too (see the IdType directive). It is also possible to generate the id field in the SELECT statement rather than using a field directly.

Writing a working SELECT statement for the SQL directive requires consideration of the id field in two ways.

The SELECT statement must return an id field. While there could be a field named id in a table, it is more common to alias a field as id with the AS clause.
The SELECT statement must limit the results by including a WHERE clause. The WHERE clause should include a question mark (?) which will be substituted with the highest value of the id that was previously seen by the module instance.

The ways that the id can be generated are limited only by the database and the SQL language. However, the following examples show the basic use of the int and datetime2 data types, as well as three which may require special handling: datetimeoffset, datetime, and timestamp (or rowversion).

Example 334. Reading Logs by int ID

In this example, im_odbc collects logs from a table with an auto-increment (identity) int ID field.

Sample Table

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

INSERT INTO dbo.test1 (EventTime, Message)
VALUES ('2018-01-01T00:00:00', 'This is a test message');
GO

nxlog.conf


  1
2
3
4
5
6
7
8

  <Input reading_integer_id>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 17 for SQL Server}; Server=MSSQL-HOST; \
                        Trusted_Connection=yes; Database=TESTDB
    IdType  integer
    SQL     SELECT RecordID AS id, * FROM dbo.test1 WHERE RecordID > ?
    Exec    delete($id);
</Input>

Event Fields

{
  "RecordID": 1,
  "EventTime": "2017-12-31T23:00:00.000000Z",
  "Message": "This is a test message",
  "EventReceivedTime": "2018-04-01T10:40:54.313071Z",
  "SourceModuleName": "reading_integer_id",
  "SourceModuleType": "im_odbc"
}

Example 335. Reading Logs by datetime2 ID

This example shows a table with a datetime2 timestamp field, which im_odbc is configured to use as the id.

Sample Table

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

INSERT INTO dbo.test1 (EventTime, Message)
VALUES ('2018-01-01T00:00:00', 'This is a test message');
GO

nxlog.conf


  1
2
3
4
5
6
7
8

  <Input reading_datetime2_id>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 17 for SQL Server}; Server=MSSQL-HOST; \
                        Trusted_Connection=yes; Database=TESTDB
    IdType  timestamp
    SQL     SELECT EventTime AS id, * FROM dbo.test1 WHERE EventTime > ?
    Exec    delete($id);
</Input>

Example 336. Reading Logs by datetimeoffset ID

This example collects logs from a table with a datetimeoffset field used as the id. The datetimeoffset type stores both a timestamp and an associated time-zone offset, and is not directly supported by im_odbc. Thus, the CAST() function is used to convert the value to a datetime2 type.

Sample Table

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

INSERT INTO dbo.test1 (EventTime, Message)
VALUES ('2018-01-01T00:00:00+01:00', 'This is a test message');
GO

nxlog.conf


  1
2
3
4
5
6
7
8
9

  <Input reading_datetimeoffset_id>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 17 for SQL Server}; Server=MSSQL-HOST; \
                        Trusted_Connection=yes; Database=TESTDB
    IdType  timestamp
    SQL     SELECT CAST(EventTime AS datetime2) AS id, Message FROM dbo.test1 \
            WHERE EventTime > ?
    Exec    delete($id);
</Input>

Example 337. Reading Logs by datetime ID

This example shows a table with a datetime type timestamp which will be used as the id. The datetime type has been deprecated, and due to a change in the internal representation of datetime values in SQL Server, some timestamp values (such as the one shown below) cannot be compared correctly without an explicit casting in the WHERE clause. Without the CAST(), SQL Server may return certain records repeatedly (at each PollInterval) until a later datetime value is added to the table.

Sample Table

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

INSERT INTO dbo.test1 (EventTime, Message)
VALUES ('2018-01-01T00:00:00.333', 'This is a test message');
GO

nxlog.conf


  1
2
3
4
5
6
7
8
9

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

Example 338. Reading Logs by timestamp (rowversion) ID

This example shows a table with a timestamp (or rowversion, see rowversion (Transact-SQL) on Microsoft Docs) type field which is used as the id. Notice that the IdType directive is set to integer rather than timestamp, because the timestamp type is not actually a timestamp.

Sample Table

CREATE TABLE dbo.test1 (
    RowVersion timestamp NOT NULL,
    Message varchar(100) NOT NULL,
)

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

nxlog.conf


  1
2
3
4
5
6
7
8

  <Input reading_rowversion_id>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 17 for SQL Server}; Server=MSSQL-HOST; \
                        Trusted_Connection=yes; Database=TESTDB
    IdType  integer
    SQL     SELECT RowVersion AS id, * FROM dbo.test1 WHERE RowVersion > ?
    Exec    delete($id);
</Input>

74.3.2. Handling Unsupported Data Types

Some of SQL Server’s data types are not directly supported by im_odbc. If an im_odbc instance is configured to read one of these types, it will log an unsupported odbc type error to the internal log. In this case, the CAST() function should be used in the SELECT statement to convert the field to a type that im_odbc supports.

Example 339. Reading the datetimeoffset Type

In this example, a datetimeoffset type field is read as two distinct fields: $EventTime for the timestamp value and $TZOffset for the time-zone offset value (in minutes).

Sample Table

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

INSERT INTO dbo.test1 (LogTime, Message)
VALUES ('2018-01-01T00:00:00+01:00', 'This is a test message');
GO

nxlog.conf


  1
2
3
4
5
6
7
8
9
10
11
12

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

Event Fields

{
  "RecordID": 1,
  "EventTime": "2017-12-31T23:00:00.000000Z",
  "TZOffset": 60,
  "Message": "This is a test message",
  "EventReceivedTime": "2018-04-01T10:40:54.313071Z",
  "SourceModuleName": "odbcdrv17_in",
  "SourceModuleType": "im_odbc"
}

74.4. Writing Logs to a Database

NXLog provides the om_odbc module for writing logs to a database via ODBC. For more information about setting up ODBC (and setting the ConnectionString directive), see the Setting up ODBC section.

The om_odbc sql_exec() function can be used to execute INSERT statements.

Example 340. Writing Events to an SQL Server Database

The following configuration inserts records into the dbo.test1 table of the specified database. The $EventTime and $Message fields in the event record are used for the EventTime and Message fields in the table.

Sample Table

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

nxlog.conf


  1
2
3
4
5
6
7

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

74.5. Setting up ODBC

To connect to a database, an ODBC connection string must be provided via the im_odbc module’s ConnectionString directive. This section provides instructions for setting up a DSN-less ODBC connection to a database from a Linux or Windows system. All connection parameters are given in the connection string and it is not necessary to set up an ODBC DSN (data source name).

Note	To use a DSN instead, consult either the ODBC Data Source Administrator and Data Source Wizard sections on Microsoft Docs (for Windows) or the unixODBC documentation (for Linux), in addition to the content below.

Connections to an SQL Server database can use either Windows Authentication (also called "trusted connection") or SQL Server Authentication. For more information, see Choose an Authentication Mode on Microsoft Docs.

Warning

When connecting to an SQL Server database with SQL Server Authentication, the connection string stored in the NXLog configuration file will need to include UID and PWD keywords for username and password, respectively (this is true for both DSN and DSN-less connections). Because these credentials are stored in plain text, it is important to verify that the configuration file permissions are set correctly. It is also possible to fetch the connection string from another file with the include directive or via a script with include_stdout.

74.5.1. ODBC Driver for SQL Server

Download and install the ODBC driver version and package appropriate for the platform and requirements; see Download ODBC Driver for SQL Server on Microsoft Docs. For more detailed instructions regarding installation on Linux, see Installing the Microsoft ODBC Driver for SQL Server on Linux and macOS on Microsoft Docs.

Example 341. Using ODBC Driver 17 for SQL Server With Windows Authentication

This example uses the "ODBC Driver 17 for SQL Server" driver to connect to the specified server and database. Windows Authentication is used to authenticate (the Trusted_Connection keyword). The UID and PWD keywords are not required in this case. The user account under which NXLog is running must have permission to access the database.

nxlog.conf


  1
2
3
4
5
6
7

  <Input win_auth>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 17 for SQL Server}; Server=MSSQL-HOST; \
                        Trusted_Connection=yes; Database=TESTDB
    IdType  integer
    SQL     SELECT Record AS id, * FROM dbo.test1 WHERE Record > ?
</Input>

Example 342. Using ODBC Driver 13 for SQL Server With SQL Server Authentication

This example uses the "ODBC Driver 13 for SQL Server" driver to connect to the specified server and database. In this case, SQL Server Authentication is used to authenticate. The UID and PWD keywords must be used to provide the SQL Server login account and password, respectively.

nxlog.conf


  1
2
3
4
5
6
7

  <Input sql_auth>
    Module              im_odbc
    ConnectionString    Driver={ODBC Driver 13 for SQL Server}; Server=MSSQL-HOST; \
                        UID=test; PWD=testpass; Database=TESTDB
    IdType  integer
    SQL     SELECT Record AS id, * FROM dbo.test1 WHERE Record > ?
</Input>

74.5.2. FreeTDS

It is also possible to use the FreeTDS driver on Linux.

Run the following commands to install the FreeTDS driver on RHEL 7.

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

Run these commands to install the FreeTDS driver on Debian 9.

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

For more information about using FreeTDS, see the FreeTDS User Guide.

Example 343. Using FreeTDS With SQL Server Authentication

This example uses the FreeTDS driver to connect to the specified server and database.

nxlog.conf


  1
2
3
4
5
6
7

  <Input freetds>
    Module              im_odbc
    ConnectionString    Driver={FreeTDS}; Server=MSSQL-HOST; Port=1433; UID=test; \
                        PWD=testpass; Database=TESTDB
    IdType  integer
    SQL     SELECT Record AS id, * FROM dbo.test1 WHERE Record > ?
</Input>