Check_mssql - Could not connect to database

This support forum board is for support questions relating to Nagios XI, our flagship commercial network monitoring solution.
npolovenko
Support Tech
Posts: 3457
Joined: Mon May 15, 2017 5:00 pm

Re: Check_mssql - Could not connect to database

Post by npolovenko »

@bsivavani, According to the chart below Microsoft SQL Server 2016 needs at least TDS 7.3 or 7.4.

Code: Select all

http://wikis.openlinksw.com/UdaWikiWeb/TDSVersionsExplained
However, it seems like Centos OS only supports TDS up to 7.0 at this moment. I'd still try to change the TDS version to 7.4 in freetds.conf.
As of May 25th, 2018, all communications with Nagios Enterprises and its employees are covered under our new Privacy Policy.
bsivavani
Posts: 339
Joined: Tue Oct 06, 2015 9:17 am

Re: Check_mssql - Could not connect to database

Post by bsivavani »

I tried changing freetds.conf to 7.3 and 7.4, but still receiving the same error

[nagios@XXXX~]$ /usr/local/nagios/libexec/check_mssql -H MSSQLLISTENER --username XXXX --password YYYY--port 1433 --database TESTDB
CRITICAL: Could not connect to dblib:host=MSSQLLISTENER;dbname=TESTDB as XXXX (Exception: SQLSTATE[01002] Adaptive Server connection failed (severity 9)).
[nagios@XXXX~]$
[nagios@XXXX~]$ /usr/local/nagios/libexec/check_mssql -H MSSQLServer --username XXXX --password YYYY --port 2056 --database TESTDB
CRITICAL: Could not connect to dblib:host=MSSQLServer;dbname=TESTDB as XXXX (Exception: SQLSTATE[HY000] Unable to connect: Adaptive Server is unavailable or does not exist (severity 9)).
[nagios@XXXX ~]$

Kindly advice ?
npolovenko
Support Tech
Posts: 3457
Joined: Mon May 15, 2017 5:00 pm

Re: Check_mssql - Could not connect to database

Post by npolovenko »

Hi, @bsivavani. Can you upload your MSSQL servers log file? You can find it in the Admin web interface.
Also, on your Nagios server you can further modify freetds.cong to write a debugging output. Please uncomment this line:

Code: Select all

dump file = /tmp/freetds.log
Then attempt to run the plugin again. After that, you can check on freetds.log to see if there are any errors.
As of May 25th, 2018, all communications with Nagios Enterprises and its employees are covered under our new Privacy Policy.
bsivavani
Posts: 339
Joined: Tue Oct 06, 2015 9:17 am

Re: Check_mssql - Could not connect to database

Post by bsivavani »

Hi,

Please find MSSQL server log file.

2017-10-17 15:12:29.300 Logon Error: 17835, Severity: 20, State: 1.
2017-10-17 15:12:29.300 Logon Encryption is required to connect to this server but the client library does not support encryption; the connection has been closed. Please upgrade your client library. [CLIENT: XXXX]

Please find freetd.log file

