Monitoring MSSQL in Zabbix with Microsoft ODBC Driver

[pawulon]

Hi,

After I successfuly installed OracleSQL driver and monitor some databases it is time to monitor MSSQL database. Unfortunalety it is not documented as well as OracleSQL.

isql -v wms01 <uid> <pwd>

This command in linux cmd of Zabbix 4.0.4 server is working correctly (MSSQL). I can connect to remote server and request SQL commands. There is no problem here.

But in Zabbix console I have an error:

Cannot connect to ODBC DSN: [SQL_ERROR]:[IM002][0][[unixODBC][Driver Manager]Data source name not found, and no default driver specified]

My configuration in CentOS 7:

• /etc/sysconfig/zabbix-server - here I put environment variables for Zabbix user (for OracleSQL and MSSQL Driver)
  
  

  LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/lib/oracle/12.1/client64/lib
  	PATH=$PATH:/usr/bin:/bin:/sbin:/usr/lib/oracle/12.1/client64/bin:[U]/opt/mssql-tools/bin[/U]
  	ORACLE_HOME=/usr/lib/oracle/12.1/client64
  	export LD_LIBRARY_PATH
  	export PATH
  	export ORACLE_HOME

• /root/.bash_profile - main environment variables config
  
  

  export LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib:$LD_LIBRARY_PATH
  	PATH=$PATH:/sbin:/bin:/usr/lib/oracle/12.1/client64/bin:/opt/mssql-tools/bin
  	export ORACLE_HOME=/usr/lib/oracle/12.1/client64
  	export PATH

• /etc/odbcinst.ini - configuration of drivers
  
  

  [Oracle12c]
  	Description=Oracle ODBC Driver
  	Driver=/usr/lib/oracle/12.1/client64/lib/libsqora.so.12.1
  	Trace=yes
  	TraceFile=/var/log/odbcoracle
  
  [B][MSSQL17][/B]
  	Description=Microsoft ODBC Driver 17
  	Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.3.so.1.1
  	UsageCount=1

• /etc/odbc.ini - DSN configuration
  
  

  [ifs7]
  	Driver = Oracle12c
  	DSN = Oracle12c
  	Servername = xx.xx.xx.xx:xxxx/xxxxxxx
  	Database = xx.xx.xx.xx:xxxx/xxxxxxx
  	Port = xxxx
  	UserID = xxxxxxxxxxxxxxxxx
  	Password = xxxxxxxxxxxx
  	DatabaseCharacterSet=UTF8
  
  [B][wms01][/B]
  	Driver = MSSQL17
  	DSN = MSSQL17
  	Server = tcp:xx.xx.xx.xx, xxxx
  	Port = xxxx
  	uid [COLOR=#B22222]#[I] - command not working[/I][/COLOR]
  	pwd [COLOR=#B22222]#[I] - command not working[/I][/COLOR]

  I don knot why uid and pwd set in DSN config are not working for MSSQL driver. I neet do put it in isql command or zabbix item config.

On this config OracleSQL connection in working correctly with isql command and in Zabbix console.

Unfortunately Zabbix can not find MSSQL driver in Zabbix console and return Cannot connect to ODBC DSN: [SQL_ERROR]:[IM002][0][[unixODBC][Driver Manager]Data source name not found, and no default driver specified] error. isql command is working correctly as well.

It seems that DSN wms01 is not visible in Zabbix.


What can I do else to connect Zabbix user to driver and MSSQL Database?

[vso]

Please try patch attached in following ZBX: https://support.zabbix.com/browse/ZBX-15634

[pawulon]

Please try patch attached in following ZBX: https://support.zabbix.com/browse/ZBX-15634

The solution that you quoted, concerns a different problem.

I don't have a credensials problem. In my zabbix item the User name and Password are set for the [wms01] DSN.

[vso]

Sorry, misunderstood, can you please attach screenshot of Database monitor item ? Also output of following command: odbcinst -j

[pawulon]

[root@skiezbx01 ~]# odbcinst -j
unixODBC 2.3.1
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

The item:


Variables are set in server Macros:


Zabbix linux commandline:

[root@skiezbx01 ~]# isql -v WMS01 [I]<login> <password>[/I]
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> SELECT case when count(1)>0 then 1 else 0 end FROM Cersanit_Walbrzych_OPTIPROMAG.dbo.WYDRUK_ONLINE  WITH(NOLOCK) where left(WRD_UWAGI, 20) = 'Exception: Eror:500' and WRD_AUDYT_DT > DATEADD(HH, -1, GETDATE())
+------------+
|            |
+------------+
| 0          |
+------------+
SQLRowCount returns 0
1 rows fetched

Zabbix web console:

[vso]

Sorry for asking but what user is zabbix running under, is it also root ?

[pawulon]

No, zabbix is running default user as after fresh install, I have not changed anything.
This is the reason to use /etc/sysconfig/zabbix-server to confgure environment variables.

I am loging in with root to linux commandline via SSH.

[vso]

Can you try calling isql from the same user that Zabbix uses ?

[pawulon]

I don't know... Maybe I can... Where can I find login and password for zabbix?

Is it $DB['USER'] and $DB['PASSWORD'] from /etc/zabbix/webzabbix.conf.php ?

I thought that loging in to commandline with zabbix user is blocked.

[pawulon]

[root@skiezbx01 ~]# su zabbix
This account is currently not available.

[salai]

Were you able to resolve this issue? I am currently facing a similar issue.

I am able to connect to SQL Server from Linux box using isql or sqlcmd but Zabbix is not able to connect to SQL server. Any ideas?

Having multiple DSN entries in the odbc.ini a problem?

[Veravul]

The reason is that SQ server DSN does not use user and password in the odbc.ini file ( this depends on security ), just use in the Zabbix item username and password and this will work, or connection string

[vladimir_lv]

[root@skiezbx01 ~]# su zabbix
This account is currently not available.

Try to use this one:

su - zabbix -c 'isql -v WMS01 <login> <password>' -s '/bin/bash'