SMA Management Pack Could Not Connect To Database Alerts – My Troubleshooting Experience

I’ve setup 2 servers for the SMA environment in my lab a while back. Yesterday, I loaded the SMA MP (version 7.2.89.0) into my OpsMgr management group. Needless to say, I followed the MP guide and configured the Database RunAs profile. However, soon after the MP is loaded, I started getting these 2 alerts:

• The Service Management Automation web service could not connect to the database.
• The Service Management Automation worker server could not connect to the database.

To troubleshoot these alerts, I firstly unsealed the management pack Microsoft.SystemCenter.ServiceManagementAutomation.2012R2.mp, as this is where the monitors are coming from. The Data Source module of the monitor type uses System.OleDbProbe probe action module to make the connection to the database.

To simulate the problem, I used a small free utility called Database Browser Portable to test the DB connection. I launched Database Browser using the same service account as what I configured in the RunAs profile in OpsMgr, and selected OleDB as the connection type:

I populated the Connection String based on the parameters (monitoring object properties) passed into the data source module: Provider=SQLOLEDB;Server=SQLDB01.corp.tyang.org\;Database=SMA;Integrated Security=SSPI

Note the Database Instance property is empty. this is OK in my lab because I’m using the default SQL instance. I’ll explain this later.

The test connection result is positive:

However, after connected, when I clicked the connection, nothing happened, the list of tables did not get populated. I then tried using my own account (which has god rights on everything in the lab), and I got the same result.

Long story short, after trying different configuration changes on the SQL server, I finally found the issue:

On the SQL server, the Name Pipes protocol was disabled

After I enabled it, I was able to populate the tables in Database Browser:

And within few minutes, the alerts were auto closed.

While I was troubleshooting this issue, I came across a blog post from Stanislav Zhelyazkov. In the blog post, Stan mentioned adding the DB instance name in the registry (where the discoveries are looking for). However, when I added “MSSQLSERVER” in the registry and forced re-discovery, the monitors became critical again and I received several 11852 event in Operations Manager event log:

I email Stan and he got back to me and told me he’s using a named instance in his lab and these monitors are working fine in his lab after he added the SQL instance name in the registry. He also told me he didn’t recall specifying the SQL Instance name during the SMA setup but the setup went successful. My guess is that the SQL Browser service must be running on his SQL server, so the setup had no problem identifying the named instance.

Conclusion

Based on my experience and Stan’s experience, we’d like to make the following recommendations:

• Enable the Name Pipes protocol
• If using the default SQL instance, please do not manually populate the registry key
• If using a named instance, please add the SQL instance name in the registry if it’s not populated after setup.

 

Thanks Stan for his input on this one!