log.c:196:Starting log file for FreeTDS 0.91
on 2017-10-17 16:12:29 with debug flags 0x4fff.
iconv.c:330:tds_iconv_open(0x2331bb0, UTF-8)
iconv.c:187:local name for ISO-8859-1 is ISO-8859-1
iconv.c:187:local name for UTF-8 is UTF-8
iconv.c:187:local name for UCS-2LE is UCS-2LE
iconv.c:187:local name for UCS-2BE is UCS-2BE
iconv.c:349:setting up conversions for client charset "UTF-8"
iconv.c:351:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
iconv.c:394:tds_iconv_open: done
net.c:205:Connecting to 10.90.16.160 port 2056 (TDS version 7.0)
net.c:270:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:310:tds_open_socket() succeeded
util.c:156:Changed query state from DEAD to IDLE
login.c:782:quietly sending TDS 7+ login packet
token.c:328:tds_process_login_tokens()
util.c:331:tdserror(0x219f2c0, 0x2331bb0, 20017, 0)
dblib.c:7929:dbperror(0x2331070, 20017, 0)
dblib.c:7981:20017: "Unexpected EOF from the server"
dblib.c:5780:dbgetuserdata(0x2331070)
dblib.c:8002:"Unexpected EOF from the server", client returns 2 (INT_CANCEL)
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2)
util.c:156:Changed query state from IDLE to DEAD
token.c:337:looking for login token, got 0()
token.c:122:tds_process_default_tokens() marker is 0()
token.c:125:leaving tds_process_default_tokens() connection dead
login.c:466:login packet accepted
util.c:331:tdserror(0x219f2c0, 0x2331bb0, 20002, 0)
dblib.c:7929:dbperror(0x2331070, 20002, 0)
dblib.c:7981:20002: "Adaptive Server connection failed"
dblib.c:5780:dbgetuserdata(0x2331070)
dblib.c:8002:"Adaptive Server connection failed", client returns 2 (INT_CANCEL)
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2)
dblib.c:1443:dbclose(0x2331070)
dblib.c:258:dblib_del_connection(0x7f27d789f8e0, 0x2331bb0)
mem.c:615:tds_free_all_results()
dblib.c:305:dblib_release_tds_ctx(1)
dblib.c:5882:dbfreebuf(0x2331070)
dblib.c:739:dbloginfree(0x22ef130)
dblib.c:1512:dbexit(void)
dblib.c:1512:dbexit(void)
dblib.c:305:dblib_release_tds_ctx(1)
npolovenko
Support Tech
Posts: 3457
Joined: Mon May 15, 2017 5:00 pm

Re: Check_mssql - Could not connect to database

Post by npolovenko »

@bsivavani, Can you turn off Force encryption option on the MSSQL server for testing purposes?

Code: Select all

Force Encryption Off
Will the plugin start working?
As of May 25th, 2018, all communications with Nagios Enterprises and its employees are covered under our new Privacy Policy.
bsivavani
Posts: 339
Joined: Tue Oct 06, 2015 9:17 am

Re: Check_mssql - Could not connect to database

Post by bsivavani »

I did turn off Encryption and plugin starts working.

[root@XXXX ~]# /usr/local/nagios/libexec/check_mssql -H XXXX --username XXXX --password XXXX --port 2056 --database TESTDB
OK: Connect time=0.004647 seconds.|query_duration=0.004647s;;
[root@XXXX ~]#

Is it possible to get the plugin to work on encryption enabled SQL instance ? or please suggest if there any other approach for encryption enabled instance.
npolovenko
Support Tech
Posts: 3457
Joined: Mon May 15, 2017 5:00 pm

Re: Check_mssql - Could not connect to database

Post by npolovenko »

@bsivavani, You could try to further modify the freetds.conf file. Take a look at the latest entry i've added:

Code: Select all

[myserver]
host = db.firstworks.com   
port = 1433
tds version = 7.0
encryption = require
Put this into your freetds.conf file, but change the host address.

Code: Select all

# TDS protocol version
tds version = 7.0

# 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

# If you get out-of-memory errors, it may mean that your client
# is trying to allocate a huge buffer for a TEXT field.
# Try setting 'text size' to a more reasonable limit
text size = 64512

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

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


# Change to your MSSQL server settings below:

[myserver]
 host = db.firstworks.com   
 port = 1433
 tds version = 7.0
 encryption = require
You can take a look at this article for more information:

Code: Select all

http://software.firstworks.com/2017/04/tlsssl-encryption-with-ms-sql-server.html
As of May 25th, 2018, all communications with Nagios Enterprises and its employees are covered under our new Privacy Policy.
User avatar
mcapra
Posts: 3739
Joined: Thu May 05, 2016 3:54 pm

Re: Check_mssql - Could not connect to database

Post by mcapra »

Use the fully qualified host name (or IP address) in your FreeTDS configuration name. Like so:

Code: Select all

[host.domain.com]
    host = host.domain.com
    port = 1433
    tds version = 7.1
    encryption = require
Double check the TDS version and make sure it's appropriate for your environment.

This worked for me. Before the fix:

Code: Select all

[root@nagios libexec]# /usr/local/nagios/libexec/check_mssql.php --hostname host.domain.com --username username --password password --port 1443 --database database --query "EXEC some_sp @val1=2815, @val2=76;"
PHP Notice:  Undefined variable: db_connstr in /usr/local/nagios/libexec/check_mssql.php on line 416
CRITICAL: Could not connect to  as username (Exception: SQLSTATE[01002] Adaptive Server connection failed (host.domain.com) (severity 9)).
After the fix:

