We are struggling to identify the correct set of permissions required to be able to monitor SQL instances and DBs in our estate. This is a 2 part question/issue, so bear with me
What permissions are required to monitor both SQL instances and DBs? So far, we have the following assigned to our AD service account:
Windows - Logon Locally, Logon as a service. (As it happens, the service account is local admin, so shouldn't have any restrictions)
SQL - View Any Definition, View Server State, and also db_datareader on MSDB/MODEL and each user database.
In some cases, we're getting monitoring on the instance but not on the DBs, others we're not getting any monitoring at all. It seems very random, but permissions are consistent as far as we can tell.
We have 2 vROps instances - PROD and NON-PROD. Both are identical versions, levels and policies, using the same version of the plugins.
In NON-PROD, we are able to discover SQL instances and the child databases.
In PROD, we only see SQL instances - there are no child objects - Checking inventory explorer in both environments we can clearly see that there are no objects of type MSSQL Agent, Analysis, Database, Databases in the PROD environment. Before anyone says anything, they DO exist!
How can this be? Is there anything we can check? Bit stumped on this one....
Here is a screenshot for point #2 above - With PROD on the left, and NON-PROD on the right. This shows that we have SQL objects detected in both environments, but in PROD we have no other SQL child or related objects.
FYI I believe we see duplicates for each object type in both environments due to the Hyperic solution being installed but not used - this cannot be removed as per VMware KB article (once installed, unable to uninstall)
Minimum Privileges to Monitor the Microsoft SQL Server Plug-in:
Ensure that the user the agent runs with after installation has sufficient privileges to monitor the Microsoft SQL Server Plug-in .
If the Microsoft SQL Server runs in a domain, the user should be a part of that domain.
The user must have read access to all databases including system databases.
The user must be a local administrator
Please check the above . those are permissions required for collecting MSSQL data with EPOps agents
Thanks for the response.
As per above, the user account is already a local administrator, it is an AD account, and it has read access to all DBs.
The issue still exists.
Any other ideas?
have you installed EPOps agent with domain credentials. Can you check that the Windows service's properties > Log On has the domain account (wu=ith all the privileges) . It will be Local System account by default. But you can try giving the domain user details selecting "This account:" radio button
Yes it is running under a domain account as per above. It has been stopped and restarted and also ensured that LogonAsAService right has been granted successfully.
OK, can you please post agent log from both PROD and NON-PROD
Also try obvious - grant service account sysadmin right on MS SQL instance and see if it makes different.