Facing Issues while monitoring MSSQL (issue is for 4 servers out of 80)

[DileepKumar]

Hi,

I'm facing issues while monitoring MSSQL Windows servers. I'm using Nagios XI and configured monitoring for around 100 MSSQL servers and some services but I'm facing the issue only with 4 servers which are of version "SQL SERVER 2016". Issues I'm facing is only with MSSQL DB related service checks but not to OS related service checks

I'm using check_mssql_health and check_sql (for query monitoring) facing below issues respectively for each plugin

"CRITICAL - DBI connect(':host=IPADDRESS:port=PORT:encryptPassword=1','USER',...) failed: OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (44)
Server , database
Message String: Server name not found in configuration files.
OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (34)
Server , database
Message String: Adaptive Server connection failed
OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (34)
Server , database
Message String: Adaptive Server connection failed
at /usr/local/nagios/libexec/check_mssql_health line 6929."

&&

"CHECK_SQL UNKNOWN - OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (34)
Server 10"

I have tried checking for DB logs and below is the error I found
"Length specified in network packet payload did not match number of bytes read; the connection has been closed. Please contact the vendor of the client library. [CLIENT: Nagios XI IP]"

I made sure same level of access has been provided for all the MSSQL servers at DB level but issue is for only 4 servers. Please help me on this.

Thank you

[lgute]

Hi @DileepKumar,

Thanks for reaching out. I'm wondering if this might have to do with a fix for the MSSQL plugins, where it now allows setting the TDS level. According to this document, 2026 uses TDS 7.4.

I'll do some more digging and get you the argument for the plugin.

[DileepKumar]

Hi @DileepKumar,

Thanks for reaching out. I'm wondering if this might have to do with a fix for the MSSQL plugins, where it now allows setting the TDS level. According to this document, 2026 uses TDS 7.4.

I'll do some more digging and get you the argument for the plugin.

Hi @Igute,

Thank you very much for the quick help.

If it is indeed related to TDS version, then why does all other MSSQL Server monitoring is working and not the mentioned 2016 MSSQL Servers when as per the document 7.4 is the common version required for SQL Server 2012 - SQL Server 2025

Anyhow, below is my Nagios server TDS details

tsql -C
Compile-time settings (established with the "configure" script)
Version: freetds v1.4.23
freetds.conf directory: /etc
MS db-lib source compatibility: yes
Sybase binary compatibility: yes
Thread safety: yes
iconv library: yes
TDS version: auto
iODBC: no
unixodbc: yes
SSPI "trusted" logins: no
Kerberos: yes
OpenSSL: no
GnuTLS: yes
MARS: yes

and

cat /etc/freetds.conf
#
# This file is installed by FreeTDS if no file by the same
# name is found in the installation directory.
#
# For information about the layout of this file and its settings,
# see the freetds.conf manpage "man freetds.conf".

# Global settings are overridden by those in a database
# server specific section
[global]
# TDS protocol version
tds version = auto

# Whether to write a TDSDUMP file for diagnostic purposes
# (setting this to /tmp is insecure on a multi-user system)
; dump file = /tmp/freetds.log
; debug flags = 0xffff

# Command and connection timeouts
; timeout = 10
; connect timeout = 10

# To reduce data sent from server for BLOBs (like TEXT or
# IMAGE) try setting 'text size' to a reasonable limit
; text size = 64512

# If you experience TLS handshake errors and are using openssl,
# try adjusting the cipher list (don't surround in double or single quotes)
# openssl ciphers = HIGH:!SSLv2:!aNULL:-DH

# A typical Sybase server
[egServer50]
host = symachine.domain.com
port = 5000
tds version = 5.0

# A typical Microsoft server
[egServer73]
host = ntmachine.domain.com
port = 1433
tds version = 7.3


please let me know in case of any more details are required or whether I need to update it to newer version

Thank You

[lgute]

Hi @DileepKumar,

I do not know that the TDS version is in fact the issue. I know other users have had issues with the TDS version, because we had to add support for specifying the TDS version. So, I am just giving you a suggestion for something to try.

Probably the easiest way to check the TDS version is in the CCM

1. Navigate to the Configure | Core Config Manager (CCM) | click Services.
2. Click on one of the MS SQL Server services.
3. Look at the $ARG1$ field to see if --tdsversion is set.
4. If not, you can add the parameter and value --tdsversion 7.4, otherwise, you can try changing the value.
5. Click the the Run Check Command button.
6. In the popup click Run Check Command, to test if the changes made any difference.

If you need more help, please open a ticket with our Technical Support team. You can open a ticket at https://support.nagios.com/.

[DileepKumar]

Hi @DileepKumar,

I do not know that the TDS version is in fact the issue. I am just giving you a suggestion for something you can try.

Probably the easiest way to check the TDS version is in the CCM

1. Navigate to the Configure | Core Config Manager (CCM) | click Services.
2. Click on one of the MS SQL Server services.
3. Look at the $ARG1$ field to see if --tdsversion is set.
4. If not, you can add the parameter and value --tdsversion 7.4, otherwise, you can try changing the value.
5. Click the the Run Check Command button.
6. In the popup click Run Check Command, to test if the changes made any difference.

If you need more help, please open a ticket with our Technical Support team. You can open a ticket at https://support.nagios.com/.

Hi @lgute,

Thank you very much for the help.

