ZABBIX Integration Issue with MSSQL Server via MS ODBC for Ubuntu

[La Su]

Information:

Integration with MSSQL by ODBC DB Template

https://www.zabbix.com/br/integrations/mssql

ubuntu 20.04 in Virtual BOX Mode Bridge

Microsoft ODBC Driver for SQL Server (Linux - Ubuntu) - ms odbc 18

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15

zabbix 6.0 LTS

MSSQL server 2019 developer

I'm not able to get data from the DB in ZABBIX using the Template DB MSSQL by ODBC in my test environment and I can't find where I'm going wrong.

1 - I created the user zbx_monitor1 in MSSQL for Monitoring via graphical interface in MS SQL Server Management Studio.
2 - I gave Read (db_datareader) and Login permissions to the master, msbd and tempdb databases via GUI and other necessary permissions.

USE master;
GRANT VIEW SERVER STATE
,VIEW ANY DEFINITION
TO zbx_monitor1;
GO

use msdb;
GRANT SELECT ON OBJECT::msdb.dbo.sysjobs TO zbx_monitor1
GRANT SELECT ON OBJECT::msdb.dbo.sysjobsersers TO zbx_monitor1
GRANT SELECT ON OBJECT::msdb.dbo.sysjobactivity TO zbx_monitor1
GRANT EXECUTE ON OBJECT::msdb.dbo.agent_datetime TO zbx_monitor1

3 - I tested the login.

4 - I tested the read access to the tables with the user zbx_monitor1.

SELECT TOP (1000) *
FROM msdb.dbo.sysjobs

SELECT TOP (1000) *
FROM msdb.dbo.sysjobsersers

SELECT TOP (1000) *
FROM msdb.dbo.sysjobactivity

SELECT TOP (1000) *
FROM msdb.dbo.agent_datetime


5 - I installed the Microsoft ODBC Driver 18 on Ubuntu Server 20.04 according to the official website.


if ! [[ "18.04 20.04 21.04" == *"$(lsb_release -rs)"* ]];
then
echo "Ubuntu $(lsb_release -rs) is not currently supported.";
exit;
fi

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit
sudo apt-get update

sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18

Note: I also tested with msodbcsql17.

# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools18

echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc

source ~/.bashrc

# optional: for unixODBC development headers

sudo apt-get install -y unixodbc-dev

6 - I created the odbc.ini in /etc/odbc.ini
Note: I tested several configurations.

[D-TI-01\MSSQLSERVER01] #DSN: device name\MS SQL server name
#[MSSQLSERVER01]
#Driver = ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
[HASHTAG="t988"]description[/HASHTAG] = 'Attempt number 342343'
#Trace = yes
#TraceFile = /tmp/odbc_sqlserver.log
Server = tcp:xx.x.xxx.xx,1433
[HASHTAG="t221"]server[/HASHTAG] = xx.x.xxx.xx
[HASHTAG="t1852"]port[/HASHTAG] = 1433
[HASHTAG="t479"]user[/HASHTAG] = zbx_monitor1
[HASHTAG="t2541"]password[/HASHTAG] = xxxxxxxxx
[HASHTAG="t245"]database[/HASHTAG] = msdb
TrustServerCertificate=YES
Encrypt=NO

7 - Include the macros on the host in Zabbix 6.0 LTS
Note: I tested several configurations.

8 - I performed ISQL test

odbcinst -q -s
[D-TI-01\MSSQLSERVER01]


root@ubusrvzbx01:/etc# odbcinst -q -d
[ODBC Driver 18 for SQL Server]


root@ubusrvzbx01:~# 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


isql D-TI-01\MSSQLSERVER01 zbx_monitor1 xxxxxx
[ISQL]ERROR: Could not SQLConnect


isql D-TI-01\MSSQLSERVER01 'zbx_monitor1' 'xxxxxxx'
[ISQL]ERROR: Could not SQLConnect


