locked
SQL Management Pack 6.6.4 SCOM 2007 R2 alerts by tons!!! RRS feed

  • Question

  • Hello,

    I have the SQL Management Pack 6.6.4.0 SCOM 2007 R2 recently imported.

    I am getting a lot of alerts (1,200) of the type:

    Alert: MSSQL 2008: Discovery failed
    
    Resolution state: New
    
    Source: ServerName
    
    Path:                                                   ServerName
    Logging Computer:                              
    EventNumber:                                      
    Last modified by: System
    Last modified time: 1/4/2016 11:05:14 PM Alert description: Management Group: SCOM-MED. Script: SQLAgentJobDiscovery.vbs. Instance: MSSQLSERVER : SQL Agent Job discovery script 'SQLAgentJobDiscovery.vbs' for SQL instance 'MSSQLSERVER'  failed.

    OR

    Alert: MSSQL 2008: Discovery failed
    
    Resolution state: New
    
    Source: server name
    
    Path:                                                   servername
    Logging Computer:                              
    EventNumber:                                      
    Last modified by: System
    Last modified time: 1/4/2016 11:05:14 PM Alert description: Management Group: SCOM-MED. Script: DiscoverSQL2008DB.vbs. Instance: MSSQLSERVER : SQL Database discovery script 'DiscoverSQL2008DB.vbs' for instance 'MSSQLSERVER'  failed.
    

    I checked some articles like

    https://sqlmate.wordpress.com/2012/09/15/database-engine-and-databases-not-discovered-by-scom-2012/#comment-765

    But the SQL MP Monitoring Account RunAs account is local administrator on all server with Full Control Access on C:\Program Files\System Center Operations Manager folder and below...

    Any other idea ?

    Thanks,

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager



    • Edited by Felyjos Tuesday, January 5, 2016 7:24 AM
    Tuesday, January 5, 2016 7:21 AM

