Every query executed by the mssql() source can be customized. These customized queries are similar to SQL statements, but in can also refer to syslog-ng PE-specific variables with the prefix $. For example, $table is the name of the table.
|
|
Caution:
Hazard of data loss!
The syslog-ng PE application does not validate or limit the contents of customized queries. Consequently, queries performed with a user with write-access can potentially modify or even harm the database. Use customized queries with care, and only on your own responsibility. |
|
|
Caution:
Using unique column names in mssql() fetch-query() results is mandatory from syslog-ng PE 7.0.34. Explicit usage of "AS" SQL keyword could help to achieve this. Ignoring it may raise an error in mssql() source driver and queries will be dropped. |
The available variables are the following:
$last_read_uid: The uid value of the last read record.
-
$columns: Reach the columns() option of the MSSQL source (the default is *). If $columns is defined in the configuration (for example, columns("id" "message")), then the value of this variable will be a comma separated list (for example, "id,message").
-
$fetch_limit: Reach the value of the log-fetch-limit() option.
-
$log_fetch_limit: Alias for $fetch_limit.
-
$table: The name of the table() option.
-
$uid: The name of the uid-column().
-
$uid_column: Alias for $uid.
NOTE: The variables in the statement are not macro references.
Example: mssql() source fetch-query()
The following mssql() source fetch-query() example queries records that are older than the last record:
SELECT * FROM $table WHERE $table.$uid > $last_read_uid ORDER BY $table.$uid
From version 7.0.28, syslog-ng Premium Edition (syslog-ng PE) supports TLS encryption for MSSQL servers.
For more information, see Enable encrypted connections to the Database Engine in the Microsoft SQL Docs online documentation.
Prerequisites
Using TLS encryption for the MSSQL server with syslog-ng PE requires CA certificates correctly configured on the MSSQL server side.
Limitations
Using TLS encryption for the MSSQL server with syslog-ng PE has the following limitations:
NOTE: The tls() configuration block used for the network() source is similar in functionality, but there is no 100% feature parity between them. Consider the details for each tls() configuration block option before using them with your mssql() source.
Configuration
You can enable TLS encryption for your mssql() source on the syslog-ng PE side by including it in your configuration as follows:
source{
mssql(
host(...)
...
tls( ca-file(path/to/ca-file.pem) peer-verify(yes) ssl-options(no-tlsv1) )
);
};
For further details about TLS encryption for your mssql() source, see the description of the tls() configuration block.
Enabling TLS encryption for your MSSQL server
To enable TLS encryption for MSSQL Server,
-
Open the SQL Server Configuration Manager (mmc snapin), navigate to SQL Server Network Configuration > Protocols for SQL Server, and right click Protocols for SQL Server to open the Properties page.
-
On the Flags tab, set Force Encryption to yes.
-
On the Certificate tab, use TLS CA Certificate to browse or import custom TLS CA Certificates for your encrypted connections.
-
To confirm your settings, click OK.
-
Under SQL Server Services, then restart SQL Server instance.
While using MSSQL servers with syslog-ng Premium Edition (syslog-ng PE), you may run into connection errors.
This section provides information about possible connection errors, the return errors you encounter in your syslog-ng PE console log in these cases, the possible reasons behind the issues, and their possible resolutions.
For details about configuring TLS encryption for your MSSQL server, see Configuring TLS encryption for MSSQL servers.
Connection errors listed in this section:
TCP/IP connection error between syslog-ng PE and the MSSQL server
Console return error:
[2021-10-06T09:02:13.708984] Failed to connect to DB; connection-string='DRIVER=libtdsodbc.so;SERVER=mssql-server;PORT=1433;UID=sa;PWD=password;DATABASE=master;ClientCharset=UTF-8', ODBC-Diag='SQLRETURN: SQL_ERROR(-1) | entry: 1, SQLSTATE: 08S01, error_code: 20009, error_string: [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist | entry: 2, SQLSTATE: 08001, error_code: 0, error_string: [FreeTDS][SQL Server]Unable to connect to data source', driver='s_mssql_win_2019#0'
Possible reason: There is a TCP/IP connection error between syslog-ng PE and the MSSQL server.
Possible resolution: Enable TCP/IP protocol over the SQL server.
To enable TCP/IP protocol over SQL server,
-
Open the SQL Server Configuration Manager (mmc snapin), and navigate to SQL Server Network Configuration > Protocols for SQL Server.
-
Next to TCP/IP, double click on the Disabled status.
-
Next to Enable, change the option from No to Yes.
-
Switch to the IP Addresses tab.
-
In the IPAll settings node list, find TCP port, and set it to 1433. .
-
To confirm the settings, click OK.
-
Under SQL Server Services, select the SQL server instance you want to restart.
-
Check your syslog-ng PE connection again.
Incorrect SQL server settings for allowed connections and user settings
Console return error:
[2021-10-06T09:16:04.719213] Failed to connect to DB; connection-string='DRIVER=libtdsodbc.so;SERVER=mssql-server;PORT=1433;UID=test_user;PWD=password;DATABASE=master;ClientCharset=UTF-8', ODBC-Diag='SQLRETURN: SQL_ERROR(-1) | entry: 1, SQLSTATE: 42000, error_code: 18456, error_string: [FreeTDS][SQL Server]Login failed for user \'test_user\'. | entry: 2, SQLSTATE: 08001, error_code: 0, error_string: [FreeTDS][SQL Server]Unable to connect to data source', driver='s_mssql_win_2019#0'
Possible reason: Your SQL server settings for allowed connections and user settings are incorrect.
Possible resolutions:
To resolve the issue, try one of the following methods:
-
Check Server Authentication mode in your MSSQL server settings
-
Start Microsoft SQL Server Management Studio and log in with the following credentials:
-
Server type: Database Engine
-
Server name: <your-current-installation> (default)
-
Authentication:Windows Authentication
-
Open Server Properties and navigate to Security page.
-
Under Server Authentication, enable the following SQL Server and Windows Authentication mode.
-
Open the SQL Server Configuration Manager (mmc snapin), and navigate to SQL Server Services, Restart SQL Server instance.
-
Check Remote server connections in your MSSQL server settings
-
Log in to Microsoft SQL Server Management Studio with the previously used mode.
-
Open Server Properties and navigate to the Connections page.
-
Under Remote server connections, check if Allow remote connections to this server is enabled.
-
Check SQL Login status and Roles
-
Log in to Microsoft SQL Server Management Studio with the previously used mode.
-
Navigate to Security > Logins, and right click Login to open the Properties page for your Login user.
-
Check that all of the following pages have the right settings for your Login user:
-
General
-
Server Roles
-
User mapping
-
Securables
-
Status
Login may not have proper permissions to the SQL server
Console return error:
[2021-10-06T09:22:51.479930] Failed to execute fetch-query; ODBC-Diag='SQLRETURN: SQL_ERROR(-1) | entry: 1, SQLSTATE: 42000, error_code: 297, error_string: [FreeTDS][SQL Server]The user does not have permission to perform this action. | entry: 2, SQLSTATE: 42000, error_code: 300, error_string: [FreeTDS][SQL Server]VIEW SERVER STATE permission was denied on object \'server\', database \'master\'.', driver='s_mssql_win_2019#0'
Possible reason: Login may not have proper permissions to the SQL server.
Possible resolutions:
To resolve the issue, try one of the following methods check SQL Login status and Roles:
-
Log in to Microsoft SQL Server Management Studio with the previously used mode.
-
Navigate to Security > Logins, and right click Login to open the Properties page for your Login user.
-
Check that the Server Roles page has the right status and roles for your Login user.
Low level logging with the mssql() source
In case of the mssql() source, the user can enable an extra level of logging. Developers and users new to syslog-ng PE commonly use this option.
You can use the following debug logging methods:
-
The syslog-ng PE PE7 FreeTDS verbose debug log method
If you start syslog-ng PE with the following enabled environment variable, you can find FreeTDS debug logs in the configured output file:
export TDSDUMP=/tmp/freetds_debug.log
TIP: This debug logging method is recommended if you start syslog-ng PE from service.
-
The syslog-ng PE PE7 FreeTDS stdout debug log method
If you start syslog-ng PE with the following enabled environment variable, you can find FreeTDS debug logs in syslog-ng PE's stdout:
export TDSDUMP=stdout
TIP: This debug logging method is recommended if you start syslog-ng PE from the foreground.
The network() source driver can receive syslog messages conforming to RFC3164 from the network using the TCP, TLS, and UDP networking protocols.
You can use the ALTP protocol as well. For details about the ALTP protocol, see Advanced Log Transport Protocol .
-
UDP is a simple datagram oriented protocol, which provides "best effort service" to transfer messages between hosts. It may lose messages, and no attempt is made to retransmit lost messages. The BSD-syslog protocol traditionally uses UDP.
Use UDP only if you have no other choice.
-
TCP provides connection-oriented service: the client and the server establish a connection, each message is acknowledged, and lost packets are resent. TCP can detect lost connections, and messages are lost, only if the TCP connection breaks. When a TCP connection is broken, messages that the client has sent but were not yet received on the server are lost.
-
The syslog-ng application supports TLS (Transport Layer Security, also known as SSL) over TCP. For details, see Encrypting log messages with TLS.
Declaration:
network([options]);
By default, the network() driver binds to 0.0.0.0, meaning that it listens on every available IPV4 interface on the TCP/514 port. To limit accepted connections to only one interface, use the localip() parameter. To listen on IPv6 addresses, use the ip-protocol(6) option.
Example: Using the network() driver
Using only the default settings: listen on every available IPV4 interface on the TCP/514 port.
source s_network {
network();
};
UDP source listening on 192.168.1.1 (the default port for UDP is 514):
source s_network {
network(
ip("192.168.1.1")
transport("udp")
);
};
TCP source listening on the IPv6 localhost, port 2222:
source s_network6 {
network(
ip("::1")
transport("tcp")
port(2222)
ip-protocol(6)
);
};
A TCP source listening on a TLS-encrypted channel.
source s_network {
network(
transport("tcp")
port(2222)
tls(
peer-verify("required-trusted")
key-file("/opt/syslog-ng/etc/syslog-ng/syslog-ng.key")
cert-file("/opt/syslog-ng/etc/syslog-ng/syslog-ng.crt")
);
);
};
A TCP source listening for messages using the IETF-syslog message format. Note that for transferring IETF-syslog messages, generally you are recommended to use the syslog() driver on both the client and the server, as it uses both the IETF-syslog message format and the protocol. For details, see syslog: Collecting messages using the IETF syslog protocol (syslog() driver).
source s_tcp_syslog {
network(
ip("127.0.0.1")
flags(syslog-protocol)
);
};
For details on the options of the network() source, see network() source options.
