Recommended method to monitor MSSQL?


#1

What is the recommended method to monitor MSSQL? I need to run queries to add custom checks.

I have got check_mssql working but I am concerned about security. Is it generally a good idea to run plugins that require user / password to remote resources? nrpe use ssl but a simple php /sh plugin will send the password in clear text?

I am considering to use nsclient and a vbs script instead.

What is normally considered safe enough?


#2

This is a great question. Over nrpe, or any other network protocol, I would suggest to put any passwords on the remote end and not place them in the transport. You can use meaningless aliases, over the transport, for your passwords and look them up from a table. Obviously if you are talking to a server, like MSSQL, then you must use whatever authentication it requires. As you MUST use whatever authentication it requires then wouldn’t you have already agreed to making use of this whatever?

If your concern is that Nagios is the only remote user of this service, then I would recommend using some form of RPC to perform the check. Under Windows your options are limited, but I’m sure there are tools like IIS and SSH Server for Windows.

If your concern is that the Nagios host would be another node that would have access to the service and at that it’s a whole new OS and perhaps a new set of security risks, then see the previous concern.

If your concern is that the Nagios host is on another network, then place a Nagios host on the network that should have access to this service. Also see previous concerns.

I hope this helps :wink:

This brings us back to storing passwords on the host that will have access to the server. Firstly make sure this password and it’s access are absolute minimal, read-only and only-read meaningless randomly generated data. After that you may just decide to let the password be generally secure, an easy precaution to take is to remove group and other flags from a file "chmod a-go " or over FTP these commands can be quoted "chmod 400 " and "chmod 500 ".

For more security gpg can be used to encrypt the data, but this is not-so useful if the passphrase or passkey is stored on the same host. Putting a passphrase into a nrpe session is not a bad idea, an attacker can’t use this data against any service. Still the data will be available on the host during checks(every X minutes) and it won’t be any less available to some one who already cracked system security then a chmod/ed file.

In the end you may just have to settle for trusting your firewalls and other security to keep your MSSQL servers safe, honestly if you are worried about some one getting the password how worried would you be to discover they could actually make use of it? Wouldn’t you be more concerned about what attacks are possible on an open MSSQL server when the attacker does not have an account? My gut says security for MSSQL is tight, but there are plenty of bugs and exploits. An attacker would likely jump straight to installing her/his fav. bot, rather then messing with the limited access logging into the SQL side of the server(as an SQL user VS executing an arbitrary blob of rootkit) would gain.


#3

Hello,
Thank you for a very good answer!

I think this is pretty much the situation. As far as I can tell the .Net application that “owns” the database does not encrypt the password when accessing.

Yes, my concern is that I bring something new in. The real concern is that this may raise questions more than a real concern for data theft or corruption. (everything is on the intranet)
Thanks to your answer I think I have it under control.
When I use check_mssql, which is the quickest fix, I will use a readonly user and store the password as a macro in the nagios resource file.

If I have time I will still try the nsclient & vbs script for the application related sql checks since this have the added value that I can get them categorised under the application server instead of the database server in the list.

//J


#4

I think one solution I might suggest is to setup a Windows machine just to check MSSQL. On this box put an IIS server with a handful of application scripts(exactly like on the real web servers) that do your costume monitoring thing. This way you don’t introduce anything new and you keep your monitoring on a backend machine where prying Internet eyes hopefully can’t get.

You can also go out on a limb and run a VM on this box to run NRPE and easily bind the whole thing into Nagios.

This all of course is over-designed and may be seen as indirect slander on the stability and security of Linux/Unix. I’d hope most ppl would admire the amount of work that went into building such a homogeneous environment and feel enlightened by the concept.

– The walls have eyes, The trees have ears, so the Ethernet cables must have a good sense of smell.