MSSQL via Agent 2 doesn't work

[db_owner]

"The TCP Port of SQL Server service is currently unavailable."

Host:
Debian 12
Zabbix 6.4.13
Agent 2 is installed.
Plug in is installed.
Template is installed.

Macros:
User: user1
Password: somepass293283298
URI: sqlserver://sql1.example.com

Server:
SQL Server 2019 on Windows Server 2022

zabbix_agent2.log

2024/04/22 15:09:37.672483 [101] no active checks on server [127.0.0.1:10051]: host [Zabbix server] not found
2024/04/22 15:09:43.673320 [101] no active checks on server [127.0.0.1:10051]: host [Zabbix server] not found
2024/04/22 15:09:49.673167 [101] no active checks on server [127.0.0.1:10051]: host [Zabbix server] not found
2024/04/22 15:09:55.673138 [101] no active checks on server [127.0.0.1:10051]: host [Zabbix server] not found

zabbix_server.log

491541:20240422:151149.672 cannot send list of active checks to "127.0.0.1": host [Zabbix server] not found
491541:20240422:151155.673 cannot send list of active checks to "127.0.0.1": host [Zabbix server] not found
491541:20240422:151201.675 cannot send list of active checks to "127.0.0.1": host [Zabbix server] not found
491540:20240422:151207.672 cannot send list of active checks to "127.0.0.1": host [Zabbix server] not found​

Zabbix host can telnet to the SQL server on 1433.
user1 can login with the set password via SSMS.

[cyber]

First, configure your agent to connect to server. It currently wants to connect to localhost as "Zabbix server". If this agent IS on your server, then you have no host in your Zabbix called "Zabbix server".

[db_owner]

cyber, thanks! That helped a little. I now have data for some items. But I still get the same error that the TCP port is unavailable.

The agent2.log is now empty.

The server log shows the following:

508358:20240423:083501.055 error reason for "sqlserv.example.com:mssql.cache_hit_ratio" changed: Preprocessing failed for: [{"object_name":"SQLServer:Plan Cache","cntr_value":3497,"counter_name":"Cache Hit Ratio","instan...
1. Failed: cannot extract value from json by path "$[?(@.counter_name=='CacheHitRatio')].cntr_value.first()": no data matches the specified path

Doesn't seem relevant.

[db_owner]

This appears to be a bug in the Template. It's using HOST.CONN which obviously won't work when using agent2 to monitor a remote server since it resolves to the Zabbix host's 127.0.0.1 instead of the SQL server.

I've posted a patch request with a solution here, in case someone needs it:

https://support.zabbix.com/browse/ZBX-24390

[ccekli]

I was searching about MSSQL connection on Zabbix and it is really annoying. Official document is not helped me. (Setup is; MSSQL 2019 , Zabbix 6.1.4)

I created user on DB with full permission. Agent v1 v2 tried, ODBC connection tried too. But nothing comes to zabbix. Says always Zabbix has not received any data for items for the last 30 minutes.

Did you see same error ? If yes could you tell how solved ?

[leckbush]

For anyone, having this issue. I solved this by getting the Zabbix Agent 2 for the Windows server and installing the Zabbix Agent Plugin Installer. Download the link below but can get it on the website.

Zabbix Agent: https://cdn.zabbix.com/zabbix/binari...64-openssl.msi
Zabbix Agent Plugin: https://cdn.zabbix.com/zabbix/binari...dows-amd64.msi

If somehow the version update, just download the agent as normal like this and agent installer should appear:


For the installer of the plugin, you need to select no encryption then msi for packaging so installer should appear for download:


Follow the documentation as instructed, once that setup, you need to get the plugin configuration file of MSSQL to the directory of the Zabbix Agent. Zabbix Agent and Zabbix Agent plugin have two different installation directory on the windows.



You need to get the mssql.conf from the "Zabbix Agent2 Plugins" ( Zabbix Agent 2 Plugins > zabbix_agent2.d >) it should be on the sub folder.


Copy that mssql.conf to the Zabbix Agent installation directory and paste it under ( Zabbix Agent 2 > zabbix_agent2.d > plugins.d )

Once you paste that there, restart the zabbix agent on the windows services and check the zabbix server to verify. You should see the data coming in now.