Monitoring a Microsoft SQL Server

[Kaplan, Andrew H.]

Hi there --
 
I finally got both the check_mssql and check_mssql_health scripts to work with
the server in question. Listed below are the notes
that I took during the testing process. I hope they are of use, and if there are
any questions, please feel free to contact me at your
convenience. Thanks again to everyone for the help.
 
NOTE: The SQL Server in this scenario uses named instances, which result in
dynamic TCP/IP ports being used rather than 

static ones. The ports can be identified by running the SQL Server Configuration
Manager utility. Once the utility is open, go 

to the SQL Server Network Configuration section. Select the database in question
from the list of Protocols for... entries. 

Double-click on the TCP/IP option listed in the right pane. Select the IP
Addresses tab of the window that appears on-screen. 

Scroll down to the IPAll section, and make note of the value that is listed next
to the TCP Dynamic Ports field. That value is what 

will be used in the command syntax when running the script.

 

The user account, sa, along with its password will be needed to successfully
access the database. The command syntax, 

with the resulting output is shown below. 

 

check_mssql -H <ip address>  --username sa --password <password> --port <dynamic
TCP port>

 

OK: Connect time=0.022503 seconds.

 

When using the check_mssql_health plugin, the first thing that needs to be done
is to add an entry of the SQL server to the 

/etc/freetds/freetds.conf file. This file is used by the plugin to connect to
the server. An excerpt of the file is shown below:

 

# <SQL Server Name>

[name of sql server]                                    <- name of the server
used in the command syntax

        host = <ip address> or <fqdn>

        port = <dynamic tcp port>                   <- TCP Dynamic port used by
the server instance      

        tds version = 7.0

 

An example of the command syntax that is used to connect to the server is the
following:

 

/usr/local/nagios/libexec/check_mssql_health --server=<name of sql server>
--username=sa 

--password=<password> --port=<dynamic tcp port> --mode=connection-time

 

The output of the above command is shown below:

 

OK - 0.06 seconds to connect as sa | connection_time=0.06;1;5


________________________________

From: Claudio Kuenzler [mailto:ck at claudiokuenzler.com] 
Sent: Thursday, November 17, 2011 3:33 PM
To: Kaplan, Andrew H.
Cc: Nagios Users List
Subject: Re: [Nagios-users] Monitoring a Microsoft SQL Server


The other ports don't appear like typical MSSQL ports.
Do you have by any chance the Windows firewall still active? Maybe the port is
being blocked there?
http://support.microsoft.com/kb/968872

Or do you use a named instance? I found this information on
http://benchmarkitconsulting.com/colin-stasiuk/2009/02/02/what-tcp-port-is-sql-s
erver-running-under/: "If you have a named instance the TCP port is dynamically
configured."
I've never seen this in a practical way before though.



On Thu, Nov 17, 2011 at 8:59 PM, Kaplan, Andrew H. <AHKAPLAN at partners.org>
wrote:


	
	Hi there --
	 
	I tried connecting to port 1433 of the SQL server, and the connection
was refused. It appears that efforts to connect via the default
	port will not work in this case. I took the liberty of running the
nestat -abn command syntax on the console of the SQL server, and
	searched for all references to the sqlservr.exe binary. The results are
shown below:
	 
	Active Connections

	[sqlservr.exe]

	TCP 0.0.0.0:58477 0.0.0.0:0 LISTENING

	[sqlservr.exe]

	TCP 0.0.0.0:62502 0.0.0.0:0 LISTENING

	[sqlservr.exe]

	TCP 0.0.0.0:65249 0.0.0.0:0 LISTENING

	[sqlservr.exe]

	TCP 127.0.0.1:58751 0.0.0.0:0 LISTENING

	[sqlservr.exe]

	TCP 127.0.0.1:62503 0.0.0.0:0 LISTENING

	[sqlservr.exe]

	TCP 127.0.0.1:63954 127.0.0.1:63955 ESTABLISHED

	[sqlservr.exe]

	TCP 192.168.40.114:62502 192.168.40.122:50342 ESTABLISHED

	[sqlservr.exe]

	TCP 192.168.40.114:62502 192.168.40.125:60257 ESTABLISHED

	[sqlservr.exe]

	TCP 192.168.125.1:139 0.0.0.0:0 LISTENING

	[sqlservr.exe]

	TCP [::]:58477 [::]:0 LISTENING

	[sqlservr.exe]

	TCP [::]:62502 [::]:0 LISTENING

	[sqlservr.exe]

	TCP [::]:65249 [::]:0 LISTENING

	[sqlservr.exe]

	TCP [::1]:58751 [::]:0 LISTENING

	[sqlservr.exe]

	TCP [::1]:62005 [::1]:62006 ESTABLISHED

	[sqlservr.exe]

	UDP 0.0.0.0:123 *:* 

	 

	If the default port, 1433, is not reachable would one the above ports be
