Share via

MSSQL on Windows: Discovery error for single instance in a server

shankar431 471 Reputation points
2022-07-12T07:31:12.053+00:00

Hi All ,

We are using the Kevin holamn's SQL run as addenddum management pack to enable SID's for SQL servers to use NT SERVICE\HealthService to avoid creating separate run as accounts to monitor SQL servers and its components.
https://kevinholman.com/2016/08/25/sql-mp-run-as-accounts-no-longer-required/

We have enabled the SID and SQL team has run the required SQL script from their end to grant the proiviliges to NT SERVICE\HealthService in the SQL server as mentioned in the Kevin blog.

But still we are receving the below error.
Error(s) was(were) occurred:
Message: [Error number: 916] The server principal "NT AUTHORITY\SYSTEM" is not able to access the database "XYZ" under the current security context.

Our DBA team does not understand why the SCOM tries to access/monitor database XYZ on Server\Instance ABC\wave with NT AUTHORITY\SYSTEM
The rights script has successfully granted rights to NT SERVICE\HealthService (as is used by the Run As Addendum MP from Kevin Holman) for SQL Server monitoring using Service SID.
According to our DBA team, server ABC.com has other Instances (containing databases) as well and even instance wave contains also other databases apart from database XYZ.
For these other instances and databases no alerts are being generated, so monitoring is fine for those.

So why is SCOM trying to access/monitor database XYZ on Server\Instance ABC\wave with NT AUTHORITY\SYSTEM instead of NT SERVICE\HealthService?

Can you please assist us.

Regards,
Ravi shankar

Operations Manager
Operations Manager
A family of System Center products that provide infrastructure monitoring, help ensure the predictable performance and availability of vital applications, and offer comprehensive monitoring for datacenters and cloud, both private and public.
1,446 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. shankar431 471 Reputation points
    2022-07-14T15:42:43.113+00:00

    Hi All,

    Any one has idea why this is happening only for one instance on a DB server.

    Ravi shankar

    0 comments
  2. Graham 176 Reputation points
    2022-07-30T11:16:44.13+00:00

    Hi

    Not sure if you have resolved this yet - if you have it would be great if you could post the fix.

    One thing to review is to manually check off that each requirement is in place. Sometimes scripts fail .. sometimes individuals forget to run them or target the wrong instance ... sometimes things just don't seem to do what they should. Given that it is just one box then there is something specific here and not a general issue with the scripts.

    1. Check that the Health Service is correctly set in the registry on the server (ServiceSidType = 1). I suspect this is as the DBAs are saying that other SQL Servers \ databases are correctly discovered.
    2. Verify that the login NT Service\Health Service exists as a login on that instance of SQL
    3. Is it failing for all database discoveries on the SQL Server or just specific databases. Double check that the login exists on each of the databases e.g. is this a new database that has been created since the scripts were run? Or is it a database that gets dropped and recreated on a regular basis?

    The fact that it is trying to run as local system suggests that the service sid hasn't been set correctly somewhere. Are you using the SQL agnostic management packs or a mix of SQL agnostic and "old style".

    Kevin has further troubleshooting ideas at the bottom of his article - https://kevinholman.com/2016/08/25/sql-mp-run-as-accounts-no-longer-required/

    Regards

    Graham

    0 comments