SSRS: An error occurred during the execution of an SSRS MP managed module

Question

Hello All,

I have received this alert in SCOM console after installing SSRS MP 7.0.42.0 version.

|Management Group: "TM-NonProd"Module:
Microsoft.SQLServer.ReportingServices.Windows.Module.Discovery.DeploymentSeedDiscoveryVersion:
7.0.42.0Error(s) was(were) occurred:Message: An error occurred
during discovery.---------- Exception: ----------Exception Type:
System.Data.SqlClient.SqlExceptionMessage: The SELECT permission was denied
on the object 'Keys', database 'ReportServer', schema 'dbo'.Number:
229Source: .Net SqlClient Data ProviderStack Trace: at
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean
breakConnection, Action1 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.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2 endFunction, Action1 endAction, Task1 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.<ExecuteCommandInternalAsync>d__411.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.<ExecuteCommandWrapperAsync>d__451.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.ReportingServices.Windows.Module.Discovery.DeploymentSeedDiscovery.<FillListsOfClassesAndRelationsAsync>d__15.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 = TM-NonProdPublisher = SQLReportingServicesDiscoveryWindowsDBConnectionString = CHI4WDMCSQL603InstallationID = 31fd2d9a-f65e-42c0-89cb-8ef428a3ad1dInstanceName = SSRSMachineName = CHI4WDMCSQL603Login = DBName = ReportServerDBServerFQDN = DeploymentSeedClassId = 5b935dfa-a383-f063-1308-8c3f5b791b11DiscoverySourceManagedEntityId = 3a7061fa-a9a4-849d-1d70-e972b9c67295DiscoverySourceObjectId = 5a3de8aa-ff73-4e21-3f82-86109e389da0Password = ********SqlTimeoutSeconds = 15TempDBName = ReportServerTempDBTimeoutSeconds = 300Error(s):An error occurred during discovery.---------- Exception: ----------Exception Type: System.Data.SqlClient.SqlExceptionMessage: The SELECT permission was denied on the object 'Keys', database 'ReportServer', schema 'dbo'.Number: 229Source: .Net SqlClient Data ProviderStack Trace: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action1 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.TaskFactory1.FromAsyncCoreLogic(IAsyncResult iar, Func2
endFunction, Action1 endAction, Task1 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.<ExecuteCommandInternalAsync>d__411.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.<ExecuteCommandWrapperAsync>d__451.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.ReportingServices.Windows.Module.Discovery.DeploymentSeedDiscovery.<FillListsOfClassesAndRelationsAsync>d__15.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()| | -------- | ||

Below are event error received on the server:

Event : 4511

Initialization of a module of type "RegistrySyncProbe" (CLSID "{472364F2-A1F0-41C0-9A8F-E00C92C2AB31}") failed with error code The parameter is incorrect. causing the rule "Microsoft.Windows.FileServer.DFSN.10.0.RoleServiceDiscovery" running for instance "server name" with id:"{D8230E2A-2ED3-DF50-3C12-50553A4B232E}" in management group.

Event ID : 21406 - Health Service Modules

The process started at 11:28:12 AM failed to create System.Discovery.Data. Errors found in output:

C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Monitoring Host Temporary Files 12397\19646\DiscoverMSDTCServer.vbs(82, 5) (null): 0x8004100E

Command executed: "C:\Windows\system32\cscript.exe" /nologo "DiscoverMSDTCServer.vbs" {EE64D262-5AC7-9381-F371-93AFB04A3463} {D8230E2A-2ED3-DF50-3C12-50553A4B232E} Server name "true" "true"

Working Directory: C:\Program Files\Microsoft Monitoring Agent\Agent\Health Service State\Monitoring Host Temporary Files 12397\19646\

One or more workflows were affected by this.

Workflow name: Microsoft.MSDTC.6.3.ClusteredRoleDiscovery

Instance name: sserver name

Instance ID: {D8230E2A-2ED3-DF50-3C12-50553A4B232E}

Management group:

I have checked our DB team they said permissions are in place and something have changed post installing the MP.

Could someone pls help me on this ?

Thanks,

Adam

Answer 1

Did they check this permission?

System.Data.SqlClient.SqlExceptionMessage: The SELECT permission was denied
on the object 'Keys', database 'ReportServer', schema 'dbo'.Number:
229.

I have run into this issue where the account that runs this discovery has "sysadmin" privileges but those did not translate to this exact permission?

Answer 2

Hi,

The error message you're receiving indicates that the account running the SSRS MP discovery module does not have sufficient permissions to access the 'Keys' object in the 'ReportServer' database.^[1] This error is common when the account running the discovery module lacks the SELECT permission on the 'dbo' schema. This issue may arise when the user specified in the Management Pack for the SSRS MP does not have necessary permissions. Check to ensure that the user account specified for SQL Server authentication has a strong password. If the user account has sufficient permission and a strong password pool, restart the SQL Server and the Health Service.

Additionally, check whether the WMI is experiencing any issues since you are using a SQL instance. In case there are NO issues, you can proceed to check the Security for ROOT, advance security settings for Root, and Permission entry for Root within WMI.^[2]

If the issue is still not resolved, further troubleshooting such as examining the Operations Manager event logs on the agent server or looking for known issues in the Management Pack for SQL Server might be necessary. ^[3][4]

References:

1. https://docs.microsoft.com/en-ca/troubleshoot/system-center/scom/troubleshoot-sql-database-discovery#missing-objects-object-that-for-discovery-is-not-in-the-discovery-data
2. https://learn.microsoft.com/en-us/azure/migrate/troubleshoot-discovery#error-9022-getwmiobjectaccessdenied
3. https://learn.microsoft.com/en-us/troubleshoot/system-center/scom/troubleshoot-sql-database-discovery#examine-the-operations-manager-event-log-on-the-agent-for-errors
4. https://learn.microsoft.com/en-us/system-center/scom/sql-server-management-pack-known-issues-and-troubleshooting

Answer 3

Followed the steps but this is not fixed my issue. Our DB team provided DB reader permission for that KEYS objects post that issue has been fixed.

Is there any fixe for this ? Could some please help with the solution ?\

Thannks,

Adam

Answer 4

Hi ,

• Enable the SQL Server Browser service. This service is required for Reporting Services discovery and monitoring and must be running on computers with Reporting Services and on computers that host the reporting server database.- Use a domain account for monitoring. Monitoring with a domain account is highly recommended. For more information, see Least-Privilege Monitoring Configuration. You can use the Local System account or HealthService SSID as an action account.

Source: https://learn.microsoft.com/en-us/system-center/scom/reporting-services-management-pack-management-pack-delivery?view=sc-om-2022

NotePre-installation of Microsoft SQL Server on Windows (Discovery) version 7.0.20.0 or later is required to discover Server Reporting Services Deployment. This MP file is part of the Microsoft System Center Management Pack for SQL Server on Windows delivery. In case of absence of Microsoft SQL Server on Windows (Discovery), this management pack won't be able to discover and monitor the availability and performance of Server Reporting Services Deployment. Monitoring of Server Reporting Services is still possible without having Microsoft SQL Server on Windows (Discovery) installed.

NoteIt's not recommended to use the Local System account or HealthService SSID because it's a special case to monitor SSRS. Some workflows run on the server hosting an SSRS instance and try to reach the SSRS Database usually installed on another server. You'll need to provide computer accounts of all the servers hosting SSRS instances with the required permissions to access the SSRS Database. A domain account is a more preferable option.

Source: https://learn.microsoft.com/en-us/system-center/scom/reporting-services-management-pack-run-as-profiles?view=sc-om-2022

To test can you use a regular domain account for the discovery instead of using a managed service account?

Marius ENE (https://mariusene.com/ )