Collect logs from Microsoft SQL Server
Microsoft SQL Server is a relational database management system (RDBMS) developed by Microsoft. It provides transaction processing, business intelligence tools, and analytics applications for corporate IT environments.
NXLog Agent can collect, parse, and enrich SQL Server error and audit logs and forward them to your chosen SIEM. In addition, it can also read and write third-party logs to an SQL Server database via ODBC.
Microsoft SQL Server error log
Microsoft SQL Server logs errors to a UTF-16LE-encoded file with log records spanning multiple lines. We recommend normalizing the encoding to UTF-8, as shown in the examples below.
This configuration specifies the xm_charconv LineReader directive to convert the input from UTF-16LE to UTF-8 encoding. It processes events spanning multiple lines as a single event, and parses log records into $EventTime
, $Source
, and $Message
fields.
<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_error>
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>
SQL Server error events do not contain a closing/footer line. Therefore, each log record is buffered until a new one is received. |
This configuration uses the xm_charconv convert() function to convert the input from UTF-16LE to UTF-8 encoding.
If an event spans multiple lines, it will record an event per line but uses Variables to retain the same $EventTime
and $Source
for all records.
<Extension charconv>
Module xm_charconv
</Extension>
<Input mssql_error>
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>
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 the Windows Event Log Application or Security logs.
The following instructions require SQL Server Management Studio (SSMS) and a Microsoft SQL Server edition with support for 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) on Microsoft Learn for more information.
Configuring 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.
Creating a SQL Server Audit object
- GUI
-
-
Open SQL Server Management Studio and connect to the relevant database instance.
-
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. We do not suggest 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 arrow will be displayed next to the new object’s name, which indicates a disabled object. Right-click on the audit object and select Enable audit. See Verifying SQL Server audit logs below if you encounter any errors.
-
- SQL script
-
Alternatively, create the Server Audit object via SQL by executing the
CREATE SERVER AUDIT
andALTER 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)
Creating a SQL Server Audit Specification
- GUI
-
-
Open SQL Server Management Studio and connect to the relevant database instance.
-
Expand Security, right-click on Server Audit Specifications, and select New Audit.
-
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 arrow will be displayed next to the new object’s name, which indicates the object is disabled. Right-click on the audit object and select Enable audit.
-
- SQL script
-
Alternatively, create the Server Audit Specification object via SQL by executing the
CREATE SERVER AUDIT SPECIFICATION
andALTER 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)
Creating a SQL Server Database Audit Specification
- GUI
-
-
Open SQL Server Management Studio and connect to the relevant database instance.
-
Expand Databases > [database_name] > Security.
-
Right-click on Database Audit Specifications and select New Audit.
-
Choose the Server Audit object created earlier and select the actions you want to audit.
-
Click OK to create the Database Audit Specification object.
-
- SQL script
-
Alternatively, create the Database Audit Specification object via SQL by executing the
CREATE DATABASE AUDIT SPECIFICATION
andALTER 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);
Verifying SQL Server audit logs
- Audit file
-
If you chose File as the audit destination, check that SQL Server created the file and writes new logs when meeting the audit criteria. Check NTFS permissions and ensure the SQL Server user can write to the destination path if you do not see the file.
- 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 enabled. 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
andNETWORK 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:-
Open the Registry Editor (
regedit.exe
). -
Navigate to
HKLM\SYSTEM\CurrentControlSet\Services\EventLog\Security
. -
Right-click on the key name and select Permissions…
-
Add the SQL Server service user and assign it Full Control.
-
Disable, then re-enable the Server Audit object so SQL Server attempt to recreate the
MSSQLSERVER$AUDIT
sub-key. -
Once the key is created, you may remove the Full Control access permission since it is no longer required.
-
-
Collecting SQL Server audit logs
Once you enable 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.
This configuration uses the im_msvistalog input module to collect events with ID 33205
from the Security log.
It parses the $Message
field to enrich log records with further information.
2023-06-25 11:00:00 sql2008-ent AUDIT_SUCCESS 33205 Audit event: event_time:2023-06-25 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:
<Input mssql_audit>
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>
SQL Server saves the audit log file in a 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 more information about ODBC, see Setting up ODBC below.
The configuration below uses the im_odbc input module to collect SQL Server audit logs.
The defined SQL query retrieves the corresponding action details from the sys.dm_audit_actions
table to enrich log records with further information.
<Input mssql_audit>
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>
The action_id
field in the received events represents the ID of the logged operation.
See SQL Server Audit Records on Microsoft Learn for more information.
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.
Execute the following query to retrieve a list of all possible actions.
SELECT DISTINCT action_id, name, class_desc, parent_class_desc
FROM sys.dm_audit_actions
Collecting logs from an SQL Server database
NXLog Agent can read logs from various data sources, including SQL Server databases, via the im_odbc input module. This module requires an ODBC client to establish a connection to the database. See Setting up ODBC for more information.
im_odbc requires the ConnectionString directive, which specifies the database connection parameters, and a SQL SELECT
statement that defines how to query the data.
The statement must return an incremental id field and contain a WHERE
clause specifying 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-increment integer, but several other data types are supported too.
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 using int, datetime2, datetimeoffset, datetime, and timestamp data types.
This configuration uses the im_odbc input module to collect logs from the dbo.test1
table and converts the data to JSON format.
The table uses an auto-increment (identity) int
ID field.
Therefore, the configuration uses the default IdType value.
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 ('2023-06-27T00:00:00', 'This is a test message');
GO
<Extension json>
Module xm_json
</Extension>
<Input mssql>
Module im_odbc
ConnectionString Driver={ODBC Driver 17 for SQL Server}; Server=MSSQL-HOST; \
Trusted_Connection=yes; Database=TESTDB
SQL SELECT RecordID AS id, * FROM dbo.test1 WHERE RecordID > ?
Exec delete($id);
Exec to_json();
</Input>
{
"RecordID": 1,
"EventTime": "2023-06-27T23:00:00.000000Z",
"Message": "This is a test message",
"EventReceivedTime": "2023-06-27T23:00:54.313071Z",
"SourceModuleName": "mssql",
"SourceModuleType": "im_odbc"
}
This configuration uses the im_odbc input module to collect logs from the dbo.test1
table.
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
.
CREATE TABLE dbo.test1 (
EventTime datetime2 NOT NULL,
Message varchar(100) NOT NULL,
)
INSERT INTO dbo.test1 (EventTime, Message)
VALUES ('2023-06-27T00:00:00', 'This is a test message');
GO
<Input mssql>
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>
This configuration uses the im_odbc input module to collect logs from the dbo.test1
table.
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 the timezone offset, which im_odbc does not support by default.
Therefore, the configuration uses the CAST()
SQL Server function to convert the EventTime
to datetime2
.
CREATE TABLE dbo.test1 (
EventTime datetimeoffset NOT NULL,
Message varchar(100) NOT NULL,
)
INSERT INTO dbo.test1 (EventTime, Message)
VALUES ('2023-06-27T00:00:00+01:00', 'This is a test message');
GO
<Input mssql>
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>
This configuration uses the im_odbc input module to collect logs from the dbo.test1
table.
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
.
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 certain records repeatedly until a newer event is recorded.
CREATE TABLE dbo.test1 (
EventTime datetime NOT NULL,
Message varchar(100) NOT NULL,
)
INSERT INTO dbo.test1 (EventTime, Message)
VALUES ('2023-06-27T00:00:00.333', 'This is a test message');
GO
<Input mssql>
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>
This configuration uses the im_odbc input module to collect logs from the dbo.test1
table.
The table uses a rowversion ID field.
Therefore, the configuration uses the default IdType value.
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
<Input mssql>
Module im_odbc
ConnectionString Driver={ODBC Driver 17 for SQL Server}; Server=MSSQL-HOST; \
Trusted_Connection=yes; Database=TESTDB
SQL SELECT RowVersion AS id, * FROM dbo.test1 WHERE RowVersion > ?
Exec delete($id);
</Input>
Converting SQL Server data types
The im_odbc module does not support all SQL Server data types.
Configuring an im_odbc instance to read such a data type will log an unsupported odbc type
error in the log file.
Use the SQL Server CAST()
function to convert the field to a supported data type.
This configuration uses the im_odbc input module to collect logs from the dbo.test1
table.
The table contains a datetimeoffset
field, i.e., a timestamp with the timezone offset.
The configuration converts the field to two distinct fields and stores the timestamp in the $EventTime
field and the timezone offset in minutes in the $TZOffset
field.
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 ('2023-06-27T00:00:00+01:00', 'This is a test message');
GO
<Extension json>
Module xm_json
</Extension>
<Input mssql>
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);
Exec to_json();
</Input>
{
"RecordID": 1,
"EventTime": "2023-06-27T23:00:00.000000Z",
"TZOffset": 60,
"Message": "This is a test message",
"EventReceivedTime": "2023-06-27T23:00:54.313071Z",
"SourceModuleName": "mssql",
"SourceModuleType": "im_odbc"
}
Writing logs to an SQL Server database
NXLog Agent can write logs to an SQL Server database with the om_odbc output module. This module requires an ODBC client to be installed on the host to establish a connection to the database. See Setting up ODBC below for more information.
om_odbc requires the ConnectionString directive, which specifies the database connection parameters, and a SQL INSERT
statement that defines how to write the data.
This configuration inserts records into the dbo.test1
table in the TESTDB
database.
The $EventTime
and $Message
fields in the event record are used for the EventTime
and Message
fields in the table.
CREATE TABLE dbo.test1 (
RecordID int IDENTITY(1,1) NOT NULL,
EventTime datetime2 NOT NULL,
Message varchar(100) NOT NULL,
)
<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>
Setting up ODBC
You must install an ODBC client on the machine where NXLog Agent is installed to connect to a SQL Server database. You can then use the ODBC connection string for the im_odbc ConnectionString directive. The instructions below guide you to set up a DSN-less ODBC connection to your database from a Windows or Linux host. The connection parameters are specified in the connection string; therefore, setting up an ODBC DSN is not necessary.
If you prefer to use DSN instead, consult the ODBC Data Source Administrator and Data Source Wizard guides on Microsoft Learn for Windows or the unixODBC documentation for Linux in addition to the steps below.
Connections to an SQL Server database can use either Windows Authentication, also known as a trusted connection, or SQL Server Authentication. See Choose an Authentication Mode on Microsoft Learn for more information.
To verify that you set up the ODBC driver correctly and that NXLog Agent can connect to the database, we recommended testing the connection using the ODBC Data Source Administrator on Windows or isql on Linux.
When using SQL Server Authentication, the connection string in the NXLog Agent configuration must include the username and password. 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 with the include directive or via a script with include_stdout. |
ODBC driver for SQL Server
Visit Download ODBC Driver for SQL Server on Microsoft Learn to download the appropriate ODBC driver version for your platform. See Install the Microsoft ODBC driver for SQL Server (Linux) for detailed Linux installation instructions.
This configuration uses the ODBC Driver 17 for SQL Server to connect to the SQL Server database.
It specifies the Trusted_Connection=yes
parameter in the connection string to use Windows Authentication.
The NXLog Agent service user will be used for authentication, so you must ensure that the account has permission to access the database.
<Input mssql>
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>
This configuration uses the ODBC Driver 17 for SQL Server to connect to the SQL Server database.
It specifies the UI
and PWD
parameters in the connection string to use SQL Server Authentication.
<Input mssql>
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>
FreeTDS
You can use the FreeTDS driver on Linux hosts. Execute the following commands to install it.
- 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
- Debian 9
-
$ sudo apt-get install tdsodbc unixodbc $ sudo dpkg-reconfigure tdsodbc
See the FreeTDS User Guide for more information.
This example uses the FreeTDS driver to connect to the specified server and database.
This configuration uses the FreeTDS driver to connect to the SQL Server database.
It specifies the UI
and PWD
parameters in the connection string to use SQL Server Authentication.
<Input mssql>
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>