使用Zabbix监控SQL Server

安装unixodbc
CentOS

yum install unixodbc
yum install unixodbc-devel

Debian

apt install unixodbc
apt install unixodbc-dev

重新编译Zabbix
增加参数--with-unixodbc

安装Microsoft ODBC 18

下载rpm或者deb包，手动安装
https://learn.microsoft.com/zh-cn/sql/connect/odbc/download-odbc-driver-for-sql-server

查看/etc/odbcinst.ini，会发现已经包含配置信息

[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.2.so.1.1
UsageCount=1

修改/etc/odbc.ini，添加数据源

[TEST]
Driver = ODBC Driver 18 for SQL Server
Server = 192.168.1.2
Port = 1433
Database = master
TrustServerCertificate = Yes

测试连接
isql -v <数据源名称> <用户名> <密码>
例如
isql -v TEST sa password
显示如下内容就是成功连接

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>

模板部署
使用“MSSQL by ODBC”模板，手动配置宏

{$MSSQL.USER}
{$MSSQL.PASSWORD}
{$MSSQL.DSN}

其中DSN就是odbc.ini配置的数据源名称

账号授权
如果没有数据库账号或者账号权限不够，需要新建和授权

创建账号zabbix

授予“登录”和“连接到数据库引擎”的权限，也使用以下语句

use master
GRANT VIEW SERVER STATE TO [zabbix];
GRANT VIEW ANY DEFINITION TO  [zabbix];

授予SQL Server代理作业相关权限，使用语句

use msdb
GRANT SELECT ON OBJECT::msdb.dbo.sysjobs TO zabbix;
GRANT SELECT ON OBJECT::msdb.dbo.sysjobservers TO zabbix;
GRANT SELECT ON OBJECT::msdb.dbo.sysjobactivity TO zabbix;
GRANT EXECUTE ON OBJECT::msdb.dbo.agent_datetime TO zabbix;

问题处理

Zabbix报错Support for Database monitor checks was not compiled in

重新编译Zabbix，增加参数--with-unixodbc

使用isql测试连接报错

[08001][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]SSL Provider: [error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol]
[08001][unixODBC][Microsoft][ODBC Driver 18 for SQL Server]Client unable to establish connection
[ISQL]ERROR: Could not SQLConnect

修改/etc/ssl/openssl.cnf

在顶部增加openssl_conf = default_conf

在底部增加

[default_conf]
ssl_conf = ssl_sect

[ssl_sect]
system_default = system_default_sect

[system_default_sect]
MinProtocol = TLSv1.2 TLSv1.0
CipherString = DEFAULT@SECLEVEL=1

来源：
https://www.modb.pro/db/574524
https://github.com/microsoft/msphpsql/issues/1112