unable to monitor few mssql database parameters

[RIDS_I2MP]

Hi Team,

We are unable to monitor few mssql parameters(mentioned below):
1. bufferhitratio
2. cachehitratio
3. Deadlocks Per Sec

below is the error message:

[nagios@nagioshostname libexec]$ ./check_mssql_server.py -H <database_server_IP>-U 'nagios' -P '<passwd> ' -p 1433 --bufferhitratio --warning 80: --critical 70:
SQL Server message 18456, severity 14, state 1, line 1:
Login failed for user 'nagios'.
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

Below permissions are granted to the 'nagios' user:

The user has to be a SQL authentication user  "server role" permission should be set to "public" 
"user mapping" permission should be set to "all databases in the instance dbreader permission". 
Permission to query the sysperfinfo table.


Kindly suggest if any more permissions needs to be granted.

[mcapra]

Does the MSSQL user's password have any special characters in it? Stuff like ! # $ ' ". Certain characters are reserved in Bash and may be causing parts of your command to break.

A sequence of errors messages almost identical to yours was solved in this thread due to special characters in the command's arguments conflicting with Bash:
https://support.nagios.com/forum/viewto ... =6&t=25948

[lmiltchev]

RIDS_I2MP, did mcapra's post help? Did you check for special characters in the password?

My test user's "server role" permission is set to "public", and I have no issues running the check below:

Code: Select all

# /usr/local/nagios/libexec/check_mssql_server.py -H x.x.x.x -U 'test' -P 'test' -I 'MyInstance' --bufferhitratio --warning 90: --critical 95:
OK: Buffer Cache Hit Ratio is 100.0%|buffer_cache_hit_ratio=100.0%;90:;95:;;

Code: Select all

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

[RIDS_I2MP]

Hi Team,

yes the password consists '+' char. But we are using the same password for monitoring mssql database of other servers and we are successfully able to monitor it.

[RIDS_I2MP]

Hi Team,

Below is the reply to the command, it seems I have mentioned wrong password in the cmd resulting previously mentioned output:

[nagios@nagiosserverhotsname libexec]$ ./check_mssql_server.py -H <IP>-U '<username>' -P '<password>' -p 1433 --bufferhitratio --warning 80: --critical 70:
Traceback (most recent call last):
File "./check_mssql_server.py", line 464, in <module>
main()
File "./check_mssql_server.py", line 430, in main
execute_query(mssql, options, host)
File "./check_mssql_server.py", line 443, in execute_query
mssql_query.do(mssql)
File "./check_mssql_server.py", line 283, in do
self.calculate_result()
File "./check_mssql_server.py", line 292, in calculate_result
self.result = (float(self.query_result[0]) / self.query_result[1]) * self.modifier
IndexError: list index out of range


Password consists numbers, alphabets and a '+' char.

[nagios@nagiosserverhotsname libexec]$ grep Version /usr/local/nagios/libexec/check_mssql_server.py
# Version 2.0.2

[lmiltchev]

Can you log in as the user in question to the mssql server, run the following query, and show the output?

Code: Select all

SELECT cntr_value FROM sysperfinfo WHERE counter_name LIKE 'Buffer cache hit ratio' AND instance_name=''

This is the query that the plugin runs. I just want to see if you have permissions to run it "locally". I tested my check against Windows server 2008. There may be issues with server 2012 that we are not aware of. What kind of server are you running the check against?

[RIDS_I2MP]

Hi Team,

We didn't find any output against the query suggested by you. What does this mean?

Servers OS is Windows Server 2016 standard.

Unexpectedly the monitoring is working now. No changes were made from Nagios end, not aware about changes on DB server i.e. remote host.

[lmiltchev]

We didn't find any output against the query suggested by you. What does this mean?

I am not sure, but this is the query that is run by the plugin. I just wanted to see if your user had the permissions to run the query "locally". BTW, do you get any output if you change your query to this:

Code: Select all

SELECT cntr_value FROM sysperfinfo WHERE counter_name='Buffer cache hit ratio' AND instance_name=' '

Unexpectedly the monitoring is working now. No changes were made from Nagios end, not aware about changes on DB server i.e. remote host.

This is strange... but I suppose it's good news. Keep an eye on it for a while, and get back to us if case you see any new issues. We will keep the thread open for the time being.