MSSQL Server wizards not working

[ceme.it]

Hello all, I've a problem with MSSQL wizard: they do not seem to work.
Nagios XI Ver 5.11.1
MS SQL wizard ver. 2.0.6

I tried to add the a server\instance, a the specific db, but nothing, I receive the following error:

ERROR: [run_check] CRITICAL: Could not connect to odbc:Driver=FreeTDS:Server=sqlname.domain.local:Port=1433:dbname=master:charset=UTF8 as sa (Exception: SQLSTATE[08001] SQLDriverConnect: 0 [FreeTDS][SQL Server]Unable to connect to data source)

I do not understand why though, Nagios XI is in a different site, we have created all the policies needed on the firewalls for Nagios to this SQL Server, I tried to ping and telnet on 1433 from Nagios server and it worked, and yet it does not seem able to connect.
I tried to reinstall the wizard to a previous version (current installed 2.0.6 ) with no avail.

Is there anything else I can do?
We were really looking forward to monitor the SQL Servers on Nagios XI..

Thank you all for any help

[lgute]

Hi @ceme.it, thanks for reaching out.

You can run the plugins from the command line, to make it easier to debug.

First make sure the check_ping plugin works.

Code: Select all

/usr/local/nagios/libexec/check_ping -H sqlname.domain.local -w 10,2% -c 20,5%

If you get output like this...

Code: Select all

CRITICAL - Host Unreachable (sqlname.domain.local)

There is a network/firewall/Windows firewall issue.

If you get output like this...

Code: Select all

PING OK - Packet loss = 0%, RTA = 0.08 ms|rta=0.078000ms;10.000000;20.000000;0.000000 pl=0%;2;5;0

Try running the MSSQL plugin.

Code: Select all

/usr/local/nagios/libexec/check_mssql_server.php -H sqlname.domain.local --checktype 'server' -U 'sa' -P 'your_password' --perftype default -p 1433 --mode autoparamattempts --warning 100 --critical 200

If you get a response like this...

Code: Select all