All replies

  • Make sure you follow the guide, because you also need to properly configure the three run as profiles. You are also only talking about local admin permissions, did you also permissioned the account in SQL itself?

    One option is to give sysadmin permissions, or you can go for the low privileged solution. Usually I would post this information directly here, but for SQL it's a huge piece so I recommend to read it from the guide. You can download the guide here: https://www.microsoft.com/en-us/download/details.aspx?id=10631

    Tuesday, January 5, 2016 10:08 AM
  • Hello,

    Yes I have the three RunAs account created:

    For simplification I have attached the MOM Action account to the three of them for now...

    attached each of them to the corresponding profiles

    and

    I closed the rules from yesterday and this morning and still they are coming back...

    Any idea?

    Thanks

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

    Tuesday, January 5, 2016 7:09 PM
  • Hello Dom,

    It seems only SQL 2008 have this kind of alert. I would like to suggest you add the action account to sysadmin server role for monitored SQL Server 2008 to check the result.

    Regards,

    Yan Li


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

    Wednesday, January 6, 2016 6:32 AM
  • Hello Yan,

    I will try this...

    It concerns all SQL version

    SQL 2005

    SQL 2008

    SQL 2012

    I have 701 alerts !!!

    it was working fine with 6.6.0.0 and started flooding with the new SQL MP 6.6.4.0

    Thanks,
    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager





    • Edited by Felyjos Wednesday, January 6, 2016 2:59 PM
    Wednesday, January 6, 2016 2:45 PM
  • Hello,

    I added the sysadmin role for the svcmomaction account on several databases and servers.

    I closed the rules for these databases and servers.

    The alert is still coming back.

    Any idea?

    Thanks,

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

    Thursday, January 7, 2016 9:04 PM
  • Hello,

    Reading the script DiscoverSQL2008DB.vbs it seems there is a discrepancy within the parameter numbers passed or to be passed to the script:

    'Copyright (c) Microsoft Corporation. All rights reserved.
    ' Parameters that should be passed to this script
    ' Parameters that should be passed to this script
    ' 0 MPElement ID ({157E391B-AE98-47B4-529A-544481162729})
    ' 1 Target Id for ME this rule is running against ({24345584-0FD5-0FAB-2293-D4C82BEDB086})
    ' 2 Computer ID 
    ' 3 Computer FQDN
    ' 4 SQL Connection String for the instance that the DBs are being discovered on
    ' 5 SQL Instance that this rule is being run for 
    ' 6 Exlcuded database list (prefixed with Exclude:)

    this looks at 7 parameters but later in the script

    if oArgs.Count < 8 Or oArgs.Count > 9 Then
       Wscript.Quit -1
    End If
    
    Dim  SourceID, ManagedEntityId, TargetComputer, InstanceName, ConnectionString, TargetComputerID, ExcludeList, TcpPort
    Dim DiscoveryErrorEventId
    
    SourceId = oArgs(0)
    ManagedEntityId = oArgs(1)
    DiscoveryErrorEventId = CInt(oArgs(2))
    TargetComputerID = oArgs(3)
    TargetComputer = oArgs(4)
    ConnectionString = oArgs(5)
    InstanceName = oArgs(6)
    ExcludeList = Mid(oArgs(7), 9)
    TcpPort = ""
    If oArgs.Count = 9 Then
        TcpPort = oArgs(8)
    End If


    this is looking at a minimum of 8 parameters so most likely the script is just exiting right away!!!

    any idea? there are several partial correction on the web but does Microsoft will update and/or fix this issue?

    Also when running the script manually (http://blogs.technet.com/b/kevinholman/archive/2010/03/09/basic-troubleshooting-of-discovery-scripts.aspx) I am getting an error on the line:

    DiscoveryErrorEventId =

    CInt(oArgs(2))

    DiscoverSQL2008DB.vbs(638, 1) Microsoft VBScript runtime error: Type mismatch: 'CInt'...

    apparently the evnt id should be at the end in numeric

    In progress not sure of the format for the TCP and Event ID but still trying !!!
    cscript /nologo DiscoverSQL2008DB.vbs {00000000-0000-0000-0000-000000000000} {00000000-0000-0000-0000-000000000000} MSQLCL1A.ad MSQLCL1A.ad Server=TRANSPLANTxx\TRANSPLANTxx;Database=TRANSPLANT;User Id=zzzzzz;Password=123456789 TRANSPLANTxx Exclude:True TCP:1433 1 but still exiting right away !!!

    Any idea?

    Thanks,

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager



    • Edited by Felyjos Friday, January 8, 2016 11:20 PM
    Friday, January 8, 2016 10:55 PM
  • Don't look at the comments in the script, because they are incorrect. The arguments that are send to the script are these (also in this order).

    $MPElement$
    $Target/Id$
    7102
    $Config/ComputerID$
    $Config/ComputerName$
    $Config/SQLConnectionString$
    $Config/SQLInstanceName$
    "Exclude:$Config/ExcludeList$"
    $Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/TcpPort$

    which match the arguments in the script exactly:

    SourceId = oArgs(0) = $MPElement$
    ManagedEntityId = oArgs(1) = $Target/Id$
    DiscoveryErrorEventId = CInt(oArgs(2)) = 7102
    TargetComputerID = oArgs(3) = $Config/ComputerID$
    TargetComputer = oArgs(4) = $Config/ComputerName$
    ConnectionString = oArgs(5) = $Config/SQLConnectionString$
    InstanceName = oArgs(6) = $Config/SQLInstanceName$
    ExcludeList = Mid(oArgs(7), 9) = $Config/ExcludeList$
    TcpPort = oArgs(8) = $Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/TcpPort$

    One thing I see in your screenshots is the runas account that is targeted to "All targeted objects". This actually means that the account will be used on ALL classes in your management group.
    If the account only has permissions on your SQL servers this should start alerting on all of your non SQL servers with an alert about Health Service Credentials, but that's a different issue.

    In my opinion and based on your screenshot you're missing classes and/or groups in your targeting. In your screenshot I can only see the Discovery account. According to the guide you need this:

      
      

    Run As Profile

      
      

    Targets

      

    SQL Server   Discovery Account

    Classes:

    • Windows Server
        SQL Server 2005 DB Engine
    • SQL Server 2008 DB Engine
    • SQL Server 2012 DB Engine
    • SQL Server 2005 Agent
    • SQL Server 2008 Agent
    • SQL Server 2012 Agent
    • SQL Server 2008 Mirroring DB
    • SQL Server 2008 Mirroring Witness
    • SQL Server 2012 Mirroring DB
    • SQL Server 2012 Mirroring Witness

    Groups:

    • SQL 2005 Computers
    • SQL 2008 Computers
    • SQL 2012 Computers
    • SQL Server 2008 Mirroring Group
    • SQL Server 2012 Mirroring Group
    • SQL 2005 DB Engine Group
    • SQL 2008 DB Engine Group
    • SQL 2008 R2 DB Engine Group
    • SQL 2012 DB Engine Group

    Most of them you targeted to the group instead of the class. I'm missing almost all classes. You have to follow this part in the guide line for line otherwise you'll get errors like you have now.

    Tuesday, January 12, 2016 1:44 PM
  • One small thing why you started seeing this since version 6.6.4.0 is because the Alert "MSSQL 20XX: Discovery failed" is coming from a new rule that is added in version 6.6.2.0. You probably already had this issue, only you were never aware of it.

    October, 2015 (version 6.6.2.0)

    Added new type of events from failed discoveries; added a new rule that collects such events

    Tuesday, January 12, 2016 1:56 PM
  • Don't look at the comments in the script, because they are incorrect. The arguments that are send to the script are these (also in this order).

    $MPElement$
    $Target/Id$
    7102
    $Config/ComputerID$
    $Config/ComputerName$
    $Config/SQLConnectionString$
    $Config/SQLInstanceName$
    "Exclude:$Config/ExcludeList$"
    $Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/TcpPort$

    which match the arguments in the script exactly:

    SourceId = oArgs(0) = $MPElement$
    ManagedEntityId = oArgs(1) = $Target/Id$
    DiscoveryErrorEventId = CInt(oArgs(2)) = 7102
    TargetComputerID = oArgs(3) = $Config/ComputerID$
    TargetComputer = oArgs(4) = $Config/ComputerName$
    ConnectionString = oArgs(5) = $Config/SQLConnectionString$
    InstanceName = oArgs(6) = $Config/SQLInstanceName$
    ExcludeList = Mid(oArgs(7), 9) = $Config/ExcludeList$
    TcpPort = oArgs(8) = $Target/Property[Type="SQL!Microsoft.SQLServer.DBEngine"]/TcpPort$

    One thing I see in your screenshots is the runas account that is targeted to "All targeted objects". This actually means that the account will be used on ALL classes in your management group.
    If the account only has permissions on your SQL servers this should start alerting on all of your non SQL servers with an alert about Health Service Credentials, but that's a different issue.

    In my opinion and based on your screenshot you're missing classes and/or groups in your targeting. In your screenshot I can only see the Discovery account. According to the guide you need this:

      
      

    Run As Profile

      
      

    Targets

      

    SQL Server   Discovery Account

    Classes:

    • Windows Server
        SQL Server 2005 DB Engine
    • SQL Server 2008 DB Engine
    • SQL Server 2012 DB Engine
    • SQL Server 2005 Agent
    • SQL Server 2008 Agent
    • SQL Server 2012 Agent
    • SQL Server 2008 Mirroring DB
    • SQL Server 2008 Mirroring Witness
    • SQL Server 2012 Mirroring DB
    • SQL Server 2012 Mirroring Witness

    Groups:

    • SQL 2005 Computers
    • SQL 2008 Computers
    • SQL 2012 Computers
    • SQL Server 2008 Mirroring Group
    • SQL Server 2012 Mirroring Group
    • SQL 2005 DB Engine Group
    • SQL 2008 DB Engine Group
    • SQL 2008 R2 DB Engine Group
    • SQL 2012 DB Engine Group

    Most of them you targeted to the group instead of the class. I'm missing almost all classes. You have to follow this part in the guide line for line otherwise you'll get errors like you have now.

    Hello,

    Yes you are correct the classes were missing, they had been added 01/08/2016:

    But the error is still here and tested on one server only so far due to the noise... I removed the RunAs Account targeting "All monitored objects" as hopefully the list of classes and groups above would be enough...

    It was the account used previously to 6.0 MP and it was the Mom Action account having Windows and SQL accesses usually as I noticed some DBAs removed it...

    I will try to run the script again manually on one client...

    Thanks,

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager


    • Edited by Felyjos Tuesday, January 12, 2016 2:16 PM
    Tuesday, January 12, 2016 2:15 PM
  • One small thing why you started seeing this since version 6.6.4.0 is because the Alert "MSSQL 20XX: Discovery failed" is coming from a new rule that is added in version 6.6.2.0. You probably already had this issue, only you were never aware of it.

    October, 2015 (version 6.6.2.0)

    Added new type of events from failed discoveries; added a new rule that collects such events


    Correct I never imported 6.6.2.0...

    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

    Tuesday, January 12, 2016 2:17 PM
  • No need to run the script manually. This is most likely still an issue with your targeting and if you want to make sure that it runs the discovery again you could flush the cache of the agent or force the discovery to run again via the task in the console. Option 1 is usually the easiest one, but try that on a test server to prevent loss of monitoring.

    You still have some items in your run as account profile that shouldn't be here. I'm not saying that this is the cause of the issue, but they don't belong in here. Everything related to SQL Server 2014 must be removed as they have their own run as profiles. Only 2005, 2008 and 2012 must be configured in the "SQL Server * Account" profiles. All of the yellow one are correct.

    Also check the distribution of the run as account. Is that set to "More secure" and are the SQL servers added to the distribution list? After you configured all of this correctly I recommend to flush the cache on a test server and check the Operations Manager eventlog on that server to see what happens. Post the results here.

    Tuesday, January 12, 2016 2:43 PM
  • Distribution is set to "Less Secure"

    Cache Flushed from the console

    but I am getting an error:

    I will clear it manually:

    Stop HealthService

    Rename HealthState Folder

    Start HealthService

    The Alert is still here!!!


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager




    • Edited by Felyjos Wednesday, January 13, 2016 6:23 AM
    Tuesday, January 12, 2016 4:48 PM
  • The alert will not close automatically as this is coming from a rule (and not a monitor). Please check the last modified date and repeat count of the alert to determine if there is still an actual issue. You also need to have a look in the Operations Manager eventlog of one of the servers having this issue to see what additional events are raised. They can probably provide more information.

    Another thing (but not causing the issue) is the fact that your account is set to less secure. I cannot recommend this. This setting means that the run as account is distributed to all machines in your management group. This also means all systems that don't have SQL installed. Those systems have no need to be aware of the user credentials of this run as account. This also means that the account must be able to logon to these systems as otherwise they will report the alert "Run As Account Cannot Log On Locally".

    After you set it to More Secure, press the Add button. Change the Option to "Show suggested computer" and press search. If you set your targeting right the list should only display servers that have SQL installed. If you see others as well, then you know for sure that your targeting is incorrect.

    If you want to flush the cache via the console you need to open a view to the correct class (Health Service). This gives you the task you require.

    NOTE: please use the Operations Manager eventlog of one of the SQL servers having this issue to speed up your investigation. This will help you a lot.

    Wednesday, January 13, 2016 6:52 AM
  • Hello,

    I closed the rule and the alerts came back right away with a count of 40+... last modified today ...

    I pass the accounts in more secure but I am getting a lot of alerts for the "Non-SQL" which might have received the rule before:

    Alert: Health Service Credentials Not Found Alert Message
    
    Resolution state: New
    
    Source: BOPFSCL2A.adPath:                                                    BOPFSCL2A.ad
    
    Last modified by: System
    Last modified time: 1/13/2016 7:01:07 AM
    
    Alert description: An account specified in the Run As profile "Microsoft.SQLServer.SQLDiscoveryAccount" cannot be resolved.
    
    This condition may have occurred because the account is not configured to be distributed to this computer. To resolve this problem, you need to open the Run As profile specified below, locate the account entry as specified by its SSID, and either choose to distribute the account to this computer if appropriate, or change the setting in the profile so that the target object does not use the specified account.
    Note: you may use the command shell to get the Run As account display name by its SSID.
    
    Management Group:  SCOM-MED
    Run As Profile:  Microsoft.SQLServer.SQLDiscoveryAccount
    Account SSID:  00707C686769F3C0C9897403644020F428FEC723C300000000000000000000000000000000000000
    
    
    this seems there is a cleanup to do? Is it manual or automatic when the server will connect to get the MPs?

    Cleaning the cache now...

    Thanks,

    Dom


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

    Wednesday, January 13, 2016 3:19 PM
  • the Flush Health Service State and cache failed


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

    Wednesday, January 13, 2016 5:33 PM
  • pending the resolution of another issue to correct this one

    https://social.technet.microsoft.com/Forums/en-US/148f9d34-11e9-47fc-a7aa-6f50b4a6d002/remove-mp-from-clients?forum=operationsmanagergeneral


    System Center Operations Manager 2007 / System Center Configuration Manager 2007 R2 / Forefront Client Security / Forefront Identity Manager

    Thursday, January 21, 2016 3:33 PM