ODBC hell when trying to monitor MS SQL server

[StefTresMan]

Hi all,

Trying to monitor our SQL server. Not working

I have installed the ODBC driver 18 for SQL Server on ubuntu 20.04, Zabbix version 6.0

/etc/odbcinst.ini:

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
UsageCount=1

/etc/odbc.ini

[db01]
Driver = ODBC Driver 18 for SQL Server
Server = tcp:192.168.117.13,1433

when i launch

isql -v db01 user pass

I get this error:

[S1T00][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired
[08001][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2AFA
[08001][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
[ISQL]ERROR: Could not SQLConnect

Server is up and running, IP is right.

I've learned that driver v18 has mandatory encryption, but no further information available on Doctor Google, maybe something to do with that?
isql doesn't connect, but zabbix gives an other error:

Cannot connect to ODBC DSN: [SQL_ERROR]:[08001][-1][[unixODBC][Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:c]|[08001][-1][[unixODBC][Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection

Zabbix server config file has collectors started:

StartODBCPollers=5

Does anyone have an idea?

[StefTresMan]

Solved. Reverted to ODBC driver version 17 in stead of 18 and it started collecting metrics...

Seems like ODBC driver V18 is somehow not compatible with Zabbix.

[vladimir_lv]

Hi!
check the SELinux status on your server.

[StefTresMan]

Hi Vladimir,

root@zabbix60:~# sestatus
SELinux status: disabled

Any ideas? could it be that i'm running ODBC driver version 18?

[vladimir_lv]

Make test if the TCP port 1433 on the host 10.44.192.6 is open:

curl -v telnet://192.168.117.13:1433

[StefTresMan]

Hi Vladimir,
Thanks for walking me through this. I get an error that the port is closed - connection refused. Still, when i go netstat -na on the SQL server, the port is open and listening, and we have quite a bunch of applications that connect to this server on this port. Also, firewall on he SQL host is disabled, so any device should be able to connect.
On the Zabbix host no firewall is set-up, so it's quite straightforward.

Like: it SHOULD connect but it can't.
When i open Putty on my own computer and open a raw socket to the SQL host on port 1433 it connects.

[vladimir_lv]

Try without curl:
telnet 192.168.117.13 1433
Are you getting the same error?

[Markku]

Hi Vladimir,
Thanks for walking me through this. I get an error that the port is closed - connection refused. Still, when i go netstat -na on the SQL server, the port is open and listening, and we have quite a bunch of applications that connect to this server on this port. Also, firewall on he SQL host is disabled, so any device should be able to connect.
On the Zabbix host no firewall is set-up, so it's quite straightforward.

Like: it SHOULD connect but it can't.
When i open Putty on my own computer and open a raw socket to the SQL host on port 1433 it connects.

You haven't said anything about your network topology, but based on your problem description there is a network firewall that blocks the connection between Zabbix host and SQL server.

Markku

[StefTresMan]

Hi Markku,

There is no firewall between Zabbix host and DB server, they are on the same subnet and on the same switches.
Zabbix agent can communicate perfectly with the Windows host.
Other machines on the network also communicate with the database on this port.

[StefTresMan]

Hi Vladimir,

Telnet to that port works, i can connect to the host.

[vladimir_lv]

Hi!

Check if instance name is correct and if SQL Server is configured to allow remote connections

Does your server is configured to allow remote connections?

[StefTresMan]

Hi Vladimir,

Yes, the server accepts remote connections. I went over the user configuration again for the zabbix user that connects, and I 'may' have found a small issue. I've sent the needed rights to our DBA, it might be that he's forgotten a step - will investigate further.
Thanks for your time and patience.

[StefTresMan]

Solved. Reverted to ODBC driver version 17 in stead of 18 and it started collecting metrics...

Seems like ODBC driver V18 is somehow not compatible with Zabbix.

[La Su]

Hi all,

Trying to monitor our SQL server. Not working

I have installed the ODBC driver 18 for SQL Server on ubuntu 20.04, Zabbix version 6.0

/etc/odbcinst.ini:

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
UsageCount=1

/etc/odbc.ini

[db01]
Driver = ODBC Driver 18 for SQL Server
Server = tcp:192.168.117.13,1433

when i launch

isql -v db01 user pass

I get this error:

[S1T00][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]Login timeout expired
[08001][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x2AFA
[08001][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
[ISQL]ERROR: Could not SQLConnect

Server is up and running, IP is right.

I've learned that driver v18 has mandatory encryption, but no further information available on Doctor Google, maybe something to do with that?
isql doesn't connect, but zabbix gives an other error:

Cannot connect to ODBC DSN: [SQL_ERROR]:[08001][-1][[unixODBC][Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:c]|[08001][-1][[unixODBC][Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection

Zabbix server config file has collectors started:

StartODBCPollers=5

Does anyone have an idea?

>>> I have the same problem.
>>> I removed ODBC Driver 18 for SQL Server and installed ODBC Driver 17 for SQL Server, but still not successful. how was your odbc.ini file and your macros on the zabbix host? Thank you!

My ODBC.ini:

[db01]
#Driver = ODBC Driver 18 for SQL Server
Driver = ODBC Driver 17 for SQL Server
Server = tcp:10.3.100.113,1433

My Macros in host:





Response:

isql -v db01 zbx_monitor1 ********
[S1T00][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]Login timeout expired
[08001][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x102
[08001][unixODBC][Microsoft][ODBC Driver 17 for SQL Server]A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.
[ISQL]ERROR: Could not SQLConnect

[StefTresMan]

Hi La Su,

Have you started the pollers in the zabbix config file?

I only use 3 macro's. Is your SQL server really running a seperate instance? because i would think if you run more than one instance they would be using a different port?
If it's just a single instance of SQL running on one server, you don't need the {$MSSQL_INSTANCE} macro. and IP and port are given in the ODBC.ini file, so skip that also. My setup works with these 3 macro's:

{$MSSQL.DSN}
{$MSSQL.PASSWORD}
{$MSSQL.USER}

[nagaraj]

Hi, La Su

Just make sure below config are done and your windows firewall is allowed 1433 port or disable it.