isql -v "D-TI-01\MSSQLSERVER01" 'zbx_monitor1' 'zbx_monitor1'
[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 0x102
[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

Clearly ODBC is not communicating with SQL Server, so I cannot identify the reason.
Sou relatively new not Zabbix. Sorry my English
Thank you for help






Problem: Database data is not being monitored. Any suggestion?

[vladimir_lv]

Hi!
Test the connection with ISQL (from Zabbix server):

# isql -vvv <YOUR_DSN> <user> <password>

Make sure that the command work under user zabbix.

[La Su]

Hi!
Test the connection with ISQL (from Zabbix server):

# isql -vvv <YOUR_DSN> <user> <password>

Make sure that the command work under user zabbix.

>>>>
Unfortunately, I have tried this command several times and in different ways and I have not been successful. I changed the DSN, followed the instruction of a colleague on the forum to use the ODBC Driver 17 for SQL Server version and it didn't work either

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


My current odbc.ini:

[db01]
#Driver = ODBC Driver 18 for SQL Server
Driver = ODBC Driver 17 for SQL Server
#Driver = /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
[HASHTAG="t988"]description[/HASHTAG] = 'Attempt number 342343'
#Trace = yes
#TraceFile = /tmp/odbc_sqlserver.log
Server = tcp:10.3.100.100,1433
[HASHTAG="t1852"]port[/HASHTAG] = 1433
[HASHTAG="t479"]user[/HASHTAG] = zbx_monitor1
[HASHTAG="t2541"]password[/HASHTAG] = *********
[HASHTAG="t245"]database[/HASHTAG] = msdb
#TrustServerCertificate=YES
#Encrypt=NO

[La Su]

Problem solved, below summary of what was accomplished.

Integration with MSSQL by ODBC DB Template

https://www.zabbix.com/br/integrations/mssql

-Ubuntu 20.04 in Virtual BOX Mode Bridge
-Microsoft ODBC Driver for SQL Server (Linux - Ubuntu) - ms odbc 17
-zabbix 6.0 LTS
-MSSQL server 2019 developer

0 - Check in Microsoft Configuration Manager if the port is static.
>> SQL Server Network Configuration
>> Protocol for MSSQLSERVER01
>>TCP/IP
>>IP Addresses tab
>> Remove 0 from dynamic IPs
>> Include the static port that will be used ex 1433
>> Enable TCP/IP
>>Windows Services
>>Restart MSSQLSERVER01 Server

1 - Check MSSQL Server is allowing remote connection.
>>Log in to MSSQLSERVER01
>>Right click on MSSQLSERVER01 Server
>>Properties
>> connections
>>Enable Allow remote connections to this server.
>>OK

2 - Create the user zbx_monitor1 in MSSQLSERVER01 for Monitoring via graphical interface in MS SQL Server Management Studio.
>>Security
>>login
>>SQL SERVER Authentication
>>Login: zbx_monitor1
>>Password: **********


3 - Give Read (db_datareader) and Login permissions to the master, msbd and tempdb system databases via GUI and other necessary permissions.

USE master;
GRANT VIEW SERVER STATE
,VIEW ANY DEFINITION
TO zbx_monitor1;
GO

use msdb;
GRANT SELECT ON OBJECT::msdb.dbo.sysjobs TO zbx_monitor1
GRANT SELECT ON OBJECT::msdb.dbo.sysjobsersers TO zbx_monitor1
GRANT SELECT ON OBJECT::msdb.dbo.sysjobactivity TO zbx_monitor1
GRANT EXECUTE ON OBJECT::msdb.dbo.agent_datetime TO zbx_monitor1

4 - Release the MSSQL Server Agent from MSSQLSERVER01 to user zbx_monitor1.
>>MSSQLSERVER01
>>Security
>>login
>>zbx_monitor1
>>User Mapping
>>Database: msdb
>> Database role binding: msdb , enable:
IMPORTANT NOTE: I ENABLE ALL OF THEM FOR TESTING HOWEVER I BELIEVE THAT IT IS NOT NECESSARY TO ENABLE ALL OF THEM.
>>SQLAgentOperatorRole
>>SQLAgentReaderRole
>>SQLAgentUserRole

4 - Test the connection on MSSQLSERVER01 using the login zbx_monitor1.

5 - Test read access to tables with user zbx_monitor1.

SELECT TOP (1000) *
FROM msdb.dbo.sysjobs

SELECT TOP (1000) *
FROM msdb.dbo.sysjobsersers

SELECT TOP (1000) *
FROM msdb.dbo.sysjobactivity

SELECT TOP (1000) *
FROM msdb.dbo.agent_datetime


Note: If you have previously installed Microsoft ODBC Driver 18, remove it before installing Microsoft ODBC Driver 17.
sudo apt-get --purge remove msodbcsql18

6 - Install the Microsoft ODBC Driver 17 on Ubuntu Server 20.04 according to the official website.

if ! [[ "16.04 18.04 20.04 21.04 21.10" == *"$(lsb_release -rs)"* ]];
then
echo "Ubuntu $(lsb_release -rs) is not currently supported.";
exit;
fi

sudo su
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -

curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list > /etc/apt/sources.list.d/mssql-release.list

exit
sudo apt-get update

sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17

# optional: for bcp and sqlcmd

sudo ACCEPT_EULA=Y apt-get install -y mssql-tools

echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc

source ~/.bashrc

# optional: for unixODBC development headers
sudo apt-get install -y unixodbc-dev

7 - Create odbc.ini in /etc/odbc.ini

[db01] #DSN
Driver = ODBC Driver 17 for SQL Server
Server = tcp:10.3.100.100,1433
#Driver = /opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.0.so.1.1
[HASHTAG="t988"]description[/HASHTAG] = 'Attempt number 342343'
#Trace = yes
#TraceFile = /tmp/odbc_sqlserver.log
[HASHTAG="t1852"]port[/HASHTAG] = 1433
[HASHTAG="t479"]user[/HASHTAG] = zbx_monitor1
[HASHTAG="t2541"]password[/HASHTAG] = *********
[HASHTAG="t245"]database[/HASHTAG] = msdb
#TrustServerCertificate=YES
#Encrypt=NO

8 - Test if the connection via ODBC Driver is working.

#isql -v <YOUR_DSN> <user> <password>

#isql -v db01 zbx_monitor1 ******

Other useful commands:

#dpkg --list

[HASHTAG="t2773"]sudo[/HASHTAG] systemctl | grep running

[HASHTAG="t2773"]sudo[/HASHTAG] systemctl list-unit-files --type service --all

#cat /etc/odbc.ini

#cat /etc/odbcinst.ini

#odbcinst -q -s
[db01]

#odbcinst -q -d
[ODBC Driver 17 for SQL Server]

#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

#odbcinst -q -d -n "ODBC Driver 17 for SQL Server"
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.1.1
UsageCount=1

[HASHTAG="t2773"]sudo[/HASHTAG] find /usr 2>/dev/null -name "libodbc*"
/usr/lib/x86_64-linux-gnu/libodbccr.so.1
/usr/lib/x86_64-linux-gnu/libodbc.so.2.0.0
/usr/lib/x86_64-linux-gnu/libodbccr.so
/usr/lib/x86_64-linux-gnu/libodbccr.so.2.0.0
/usr/lib/x86_64-linux-gnu/libodbcinst.so.2.0.0
/usr/lib/x86_64-linux-gnu/libodbc.so
/usr/lib/x86_64-linux-gnu/libodbcinst.la
/usr/lib/x86_64-linux-gnu/libodbcinst.so
/usr/lib/x86_64-linux-gnu/libodbccr.so.2
/usr/lib/x86_64-linux-gnu/libodbc.so.2
/usr/lib/x86_64-linux-gnu/libodbccr.la
/usr/lib/x86_64-linux-gnu/libodbc.la
/usr/lib/x86_64-linux-gnu/libodbcinst.so.1
/usr/lib/x86_64-linux-gnu/libodbc.so.1
/usr/lib/x86_64-linux-gnu/libodbcinst.so.2

[HASHTAG="t156"]ping[/HASHTAG] 10.3.100.100 #IPSQLSERVER

[HASHTAG="t783"]telnet[/HASHTAG] 10.3.100.100 1433

9 - Include the macros in the host in Zabbix 6.0 LTS

{$MSSQL.DSN} - db01
{$MSSQL.INSTANCE} - MSSQL$MSSQLSERVER01
{$MSSQL.PASSWORD} - **********
{$MSSQL.PORT} - 1433
{$MSSQL.USER} - zbx_monitor1

10 - Check the MSSQL by ODBC template items if the data is being monitored.

[JulioQc]

Had same issue after upgrading underlying Ubuntu from 22.04 to 24.04. Turns out the user odbc.ini (/home/zabbix/.odbc.ini) was superseding the global one I actually keep up to date.

The isql test as the zabbix user (instead of root) confirmed the issue. The odbcinst -q -s command confirmed as well (different output as root user).

deleted the zabbix user .odbc.ini file and fixed the issue.