Released: SCOM Management Packs for SQL Server, Reporting Services, Analysis Services (7.0.32.0)

[EbruErsan]

Updates to SQL Server, Reporting Services, and Analysis Services Management Packs are available (7.0.32.0). We also released an update to SQL Server Dashboards MP. You can download the MPs from the links below. Majority of the changes are based on your direct feedback. Thank you.

Microsoft System Center Management Pack for SQL Server

Microsoft System Center Management Pack for SQL Server Reporting Services

Microsoft System Center Management Pack for SQL Server Analysis Services

Microsoft System Center Management Pack for SQL Server Dashboards

Another change we've done recently is to move operations guides for all SQL Server family of management packs from download center to docs.microsoft.com. This unifies the content viewing experience for the user as the rest of the SCOM and SQL Server documentation is already there. Further more, it allows us to present you with the most up to date and accurate content online. The link to the operation guide for each MP can be found on the MP download page. Here are the links that show what's new in these MPs:

Features and Enhancements in Management Pack for SQL Server

Features and Enhancements in Management Pack for SQL Server Analysis Services

Features and Enhancements in Management Pack for SQL Server Reporting Services

[smilne]

After importing the SQL Server management pack, we are receiving the following alert on each monitored instance:

Management Group: "xxx"
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
Stack Trace: 
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod, Boolean isInternal)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult)
at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<ExecuteCommandDataReaderInternalAsync>d__40.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<ExecuteCommandDataReaderAsync>d__35.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SQLServer.Windows.Module.Discovery.Discoveries.AgentJobDiscovery.<FillListsOfClassesAndRelationsAsync>d__13.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SQLServer.Module.Helper.Base.DataItemHelper.<GetDiscoveryDataAsyncStatic>d__6.MoveNext()


State:
The configuration properties are: 
ManagementGroupName = xxx
Publisher = SQLDiscoveryWindows
ConnectionString = xxx.xxx.com\YYY
InstanceEdition = xxx
InstanceName = YYY
InstanceVersion = 14.0.3381.3
MachineName = xxx.xxx.com
MonitoringType = Local
NetbiosComputerName = xxx
Login = 
AgentJobClassId = 36047f61-8a87-3d85-2de3-d423c71828da
DiscoverySourceManagedEntityId = d58c3a70-6c31-c715-4e61-5511294ff713
DiscoverySourceObjectId = e247a519-1261-4cc5-5d13-f7734304b621
ExcludeList = 
LocalAgentJobClassId = c18cd1df-7c26-0bdc-f07d-749d86afaf83
SqlTimeoutSeconds = 15
TimeoutSeconds = 300
Password = ********

Error(s):
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
Stack Trace: 
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
at System.Data.SqlClient.SqlCommand.InternalEndExecuteReader(IAsyncResult asyncResult, String endMethod, Boolean isInternal)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderInternal(IAsyncResult asyncResult)
at System.Data.SqlClient.SqlCommand.EndExecuteReaderAsync(IAsyncResult asyncResult)
at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<ExecuteCommandDataReaderInternalAsync>d__40.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SQLServer.Module.Helper.TransientErrorHandling.SqlRetryClient.<ExecuteCommandDataReaderAsync>d__35.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SQLServer.Windows.Module.Discovery.Discoveries.AgentJobDiscovery.<FillListsOfClassesAndRelationsAsync>d__13.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.SQLServer.Module.Helper.Base.DataItemHelper.<GetDiscoveryDataAsyncStatic>d__6.MoveNext()

Are there additional permissions required in a low privilege environment? Based on the following script, we already have these grants and role memberships in place for the action account:

Low-privilege monitoring in Management Pack for SQL Server | Microsoft Docs

The above output is from SQL Server 2014, however this appears to be affecting multiple versions of SQL Server. 

[smilne]

This appears to be the query causing the permission denied error:

exec sp_executesql N'DECLARE @xml xml = @excludeList
;WITH excludeList
AS
(
	SELECT T.db.value(''(./@name)[1]'', ''nvarchar(max)'') AS jobName
	FROM @xml.nodes(''ExcludeList/AgentJob'') AS T(db)
)
SELECT sjv.job_id,
		sjv.name,
		sjv.originating_server,
		sjv.description,
		category = ISNULL(sc.name, FORMATMESSAGE(14205)),
		owner = msdb.dbo.SQLAGENT_SUSER_SNAME(sjv.owner_sid),
		sjv.enabled
FROM msdb.dbo.sysjobs_view sjv
		JOIN msdb.dbo.syscategories sc 
			ON (sjv.category_id = sc.category_id)
		left JOIN excludeList el
			ON ( el.jobName LIKE ''%*%'' and sjv.name = el.jobName)
			or sjv.name LIKE REPLACE(REPLACE(REPLACE(REPLACE(el.jobName, ''%'', ''[%]''), ''['', ''[[]''), ''_'', ''[_]''), ''*'', ''%'')
		where el.jobName is null
ORDER BY sjv.job_id;',N'@excludeList nvarchar(15)',@excludeList=N'<ExcludeList />'

[DVKalashnikov]

@smilne Hello Stewart, let's discuss this by email if you don't mind.

[Swati06]

@DVKalashnikov @smilne I am getting same issue. Please let me know if we can report this issue.

[DVKalashnikov]

Hi @Swati06, you could observe this issue when monitoring your environment under low privilege access. To workaround it, additional grants for the monitoring account should be granted. Please use this instruction, replacing the [LowPrivAccount] for your SQL account name:
1) Open SQL Server Management Studio and connect to the SQL Server Database Engine instance.
2) Give additional grants for the [LowPrivAccount] SQL account:

USE [msdb]
GO
GRANT EXECUTE ON SQLAGENT_SUSER_SNAME TO [LowPrivAccount]
GRANT SELECT ON sysjobs_view TO [LowPrivAccount]
GRANT SELECT ON syscategories TO [LowPrivAccount]

[Swati06]

@DVKalashnikov Thank you for the quick response. I hope Microsoft will publish this soon.