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.