I tried following your steps and gave the --tdsversion in the argument for the plugins I'm using (check_mssql_health & check_sql), but it seems those particular plugins, do not have --tdsversion option or mode.

I have tried your suggested methods, even for the working MSSQL servers but after giving "--tdsversion" it is also failing. Any idea?

Usage: check_sql -H <hostname> -d <driver> [ -p <port> ] [ -t <timeout> ] -U <user> -P <pass> [ -D <db> ] [ -w <warn_range> ] [ -c <crit_range> ] [ -W <warn_range> ] [ -C <crit_range> ] [ -q <query> ] [ -f <file> ] [ -e <expect_string> ] [ -r ] [ -s ] [ -l label ] [--hostconnect]

[lgute]

Yeah, I was just re-reading your original post and realized you are having issues with the check_mssql_health plugin, which seems to be available from the Nagios Exchange, instead of the XI plugins check_mssql_server.php, which has the TDS version parameter or check_mssql which does not have TDS version.

Do you know where your plugins came from? Maybe there are updated versions that could help you.

[DileepKumar]

Yeah, I was just re-reading your original post and realized you are having issues with the check_mssql_health plugin, which seems to be available from the Nagios Exchange, instead of the XI plugins check_mssql_server.php, which has the TDS version parameter or check_mssql which does not have TDS version.

Do you know where your plugins came from? Maybe there are updated versions that could help you.

Now, after you said, I tried with check_mssql_server.php and got the same issue even after trying with --tdsversion. However, i tried using tsql to test the login of the MSSQL server to make sure issue is not in plugins in the first place and below is the issue, I'm facing the same issue with all the 4 servers that I'm currently having issue with.

tsql -H IP -U "user" -P "password" -p 4070
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
Error 20002 (severity 9):
Adaptive Server connection failed
Error 20002 (severity 9):
Adaptive Server connection failed
There was a problem connecting to the server


I even tried giving different TDS versions as below starting from 7 to 7.4 but issue is still there
tsql -H IP -U "user" -P "password" -p 4070 -t 7.3

I do not understand where to o from here to find the solution.

[lgute]

Hi @DileepKumar,

I would check for firewall/network issues that may be preventing your XI server from connecting to your MSSQL Servers.

Other possibilities generated by a quick search (AI generated)...

The "Error 20002: Adaptive Server connection failed" when using the tsql command typically indicates a failure to establish a connection to the SQL Server, often related to configuration, network, or cryptographic issues. Common causes and solutions include:

TLS/SSL and Cryptographic Policy Mismatch: On systems like Rocky Linux 9, older cryptographic policies have been retired, which can prevent connections if the SQL Server requires older, less secure algorithms. This was observed when connecting to a SQL Server 2016 instance on Windows Server 2012 R2, where the error "handshake failed: One of the involved algorithms has insufficient security level" occurred. The solution involved setting the TDS version to 7.0 in the /etc/freetds.conf file for the specific server entry, as newer versions of FreeTDS may not support older TLS configurations on updated systems.

Incorrect TDS Version: Using an incompatible TDS version can cause connection failures. For SQL Server 2019 and later, a TDS version of 7.4 or higher is recommended, while older versions like 7.0 or 7.3 may be required for SQL Server 2008–2016. For connections to MSSQL, specifying TDSVER=8.0 or TDSVER=7.1 (for newer Ubuntu versions) can resolve the issue.

Network and Firewall Issues: Ensure that the target SQL Server port (commonly 1433 or a custom port like 1477) is accessible. Testing connectivity with ncat or telnet can verify basic network reachability. If the connection hangs or fails, the issue may lie with firewall rules or the SQL Server not listening on the expected port.

Password or Configuration Errors: Special characters in passwords can sometimes interfere with the connection. Removing or escaping such characters may resolve the issue. Additionally, incorrect or missing entries in the freetds.conf file, such as missing host, port, or tds version settings, can lead to this error.

Server-Side Restrictions: Ensure the SQL Server's IP address is whitelisted in the firewall or MSSQL Database settings, as blocked IPs will prevent connection attempts.

Ending with this suggestion for troubleshooting...

To troubleshoot, use TDSDUMP=stdout tsql -H <hostname> -p <port> -U <username> -P <password> to capture detailed diagnostic output, which can reveal the root cause, such as TLS handshake failures or incorrect TDS negotiation.

[DileepKumar]

Hi @lgute,

I have tested TDSDUMP=stdout tsql -H <hostname> -p <port> -U <username> -P <password> and here is the summary of the output as per my understanding. Also, I have attached the complete debug output with the name "MSSQL TDSDUMP"

Summary :

- TCP socket connection succeeds.
- The client and server begin a TLS 1.2 handshake using GNUTLS.
- The server responds with a SERVER HELLO, certificate, and key exchange.
- The selected cipher suite is: GNUTLS_ECDHE_RSA_AES_256_GCM_SHA384
- The server uses X25519 for key exchange and RSA-SHA1 for signature verification
- The handshake fails with this message:
handshake failed: One of the involved algorithms has insufficient security level.
login packet rejected
Adaptive Server connection failed



MSSQL server is of version MSSQL 2016
&
OS is Windows 2016 standard


Please let me know in case of any other details are required

[DylanWalker]

I attempted to use T-SQL to test the login for the MSSQL server to ensure that the problem isn't related to the plugins. Below is the issue I'm encountering, which is consistent across all four servers I'm currently having trouble with.