MSSQL Server Wizard

[kruserm]

Nagios version = 2012R1.2

I have a SQL 2005 server that I would like to pull performance info from. I created a SQL user for the MSSQL Server wizard and originally did not give it any rights. I configured the MSSQL Server wizard and added our DB server. On every metric we get CRITICAL: Unable to access SQL Server.

I went to the nagios server and ran:

/usr/local/nagios/libexec/check_mssql_server.py -H SQL-SERVER -U USERNAME -P PASSWORD --bufferhitratio
Result is: CRITICAL:Can access server but cannot query.

I tried to give the user different permissions to the master DB but it would return the same results.

I tried the same command with the sysadmin role granted with no access to the master DB and it worked.

/usr/local/nagios/libexec/check_mssql_server.py -H SQL-SERVER -U USERNAME -P PASSWORD --bufferhitratio
Result is: OK:Lazy Writes / Sec is 0.0|Lazy_Writes_/_Sec=0.0;;;;

The problem is that I cant leave the sysadmin role granted. So what permissions does the SQL user need to pull performance metrics from SQL 2005?

[nscott]

It needs SELECT on master and SELECT on tempdb.

[kruserm]

I am not familiar with the select permission. I did give that user db_owner rights to the master and tempdb databases and I get the same error.

Here is the command:

/usr/local/nagios/libexec/check_mssql_server.py -H SQL-SERVER -U USERNAME -P PASSWORD --bufferhitratio
Result is: CRITICAL:Can access server but cannot query

[abrist]

The question is "which rights?". As nscott pointed out, you need to give the user SELECT permissions. You can always pull up mssql docs on how to do so with your version of mssql.

[kruserm]

I am still having problems getting the script to work. I did give the user I created select rights to the master and tempdb. But I am getting the same message:

/usr/local/nagios/libexec/check_mssql_server.py -H SQL-SERVER -U USERNAME -P PASSWORD --bufferhitratio
Result is: CRITICAL:Can access server but cannot query

Is there someone that has gotten this to work that can give a little more detailed steps on what you need to do?

[kruserm]

I ran GRANT VIEW SERVER STATE TO SQL-USERNAME and this allowed the script to run on the nagios console.

Here are the results:
/usr/local/nagios/libexec/check_mssql_server.py -H SERVER-IP -U SQL-USERNAME -P PASSWORD --bufferhitratio
OK:Buffer Cache Hit Ratio is 100.0%|Buffer_Cache_Hit_Ratio=100.0%;;;;

But when I configure the wizard I am still an error in the web console.

Here is the error I am getting:
CRITICAL: Unable to access SQL Server.

Does anyone have any other ideas?

[scottwilkerson]

Can you show the full command you get by going to the service detail page -> configure tab -> Re-configure this service

[kruserm]

If this is not what you are asking for let me know. I did look at this and check this command in CCM to make sure it was using the same script I was using on the console and it was, see screenshot.

check_xi_mssql_server!-H 'DB-SERVER-IP' -U 'SQL-USER' -P 'PASSWORD' -I 'MSSQLSERVER' -p 1433 --bufferhitratio --warning 90: --critical 95:

[scottwilkerson]

Generally, when you are using instances you would not have the port

I would try

Code: Select all

check_xi_mssql_server!-H 'DB-SERVER-IP' -U 'SQL-USER' -P 'PASSWORD' -I 'MSSQLSERVER' --bufferhitratio --warning 90: --critical 95:

[kruserm]

WOOT!!!!! That worked. I appreciate all your help on this. I ran through the wizard on more time and read the notes under the Port parameter.....dont configure the port if you are using a named instance. I will have to pay attention to the details in the future. Thanks again.