Zabbix监控万物1--监控MSSQL

背景

由于没有升级zabbix agent2的打算，所以采用了官方的template方案
PS： 第三方的都是坑，对于MS SQL2019没法使用https://www.zabbix.com/integrations/mssql#mssql_odbc

下载及导入模版

Server安装ODBC驱动

这一步需要在zabbix server中安装ODBC驱动，这里采用微软官方的

https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver16&tabs=alpine18-install%2Calpine17-install%2Cdebian8-install%2Credhat7-13-install%2Crhel7-offline

版本采用17版本，因为18可能会有报错

Server由于是Rocky9.3，因此选择RHEL9版本

安装

#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version

#RHEL 7 and Oracle Linux 7
curl https://packages.microsoft.com/config/rhel/7/prod.repo | sudo tee /etc/yum.repos.d/mssql-release.repo

#RHEL 8 and Oracle Linux 8
curl https://packages.microsoft.com/config/rhel/8/prod.repo | sudo tee /etc/yum.repos.d/mssql-release.repo

#RHEL 9
curl https://packages.microsoft.com/config/rhel/9/prod.repo | sudo tee /etc/yum.repos.d/mssql-release.repo

sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
sudo ACCEPT_EULA=Y yum install -y msodbcsql17
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y yum install -y mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo yum install -y unixODBC-devel

配置

查看ODBC是否安装成功， 需要查看odbcinst.ini

less odbcinst.ini

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.6.1
UsageCount=1

配置DSN

[sql1] ## 名字可自定义
Driver = ODBC Driver 17 for SQL Server 选择上一步的名字
Server = 10.202.0.201 ## DB IP地址，端口默认1433
TrustServerCertificate = yes

测试

isql <DSN名称> <username> <password>

如果能够进入数据库，则说明配置成功

Zabbix Server端配置

配置模版的Macro

{$MSSQL.USER} - 数据库用户
{$MSSQL.PASSWORD} - 数据库密码
{$MSSQL.DSN} - 数据库DSN名称

Trouble Shooting

1. 通过isql命令可以连接，但是在zabbix上不行，提示A network-related or instance-specific error has occurred while establishin

解决办法： 查看SELinux以及firewalld，把这俩都关了就好了

2. Microsoft ODBC Driver 18 for SQL Server : SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate]

解决办法：在DSN中添加一行配置

TrustServerCertificate=yes;