ERROR: [run_check] CRITICAL: Could not connect to odbc:Driver=FreeTDS;Server=192.168.255.251;Port=1433;dbname=master;charset=UTF8 as xi_sql_user (Exception: SQLSTATE[08S01] SQLDriverConnect: 20009 [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist)
CRITICAL: Could not connect to odbc:Driver=FreeTDS;Server=192.168.255.251;Port=1433;dbname=master;charset=UTF8 as xi_sql_user (Exception: SQLSTATE[08S01] SQLDriverConnect: 20009 [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist).

There is likely a SQLServer configuration issue. For instance, is the TCP/IP protocol Enabled in the SQL Server Network Configuration | Protocols for MSSQLSERVER | TCP/IP?

You should get a response like this, if the XI server can connect to your SQL Server.

Code: Select all

OK: Auto-Param Attempts/sec is 0.008914/sec|autoparam_attempts=0.008914;100;200;;

[ceme.it]

Hi lgute, I did tht tests you suggested, below the results:

[TEST]
/usr/local/nagios/libexec/check_ping -H sqlname.domain.local -w 10,2% -c 20,5%
[RESULT]
PING OK - Packet loss = 0%, RTA = 4.25 ms|rta=4.247000ms;10.000000;20.000000;0.000000 pl=0%;2;5;0

[TEST]
/usr/local/nagios/libexec/check_mssql_server.php -H sqlname.domain.local --checktype 'server' -U 'sa' -P 'your_password' --perftype default -p 1433 --mode autoparamattempts --warning 100 --critical 200
[RESULT]
ERROR: [run_check] CRITICAL: Could not connect to odbc:Driver=FreeTDS;Server=cemesql01.cemegroup.local;Port=1433;dbname=master;charset=UTF8 as sa (Exception: SQLSTATE[08001] SQLDriverConnect: 0 [FreeTDS][SQL Server]Unable to connect to data source)
CRITICAL: Could not connect to odbc:Driver=FreeTDS;Server=cemesql01.cemegroup.local;Port=1433;dbname=master;charset=UTF8 as sa (Exception: SQLSTATE[08001] SQLDriverConnect: 0 [FreeTDS][SQL Server]Unable to connect to data source).

I've checked the sql network settings, the only thing down are the named pipes, I'm checking if and when I can enable them without impacting our business too much.
Could it be that the cause?

Thanks

[lgute]

Hi @ceme.it,

I setup a new Basic install SQL Server and these are the steps I needed to complete, to be able to connect from an XI Server. I also installed the SQL Server Management Studio, so to add a new user and test connectivity to SQL Server, with the new user. I am not a network administrator or Sys Admin, so this is a very basic setup and your environment may require more complicated rules.

1. Created a new SQL Server user/login (in my case: xi_sql_user) with SQL Server authentication. (You can also use windows authentication)
2. Changed the SQL Server's Security to SQL Server and Windows Authentication mode (so the new user can log in).
   SqlServerMS-Server-Security.png
3. Verified that xi_sql_user can login to the SQL Server.
4. Used this PowerShell command to allow pinging. You can also add it through the Firewall GUI.

   Code: Select all

   New-NetFirewallRule -DisplayName “ICMPv4” -Direction Inbound -Action Allow -Protocol icmpv4 -Enabled True
   

   (Ping from the XI server started working immediately.)
   .
5. Used these PowerShell commands to allow traffic to and from SQL Server

   Code: Select all

   New-NetFirewallRule -DisplayName "SQLServer default instance" -Direction Inbound -LocalPort 1433 -Protocol TCP -Action Allow
   New-NetFirewallRule -DisplayName "SQLServer Browser service" -Direction Inbound -LocalPort 1434 -Protocol UDP -Action Allow
   

   It should look like this in Windows Defender.
   MSSQLServerWindowsDefender.png
   .
6. Enable TCP/IP in the SQL Server Configuration Manager
   MSSQLServerConfigMngr.png

I hope that helps.

[ceme.it]

Hello, thank you for the reply.
I've tried on another SQL server in command line, but I receive the following error:

[root@ITADRSRV202 libexec]# ./check_mssql_server.php --checktype server -H XXX -U sa -P YYY --mode test -w 80 -c 90

ERROR: [run_check] CRITICAL: Could not connect to odbc:Driver=FreeTDS;Server=XXX;Port=1433;dbname=master;charset=UTF8 as sa (Exception: SQLSTATE[08S01] SQLDriverConnect: 20009 [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist)
CRITICAL: Could not connect to odbc:Driver=FreeTDS;Server=XXX;Port=1433;dbname=master;charset=UTF8 as sa (Exception: SQLSTATE[08S01] SQLDriverConnect: 20009 [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist).

I'm using the "sa" user, so it should be able to log in.

I don't get why it does not connect, I'm using the php plugin and the py plugin, both in error.

[lgute]

Hi @ceme.it,

The only way I can duplicate you current error message, is to disable the SQL Server firewall rules for SQL Server. There is something between your XI server and SQL Server, that is blocking traffic. Perhaps something else is running on that port?

[ceme.it]

Hi @ceme.it,

The only way I can duplicate you current error message, is to disable the SQL Server firewall rules for SQL Server. There is something between your XI server and SQL Server, that is blocking traffic. Perhaps something else is running on that port?

Hello,
I've tried on different servers and I receive different errors.
It worked on a SQLEXPRESS2017, it is not working on a SQLEXPRESS2019 even though I select SQL 2019 on the wizard.
Any advice? Maybe I should not specify the 2019 version in the wizard?

[kg2857]

Stop using the wizard and use your brain. Run the commands manually until the work, then define a service.
Wizards are good for helping, but not good to define a practical service.

[ceme.it]

Stop using the wizard and use your brain. Run the commands manually until the work, then define a service.
Wizards are good for helping, but not good to define a practical service.

FYI you should use your eyes and read my second post: I used the command line and they didn't work, I even posted the result!

[kg2857]

It isn't the wizar's fault.