This article allows you to fix connection issues to Microsoft SQL Server when using a MSSQL plugin.

Make sure you are connected to your poller using centreon-engine user and paste the Centreon command from the web interface.

Symptom

You get this typical error message:

wcentreon-engine@POLLER ~]$ /usr/lib/centreon/plugins//centreon_mssql.pl --plugin database::mssql::plugin --hostname=SERVER_HOSTNAME --database='MY_MSSQL_DATABASE' --port 1433 --username 'User' --password '***'  --mode='connection-time'  --warning='1000' --critical='5000' --verbose

CRITICAL: Cannot connect: OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (44)

Server , database

Message String: Server name not found in configuration files.

OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (41)

Server , database

Message String: Unable to connect: Adaptive Server is unavailable or does not exist

OpenClient message: LAYER = (0) ORIGIN = (0) SEVERITY = (78) NUMBER = (41)

Server , database

Message String: Unable to connect: Adaptive Server is unavailable or does not exist

Verifications

Make sure you are using the good credentials first.

Test the connection with tsql

tsql is a FreeTDS diagnostic tool.

It uses the TDS protocol to connect to a Sybase or Microsoft SQL Server, and lets the user issue queries. tsql does not use the FreeTDS client libraries. It uses instead only the lowest level library, libtds, to test the protocol implementation.

tsql -H <IP_Server> -p <Port> -U <User> -P <Password>

Example of success:

locale is "fr_FR.UTF-8"

locale charset is "UTF-8"

using default charset "UTF-8"

1 >

Let's try it:

mcentreon-engine@POLLER ~]$ tsql -S SERVER_HOSTNAME -H MY_MSSQL_DATABASE -p 1433 -U <User> -P "***"

locale is "en_US.UTF-8"

locale charset is "UTF-8"

using default charset "UTF-8"

Error 20009 (severity 9):

        Unable to connect: Adaptive Server is unavailable or does not exist

        OS error 111, "Connection refused"

Error 20009 (severity 9):

        Unable to connect: Adaptive Server is unavailable or does not exist

        OS error 111, "Connection refused"

There was a problem connecting to the server

The result isn't what we were expecting so the problem is elsewhere.

Retrieve the listening port of an MSSQL server

By default the listening port of an MSSQL server is 1433, some servers use a different dynamic port. For Microsoft this is not necessarily a good idea. However, the TCP/IP architecture permits a port scanner to query for open ports, so changing the port number is not considered a robust security measure.

To configure a port as dynamic or to reset the default port, see the following Microsoft procedure:

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port?view=sql-server-2017

Display the listening port of a MSSQL server:

     ServerName SRV-PARIS

   InstanceName SHAREPOINT2013

    IsClustered No

        Version 10.50.4000.0

            tcp 1435



     ServerName SRV-PARIS

   InstanceName K2

    IsClustered No

        Version 10.50.4000.0

            tcp 51155

Problem

You are using the wrong MSSQL port in your Centreon command: 1433 instead of 1435. 

Solution

The command must include the good port as followed:

/usr/lib/centreon/plugins//centreon_mssql.pl --plugin database::mssql::plugin --hostname=SERVER_HOSTNAME --database='MY_MSSQL_DATABASE' --port 1435 --username 'User' --password '***'  --mode='connection-time'  --warning='1000' --critical='5000' --verbose