none
MS SQL 2012 : Discovery Failed RRS feed

  • Question

  • Hi,

    I am getting repetitive critical alerts with the following information:

    Alert Rule : MSSQL 2012: Discovery failed

    Alert Description :

    DiscoverSQL2012MirroringWitness.vbs. Instance: MSSQLSERVER : Mirroring witness discovery script 'DiscoverSQL2012MirroringWitness.vbs' for instance 'MSSQLSERVER' failed.

    Please need assistance to rectify this issue.

    Please note, we have configured the 'Run As Account' for SQL Server Default Action Account, SQL Server Discovery & Monitoring Account.

    MS SCOM Version : 2012 R2

    SQL MP version : 6.6.4.0

    Thank you.

    Wednesday, December 9, 2015 12:35 AM

Answers

  • After raising a ticket with Microsoft this is the solution given:

    1. Create a new SQL Monitoring account and associated them with the 3 SQL Run As Profiles (SQL Server Default Action Account, SQL Sever Discovery Account, SQL Server Monitoring Account). (I used the SCOM default action account)
    2. Set the account as More Secure account and add the agent to the Account’s Distribution list
    3. Add the servers to the Distribution list of the SQL monitoring account
    4. Add the SQL monitoring account to the Computer’s “Performance Monitor Users”, “Event Log Readers” and “Users” group
    5. Ensure the SQL monitoring account is the computer’s local administrator and is a sysadmin to SQL server

    And here is the way to do Point 4 above

    Here is the way to add the SQL monitoring account to the computer’s “Performance Monitor Users”, “Event Log Readers” and “Users” group.

    1. Run cmd as administrator
    2. Run lusrmgr.msc, click groups
    3. Locate the target group, such as Performance Monitor Users, double click the group
    4. Click Add and then Add the user to the group, click OK

    Cheers,

    John Bradshaw


    • Marked as answer by Yan Li_Moderator Friday, March 11, 2016 8:50 AM
    • Edited by bradje Monday, March 14, 2016 2:15 AM
    Thursday, March 10, 2016 7:42 PM

