How do I make Nagios XI look at a MSSQL server instance?

I want to use Nagios XI to monitor my Microsoft SQL database, but it doesn't have a way to specify a server instance. I had thought specifying the instance port would make it work, but I still get the same error message from the command line (using their built-in python script): CRITICAL: Unable to access SQL Server. This happens even when the firewall is completely shut off.

Note: I can connect just fine to this server from Windows 7 ODBC using "servername\instancename" as well as the specific port. Also, I can connect just fine to another SQL Server from the Nagios command line that does not use "instances".

Here are some of the many variations I have tried:
./check_mssql_database.py -H server.domain.local -p 1166 -U username -P password -T MyTable --time2connect
./check_mssql_database.py -H server.domain.local\\instance -p 1166 -U username -P password -T MyTable --time2connect
./check_mssql_database.py -H "server.domain.local\\instance" -p 1166 -U username -P password -T MyTable --time2connect
./check_mssql_database.py -H server.domain.local\\$$instance -p 1166 -U username -P password -T MyTable --time2connect

Is it impossible to have this script connect to an instance? I know that Nagios's check_nt lets you specify an instance.

Thanks,
Dumont
rjonesordwayAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

meko72Commented:
rjonesordwayAuthor Commented:
That only refers to the check_nt command that I already mentioned.
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Does the errorlog on the sql report anything. Make sure youre logging failed logins.

If it logs failes logins, do you see any errors from nagios?
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

rjonesordwayAuthor Commented:
The log does include failed logins, but my attempts from NagiosXI were not showing up in the log. Nevertheless, someone at Nagios created an updated python script called check_mssql_database_update so that a server instance could be specified in the command. Strangely the new script works...but only if I DON'T use the new "instance" switch.
It can be found here:
http://support.nagios.com/forum/viewtopic.php?f=6&t=4508&p=19508#p19508

Even though --time2connect works, it seems other switches return the error "CRITICAL:Can access server but cannot query". This is happening on both SQL servers. Any idea?

Dumont
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Trace successful logins, check rights for this login in databases.
Otherwise run a profiler and see whats happening.

//Marten
rjonesordwayAuthor Commented:
Any tips on how to run a profiler?

Thanks,
Dumont
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
Here's the Microsoft page on the subject: http://msdn.microsoft.com/en-us/library/ff650699.aspx

And something to get you started fast:
http://sqlserverpedia.com/wiki/Using_SQL_Server_Profiler

//Marten
rjonesordwayAuthor Commented:
Thanks Marten.

I ran a trace and can see nagios logging in. But I can't make sense of the information it shows in the trace.
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
A simple way to check if its a permission problem would be to temprarily let the account utilized by Nagios have sysasmin rights on the sql server.

If it works than you know that everything else is confifured correctly. And from this point you can revpke sysadmin rights and start granting objects like audit server.

To read up on permissions and how to grant/revoke them you could start with this link:
http://msdn.microsoft.com/en-us/library/ms186717.aspx
And use links from the page for further in depth information.

Ofcourse, if security is not a concern, or you feel that the account being used by Nagios is safe enough then you could leave this account with the sysadmin rights. Although I would recommend against it!

Regards Marten

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rjonesordwayAuthor Commented:
That worked! Finally figured out that it needed server-level permissions of "Connect SQL" and "View server state".

Thanks!
Dumont
rjonesordwayAuthor Commented:
Also needs database permissions of "Connect" and "Select".
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Network Management

From novice to tech pro — start learning today.