check_mssql connection failed (severity 9)

[btayl]

./check_mssql -H uhempfdbprod.mcit.med.umich.edu --username "empfprddb" --password "****" --database EnterpriseDeviceInventory --port 1433 --query "SELECT+COUNT%28%2A%29+FROM+sys.sysperfinfo" --decode --warning 50 --critical 200 --querywarning 50 --querycritical 200 --result "200"

CRITICAL: Could not connect to dblib:host=uhempfdbprod.mcit.med.umich.edu:1433;dbname=EnterpriseDeviceInventory as empfprddb (Exception: SQLSTATE[01002] Adaptive Server connection failed (uhempfdbprod.mcit.med.umich.edu) (severity 9)).

Freetds file
#
# 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
tds version = 7.4
# 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 = uhempfdbprod.mcit.med.umich.edu
port = 1433
tds version = 7.4

[vtrac]

Hi btayl.
The error seems to complained about user "empfprddb":
CRITICAL: Could not connect to dblib:host=uhempfdbprod.mcit.med.umich.edu:1433;dbname=EnterpriseDeviceInventory as empfprddb
(Exception: SQLSTATE[01002] Adaptive Server connection failed (uhempfdbprod.mcit.med.umich.edu) (severity 9)).

https://stackoverflow.com/questions/372 ... severity-9

Could you please try using single quote for 'empfprddb' and password?

Regards,
Vinh

[btayl]

I still get the same error
./check_mssql -H uhempfdbprod.mcit.med.umich.edu --username 'empfprddb' --password '****' --database EnterpriseDeviceInventory --port 1433 --query "SELECT+COUNT%28%2A%29+FROM+sys.sysperfinfo" --decode --warning 50 --critical 200 --querywarning 50 --querycritical 200 --result "200"

CRITICAL: Could not connect to dblib:host=uhempfdbprod.mcit.med.umich.edu:1433;dbname=EnterpriseDeviceInventory as empfprddb (Exception: SQLSTATE[01002] Adaptive Server connection failed (uhempfdbprod.mcit.med.umich.edu) (severity 9)).

I can attach as that user and password in sql server management studio and run the query

I also have tried removing the quotes from user and password

[vtrac]

Hi btayl.
I have tested your command on my environment and it is working for me.
Here's my test command:

Code: Select all

/usr/local/nagios/libexec/check_mssql -H 192.168.XXX.XXX  -U 'admin' -P 'xxxxxxxx' -p 1433 -q "SELECT COUNT(*) FROM sys.sysperfinfo" --database master  --decode --warning 2000 --critical 3000 --querywarning 2000 --querycritical 3000 --result "200"

Here's the output:

Code: Select all

OK: Query duration=0.000802 seconds. Query result=1068|query_duration=0.000802s;2000;3000 'computed0'=1068;2000;3000

I noticed the "SQLSTATE[01002]" error is related to three things (please make sure they are all correct):

Code: Select all

--username
--password
--database

Could you please run the below commands:

Code: Select all

tsql -C
tsql -L tsql -H 192.168.223.12

Now, please run the below command and see if you can connect with your "username" and "password".
Once connected, list out all your databases.
Here's an example:
tsql -H 192.168.XXX.XXX -p 1433 -U 'admin' -P 'xxxxxxxx'
locale is "en_US.UTF-8"
locale charset is "UTF-8"
using default charset "UTF-8"
1> select name from sys.sysdatabases
2> go

Code: Select all

name
master
tempdb
model
msdb
ReportServer
ReportServerTempDB
dbase
trinity
(8 rows affected)

1> exit

Now, please use the output above and make sure your database is in that list.


Regards,
Vinh

[btayl]

Vinh I think I found my issue my account that I was using was a Domain Account. Would that cause issues?

[vtrac]

Hi btayl.
Please use the account created specifically for you to connect (login) to the database that you want to check.

Please NOTE that two type of accounts:
1. System login account - used to get onto the physical (or virtual) system .... (System-admin is the owner of this)
2. Database login account - used to connect to the database to get data (info) .... (DBA is the owner of this)

Please talk with your DBA (not your system-admin) to get the right login account for that database.


Regards,
Vinh

[btayl]

Thanks Vinh i got my user login fixed but just have one question if I want my query result to error if the results are "0" or "na" how would I do that

[vtrac]

Hi btayl.
You can try using the "negate" command.

Here's the document for using negate:
https://assets.nagios.com/downloads/nag ... ios-XI.pdf


Regards,
Vinh

[btayl]

Vinh that worked Great thanks.

On more thing our old monitoring system could also Monitor the status condition return from a query like 200 is their a plugin that can do that ?

-Bruce

[vtrac]

Hi Bruce,
I'm sorry but I am not sure what plugin you were using in the past, but if you can test that plugin out on your system and see what results you would get.

Once you are able to manually run the plugin, I'm sure you can use NCPA or NRPE to call from Nagios XI.

Also, would it be OK for me to lock/close this post/ticket?


Regards,
Vinh