SQL Plugin cannot find data for SQL jobs

Troubleshooting
, ,
1

CMK version: 2.1.0p29
OS version: 1.6.6

Error message:
<<<mssql_jobs:sep(09)>>>
MSSQLSERVER ERROR: The SELECT permission was denied on the object ‘sysschedules’, database ‘msdb’, schema ‘dbo’. (SQLState: 42000/NativeError: 229).

Hello all,

We are currently adding various SQL servers to Checkmk’s monitoring.
After placing the plugin on the host, we were already able to include various metrics from the database in the monitoring. However, we noticed that the MSSQL_Jobs section is empty and brings the error message from the top. Since we did not want to give the “system user” under which the Checkmk agent runs and under which the SQL queries are also made any further rights, we created a new local user and gave him the rights as described in the instructions.

Even after setting up the user and creating the mssql.ini file, the query of the jobs still did not work.

Afterwards we looked at the SQL logs and could see that our newly created user has no login attempts to the database.
Our current assumption is that the checkmk agent and the mssql plugin ignores our created mssql.ini file with the database user and does not use it for execution.

We are a little stumped here now as we are running out of ideas on where else to start debugging.

Thanks a lot
Andreas

2

As you mentioned, check the SQL Server logs for any messages related to login attempts by the user specified in the mssql.ini file, especially if you are dealing with B2B contact enrichment. This can help identify if there are any issues with authentication.

3

old ticket, but the docu was still not correct for me:

i had to execute:
USE msdb;
GRANT SELECT ON OBJECT::sysschedules TO [NT AUTHORITY\SYSTEM];

→ as i’m not the SQL-Admin-Expert, feel free to correct me.
Note the different syntax in my command to address the object.