Nagios Support Forum

Support for Nagios products and services
https://support.nagios.com/forum/

Check_mssql - Could not connect to database

https://support.nagios.com/forum/viewtopic.php?t=45917

Page 3 of 4

Re: Check_mssql - Could not connect to database

Posted: Fri Oct 13, 2017 1:58 pm
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.

Re: Check_mssql - Could not connect to database

Posted: Mon Oct 16, 2017 6:00 am
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 ?

Re: Check_mssql - Could not connect to database

Posted: Mon Oct 16, 2017 10:30 am
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.

Re: Check_mssql - Could not connect to database

Posted: Tue Oct 17, 2017 9:21 am
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)

Re: Check_mssql - Could not connect to database

Posted: Tue Oct 17, 2017 10:27 am
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?

Re: Check_mssql - Could not connect to database

Posted: Tue Oct 17, 2017 11:59 am
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.

Re: Check_mssql - Could not connect to database

Posted: Tue Oct 17, 2017 12:38 pm
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

Re: Check_mssql - Could not connect to database

Posted: Tue Oct 17, 2017 12:56 pm
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 :)

Re: Check_mssql - Could not connect to database

Posted: Tue Oct 17, 2017 2:19 pm
by npolovenko
Thanks, @mcapra!
@bsivavani, Keep us updated on the results.

Re: Check_mssql - Could not connect to database

Posted: Wed Oct 18, 2017 12:58 pm
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.
All times are UTC-05:00
Page 3 of 4

Powered by phpBB® Forum Software © phpBB Limited