{"id":28773,"date":"2023-11-13T14:12:04","date_gmt":"2023-11-13T13:12:04","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=28773"},"modified":"2024-09-11T10:16:05","modified_gmt":"2024-09-11T08:16:05","slug":"monitor-microsoft-sql-server-with-zabbix","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/monitor-microsoft-sql-server-with-zabbix\/","title":{"rendered":"Monitor Microsoft SQL Server with Zabbix"},"content":{"rendered":"\n

In this blog post, we will quickly see what is required to monitor a Microsoft SQL Server with Zabbix.<\/p>\n\n\n\n

Agent Setup<\/h2>\n\n\n\n

Agent setup is not required by MSSQL by ODBC<\/em> template; thus this step can be skipped.<\/p>\n\n\n\n

Setup Monitoring Account<\/h2>\n\n\n\n

The best practice is to create an account in the database dedicated to monitoring which will have limited permissions (view, select). Setup chapter of MS SQL integration<\/a> on Zabbix web site explains it well.<\/p>\n\n\n\n

In our example, this user will be named zbx_monitor<\/em>.<\/p>\n\n\n\n

ODBC Driver Linux<\/h2>\n\n\n\n

Zabbix server will trigger SQL queries directly against MS SQL Server. To achieve this, it requires ODBC to be set up locally.<\/p>\n\n\n\n

You can refer to Microsoft web site (link here<\/a>) which provides all the steps to install ODBC driver depending on your linux flavor. Roughly, installation is composed of following steps:<\/p>\n\n\n\n

    \n
  1. Add MSSQL repository<\/li>\n\n\n\n
  2. Uninstall any installed ODBC packages<\/li>\n\n\n\n
  3. Install Microsoft driver<\/li>\n<\/ol>\n\n\n\n

    Setup Data Source Name (DSN)<\/h2>\n\n\n\n

    Before being able to use driver, we must declare it in \/etc\/odbcinst.ini<\/code>:<\/p>\n\n\n

    \n[ODBC Driver 17 for SQL Server]\nDescription=Microsoft ODBC Driver 17 for SQL Server\nDriver=\/opt\/microsoft\/msodbcsql17\/lib64\/libmsodbcsql-17.10.so.5.1\nUsageCount=1\n<\/pre><\/div>\n\n\n
    Next, we can declare DSN in \/etc\/odbc.ini<\/code>:<\/p>\n\n\n
    \n[mssql-mfiles]\nDescription = MS SQL M-Files Database\nDriver = ODBC Driver 17 for SQL Server\nServer = &lt;IP&gt;\nPort = 1433\nUser = zbx_monitor\nPassword = &lt;password&gt;\nDatabase = msdb\n<\/pre><\/div>\n\n\n
    \n
    The name between square bracket is the DSN that we will use later in the Zabbix configuration. Note that the ODBC driver does not use password from that file.<\/p>\n\n\n\n
    Finally, we can test our ODBC setup with the command:<\/p>\n<\/div><\/div>\n\n\n
    \nisql -v mssql-mfiles zbx_monitor <password>\n<\/pre><\/div>\n\n\n
    If all worked as expected, you should see this output and get a SQL prompt:<\/p>\n\n\n
    \n+---------------------------------------+\n| Connected!                            |\n|                                       |\n| sql-statement                         |\n| help [tablename]                      |\n| echo [string]                         |\n| quit                                  |\n|                                       |\n+---------------------------------------+\nSQL&gt;\n<\/pre><\/div>\n\n\n
    To active ODBC poller on Zabbix, we must uncomment line in \/etc\/zabbix\/zabbix_server.conf<\/code>:<\/p>\n\n\n
    \nStartODBCPollers=5\n<\/pre><\/div>\n\n\n
    Followed by a zabbix_server service restart.<\/p>\n\n\n\n