locked
MSSQL 2008, 2012, 2014 and 2005: Discovery failed RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    SQL MP Version 6.7.2.0

    After the SQL MP update to SQL MP Version 6.7.2.0 we getting discovery issue's in SCOM.

    Alert Name:- MSSQL 2008: Discovery failed
    Alert Description :- Management Group: XXX2012. Script: DiscoverSQL2008Files.js. Version: 6.7.2.0. Instance: MSSQLSERVER : File Discovery script 'DiscoverSQL2008Files.js' for instance 'MSSQLSERVER' failed.
    Inner exception: 
    Error Number : 
    Error Code : 0
    Win32 Facility : 0
    Error Description : 
    Call stack:Exception.constructor(File Discovery script 'DiscoverSQL2008Files.js' for instance 'MSSQLSERVER' failed.,Can't execute query 'SELECT database_id, name FROM sys.databases WHERE state = 0 AND source_database_id is null AND collation_name is not null AND (is_in_standby = 0 OR is_read_only = 1) AND user_access != 1 AND name NOT IN ('') ': [Microsoft][SQL Server Native Client 10.0][SQL Server]The SELECT permission was denied on the object 'databases', database 'mssqlsystemresource', schema 'sys'.

    DiscoverSQL2008FileGroups.js, DiscoverSQL2008Files.js, DiscoverSQL2008MirroredDB.vbs, DiscoverSQL2008DB.vbs, DiscoverSQL2008MirroringWitness.vbs, DiscoverSQL2008DB.vbs.
    Event Source :- Health Service Script.
    Event ID :- 7102
    Log Name: Operation Manager

    Discovery Inventory is showing as not monitored for SQL Server 2008/2005/2012/2014 Installation Seed and SQL Agent JOb not showing any information

    Good Part :- In SQL MP Folder computers are getting Discovered, SQL Server 2005/2008/2012 Servers, Database Engines, Database, Integration Services,File Groups, SQL Agent State are getting Discovered.


    Thanks and Regards Zeeshan J Mohammed
    7396325590

    Friday, November 4, 2016 11:53 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    The Issue was resolved.

    Steps

    Verified the SCOM-SQL Monitoring account.

    Given SA permission to the account on the All the SQL Server.

    Ran the on demand discovery, it resolved the issue.

    Get-SCOMClass -DisplayName "SQL Server 2008 Installation Seed" | Get-SCOMClassInstance | where{$_.Displayname -match "Servername.company.com"} | ft Displayname,id

    Get-SCOMDiscovery | Where{$_.displayname -match "SQL Server 2008 DB Installation Discovery Source"} | ft Displayname, id

    Thanks and Regards Zeeshan J Mohammed

    Thursday, December 13, 2018 3:35 AM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote


    Trouble Shooting Steps Taken from Myside.
    Run as account is linked to Run as profiles:three profiles: SQL Server Default Action Account, SQL Server Discovery Account (All Targeted object)and SQL Server Monitoring Account, and servers properly distributed to the run as account  as its "more secure" 
    Type the following commands at the command prompt:
    cd C:\Program Files\MicroSoft Monioring Agent\agent\tools 
    StopTracing.cmd 
    Delete logs in C:\Windows\Logs\OpsMgrTrace
    Run StartTracing.cmd VER 
    Restart SCOM health service
    Wait for 5 – 7 mins
    Type the following commands at the command prompt:
    Run StopTracing.cmd 
    Run FormatTracing.cmd
    Tried to manually run the script with the account (Run as account and Agent action account is same) which is having local admin and sysadmin access on SQL Server agent
    Manually verified the SQL Run as account is having Local Admin Access / Sys Admin access (Server Roles :- SysAdmin / Public     User Mapping :- Public / DB_Owner / DB_datareader) 

    Also Stopped the health Services , deleted the Health Service Folder and restarted the Health Service (to refresh the configurations).
    Then manually went to the script location "    C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Monitoring Host Temporary Files 1007\109"to run the file from CMD with Admin permissions. No Go.

    Also tried On Demand Discovery from Operation Manager / Agent health State Folder Task options
    Get-SCOMClass -DisplayName "SQL Server 2008 Installation Seed" | Get-SCOMClassInstance | where{$_.Displayname -match "Servername.company.com"} | ft Displayname,id

    Get-SCOMDiscovery | Where{$_.displayname -match "SQL Server 2008 DB Installation Discovery Source"} | ft Displayname, id
    Ran with SQL Run as account with Local Admin and Sys Admin access / Changed the Override value to Target ID from Powershell result and discovery ID from the above powershell result.

    Discovery ran within 1 Sec and gave below result and below is the output

       Trigger On Demand Discovery
    Task Description

    Status:Success
    Scheduled Time:11/4/2016 7:48:38 AM
    Start Time:11/4/2016 7:48:39 AM
    Submitted By:SAI\moh5682
    Run As:SAI\svc_scom_testadmin
    Run Location:
    Target:
    Target Type:Health Service
    Category:Maintenance
    This task is used to trigger an on demand discovery


    Task Output:

    < DataItem type =" System.OnDemandDiscoveryResponse " time =" 2016-11-04T07:48:39.4314852-04:00 " sourceHealthServiceId =" 1AFDF736-F163-EB58-99EF-A3270E2EC04C " > 
      < Result > DISCOVERY_NOT_FOUND </ Result > 
      < Timestamp /> 
      </ DataItem > 

    Please let me know how can we resolve this issue. Also check with DBA team they say they are able to manually excute the query mentioned in Alert description with the same Sql Run as account.

    Windows 2008 / R2 and above servers are used in our envi . Which remove the Bar for SQL MP 6.7.2.0 (Does not support Windows 2003 OS)

    Repeat Count is hitting high.

    Thanks and Regards Zeeshan J Mohammed

    Friday, November 4, 2016 1:21 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi Sir,

    >>The SELECT permission was denied on the object 'databases'

    Did you checked VMM DB select permission :

    Best Regards,

    Elton

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Tuesday, November 8, 2016 8:41 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The Issue was resolved.

    Steps

    Verified the SCOM-SQL Monitoring account.

    Given SA permission to the account on the All the SQL Server.

    Ran the on demand discovery, it resolved the issue.

    Get-SCOMClass -DisplayName "SQL Server 2008 Installation Seed" | Get-SCOMClassInstance | where{$_.Displayname -match "Servername.company.com"} | ft Displayname,id

    Get-SCOMDiscovery | Where{$_.displayname -match "SQL Server 2008 DB Installation Discovery Source"} | ft Displayname, id

    Thanks and Regards Zeeshan J Mohammed

    Thursday, December 13, 2018 3:35 AM