VMware Cloud Community
PaulHallettWTR
Contributor
Contributor

vROps SQL Monitoring - Security Permissions and issues discovering/monitoring DBs

Hi All,

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 Smiley Happy

1)

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.

2)

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....

Reply
0 Kudos
6 Replies
PaulHallettWTR
Contributor
Contributor

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)

Thanks!

Reply
0 Kudos
AutomationGroup
VMware Employee
VMware Employee

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

Reply
0 Kudos
PaulHallettWTR
Contributor
Contributor

Hi,


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?

Thanks

Reply
0 Kudos
AutomationGroup
VMware Employee
VMware Employee

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

Reply
0 Kudos
PaulHallettWTR
Contributor
Contributor

Hi,

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.

Reply
0 Kudos
Oulyanov
VMware Employee
VMware Employee

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.

Regards, Oleg

Reply
0 Kudos