locked
SQL monitoring of standby/read-only databases issue RRS feed

  • Question

  • We received alarms as below for SQL standby/read-only databases. Monitoring of standby/read-only database not possible??

    jkaur27 <MG> 3 1 Network MicrosoftSQLServerDBEngine <hostname>/<MG> RunAsAccountdoesnotexistonthetargetsystemordoesnothaveenoughpermissions Wed Dec 18 20:26:26 IST 2019 Thu Jan 01 05:30:00 IST 1970 Wed Dec 18 20:26:26 IST 2019 Management Group <MG> Script GetSQL2012DBFilesFreeSpacevbs  Cannot login to database <hostname><instance>xms_<database> :AlertID 7ab540422a4c47048295030b7cda58bd:ManagementGroupName <MG>

    Thursday, December 19, 2019 6:43 AM

Answers

  • Hi,

    Yes it should be possible, the alert is pretty self-explaining, the Run As Account you are using for your SQL either doesn’t exist on the SQL server/instance/database or it doesn’t have sufficient permission.

    SCOM is able to monitor the following SQL Server features:

    • SQL Server Database Engines (supported editions: Enterprise, Developer, Standard, Express,
      Evaluation)
    • SQL Server Databases (including filegroups, data files, and transaction log files)
    • SQL Server Always On Availability Groups
    • SQL Server Always On Distributed Availability Groups
    • SQL Server Memory-Optimized Tables (SQL Server In-Memory OLTP)

    Have you followed the SQL Server management pack guide for setting up the configuration for monitoring your SQL environment? I suggest you go through it and view the "Mandatory Configuration" section.

    You'll find it over here: SQLServerMPGuide.pdf

    I also highly suggest you have a look at Kevin's blog post about SQL MP Run As Accounts – NO LONGER REQUIRED

    Best regards,
    Leon


    Blog: https://thesystemcenterblog.com LinkedIn:

    • Proposed as answer by CyrAz Thursday, December 19, 2019 7:46 AM
    • Marked as answer by Stoyan ChalakovMVP Sunday, January 19, 2020 9:13 PM
    Thursday, December 19, 2019 7:41 AM
  • That's correct, local system with SA permission shouldn't need any additional configuration; but Leon is also right when he says the error message is pretty clear...

    If you check in the logs from SQL server itself, you should find error messages there as well and they may provide more details

    Thursday, December 19, 2019 8:08 AM

All replies

  • Hi,

    Yes it should be possible, the alert is pretty self-explaining, the Run As Account you are using for your SQL either doesn’t exist on the SQL server/instance/database or it doesn’t have sufficient permission.

    SCOM is able to monitor the following SQL Server features:

    • SQL Server Database Engines (supported editions: Enterprise, Developer, Standard, Express,
      Evaluation)
    • SQL Server Databases (including filegroups, data files, and transaction log files)
    • SQL Server Always On Availability Groups
    • SQL Server Always On Distributed Availability Groups
    • SQL Server Memory-Optimized Tables (SQL Server In-Memory OLTP)

    Have you followed the SQL Server management pack guide for setting up the configuration for monitoring your SQL environment? I suggest you go through it and view the "Mandatory Configuration" section.

    You'll find it over here: SQLServerMPGuide.pdf

    I also highly suggest you have a look at Kevin's blog post about SQL MP Run As Accounts – NO LONGER REQUIRED

    Best regards,
    Leon


    Blog: https://thesystemcenterblog.com LinkedIn:

    • Proposed as answer by CyrAz Thursday, December 19, 2019 7:46 AM
    • Marked as answer by Stoyan ChalakovMVP Sunday, January 19, 2020 9:13 PM
    Thursday, December 19, 2019 7:41 AM
  • We used the default ID NTAuthority\System with SA permission, it shouldn't have any issue accessing it right?

    Thursday, December 19, 2019 7:53 AM
  • That's correct, local system with SA permission shouldn't need any additional configuration; but Leon is also right when he says the error message is pretty clear...

    If you check in the logs from SQL server itself, you should find error messages there as well and they may provide more details

    Thursday, December 19, 2019 8:08 AM
  • Hi,
     
    Agree with CyrAz, we can check the SQL logs to see more details. Also after doing research, it can also related to the auto-close attribute set on the database and hence is closed when SCOM tries to log in it. A similar thread for your reference:

    https://social.technet.microsoft.com/Forums/systemcenter/en-US/0f955ea6-be07-4191-9d98-384e0e091dba/sql-monitoring-of-standbyreadonly-databases-issue?forum=operationsmanagermgmtpacks

     
    Hope it can help.
     
    Best regards.
    Crystal

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

    Friday, December 20, 2019 1:43 AM
  • Hi,
     
    I noticed that you have not updated for several days, is your issue solved or is there any update? Feel free to feedback.
     
    Best Regards,
    Ray

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

    Monday, December 23, 2019 8:47 AM
  • Hi,
     
    Here is a summary for your issue.

     
    Issue Symptoms
    ===================
    RunAsAccountdoesnotexistonthetargetsystemordoesnothaveenoughpermissions
     
    Possible Causes
    ===================
    Account Issue
     
    Action Plan
    ===================
    Check the IIS log.

     
    If there is any update, feel free to feedback.
     
    Best Regards,
    Ray


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

    Thursday, December 26, 2019 10:52 AM