MSSQL - Adaptive Server connection failed

[lmiltchev]

OK, but I remember that in both places service was not working. I will reconfigure and try win authentication and let you know.

Sure, let us know. If the worst case scenario, you could still use the SQL user credentials, but Windows authentication should work on CentOS 7. FYI, during our tests, Windows auth worked also on CentOS 6 but ONLY when the system was not on a domain. Again, we will need to do some more testing on CentOS/RHEL 6.

Also, when Nagios monitors SQL, several services does not have an input and still it sends alerts, and even when service is OK it sends alerts. Is it possible to configure Nagios not to send alerts when status is OK?

My understanding was that you are receiving notifications on OK states. These two notifications are with State = WARNING... The plugin may have some "hard-coded" thresholds. If you are not using any thresholds in your check, you may be running into some "non-ok" states. I would recommend that you use thresholds that make sense in your environment.

Here's an example of OK, WARNING, and CRITICAL output with different thresholds used:

Code: Select all

/usr/local/nagios/libexec/check_mssql_server.py -H x.x.x.x -U 'username' -P 'password' -I 'instance' --freepages --warning 500 --critical 1000
OK: Free pages is 247.0|free_pages=247.0;500;1000;;

/usr/local/nagios/libexec/check_mssql_server.py -H x.x.x.x -U 'username' -P 'password' -I 'instance' --freepages --warning 50 --critical 1000
WARNING: Free pages is 247.0|free_pages=247.0;50;1000;;

/usr/local/nagios/libexec/check_mssql_server.py -H x.x.x.x -U 'username' -P 'password' -I 'instance' --freepages --warning 50 --critical 100
CRITICAL: Free pages is 247.0|free_pages=247.0;50;100;;

[eliassal]

Yes, I have threshholds as follows

-U 'sa' -P 'mypassword' -I 'SC2K12' --stolenpages --warning 500 --critical 700

for the service "MSSQL Stolen Pages"

but still getting

Service: MSSQL Stolen Pages
Host: sql2k12
Address: sql2k12
State: WARNING
Info:
(No output on stdout) stderr: Traceback (most recent call last):
Date/Time: 2018-11-29 20:57:24

[lmiltchev]

Run the following two commands from the command line on the Nagios XI server, and show the output:

Code: Select all

/usr/local/nagios/libexec/check_mssql_server.py -H <mssql server ip address> -U 'sa' -P 'mypassword' -I 'SC2K12' --stolenpages --warning 500 --critical 700
echo $?

[eliassal]

Hi, here is the output of the command (what is this echo $, I did not use it)

Traceback (most recent call last):
File "/usr/local/nagios/libexec/check_mssql_server.py", line 465, in <module>
main()
File "/usr/local/nagios/libexec/check_mssql_server.py", line 431, in main
execute_query(mssql, options, host)
File "/usr/local/nagios/libexec/check_mssql_server.py", line 444, in execute_query
mssql_query.do(mssql)
File "/usr/local/nagios/libexec/check_mssql_server.py", line 283, in do
self.run_on_connection(connection)
File "/usr/local/nagios/libexec/check_mssql_server.py", line 270, in run_on_connection
self.query_result = cur.fetchone()[0]
TypeError: 'NoneType' object is unsubscriptable

[eliassal]

echo $? gave this

1

[lmiltchev]

Are you getting the same error if you are using MSSQL instead of Windows authentication?

What happens if you run the command without specifying an instance?

Code: Select all

/usr/local/nagios/libexec/check_mssql_server.py -H <mssql server ip address> -U 'username' -P 'password' -p 1433 --stolenpages --warning 500 --critical 700

[eliassal]

I am more than sure it won't work without the name of the instance as it will go to the default instance in which sa is disabled.
Anyway, I gave it a go and here is the rsults

SQL Server message 18456, severity 14, state 1, line 1:
Login failed for user 'sa'.
DB-Lib error message 18456, severity 14:
General SQL Server error: Check messages from the SQL Server
DB-Lib error message 20002, severity 9:
Adaptive Server connection failed

[lmiltchev]

Are you having issues with the "stolenpages" check only or with all mssql checks?

Are you seeing the same error when using both, the MSSQL and Windows authentication? Please show both commands run from the command line, along with the output. Do not show only the output. We also need to see the command that is run. Obfuscate sensitive data.

Run the following commands on the Nagios XI server and show the output:

Code: Select all

uname -a
cat /etc/*release
/usr/local/nagios/libexec/check_mssql_server.py -h | head
grep Version /usr/local/nagios/libexec/check_mssql_server.py
yum list installed | grep mssql
pip list | grep mssql

[eliassal]

Yes, it is the only service, all others are working correctly. Hereunder the output of each command, only the 3rd one in not working, it seems that it is not correct

I can't say for the win authentication as it never worked for any service as you know.

uname -a

Linux localhost.localdomain 2.6.32-504.8.1.el6.x86_64 #1 SMP Wed Jan 28 21:11:36 UTC 2015 x86_64 x86_64 x86_64 GNU/Linux

cat /etc/*release

CentOS release 6.6 (Final)
CentOS release 6.6 (Final)
CentOS release 6.6 (Final)

grep Version /usr/local/nagios/libexec/check_mssql_server.py

# Version 2.0.2

yum list installed | grep mssql

php-mssql.x86_64 5.3.3-3.el6 @epel
pymssql.x86_64 1.0.2-4.el6 @nagiosxi-deps

pip list | grep mssql

-bash: pip: command not found

/usr/local/nagios/libexec/check_mssql_server.py -h | head

Usage: check_mssql_server.py -H hostname -U user -P password -T table --mode

Options:
-h, --help show this help message and exit
Required Options:
-H HOSTNAME, --hostname=HOSTNAME
Specify MSSQL Server Address
-U USER, --user=USER
Specify MSSQL User Name

[lmiltchev]

Hereunder the output of each command, only the 3rd one in not working, it seems that it is not correct.

I wanted to check the version of the mymssql package and compare it to mine. Here's what I see on my system:

Code: Select all

pip list | grep mssql
You are using pip version 7.1.0, however version 18.1 is available.
You should consider upgrading via the 'pip install --upgrade pip' command.
pymssql (1.0.2)

It seems like you haven't followed our official documentation on installing the prerequisites for the MSSQL wizards and plugins as pip is not installed on your system.

https://assets.nagios.com/downloads/nag ... ios-XI.pdf

A word of caution: If you install pip, don't try to upgrade it on CentOS 6 by running:

Code: Select all

pip install --upgrade pip

as this could actually break it. Just leave whatever package gets installed "as is".

Yes, it is the only service, all others are working correctly.

I am glad that the rest of the checks are working correctly. Unfortunately, the "stolenpages" check may never work for you if you have a newer Windows server (2012+). After doing some digging into it, I found out that the Free Pages counter was removed in Windows Server 2012 by Microsoft. This has been mentioned on GitHub here:

https://github.com/NagiosEnterprises/ch ... /issues/10

I can't say for the win authentication as it never worked for any service as you know.

You are running CentOS 6 on which Windows authentication (with domain name) doesn't work. It does however work in CentOS 7.