MSSQL - Adaptive Server connection failed

[eliassal]

Here is the ouput from putty

login as: root
[email protected]'s password:
Last login: Tue Nov 27 20:34:50 2018 from 192.168.1.6
[root@localhost ~]# /usr/local/nagios/libexec/check_mssql_server.py -H 192.168.1.69 -U '....\salam' -P '....*....1' nmap 192.168.1.69 -p 1433
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist
Net-Lib error during Connection refused Error 111 - Connection refused
[root@localhost ~]#

[lmiltchev]

/usr/local/nagios/libexec/check_mssql_server.py -H 192.168.1.69 -U '....\salam' -P '....*....1' nmap 192.168.1.69 -p 1433

Did you run this as one command? These are two separate commands:

Code: Select all

/usr/local/nagios/libexec/check_mssql_server.py -H 192.168.1.69 -U '....\salam' -P '....*....1'

Code: Select all

nmap 192.168.1.69 -p 1433

[eliassal]

Here is the output of the 2 commands

login as: root
[email protected]'s password:
Last login: Tue Nov 27 20:35:08 2018 from 192.168.1.6
[root@localhost ~]# /usr/local/nagios/libexec/check_mssql_server.py -H 192.168.1.69 -U '...\salam' -P '....*........'
DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist
Net-Lib error during Connection refused Error 111 - Connection refused
[root@localhost ~]# nmap 192.168.1.69 -p 1433

Starting Nmap 5.51 ( http://nmap.org ) at 2018-11-27 23:31 CET
Nmap scan report for 192.168.1.69
Host is up (0.00072s latency).
PORT STATE SERVICE
1433/tcp closed ms-sql-s
MAC Address: 00:15:5D:01:16:01 (Microsoft)

Nmap done: 1 IP address (1 host up) scanned in 0.15 seconds

Of course I connect to this sql from anywhere inside my network

[eliassal]

Hold a moment just died that SQL service wa in a bad state, I rettsarted sql

[eliassal]

In the VM, it is getting connected now

Last login: Tue Nov 27 23:29:45 2018 from 192.168.1.6
[root@localhost ~]# /usr/local/nagios/libexec/check_mssql_server.py -H 192.168.1.69 -U '.....\salam' -P '.....*.....'
OK: Time to connect was 0.0885810852051s|time=0.0885810852051s;;;;
[root@localhost ~]#

whereas in the service UI test commande, getting

COMMAND: /usr/local/nagios/libexec/check_mssql_server.py -H 192.168.1.69 -U '....\salam' -P '.....\*......' -p 1433 --time2connect --warning 10 --critical 20
OUTPUT: DB-Lib error message 20009, severity 9:
Unable to connect: Adaptive Server is unavailable or does not exist
Net-Lib error during Operation now in progress Error 115 - Operation now in progress

[eliassal]

I tried another service "MSSQL Free Pages" from UI, another different error

COMMAND: /usr/local/nagios/libexec/check_mssql_server.py -H 192.168.1.69 -U '....\\......' -P '.....\.....' -p 1433 --freepages --warning 10 --critical 20
OUTPUT: SQL Server message 18452, severity 14, state 1, line 1:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
DB-Lib error message 18452, severity 14:
General SQL Server error: Check messages from the SQL Server
DB-Lib error message 20002, severity 9:
Adaptive Server connection failed

Nagios VM is not in the domain

[lmiltchev]

The check_mssql_server.py plugin will NOT work with Windows Authentication and requires SQL authentication.

[eliassal]

Thanks, correct, I switched to sql authentication and it works fine. but I am surprised, some sql installation (for sharepoint or Biztalk Server) sql authentication is not allowed, is there a workaround.

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?

Thanks again for your help

[lmiltchev]

Thanks, correct, I switched to sql authentication and it works fine. but I am surprised, some sql installation (for sharepoint or Biztalk Server) sql authentication is not allowed, is there a workaround.

Actually, I was wrong about that. The plugin never used to work with Windows authentication in the past, but it has been updated, and now it should... I am sorry about that! This is mentioned in the document below (page 11):

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

Looking back to your screenshots, I see that it worked for you when you ran it from the command line.

example02.PNG

I believe the only reason it failed in the GUI is because you tested it from within the CCM. The CCM often escapes special characters for security reasons, and gives you the impression that the check failed. See how the "\" is escaped with another backslash, and the special character in the password is escaped too.

example01.PNG

Note: Testing from the CCM was provided for convenience. It was never intended to be a substitute for testing from the CLI.

Try setting up your check with -U 'domain\username' -P 'password' in the CCM, save, and apply configuration. Go to Home > Services > <your service>, force an immediate check to see if the check works.

Important: We noticed that Windows authentication worked on CentOS 7 systems, but didn't work on CentOS 6, even with the same version of the plugin. Most probably this is caused by some old (outdated) python libraries. We will need to do some more digging into this, and test it in-house.

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?

Are you talking about alerts or notifications? Have you received actual email notifications? Were the services in a "non-OK" state prior to going to OK state? Are we talking about recovery emails? Can you show us the exact error? Screenshots would be helpful.

[eliassal]

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

Regarding the other question, it is email that I receive, example of emails just received which I really don't understand its info

Notification Type: PROBLEM
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 19:52:29

Notification Type: PROBLEM
Service: MSSQL Free Pages
Host: sql2k12.salam.net
Address: sql2k12.salam.net
State: WARNING
Info:
(No output on stdout) stderr: Traceback (most recent call last):
Date/Time: 2018-11-29 19:51:17