Post

Quick and simple monitoring for 2017 MSSQL server with Zabbix 7.0

Posted
By Kevin Schwickrath
7 min read

Quick and simple guide for monitoring a 2017 MSSQL server with Zabbix 7.0 (current). Most of the documentation, blog posts, and forums I found on this topic were wildly out of date or flat wrong, at least for me. Maybe my Google-fu is failing me.

Prepare the MSSQL server

Create a Zabbix user on MSSQL with the following permissions:

  • View Server State
  • View Any Definition
  • Read
    • sysjobschedules
    • sysjobhistory
    • sysjobs

For MSSQL versions 2017 and 2019:

1
2
3
4
5
6
7
8
9

CREATE LOGIN zabbixuser WITH PASSWORD = 'password'
GRANT VIEW SERVER STATE TO zabbixuser
USE msdb
CREATE USER zabbixuser FOR LOGIN zabbixuser
GRANT EXECUTE ON msdb.dbo.agent_datetime TO zabbixuser
GRANT SELECT ON msdb.dbo.sysjobactivity TO zabbixuser
GRANT SELECT ON msdb.dbo.sysjobservers TO zabbixuser
GRANT SELECT ON msdb.dbo.sysjobs TO zabbixuser
GO

For MSSQL version 2022:

1
2
3
4
5
6
7
8
9

CREATE LOGIN zabbixuser WITH PASSWORD = 'password'
GRANT VIEW SERVER PERFORMANCE STATE TO zabbixuser
USE msdb
CREATE USER zabbixuser FOR LOGIN zabbixuser
GRANT EXECUTE ON msdb.dbo.agent_datetime TO zabbixuser
GRANT SELECT ON msdb.dbo.sysjobactivity TO zabbixuser
GRANT SELECT ON msdb.dbo.sysjobservers TO zabbixuser
GRANT SELECT ON msdb.dbo.sysjobs TO zabbixuser
GO

Install the latest ODBC driver for Ubuntu on the proxy (or server if no proxy). Install the Microsoft ODBC driver for SQL Server (Linux) - ODBC Driver for SQL Server | Microsoft Learn

Run the script provided by Microsoft (link above) or execute the following commands individually. My server and proxy run Ubuntu 24.04, so I ran the SECOND curl command below. Pick one, not both.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# Add the signature to trust the Microsoft repo
# For Ubuntu versions < 24.04 
curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

# For Ubuntu versions >= 24.04
curl https://packages.microsoft.com/keys/microsoft.asc | sudo gpg --dearmor -o /usr/share/keyrings/microsoft-prod.gpg

# Add repo to apt sources
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list

# Install the driver
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql18

# 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

Create a DSN file

The DSN or Data Source Name is an identifier that applications use to request an ODBC Data Source connection. It stores the connection details like database name, directory, database driver, userID, password, etc., when connecting to the ODBC. To summarize, it is a 1980s way for a computer to talk to a database that won’t die. It has been around as long as I have been doing this. Are there better ways to do this? Sure, maybe, See Zabbix Agent 2. We don’t fix what isn’t broken (cough, cough, printer drivers). For more information on DSN, see:  What is a DSN (Data Source Name)? - Microsoft Support and ODBC see: Microsoft Open Database Connectivity (ODBC)

For simplicity, use a DSN name like ZABBIXDSN or MYFAVDSN.

Create an odbc.ini file with vi or your preferred editor.

1
2
3
4
5
6
7
8
9
10

$ vi /etc/odbc.ini

# Input the following and adjust as needed.

[ZABBIXDSN]
Driver = ODBC Driver 18 for SQL Server
Server= mssqlserver.domain.tld
Port = 1433
User = zabbixuser
TrustServerCertificate = yes
  • Driver - Specify the driver you wish to use
  • Server - The FQDN or IP of the MSSQL server
  • Port - Usually 1433 unless you’re special
  • User - The username you created above
  • TrustServerCertificate - Set this to yes if you want to use TLS/SSL and want to simply accept the certificate presented.
    • Note that this breaks TLS identity verification of the server and can make you susceptible to man-in-the-middle (MITM) attacks. Yes–yes, I know, you will fix it “later”.