the alternate means of connecting to the server?

	 

	 

	

	
	
________________________________

	From: Claudio Kuenzler [mailto:ck at claudiokuenzler.com] 
	Sent: Thursday, November 17, 2011 1:39 PM 

	To: Nagios Users List
	Subject: Re: [Nagios-users] Monitoring a Microsoft SQL Server
	

	Please let us know if you get it working - I might have to add such a
check soon as well.
	
	To answer your question: 1433 is the standard port of MSSQL so that
should be ok. You can launch nmap or a simple telnet to double-check that.
	
	
	On Thu, Nov 17, 2011 at 4:34 PM, Jim Avery <jim at jimavery.me.uk> wrote:
	

		On 17 November 2011 15:17, Kaplan, Andrew H.
<AHKAPLAN at partners.org> wrote:
		> Hi there --
		>
		> Thanks for your reply. I tried your suggestion on the two
plugins, and here are
		> the results.
		>
		> When the command syntax for check_mssql was the following:
		>
		> ./check_mssql -H <ip address> -U <domain>\\<username> -P
<password>
		>
		> or
		>
		> ./check_mssql -H <ip address> -U '<domain>\\<username>' -P
<password>
		>
		> The error message displayed on-screen was:
		>
		> UNKNOWN: Invalid characters in the username.
		
		
		
		The check_mssql plugin is way too fussy about what characters it
will
		allow in host names and user names.  On my system I edited the
plugin
		to remove the if/else sections entirely below the comments "//
		Validate the hostname" and "// Validate the username".
		
		hth,
		
		Jim
		

	
------------------------------------------------------------------------------
		All the data continuously generated in your IT infrastructure
		contains a definitive record of customers, application
performance,
		security threats, fraudulent activity, and more. Splunk takes
this
		data and makes sense of it. IT sense. And common sense.
		http://p.sf.net/sfu/splunk-novd2d
		_______________________________________________
		Nagios-users mailing list
		Nagios-users at lists.sourceforge.net
		https://lists.sourceforge.net/lists/listinfo/nagios-users
		::: Please include Nagios version, plugin version (-v) and OS
when reporting any issue.
		::: Messages without supporting info will risk being sent to
/dev/null
		


	
	

	The information in this e-mail is intended only for the person to whom
it is
	addressed. If you believe this e-mail was sent to you in error and the
e-mail
	contains patient information, please contact the Partners Compliance
HelpLine at
	http://www.partners.org/complianceline . If the e-mail was sent to you
in error
	but does not contain patient information, please contact the sender and
properly
	dispose of the e-mail.


-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://www.monitoring-lists.org/archive/users/attachments/20111118/c3a64e17/attachment.html>
-------------- next part --------------
------------------------------------------------------------------------------
All the data continuously generated in your IT infrastructure 
contains a definitive record of customers, application performance, 
security threats, fraudulent activity, and more. Splunk takes this 
data and makes sense of it. IT sense. And common sense.
http://p.sf.net/sfu/splunk-novd2d
-------------- next part --------------
_______________________________________________
Nagios-users mailing list
Nagios-users at lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/nagios-users
::: Please include Nagios version, plugin version (-v) and OS when reporting any issue. 
::: Messages without supporting info will risk being sent to /dev/null