MSSQL - monitoring user permissions

[lukas.zeleny]

Hi,
I have a problem with monitoring MSSQL database using Zabbix agent 2. Our ZABBIX is on version 6.4.13, the server is running Zabbix agent 2 (6.4.13). Our SQL (Microsoft SQL Server 2022) database run on Windows Server 2022 DC and is managed by an external company, to which I have submitted a request to create a monitoring account according to the instructions (https://www.zabbix.com/cz/integrations/mssql)

View Server State and View Any Definition permissions should be granted to the user. Grant this user read permissions to the sysjobschedules, sysjobhistory, and sysjobs tables.
For example, using T-SQL commands:
GRANT SELECT ON OBJECT::msdb.dbo.sysjobs TO zbx_monitor;
GRANT SELECT ON OBJECT::msdb.dbo.sysjobservers TO zbx_monitor;
GRANT SELECT ON OBJECT::msdb.dbo.sysjobactivity TO zbx_monitor;
GRANT EXECUTE ON OBJECT::msdb.dbo.agent_datetime TO zbx_monitor;

After they created the zbx_monitor account I set everything up and zabbix was able to find all the databases, create graphs for them, but unfortunately it still doesn't read any data.



I found the following errors in the latest data and reported to our external contractor that it was a permissions issue.









The contractor modified the zbx_monitor account settings and added permissions for msdb, but unfortunately the situation is still the same and no data is being retrieved.



As a next step they tried to change the default schema for one DB from dbo to dba, but still no data in graphs.





Therefore I would like to ask for advice here. Thank you very much for any suggestions

LZ.

[LukaK12]

I have same problem

But if I give sysadmin rules, I can see the values in zabbix. Can I ask for help. How to set the minimum permissions for zabbix_user in MSSQL.

Thanks

[justcore]

Hi all. Have you checked the "latest data" in the monitoring tab of Zabbix? I had a similar issue that informed me it was user permission error:

[mixer]

permission error

What rights? How to understand if the rights are given according to the instructions on the site.
How can I see a request that I don't have permission for?

[justcore]

What rights? How to understand if the rights are given according to the instructions on the site.
How can I see a request that I don't have permission for?

Can you send a screenshot of your items in "latest data" like I posted above?

[mixer]

Can you send a screenshot of your items in "latest data" like I posted above?

Here is the data

[mrnobody]

On Linux with mariadb (i know it's not same OS or DB, but same grants should work)

show grants for 'zbx_monitor'@'%';
GRANT PROCESS, SHOW DATABASES, REPLICATION CLIENT, SHOW VIEW ON *.* TO `zbx_monitor`@`%`

Instead of % you can put IPv4 or hostname, for each Source of Query reqs.

[mixer]

show grants

Read first post again please.

[justcore]

Here is the data

I had to translate but what I said still stands....
Failed to execute handler: failed to execute handler: failed to query: mssql: The user does not have permission to perform this action.

You need to set up the correct permissions for the user account you have setup.

You need $MSSQL.username
You need $MSSQL.password
You need $MSSQL.url > sqlserver://ipORhostname:<port>

1. Create an MSSQL user for monitoring. For example, "zbx_monitor".

View Server State and View Any Definition permissions should be granted to the user. Grant this user read permissions to the sysjobschedules, sysjobhistory, and sysjobs tables.

For example, using T-SQL commands:
GRANT SELECT ON OBJECT::msdb.dbo.sysjobs TO zbx_monitor;
GRANT SELECT ON OBJECT::msdb.dbo.sysjobservers TO zbx_monitor;
GRANT SELECT ON OBJECT::msdb.dbo.sysjobactivity TO zbx_monitor;
GRANT EXECUTE ON OBJECT::msdb.dbo.agent_datetime TO zbx_monitor;


EDIT* I had our DB guy re-check the perms on the account because it didnt work and drove me up the wall. Turned out it was on them fixing perms.

[mixer]

I looked at the queries with a profiler. They were missing

USE master;
GRANT VIEW SERVER STATE TO UserName;
GO

You need to read the documentation more carefully.

[justcore]

I looked at the queries with a profiler. They were missing

USE master;
GRANT VIEW SERVER STATE TO UserName;
GO

You need to read the documentation more carefully.

So... permission error. Glad its resolved.