Problem with MS SQL template (ODBC) and frontend configuration

[SzymonUnion]

Hi Everyone,

this is my 1st post and 1st problem I met with Zabbix. I read a lot of posts on this community and some other posts over Internet, but I am stuck now.
I have RedHat server with Zabbix 5.0.2 and I would like to monitor some MSSQL perfs and other useful items.

I installed unixODBC:

[root@XXX ~]# odbcinst -j
unixODBC 2.3.7
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

[root@XXX ~]# odbcinst -q -d -n
[ODBC Driver 17 for SQL Server]

[root@XXX ~]# cat /etc/odbcinst.ini
[ODBC Driver 17 for SQL Server]
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.6.so.1.1
UsageCount=1

[root@XXX ~]# cat /etc/odbc.ini
[XXXPROD01]
Driver = ODBC Driver 17 for SQL Server
Server = aa.bb.cc.dd\\\XXXPROD01,1433

ISQL -v works (from root and also via sudo -H -u zabbix -s /bin/bash -c 'isql -v XXXPROD01 aaaaa bbbbb')
SQLcmd works


In frontend in Configuration/Templates/Template DB MSSQL by ODBC I entered {$MSSQL.DSN} (the same as entry in odbc.ini), {$MSSQL.USER} and {$MSSQL.PASSWORD}.

I am trying to test the most simple item so I mean MSSQL: Service's TCP port state with key -> net.tcp.service[tcp,{HOST.CONN},{$MSSQL.PORT}]
When I am clicking TEST I have to enter Host address (XXXPROD01) and in $MACROS {HOST.CONN} ? I am attaching screenshot with error.

Should I change sthg? Add sthg in configuration? Did I miss sthg ?

Maybe I do not understand sthg.

Thanks in advance for help!

Regards,
Simon

[piotrl]

Hi Szymon

the item you try to test do not use odbc, it's just simple network test to check if sql port is reachable. In test form you just need to enter servername or ip address in host address field. If you want to test ODBC try this item MSSQL: Get performance counters

[SzymonUnion]

Hi Szymon

the item you try to test do not use odbc, it's just simple network test to check if sql port is reachable. In test form you just need to enter servername or ip address in host address field. If you want to test ODBC try this item MSSQL: Get performance counters

Hi Piotr,

thanks for response. I tried both cases and two of them are not working as expected. As I wrote - ODBC connection is working through isql and sqlcmd, but it is not working when I am testing through template as I showed on my screenshot.

[piotrl]

Just like I wrote if you test item MSSQL: Service's TCP port state it doesn't use odbc at all so It doesn't matter is odbc connection working or not.

You should check it as below (type only server name - not name of odbc connection)



If you want to check odbc try MSSQL: Get performance counters as below.


{$MSSQL.DSN} -> name of odbc connection. In your case XXXPROD01
{$MSSQL.PASSWORD} -> password for sql user
{$MSSQL.USER} -> login of sql user

[SzymonUnion]

Piotr,

I know that it is easy, but when I tried to test item (simple check type) I stil have the same info:
Connection timeout of 3 seconds exceeded when connecting to Zabbix server "localhost".

I searched already forum for this error, but I found nothing interesting.

EDIT: seems be after reading other sources that it can be problem with zabbix configuration. I checked hostname option for passive checks and active checks in configs. Also checked /etc/hosts. Something is wrong here regarding my error with localhost.

Regards,

[piotrl]

Yeah that error with localhost is strange.

Did you try that?

https://www.zabbix.com/forum/zabbix-troubleshooting-and-problems/49606-connection-timeout-of-3-seconds-exceeded-when-connecting-to-zabbix-server-localhost

[SzymonUnion]

Piotr,
link You provided is not containing useful info for my problem unfortunately. As I wrote before I read almost everything about it on Zabbix forum. Problem is locally with zabbix server I was not responsible for before. It is rather easy-peasy: /etc/hosts or zabbix_server.conf. Somewhere where 'localhost' is used.

EDIT: Changing debuglevel to '4' - we will see.

EDIT part 2: Works simple check - problem was with selinux after few restarts of zabbix-server. Now it is better. Time to check odbc.

[SzymonUnion]

Piotr,

ODBC is not working. I tried the simplest:

db.odbc.discovery[dbname,"{$MSSQL.DSN}"]

It is freezing as showed on new attached screenshot
.
EDIT - SOLVED - capital letters in SQL query helped Very strange, but works fine!