Menu Close

SQL MP 7.0.32.0 brings new features and security requirements

We recently released the SQL MP 7.0.32.0 available here:  Download

This MP brings some features requested by users, but also will require updating the security configuration on all your SQL servers to support these features.

 
What’s New
  • Added a new exclude list option for the “SQL Server Agent Job” discovery
  • Updated the “Database Status” monitor. Added a new override ‘Disable if Availability Group is offline’ to avoid false positives in high-availability environments
  • Updated the “Database Backup Status” monitor. Added a new override ‘Track Availability Group Backup Preferences’ to instruct the monitor to track the backup location configured in the Availability Group backup preferences
  • Updated override for the “Service Principal Name Configuration Status” monitor. The ‘Interval’ value is now set to 3600 (1 hour) to avoid an alert storm in multiple domain controller environments
  • Updated overrides for the “WMI Health State” monitor. The ‘Interval’ value is now set to 3600 (1 hour) and the ‘Samples count’ value is set to 2
  • Updated the “Virtual Log File Count” monitor. Now it uses the ‘sys.dm_db_log_info’ view instead of DBCC on SQL Server 2016 and higher
  • Renamed some Dashboard tiles
  • Added new rules targeted to the SQL Server Agent Job: “SQL Server Agent Job Duration Alert Rule” and “SQL Server Agent Job Duration” performance rule
  • Updated the following space related discoveries: “DB Filegroups”, “DB Files”, “Transaction Log File”, “FILESTREAM Filegroups”, “Memory-Optimized Data Filegroup”, and “Memory-Optimized Data Filegroup Containers”. Now they are disabled by default to reduce the load on the environment
  • Updated the “Summary Dashboard” views. Now they also contain tiles for the SQL Server Integration Services
  • Updated the “Product Version Compliance” monitor with the most recent version of the public updates for SQL Server
  • Updated display strings
Bug Fixes
  • Fixed an issue with continuous login attempts from passive SQL Server cluster node after the failover
  • Fixed an issue with the failing “SQL Server Agent Jobs” discovery in cases of unsupported ASCII characters in job names—added proper error handling
  • Fixed performance data collection for SQL Server DB Engines with Latin1_General_CP850_BIN collations
  • Fixed an issue with the incorrect alert name for the “LOG Free Space Left” monitor
  • Fixed an issue with the “Blocking Sessions” monitor that was enabled by default
  • Fixed an issue with the lost ‘Timeout’ override for the “WMI Health State” monitor
  • Fixed an issue with the wrong alert parameter reference in the “Summary Dashboard” view tiles

 

The new feature of being able to filter the Agent Job discovery by name, does bring some new security requirements for your action account in the Low Priv model (a best practice).  Specifically:

USE [msdb]

GO

GRANT EXECUTE ON SQLAGENT_SUSER_SNAME TO [LowPrivAccount]

GRANT SELECT ON sysjobs_view TO [LowPrivAccount]

GRANT SELECT ON syscategories TO [LowPrivAccount]

I have added these to the updated 7.0.32.0 version of the SQL RunAs Config Addendum:

SQL MP Run As Accounts – NO LONGER REQUIRED

GitHub Download

If you do not make these changes, the SQL agent job discovery will start failing and you will see alerts in SCOM:

MSSQL on Windows: Discovery error

Management Group: “OM”
Module: Microsoft.SQLServer.Windows.Module.Discovery.Discoveries.AgentJobDiscovery
Version: 7.0.32.0
Error(s) was(were) occurred:
Message: An error occurred during discovery.

———- Exception: ———-
Exception Type: System.Data.SqlClient.SqlException
Message: The EXECUTE permission was denied on the object ‘SQLAGENT_SUSER_SNAME’, database ‘msdb’, schema ‘dbo’.
Number: 229
Source: .Net SqlClient Data Provider

 

 

SQL Agent Job Discovery

SQL Agent Jobs are not discovered by default.  We monitor SQL Agent Jobs through the SQL Agent object out of the box.  Sometimes customers wish to monitor each job separately and want different settings on a per-job basis.  I explain this process in detail here:  Monitoring SQL Agent Jobs in SCOM – Kevin Holman’s Blog

In this updated MP, there is a new override-able parameter that allows further filtering of SQL Agent Jobs.

The discovery is named “MSSQL on Windows: Discover SQL Server Agent Jobs”.

image

 

The new override parameter is named “Exclude List”

image

 

Here is the description:

Parameter Description:
A comma-separated list of SQL Server agent jobs that must be excluded from discovery. Use a wildcard character ‘*’ to exclude all agent jobs. Otherwise, use conditions like ‘*_test’ to exclude agent jobs, the names of which end with _test, ‘Test*’ to exclude agent jobs, the names of which start with Test, or a ‘*test*’ condition to exclude agent jobs that have a test entry in any part of the job’s name.

It actually goes beyond that – you can also use multiple wildcards, something like *-*-*-* 

I’ll demonstrate.

I have 186 SQL Agent Jobs in my lab.  However, 116 of these agent jobs are default jobs created by the SCOM Reporting role, and I do not wish to discover nor monitor these. 

image

 

Since they are defined with a GUID as a name… the hyphens are the only static characters.  Therefore – I can use the following filter that contains 4 hyphens”  *-*-*-*-*

image

That *undiscovered* any SQL Agent Job with 4 hyphens in it.  Cool!

If you have any SQL agent jobs that you do not wish to monitor, you should consider undiscovering (excluding) them.  Lots of agent job monitoring adds load to the agent machine and the SCOM management group.  This is a great new feature!

4 Comments

  1. Erik Suiveer

    Hi Kevin,

    We’ve tried the task Creat HealthService Login as Low Priv Task, but the task output only shows errors. Is there something else we can try?
    Script is starting.
    Running as (domain\sqladmin).
    SQLServer: (mysqlserver.domain\INST01).
    Opening Connection using connect string: (mysqlserver.domain\INST01).
    Connected to SQL Server: (mysqlserver.domain\INST01).
    Running SQL command to Create SQL Login and assign permissions for Healthservice.
    ERROR executing query on: (mysqlserver.domain\INST01).
    TERMINATING.
    Error is: (Exception calling “ExecuteNonQuery” with “0” argument(s): “The server principal ‘SCOM_HealthService’ already exists.
    The server principal ‘NT SERVICE\HealthService’ already exists.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    Changed database context to ‘master’.
    Changed database context to ‘master’.
    Changed database context to ‘msdb’.”).
    Exception calling “ExecuteNonQuery” with “0” argument(s): “The server principal ‘SCOM_HealthService’ already exists.
    The server principal ‘NT SERVICE\HealthService’ already exists.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    User, group, or role ‘NT SERVICE\HealthService’ already exists in the current database.
    User, group, or role ‘SCOM_HealthService’ already exists in the current database.
    Changed database context to ‘master’.
    Changed database context to ‘master’.
    Changed database context to ‘msdb’.”
    At line:134 char:1
    + $SQLCmd.ExecuteNonQuery() | Out-Null
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : SqlException

    • Kevin Holman

      This is normal. These are simply error messages because you have already run the script before, and the login exists already. If you check, you can see the new permissions were most likely added.

Leave a Reply

Your email address will not be published. Required fields are marked *