MS SQL

[ecarrasq]

System Info
Linux Distribution and version?

*Linux version 2.6.18-274.3.1.el5 ([email protected]) (gcc version 4.1.2 20080704 (Red Hat 4.1.2-51)) #1 SMP Tue Sep 6 20:14:03 EDT 2011
CentOS release 5.7 (Final)

32 or 64bit?

32 bit

VMware Image or Manual Install of XI?

VMware Image

Are there specials configurations on your system, ie; is Gnome installed? Are you using a proxy? Are you using SSL?

No.

ISSUE

I tried using the MS SQL NagiosXi Wizards to monitor MS SQL databases, but I keep getting "CRITICAL:Unable to access SQL Server."

A more detailed error:

MSSQLFDLauncher: Not found

when trying to monitor a the ms sql service "MSSQLFDLauncher$INSTANCE01" which is the "SQL Server INSTANCE01"

MSSQL: Not found

when trying to monitor a the "MSSQL$INSTANCE01" which is the "SQL Server Integration Services 10.0"


Eddie

[nscott]

Eddie,

Lets cut right to basics of it. Make sure your MSSQL is accessible via IP. I don't think they come accessiable out of the box, so if you haven't already, make it accessible via TCP/IP. Here is a link on how to to do it for 2005, coincedentally it will also give the instance name:

http://kbase.gfi.com/showarticle.asp?id=KBID002920

Drop to the terminal and go to the nagios libexec directory. I find one trouble of MSSQL is the naming scheme they've been rather willy nilly about. So use this to figure out the name credentials:

./check_mssql_database.py -H <MSSQL SERVER> -U <username> -P <password> -T <database you want to connect to> --time2connect

Whats kind of tough here is the MSSQL Server. For SQL 2000, it was simply the IP of the server. For 2005 and 2008 it seemed to be a mix of

IP_ADDRESS

and

IP_ADDRESS/INSTANCE_NAME

Either way, this plugins needs to utilize an account that has select access on the master database. Let me know if you're able or not able to connect.

[ecarrasq]

I got the service check to work by including single quotes around the service name. Since the windows sql service name had "$", Nagios was not treating it, as such, but with 'single quotes', it reads it just fine )

I'm still working on getting the access granted to the MS SQL servers accordingly. I suspect its a permissions issue, but NOT from Nagios, from our SQL Admin. Will keep you posted.

Thank you for all your help, as it guided me in the right direction. I will keep this post open until I get the MS SQL Wizards to work, for me.

Thanks,
Eddie

[nscott]

Yes definitely do that. I'll also put it on my todo list to set the command to use single quotes.

[ecarrasq]

I tried doing a query, as follows: "check_xi_mssql_query!-H <host>\<instance> --port 1433 --username <username> --password "<password>" --database master --query 'SELECT * from sys.databases' --result 'Expected result' --warning 50 --critical 200

But I keep getting:

"UNKNOWN: Invalid characters in the hostname."

I have the following info:

Physical Server
MS SQL Server
Instance
Database

I tried using both "/" and "\", but still get "Invalid characters in the hostname".

I verified the username/password used has "Select" rights also. Is there a certain way to input the hostname?

Feels like I'm very close, but just missing something.

[mguthrie]

Try either wrapping the <hostname>\<instance> in single quotes, or if that doesn't work, try adding an additional '\' so that the second backslash escapes correctly. '\' signals a line break in a shell command.

[ecarrasq]

The single quotes seem to help, now I'm getting:

CRITICAL: Could not connect to <IP Address><instance> as <username>.

The alert above is displayed without the "\", is that normal?

Also, I checked with our SQL Admins and they replied with:

When I attempt to run the query on SQL management studio with those credentials, an error comes up "Logon Failure: the user has not been granted the requested logon type at this computer." That's because of the policy the server admins have on service accounts.

From Nagios perspective, the scripts seem to be working. If we move forward in changing the policy on the service account (<username>), I will keep you posted if all works well.

Thanks,
Eddie

[nscott]

The backslash probably isn't showing up because its an escape character normally. So it may be escaping itself, there are several layers of escaping going with Nagios, one when it gets put into HTML, then another when it gets put into the MySQL database, then possibly another when it gets put into Nagios config files, then another when it gets placed on the command line. So that backslash is probably getting escaping itself once then getting escaped out and thats why its not displaying properly.

But yes, please keep me informed.

[ecarrasq]

Revisiting this problem again.

CRITICAL:Unable to access SQL Server

I am trying to connect to the database, so that I can alert if it is not there (if it was deleted). I'm using the Nagios Xi MS SQL Database Monitoring Wizard which uses the check_mssql_database.py. The MSSQL Server's database is not accessible via IP.

I have the following information, and need the correct syntax to put it together:

**Username (the user is part of a domain, so it would read "domain\username". It is also a service account which has the requested logon type disabled, for security purposes)
**Server
**Instance
**Database with read only

From previous posts, I obtained the following syntax for the MSSQL Connection Time which is not working:

check_xi_mssql_database -H 'Server\Instance' -p 1433 -U 'username' -P "password" -T database --time2connect --warning 1 --critical 5

[nscott]

Eddie,

That plugin thats being used for that particular check (check_mssql) is an older plugin that does not allow instances. However, I'd be willing to work with you on converting on of the Python plugins (which can connect to instances) to a query plugin (rather than just a metrics check plugin). I'll throw something together tonight on it and get back to you tomorrow.