Opsview Knowledge Center

Database - Microsoft SQL

An overview of the Microsoft SQL Opspack for Opsview Monitor

Prerequisites

The plugins check_mssql_database.py and check_wmi_plus.pl are used with this Opspack. check_wmi_plus.pl is delivered from the WMI Opspack. check_mssql_database.py is also included, but has some dependencies:

  • Requires the Python MSSQL module. For Ubuntu and Debian, you need to install python-pymssql. For RHEL/CentOS, you will need to install pymssql. For SLES, you will need to source the dependency manually as it is not clear which package will provide this.

Download Links:

Download the above .rpm files, and then use a command such as 'yum 'nogpgcheck localinstall pymssql-1.0.2-4.el6.i686.rpm'

For SLES: Download and compile using the source .tar.gz file here: http://sourceforge.net/projects/pymssql/files/pymssql/1.0.2/

Note: There have been issues during initial testing with monitoring from Ubuntu 12.04 LTS, due to differences in Python versions.

This Opspack has been tested on MS SQL Server 2008 R2 running on Windows Server 2008 R2.

Network Dependencies

TCP port 1433 needs to be open from the Opsview Monitor server to connect to the monitored host. For more details, see the Microsoft document: http://support.microsoft.com/kb/287932

Configuration

In Opsview Monitor, add your Microsoft SQL Database as a Host and apply the Host Template 'Database - Microsoft SQL' to that host.

Creating a monitoring user

  1. Create a new 'SQL Authentication user' on the server you are monitoring by logging into the 'Microsoft SQL Server Management Studio', and navigating to 'localhost > Security > Logins' and right clicking on Logins and selecting 'New Login \x{00E2} ** \x{0080} ** \x{00A6} '.
  2. Populate the sections as below:

GENERAL**: create a new login name (username), and change to 'SQL Server Authentication'. Enter a password (must be complex, e.g. numbers and upper case characters) and then remove 'Enforce password expiration and 'user must change password at new login'. SERVER ROLES**: Select 'public' and 'sysadmin'.
USER MAPPING**: Leave as default. SECURABLES**: Click 'Search..', select 'All objects of the types..', and then 'Servers' and click OK. Then scroll down the lists to find the permission titled 'View server state', and check 'Grant'.
STATUS**:** Ensure 'Grant' and 'Enabled' are checked respectively.

Variables

Add the MSSQLCREDENTIALS Variable to the host (the value here can be anything). Set arg1 to be the username of the database (e.g., sa) and arg2 to be the password.

We recommend that you do not use the sa user and instead create a specific user for monitoring. See Microsoft's document at: http://msdn.microsoft.com/en-us/library/aa337545.aspx.

Try to avoid using passwords that contain the exclamation mark (!), as Nagios uses this as a special argument separator.

You will also need to set WINCREDENTIALS. Set arg1 to be the username of the server, eg. Administrator or DOMAIN\User. Set arg2 to be the password.

You will need to set MSSQLDATABASE for each database you want to monitor.

Troubleshooting

If we see an error such as:

CRITICAL:Unable to access SQL Server, then you should check that the credentials you have specified are those for MSSQL (e.g. sa), and not the Windows credentials (e.g. Administrator).

Database - Microsoft SQL

An overview of the Microsoft SQL Opspack for Opsview Monitor