SCOM 2012 R2: Alert as MSSQL 2016: Discovery failed - Event ID: 7105

Question

Hi All,

We have been receiving alert as "MSSQL 2016: Discovery failed" below is the Alert Description. kindly assist us on this alert which we receive.

Details
we are running SCOM 2012 R2
SQL MP 7.0.15
We have enabled Run As Addendum MP to monitor the SQL

Note: this is happening on only one particular server

Alert Description:
Event ID: 7105 Management Group: XXXXXX. Script: DiscoverSQL2016MirroringWitness.vbs. Version: 7.0.15.0. Instance: ^^^^^: Mirroring witness discovery script 'DiscoverSQL2016MirroringWitness.vbs' for instance '^^^^^' failed.
The next errors occurred:
Query execution failed: [Microsoft][SQL Server Native Client 11.0][SQL Server]The SELECT permission was denied on the object 'database_mirroring_witnesses', database 'mssqlsystemresource', schema 'sys'.

Regards,
Kumar

Answer 1

The error message is pretty explicit : the account under which the discovery is running doesn't have enough permissions on database_mirroring_witnesses.
The SQL MP documentation clearly states that the account needs the SELECT permission on database_mirroring_witnesses.
This should be taken care of automatically by Kevin Holman's RunAs addendum, since its script has this instruction among others :

GRANT SELECT ON sys.database_mirroring_witnesses TO [SCOM_HealthService];

So you need to find out what account you're using to run SQL Discovery and verify what permissions it has.