#SMA #SCOM #Monitoring #11852
#SMA #SCOM #Monitoring #11852

 

 

There was an alert on my SCOM Lab while I was configuring SMA monitoring in SCOM.

 

 

Now checked the health explorer and found that the alert is raised for the below Monitor

 

 

While looked at the alert configuration, it is actually checking the following criteria.

 

  <ProbeAction ID="OleDbProbe" TypeID="System!System.OleDbProbe">
<ConnectionString>Provider=SQLNCLI11;Server=$Config/DatabaseServer$\$Config/DatabaseInstance$;Database=$Config/DatabaseName$;Integrated Security=SSPI</ConnectionString>
<Query>$Config/Query$</Query>
<GetValue>true</GetValue>
<QueryTimeout>0</QueryTimeout>
<GetFetchTime>true</GetFetchTime>
</ProbeAction> 

 

Now looked at the configuration from the SMA server and I could see that database information will be present in two locations.

one under the following registry as mentioned below.

 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ServiceManagementAutomation\RunbookWorker

 

and other under IIS config file.

or from the below location in IIS.

 

 

To see the connection string from IIS, you need to first run the below query in PowerShell to decrypt the config file.

PS C:\Windows\Microsoft.NET\Framework64\v4.0.30319> .\aspnet_regiis.exe -pdf "connectionStrings" 'C:\inetpub\Service Management Automation'

To encrypt the config file, please run the following command in PowerShell

PS C:\Windows\Microsoft.NET\Framework64\v4.0.30319> .\aspnet_regiis.exe -pef "connectionStrings" 'C:\inetpub\Service Management Automation'

 

https://docs.microsoft.com/en-us/system-center/sma/encrypt-sql-connection-using-ssl?view=sc-sma-2019

 

Now Open IIS and then expand the sites and go to SMA

Under Features view, we have the “Connection Strings”

Double click on “Connection Strings”

We will get the OrchestratorStoreConnectionString

Server=sqlservername;Integrated Security=sspi;Database=SMA

Firstly I got the below error event on SMA server in my Lab.

 

Log Name:      Operations Manager

Source:        Health Service Modules

Date:          8/10/2019 12:19:24 AM

Event ID:      11852

Task Category: None

Level:         Warning

Keywords:      Classic

User:          N/A

Computer:       server.domain.root 

Description:

OleDb Module encountered a failure 0x80040154 during execution and will post it as output data item. Class not registered

: Class not registered 

Workflow name: Microsoft.SystemCenter.ServiceManagementAutomation.2012R2.Monitor.DatabaseConnectionWeb 

Instance name:  server.domain.root  

Instance ID: {559645DB-4383-01B1-1399-4EA2D653FCDE} 

Management group: SCOM2016

 

 

It was happen due to Microsoft SQL Server 2012 Native Client was not installed on SMA server.

So we have installed the “Microsoft SQL Server 2012 Native Client”

To download the “Microsoft SQL Server 2012 Native Client”, please click here “Microsoft SQL Server 2012 Native Client”

Now the above event is resolved.

Now after few minutes later, we could see the below error.

 

Log Name:      Operations Manager

Source:        Health Service Modules

Date:          8/10/2019 5:48:34 PM

Event ID:      11852

Task Category: None

Level:         Warning

Keywords:      Classic

User:          N/A

Computer:       server.domain.root 

Description: OleDb Module encountered a failure 0x80004005 during execution and will post it as output data item. Unspecified error

: Login timeout expired 

Workflow name: Microsoft.SystemCenter.ServiceManagementAutomation.2012R2.Monitor.MessageQueueLength 

Instance name:  server.domain.root 

Instance ID: {24C60D0E-6CE0-F387-1CA0-871E4B0797E1} 

Management group: SCOM2016

 

 

Observed that if we are using default instances for SQL, the following registry keys shouldn’t have any database server instance name.

 

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ServiceManagementAutomation\WebService\DatabaseServerInstance

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ServiceManagementAutomation\RunbookWorker\DatabaseServerInstance

 

But we could see that DatabaseServerInstance key is having the value as  MSSQLSERVER

 

If we do a UDL test like below, it will fail with the below exception.

 

 

So, in order to do the test connection successful, we need to just provide the SQL server name in udl file as our database is hosted on default SQL server instance.

 

 

So we have now removed the DatabaseServerInstance value and keep it as blank  for both the below registry keys. 

 

 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ServiceManagementAutomation\RunbookWorker\DatabaseServerInstance

 HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ServiceManagementAutomation\WebService\DatabaseServerInstance

 

Now we could see that after we do a restart of the health service on SMA server, issue is resolved and alert is closed and monitor is also turned into healthy state.

 

Leave a Reply

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