Other options:

1
2

Server = [protocol:]server[,port] 
Server = tcp:localhost,1433

Double-check all the things

To see what driver version and options you have installed, run odbcinst -j or cat /etc/odbcinst.ini. Tip: odbcinst has some fancy things it can do.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

$ odbcinst -j 
unixODBC 2.3.12
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /home/kevin/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

$ cat /etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.4.so.1.1
UsageCount=1

Another option for viewing the driver version only.

1
2

$ odbcinst -q -d
[ODBC Driver 18 for SQL Server]

Test the connection

Run the following with your creds and modify as needed.

1

$ isql DSNNAME 'username' 'password'

For example:

1
2
3
4
5
6
7
8
9
10
11

$ isql -v ZABBIXDSN 'zabbixuser' 'password'
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| echo [string]                         |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> quit

Configure the web interface

Link the templates in the web interface. I’m using the following templates for my host:

Update the host you will be monitoring with the following macros. Set the username, password, and host macros

1
2
3

{$MSSQL.USER} - Username you created above
{$MSSQL.PASSWORD} - Password you created above
{$MSSQL.DSN} - The DSN you created above

Zabbix Host Zabbix Host Macros

Troubleshooting

ISQL errors

You might get a very non descript error message that says it can’t connect. a.k.a = PC LOAD LETTER

1
2

$ isql DSNNAME zabbixuser 'password'
[ISQL]ERROR: Could not SQLConnect

To actually see what caused this error use verbose mode. I bet Michael Bolton would have liked that switch to see he had the wrong paper size set.

1

$ isql -v DSNNAME 'username' 'password'

For example, this is my server reminding me that I forgot to setup the certs on this proxy:

1
2
3
4

$ isql -v DSNNAME 'zabbixuser' 'password'
[08001][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:0A000086:SSL routines::certificate verify failed:unable to get local issuer certificate]
[08001][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2226722
[16:55:59.927 §](<16:55:59.927 §>)[ISQL]ERROR: Could not SQLConnect

All else fails read the man page man isql or reach out to me via one of the buttons I have everywhere, email is good.

Template JSON errors

I got some JSON errors on some item checks (little red icon!). Logs looked like this:

1
2
3

Failed: cannot extract value from json by path "$[?(@.counter_name=='CacheHitRatio')].cntr_value.first()": no data matches the specified path
error reason for "SERVER:mssql.cache_hit_ratio" changed: Preprocessing failed for: [{"object_name":"SQLServer:Plan Cache","cntr_value":"1","counter_name":"Cache Hit Ratio","inst...
error reason for "SERVER:mssql.buffer_cache_hit_ratio" changed: Preprocessing failed for: [{"object_name":"SQLServer:Buffer Manager","cntr_value":"3","counter_name":"Background writer ...

TL;DR: The template had the item’s preprocessing parameters misconfigured. I needed to add some spaces to the parameters.

To resolve this, I went into data collection, my_host, and items, searched for all the “not supported” items, and the associated “Get” item. For me, the following items were the data retrivers. I set the History to “Store up to: 1 day” to see the response coming from the server.

  • MSSQL: Get Buffer Manager counters
  • MSSQL: Get Cache counters

I then headed over to Monitoring, Latest data, searched for my_host, buffer & cache, and viewed the full response. The issue was the parameter was configured as:

1

$[?(@.counter_name=='Buffercachehitratio')].cntr_value.first()

However, the response included spaces, so it needed to be updated.

1

$[?(@.counter_name=='Buffer cache hit ratio')].cntr_value.first()

I updated the template with the spaces, and all the errors cleared. I then turned off the History I set earlier.

Have fun with this one…

Sources / Linkage

monitoring
Zabbix Zabbix Proxy Zabbix Agent MSSQL Ubuntu Ubuntu 24.04 SQL ODBC
This post is licensed under CC BY 4.0 by the author.

Comments powered by Disqus.

© Kevin Schwickrath. Some rights reserved.

Using the Chirpy theme for Jekyll.