locked
SQL Server RunAs Addendum Management Pack by Kevin Holman RRS feed

  • Question

  • Hi Guys,

    In order to make thigs easier with Configuring Run As accounts for SQL servers discovered any monitored by SCOM, we implemented SQL Server RunAs Addendum Management Pack. It is a very good Management Pack by Kevin. But after we have imported this Management Pack we are regulary receiving MSSQL: Discovery failed and MSSQL: Monitoring warning/failed alerts from Agent Managed SQL servers. I dont find any solution for this alerts. Your advice will be helpful.

    The Alert messages are as follows:

    MSSQL: Discovery failed

    Event ID: 7105. Management Group: XXXX. Script: DiscoverSQL2016FileGroups.js. Version: 7.0.15.0. Instance: XXXX : File Groups Discovery script 'DiscoverSQL2016FileGroups.js' for instance 'XXXX' failed.
    Call stack:Exception.constructor(File Groups Discovery script 'DiscoverSQL2016FileGroups.js' for instance 'XXXX' failed.,Can't execute query ' SET NOCOUNT ON USE [XXXXXXXXXX] SELECT fg.name as fileGroupName, fg.data_space_id as fileGroupId, fg.is_read_only as fileGroupReadOnly, fg.type as fileGroupType, fg.type_desc as fileGroupTypeDesc FROM sys.filegroups fg': [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal "NT AUTHORITY\SYSTEM" is not able to access the database "XXXXXXXXXX" under the current security context.

    MSSQL: Monitoring warning

    Event ID: 4211. Management Group: XXXX. Script: GetSQL2016DBFreeSpace.vbs. Version: 7.0.15.0 : The next errors occurred:
    Cannot connect to database 'XXXXXXXXXX'
    Error Number: -2147467259
    Description: [Microsoft][SQL Server Native Client 11.0][SQL Server]The server principal "NT AUTHORITY\SYSTEM" is not able to access the database "XXXXXXXXXX" under the current security context.
    Instance: XXXX

    Full Path Name:    XXXX
    Alert Rule:    MSSQL 2016: Monitoring warning
    Created:    2/6/2020 1:27:17 AM
    Event ID: 4211. Management Group: XXXX. Script: GetSQL2016DBFreeSpace.vbs. Version: 7.0.15.0 : The next errors occurred:
    Cannot connect to database 'XXXX'
    Error Number: -2147467259
    Description: [Microsoft][SQL Server Native Client 11.0]Database is invalid or cannot be accessed
    Instance: XXXX

    Regards,

    Sreejeet

    Thursday, February 6, 2020 2:20 PM

Answers

  • Read SRNONE's reply on Kevin's blog post:

    https://kevinholman.com/2016/08/25/sql-mp-run-as-accounts-no-longer-required

    He's also having discovery issues like you Sreejeet, check Kevin's reply:

    "These are required for Service SID’s to work for the SCOM HealthService and SQL Monitoring:

    Health Service SID needs to be enabled in the registry. (ServiceSidType = 1)
    Health Service must be restarted in order for SID’s to work after enabling service SID in registry.
    A SQL login must be created for NT SERVICE\HealthService at the instance level.
    The proper rights must be granted to NT SERVICE\HealthService at the instance level. (low priv script or sysadmin)
    Each database to be monitored must have a User present of NT SERVICE\HealthService at the individual database level (if not sysadmin)
    NT AUTHORITY\SYSTEM when present as a SQL login, must NOT be set to “Login:Disabled” in the SQL status for the Login.
    NT AUTHORITY\SYSTEM does not need to be present on stand alone SQL servers as a SQL login, but is required for Clusters and AlwaysOn

    If you are still getting alert errors for MSSQL on Windows: Discovery error or MSSQL on Windows: Monitoring error then check to ensure the problem is rights. Verify that you did not miss any steps in enabling service SID, the agent was restarted, and ALL required security configurations are met above.

    Since you are getting the error from WMI – I’d start there. What does that WMI query return when you run it manually?"


    Blog: https://thesystemcenterblog.com LinkedIn:


    Thursday, February 6, 2020 3:04 PM
  • Hi Sreejeet,
     
    Agree with Leon, it seems the issue is the same as yours. Please try the suggestion Kevin provided in the reply to see if it is working.
     
    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, February 7, 2020 2:07 AM

All replies

  • Hi,

    There are two (2) configurations that needs to be done after importing the management pack:

    1. Enable the HealthService to be able to use a service SID.

    2. Create a login in SQL for the HealthService SID to be able to access SQL server.

    Kevin covers everything here:

    https://kevinholman.com/2016/08/25/sql-mp-run-as-accounts-no-longer-required

    Best regards,
    Leon


    Blog: https://thesystemcenterblog.com LinkedIn:

    Thursday, February 6, 2020 2:26 PM
  • Hi Leon, How are you?

    Thanks for your reply!

    We have configured this Management Pack as per the steps mentioned by Kevin.

    1. HealthService SID is enabled.
    2. Login in SQL for the HealthService SID also created with Low Priv.... rights and not SA rights.

    We do not get this alerts from all the SQL Servers, but only from few of the SQL Servers.

    Thanks,

    Sreejeet

    Thursday, February 6, 2020 2:41 PM
  • Perhaps the low privilege isn't sufficient for all databases? Maybe some databases have more hardening and might require high privileges, did you try using high privileges instead?


    Blog: https://thesystemcenterblog.com LinkedIn:

    Thursday, February 6, 2020 2:43 PM
  • Cannot try high privilege in our environment due to security policy.

    Also I noticed the message in the Alert description "The server principal "NT AUTHORITY\SYSTEM" is not able to access the database "XXXXXXXXXX" under the current security context."

    Since we are now using Health Service Login to discover and monitor SQL servers why is the NT AUTHORITY\SYSTEM used to access the database. Any idea?


    • Edited by Sreejeet Thursday, February 6, 2020 2:50 PM add more comment
    Thursday, February 6, 2020 2:47 PM
  • Can you check the SQL logs in SQL MAnagement Studio? It may show you more precisely what's happening
    • Edited by CyrAz Thursday, February 6, 2020 2:59 PM
    Thursday, February 6, 2020 2:59 PM
  • Hi CyrAZ,

    Sure I will ask our DBA team to check the SQL logs as I am not so good in SQL.

    I will keep you all posted if we find something in the logs.

    Thanks,

    Sreejeet

    Thursday, February 6, 2020 3:03 PM
  • Read SRNONE's reply on Kevin's blog post:

    https://kevinholman.com/2016/08/25/sql-mp-run-as-accounts-no-longer-required

    He's also having discovery issues like you Sreejeet, check Kevin's reply:

    "These are required for Service SID’s to work for the SCOM HealthService and SQL Monitoring:

    Health Service SID needs to be enabled in the registry. (ServiceSidType = 1)
    Health Service must be restarted in order for SID’s to work after enabling service SID in registry.
    A SQL login must be created for NT SERVICE\HealthService at the instance level.
    The proper rights must be granted to NT SERVICE\HealthService at the instance level. (low priv script or sysadmin)
    Each database to be monitored must have a User present of NT SERVICE\HealthService at the individual database level (if not sysadmin)
    NT AUTHORITY\SYSTEM when present as a SQL login, must NOT be set to “Login:Disabled” in the SQL status for the Login.
    NT AUTHORITY\SYSTEM does not need to be present on stand alone SQL servers as a SQL login, but is required for Clusters and AlwaysOn

    If you are still getting alert errors for MSSQL on Windows: Discovery error or MSSQL on Windows: Monitoring error then check to ensure the problem is rights. Verify that you did not miss any steps in enabling service SID, the agent was restarted, and ALL required security configurations are met above.

    Since you are getting the error from WMI – I’d start there. What does that WMI query return when you run it manually?"


    Blog: https://thesystemcenterblog.com LinkedIn:


    Thursday, February 6, 2020 3:04 PM
  • Hi Sreejeet,
     
    Agree with Leon, it seems the issue is the same as yours. Please try the suggestion Kevin provided in the reply to see if it is working.
     
    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, February 7, 2020 2:07 AM
  • Hi Sreejeet,

    How's everything going? Is there any update on our issue?If yes, feel free to let us know.

    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.

    Wednesday, February 12, 2020 7:06 AM
  • Hi Crystal,

    Thank you!

    I was out of office so could not reply back.

    Update: I have asked our DBA team to check the last 5 points mentioend by Kevin to check.

    First two point were cecked by SCOM team and from SCOM side everything is fine.

    Waiting for their reply from DBA team.

    I have one query: Kevin asled to check "The proper rights must be granted to NT SERVICE\HealthService at the instance level. (low priv script or sysadmin)".

    We have given NT SERVICE\HealthService low privilege access to SQL Servers by running the script provided by Kevin Holman. But what are the exact rights this account will get as low privilege, if we get that list we can check that against all the DBs being monitoried.

    Thanks,

    Sreejeet

    Wednesday, February 12, 2020 11:49 AM
  • Thank you Leon for this information!
    Wednesday, February 12, 2020 11:49 AM
  • You can check what privileges the "low privilege mode" gives in the SQL Server management pack guide: SQLServerMPGuide.pdf 

    Under the "Security Configuration of Management Pack" section head to the "Low-Privilege Monitoring" section.


    Blog: https://thesystemcenterblog.com LinkedIn:

    Wednesday, February 12, 2020 11:53 AM
  • Yes, that is the plan, we will check, thank you for the update Leon.
    Wednesday, February 12, 2020 11:56 AM
  • Hi Sreejeet,

    Thanks for your reply. I notice you will check the permission. If there's any update, feel free to post here.

    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.

    Thursday, February 13, 2020 6:10 AM
  • Yes, that is the plan, we will check, thank you for the update Leon.

    Hi Sreejeet,

    were you able to follow all the suggestions, provided by the guys here? 

    We would love to hear the feedback. Thanks in advance!

    Regards,


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov

    Wednesday, February 19, 2020 1:03 PM
  • Hi Stoyan,

    I have informed our DBA team to check that the SQL servers are configured as mentioned by Kevin. I am waiting for their reply.

    I will update you when I get a response from them.

    Thanks,

    Sreejeet

    Wednesday, February 19, 2020 1:14 PM
  • Hi Sreejeet,

    thank you, much appreciated!

    We will wait for your feedback!

    Regards,


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov

    Wednesday, February 19, 2020 1:28 PM
  • So grateful to you all, for always providing me with advice, suggestions and solutions!

    Wednesday, February 19, 2020 1:48 PM
  • So grateful to you all, for always providing me with advice, suggestions and solutions!

    And we will continue doing this, I am pretty sure I also can speak for Leon, Crystal and CyrAz and all the other guys, who help others in their spare time. :)

    Regards,


    (Please take a moment to "Vote as Helpful" and/or "Mark as Answer" where applicable. This helps the community, keeps the forums tidy, and recognizes useful contributions. Thanks!) Blog: https://blog.pohn.ch/ Twitter: @StoyanChalakov

    Wednesday, February 19, 2020 1:50 PM