Code: Select all

[root@nagios libexec]# /usr/local/nagios/libexec/check_mssql.php --hostname host.domain.com --username username --password password --port 1443 --database database --query "EXEC some_sp @val1=2815, @val2=76;"
OK: Query duration=0.026843 seconds. Query result=2815 Query result=09 Query result=4.220000 Query result=45.500 Query result=3.978000 Query result=0.000000 Query result=4.220000 Query result=3.978000 Query result=89 Query result=1 Query result=1 Query result=1 Query result=1|query_duration=0.026843s;; 'someValue'=1;
This worked for me against my local SQL Server 2014 instance with "Force Encryption" set to "Yes". check_mssql is sort of weird and won't let you pass the FreeTDS configuration name as the host argument (which is how PDO/dblib references FreeTDS hosts, by the configuration name), so we trick it by setting the FreeTDS configuration name to the host name or IP address.

Perhaps when PHP 7 is officially supported for Nagios XI, the official Microsoft PHP driver can be used :)
Former Nagios employee
https://www.mcapra.com/
npolovenko
Support Tech
Posts: 3457
Joined: Mon May 15, 2017 5:00 pm

Re: Check_mssql - Could not connect to database

Post by npolovenko »

Thanks, @mcapra!
@bsivavani, Keep us updated on the results.
As of May 25th, 2018, all communications with Nagios Enterprises and its employees are covered under our new Privacy Policy.
bsivavani
Posts: 339
Joined: Tue Oct 06, 2015 9:17 am

Re: Check_mssql - Could not connect to database

Post by bsivavani »

Hi,

Thanks for your help on this.

The setting encryption = require is working for Cluster node, but we are facing below issue to run the query with clustertype (MultiSubnetFailover).

[root@XXXXX ~]# /usr/local/nagios/libexec/check_mssql -H XXXX --username XXXX --password XXXX --port 1443 --database TESTDB
CRITICAL: Could not connect to dblib:host=XXXX;dbname=TESTDB as XXXX (Exception: SQLSTATE[HY000] Unable to connect: Adaptive Server is unavailable or does not exist (severity 9)).
[root@XXXXX ~]#

freetds.log file output
log.c:196:Starting log file for FreeTDS 0.91
on 2017-10-18 19:26:15 with debug flags 0x4fff.
iconv.c:330:tds_iconv_open(0x1cb3bb0, UTF-8)
iconv.c:187:local name for ISO-8859-1 is ISO-8859-1
iconv.c:187:local name for UTF-8 is UTF-8
iconv.c:187:local name for UCS-2LE is UCS-2LE
iconv.c:187:local name for UCS-2BE is UCS-2BE
iconv.c:349:setting up conversions for client charset "UTF-8"
iconv.c:351:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
iconv.c:394:tds_iconv_open: done
net.c:205:Connecting to XXXX port 1443 (TDS version 7.1)
net.c:270:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:306:getsockopt(2) reported: Connection timed out
net.c:316:tds_open_socket() failed
util.c:331:tdserror(0x1b212c0, 0x1cb3bb0, 20009, 110)
dblib.c:7929:dbperror(0x1cb3070, 20009, 110)
dblib.c:7981:20009: "Unable to connect: Adaptive Server is unavailable or does not exist"
dblib.c:5780:dbgetuserdata(0x1cb3070)
dblib.c:8002:"Unable to connect: Adaptive Server is unavailable or does not exist", client returns 2 (INT_CANCEL)
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2)
dblib.c:1443:dbclose(0x1cb3070)
dblib.c:258:dblib_del_connection(0x7ff85be748e0, 0x1cb3bb0)
mem.c:615:tds_free_all_results()
dblib.c:305:dblib_release_tds_ctx(1)
dblib.c:5882:dbfreebuf(0x1cb3070)
dblib.c:739:dbloginfree(0x1c71130)
dblib.c:1512:dbexit(void)
dblib.c:1512:dbexit(void)
dblib.c:305:dblib_release_tds_ctx(1)

Kindly suggest.
Locked