All replies

  • Hello,

    Do you see the below error message?

    DiscoverSQL2012MirroringWitness.vbs

      Query execution failed for SQL instance 'xxxxx'. The SELECT permission was denied on the object 'database_mirroring_witnesses', database 'xxxxxx', schema 'sys'.

    If you have above error message, you can try to grant select permission on sys.database_mirroring_witnesses to the action account. Basically without querying this table it is not possible to understand if the mirroring witness is configured on a given instance.

    You can choose to give select right or disable the discovery.

    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, December 9, 2015 8:39 AM
    Moderator
  • Hi Yan,

    Thank you for your response.

    I do not see the error message mentioned by you. Also, I ensured that the 'Run As' account has the privilege "grant select permission on sys.database_mirroring_witnesses" but the issue persists.

    Please suggest, how to proceed with the same.

    Thank you.




    Thursday, December 10, 2015 12:27 AM
  • Same problem here. And it cant be a action/run as account problem since it was working prior to the import of 6.6.4.0.

    6.6.0.0 worked, and so did 6.6.2.0 until it went crazy caused by the bug in that version:

    http://blogs.msdn.com/b/sqlreleaseservices/archive/2015/10/16/released-system-center-management-pack-for-sql-server-and-replication-6-6-2-0.aspx
    Tuesday, December 15, 2015 9:44 AM
  • I have the exact same error message today after importing 6.6.4:

    Please provide the following information to the support engineer if you have to contact Microsoft Help and Support :

    Microsoft.EnterpriseManagement.Presentation.DataAccess.DataProviderException: An error occurred executing the command: [Microsoft.EnterpriseManagement.Management.DataProviders!ManagedEntityProvider/GetManagedEntities] in provider: [Microsoft.EnterpriseManagement.Management.DataProviders.ManagedEntityProvider, Microsoft.EnterpriseManagement.Management.DataProviders, Version=7.0.5000.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35].The client has been disconnected from the server. Please call ManagementGroup.Reconnect() to reestablish the connection. ---> Microsoft.EnterpriseManagement.Common.ServerDisconnectedException: The client has been disconnected from the server. Please call ManagementGroup.Reconnect() to reestablish the connection. ---> System.ServiceModel.CommunicationObjectFaultedException: The communication object, System.ServiceModel.Channels.ServiceChannel, cannot be used for communication because it is in the Faulted state.

    Server stack trace: 
       at System.ServiceModel.Channels.CommunicationObject.ThrowIfDisposedOrNotOpen()
       at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
       at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
       at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

    Exception rethrown at [0]: 
       at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
       at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)
       at Microsoft.EnterpriseManagement.Common.Internal.IDispatcherService.DispatchUnknownMessage(Message message)
       at Microsoft.EnterpriseManagement.Common.Internal.ManagementPackServiceProxy.GetManagementPacksByCriteria(String criteria, String languageCodes)
       --- End of inner exception stack trace ---
       at Microsoft.EnterpriseManagement.Monitoring.DataProviders.RetryCommandExecutionStrategy.Invoke(IDataProviderCommandMethodInvoker invoker)
       at Microsoft.EnterpriseManagement.Presentation.DataAccess.DataProviderCommandMethod.Invoke(CoreDataGateway gateWay, DataCommand command)
       --- End of inner exception stack trace ---
       at Microsoft.EnterpriseManagement.Presentation.DataAccess.DataProviderCommandMethod.Invoke(CoreDataGateway gateWay, DataCommand command)
       at Microsoft.EnterpriseManagement.Presentation.DataAccess.CoreDataGateway.ExecuteInternal[TResult](DataCommand command)
       at Microsoft.EnterpriseManagement.Presentation.DataAccess.CoreDataGateway.<ExecuteAsync>b__0[TResult](<>f__AnonymousType0`1 data)
    Wednesday, December 16, 2015 2:48 PM
  • I have setup my run as account as low priveliged.

    And that worked with 6.6.0.0 (and prior) and 6.6.2.0 but not with 6.6.4.0

    If I made my run as account sysadmin the error went away. So it looks like that the account needs more rights on the sql after 6.6.4.0.

    Wednesday, December 16, 2015 2:53 PM
  • Are you referring to the SCOM SQL DB? If so, that's a non-starter for us.
    Wednesday, December 16, 2015 2:55 PM
  • I was refering to the SQL server that the scom agent where trying to run a discover on.

    Sysadmin is not a prefered approach we would like to continue with low priveliged run as/discovery accounts.

    But I have not found out what extra rights are needed since 6.6.4.0

    You can also take a look at my post here:

    https://social.technet.microsoft.com/Forums/en-US/51a7ac65-bde4-4d82-ba2b-3005aab0a638/dbfilegroupdiscoveryrule-failed-for-sql-mp-6640?forum=operationsmanagergeneral

    Wednesday, December 16, 2015 2:58 PM
  • I gather this has not been resolved yet???

    I just imported 6.6.4 and am now getting the same error.

    Anyone got a workaround?

    Thx,

    John Bradshaw

    Sunday, February 14, 2016 8:02 PM
  • I just imported 6.6.4 and am now getting the same error.

    Anyone got a workaround?

    I get the below error too after importing the 6.6.4.0 SQL MP -

    (1) Management Group: SCOM Management Group. Script: DiscoverSQL2012DB.vbs. Instance: MSSQLSERVER : SQL Database discovery script 'DiscoverSQL2012DB.vbs' for instance 'MSSQLSERVER' failed.

    (2) Management Group: SCOM Management Group. Script: SQLAgentJobDiscovery.vbs. Instance: MSSQLSERVER : SQL Agent Job discovery script 'SQLAgentJobDiscovery.vbs' for SQL instance 'MSSQLSERVER' failed.


    Thanks, S K Agrawal

    Thursday, February 18, 2016 1:49 PM
  • Hi Everyone,

    Be aware that when you move from previous SQL MP Version to 6.6.4.0 you have to confirm the changes on the MP Guide.

    SQL MP version 6.6.4.0 requires some extra permissions when using low privilege Accounts.

    Cheers

    Thursday, February 18, 2016 10:14 PM
  • This is an issue. Has it been acknowledged by Microsoft as a bug yet?

    On our SQL servers we have given SA access to the scom default action account and still the problem persists.

    It wasn't there in previous SQL MP versions. Some Microsoft guys must have seen this in the field by now and have a fix for it....Can the fix pls be shared?

    Thx,

    John Bradshaw

    Tuesday, March 1, 2016 9:51 PM
  • After raising a ticket with Microsoft this is the solution given:

    1. Create a new SQL Monitoring account and associated them with the 3 SQL Run As Profiles (SQL Server Default Action Account, SQL Sever Discovery Account, SQL Server Monitoring Account). (I used the SCOM default action account)
    2. Set the account as More Secure account and add the agent to the Account’s Distribution list
    3. Add the servers to the Distribution list of the SQL monitoring account
    4. Add the SQL monitoring account to the Computer’s “Performance Monitor Users”, “Event Log Readers” and “Users” group
    5. Ensure the SQL monitoring account is the computer’s local administrator and is a sysadmin to SQL server

    And here is the way to do Point 4 above

    Here is the way to add the SQL monitoring account to the computer’s “Performance Monitor Users”, “Event Log Readers” and “Users” group.

    1. Run cmd as administrator
    2. Run lusrmgr.msc, click groups
    3. Locate the target group, such as Performance Monitor Users, double click the group
    4. Click Add and then Add the user to the group, click OK

    Cheers,

    John Bradshaw


    • Marked as answer by Yan Li_Moderator Friday, March 11, 2016 8:50 AM
    • Edited by bradje Monday, March 14, 2016 2:15 AM
    Thursday, March 10, 2016 7:42 PM
  • Hi John:

    Thank you so much for the updating the solution in the forum.

    Regards,

    Srinivas


    Thursday, March 10, 2016 10:52 PM
  • I don't get why would your SQL monitoring account would need SYSADMIN rights. If you SQL monitor account gets hijacked they would also have SYSADMIN rights on all your databases. 

    Friday, September 9, 2016 1:46 AM
  • Late response. But what if your SQL server also serves as a DC? The snap-in cant be used on a DC (you cant make the account local admin neither).
    Friday, June 16, 2017 12:36 PM
  • You don't need to give admin rights or local admin for that matter. Read the SQL guides for the rights needed.  Bradje's point 4 isn't needed at all when you make the monitoring user local admin.

    My tips:

    - read kevin holman's blogSSSS about this matter.

    - if you want to set it up according to the guide, make a matrix of rights needed for the 3 user profiles and come to the conclusion there's really not much of a difference (except if u want to run tasks from scom) > create 1 user account for all 3 profiles and skip the tasks rights.


    Rob Korving
    http://jama00.wordpress.com/

    Monday, July 3, 2017 11:28 AM
  • Thx for that Rob.

    I remember when I logged that call with Microsoft that the people on the job did not fill me with confidence.

    But I think a more important point is that the process is still not clear about how to set up the SQL MP. Some get it, many do not. When people "live" with SCOM, that's one thing, but something like this should be equally clear for novices to SCOM and it is not. But I don't know how to escalate this for a re-write of the documentation.

    Monday, July 3, 2017 7:26 PM