Do we need to install Pre-requisites for monitoring MS SQL

[MSPk]

Hi,

I think we need to be running the plugin check_mssql_server.py as we are interested in monitoring the instance/port but n0t the individual databases.

Now, the issue is when we run the above plugin using the command ./check_mssql_server.py we get the below output.

Code: Select all

usage: check_mssql_server.py -H hostname -U user -P password -T table --mode

check_mssql_server.py: error: Hostname is a required option.
Caught unexpected error. This could be caused by your sysperfinfo not containing the proper entries for this query, and you may delete this service check.

when we run the command ./check_mssql_server.py -h we get the below output

Code: Select all

usage: check_mssql_server.py -H hostname -U user -P password -T table --mode

options:
  -h, --help            show this help message and exit

  Required Options:
    -H HOSTNAME, --hostname=HOSTNAME
                        Specify MSSQL Server Address
    -U USER, --user=USER
                        Specify MSSQL User Name
    -P PASSWORD, --password=PASSWORD
                        Specify MSSQL Password

  Optional Connection Information:
    -I INSTANCE, --instance=INSTANCE
                        Specify instance
    -p PORT, --port=PORT
                        Specify port.

  Nagios Plugin Information:
    -w WARNING, --warning=WARNING
                        Specify warning range.
    -c CRITICAL, --critical=CRITICAL
                        Specify critical range.

  Mode Options:
    --pagelooks         Page Lookups Per Second
    --batchreq          Batch Requests / Sec
    --lockwaits         Lockwaits / Sec
    --pagereads         Page Reads / Sec
    --totalpages        Total Pages (Cumulative)
    --readahead         Readahead Pages / Sec
    --pagewrites        Page Writes / Sec
    --stolenpages       Stolen Pages
    --targetpages       Target Pages
    --fullscans         Full Scans / Sec
    --locktimeouts      Lock Timeouts / Sec
    --test              Run tests of all queries against the database.
    --freepages         Free Pages (Cumulative)
    --bufferhitratio    Buffer Cache Hit Ratio
    --averagewait       Average Wait Time (ms)
    --time2connect      Time to connect to the database.
    --checkpoints       Checkpoint Pages / Sec
    --lockrequests      Lock Requests / Sec
    --pagelife          Page Life Expectancy
    --databasepages     Database Pages
    --deadlocks         Deadlocks / Sec
    --cachehit          Cache Hit Ratio
    --pagesplits        Page Splits / Sec
    --lockwait          Lock Wait Average Time (ms)
    --lazywrites        Lazy Writes / Sec
    --sqlcompilations   SQL Compilations / Sec
Caught unexpected error. This could be caused by your sysperfinfo not containing the proper entries for this query, and you may delete this service check.

whats interesting is the last line in both the outputs 'Caught unexpected error. This could be caused by your sysperfinfo not containing the proper entries for this query, and you may delete this service check', should we be worried about this.

The other question is - from the info I got from my DB team we are dealing from port based DB's, so we have DB's configured on multiple ports on a single server. what should be out command like when we are trying to monitor the port.

I have used the below command and it failed with an error

Code: Select all

./check_mssql_server.py -H ipaddress -U 'userid' -P 'password' -p 1601 --time2connect -w 20 -c 10
SQL Server message 18452, severity 14, state 1, line 1:
Login failed for user 'i2mpadmin'. The user is not associated with a trusted SQL Server connection.
DB-Lib error message 18452, severity 14:
General SQL Server error: Check messages from the SQL Server
DB-Lib error message 20002, severity 9:
Adaptive Server connection failed

PS: the pwd for DB access has a special character '!'

[abrist]

If your password includes any of the nasy meta characters ( '!' included), you will need to make use of the $USERn$ macros in the file: /usr/local/nagios/etc/resource.cfg.
Edit the above file and add a new macro for your password. For example (we will use macro $USER9$):

Code: Select all

$USER9$=pass!word

Save out and restart nagios:

Code: Select all

service nagios restart

You will then reference the in place of the password by replacing your password with $USER9$ in the service check (you may want to wrap $USER9$ in single quotes if you have any spaces in the password).

[MSPk]

We are now using a user which has no meta characters and is a alphanumeric password. I still get the same error message. Can you please check if the config wizard, plugin are fine.

MSSQL config wizard.

configwizard-mssql_server.zip

check_mssql_server.py plugin

check_mssql_server.py

[abrist]

Login failed for user 'i2mpadmin'. The user is not associated with a trusted SQL Server connection.
DB-Lib error message 18452, severity 14:

This error can be related to windows server security/authentication settings. You may want to read through the following links:
http://support.microsoft.com/kb/555332
http://stackoverflow.com/questions/3583 ... sql-server

[MSPk]

My Admin says that this activity was carried out already while the sql servers were created and said that it is not a authentication issue....!!

[slansing]

The error itself indicates otherwise, it is being sent back from the MSSQL server, it is not being generated by Nagios XI. This error can be caused by a few things:

1: The login may be a SQL Server login but the server only accepts Windows Authentication.
2: You are trying to connect by using SQL Server Authentication but the login used does not exist on SQL Server.
3: The login may use Windows Authentication but the login is an unrecognized Windows principal. An unrecognized Windows principal means that Windows can't verify the login. This might be because the Windows login is from an untrusted domain.

Or the credentials you are using are incorrect in some way. These are really the only possibilities.

[MSPk]

I'm not sure if that is the case. I have tested the connection/services using the admin account(used by my admin and has got access to everything on the DB server) and even then I get the same error message. Can I run some kind of test to confirm that the plugin is working fine.

[MSPk]

hi team,
do we have any guide or instruction in place which can help me in writing aruments for userid's, password's with special characters and then use them as arguments in service checks

[sreinhardt]

As abrist mentioned above for nasty characters:

Re: Do we need to install Pre-requisites for monitoring MS S

Unread postby abrist » Thu May 16, 2013 12:41 pm
If your password includes any of the nasy meta characters ( '!' included), you will need to make use of the $USER#$ macros in the file: /usr/local/nagios/etc/resource.cfg.
Edit the above file and add a new macro for your password. For example (we will use macro $USER9$):

Code: Select all
$USER9$=pass!word


Save out and restart nagios:

Code: Select all
service nagios restart


You will then reference the in place of the password by replacing your password with $USER9$ in the service check (you may want to wrap $USER9$ in single quotes if you have any spaces in the password).

As for your authentication issues with regard to the usernames, are these domain users or users with windows authentication that need a domain\user type credential? Are these databases using windows authentication or sql auth? I know it has been asked, but I did not see a direct response to either question. As a side note, sometimes domain\user auth will need to be inputted as domain/user to make the executing plugin happy.

[MSPk]

The database uses windows authentication to provide access to SQL Database. The windows login name is in this format my-domain\userid. we need to use the same id to access the SQL server as well. [/code]

This Wizard/Plugin requires SQL authentication and will not work with Windows Auth.