MSSQL Server wizards not working
MSSQL Server wizards not working
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
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
Re: MSSQL Server wizards not working
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.
If you get output like this...
There is a network/firewall/Windows firewall issue.
If you get output like this...
Try running the MSSQL plugin.
If you get a response like this...
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.
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)
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
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
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).
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;;
Please let us know if you have any other questions or concerns.
-Laura
-Laura
Re: MSSQL Server wizards not working
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
[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
Re: MSSQL Server wizards not working
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.
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.
- Created a new SQL Server user/login (in my case: xi_sql_user) with SQL Server authentication. (You can also use windows authentication)
- Changed the SQL Server's Security to SQL Server and Windows Authentication mode (so the new user can log in).
- Verified that xi_sql_user can login to the SQL Server.
- Used this PowerShell command to allow pinging. You can also add it through the Firewall GUI.
(Ping from the XI server started working immediately.)
Code: Select all
New-NetFirewallRule -DisplayName “ICMPv4” -Direction Inbound -Action Allow -Protocol icmpv4 -Enabled True
. - Used these PowerShell commands to allow traffic to and from SQL Server
It should look like this in Windows Defender. .
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
- Enable TCP/IP in the SQL Server Configuration Manager
You do not have the required permissions to view the files attached to this post.
Please let us know if you have any other questions or concerns.
-Laura
-Laura
Re: MSSQL Server wizards not working
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.
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.
Re: MSSQL Server wizards not working
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?
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?
Please let us know if you have any other questions or concerns.
-Laura
-Laura
Re: MSSQL Server wizards not working
Hello,lgute wrote: ↑Fri Sep 15, 2023 10:40 am 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?
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?
Re: MSSQL Server wizards not working
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.
Wizards are good for helping, but not good to define a practical service.
Re: MSSQL Server wizards not working
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!
Re: MSSQL Server wizards not working
It isn't the wizar's fault.