Another case of a SCOM investigation we got involved in was about the SQL team trying to monitor SQL Analysis Services (SSAS) using SCOM. Management pack was installed, agent deployed, proxy enabled, runas accounts arranged, rights arranged for the runas account. And nothing found on those SQL servers. In the meantime this case was already escalated to a MSFT support ticket, but we figured it might be good to look at it from the SCOM team side as well.
So first investigation is obviously the MP guide. Runas profile, rights assignment, agent proxy, sql browser service. All the default stuff. Next was to look in SCOM until what point it got in the discoveries.
The server was not listed in the SQL Computers state view. This view gets populated when on a computer a SQL role is discovered (DB engine, SSAS, SSIS, SSRS). However I know that the first step the SQL discoveries take is doing a seed discovery, which is a step before it. So open up the SCOM console and go to the Discovered Inventory view near the top of the navigation pane list, and select the target type (class) “SSAS 2016 Seed”. Sure enough we saw the servers we expected there. Next step was to try and see if the class we hoped to get populated showed anything. In this case it was “SSAS 2016 Tabular Instance”. Nothing found.
So here we go again. Find the management packs which contain the class and discoveries and go into the XML code to see what they are doing. After a bit of scrolling I found the probe which is doing the discovery. And it pointed to a DLL in which the discoveries were packaged (microsoft.sqlserver.2016.as.module.discovery.dll ). Hmmm, I cannot look inside a DLL what it is doing (I am sure it is possible you bunch of hackers, but I took it as a sign it was meant to be packaged). So I asked friends of mine what the discovery was doing. Turned out a few registry and WMI type queries with some intelligence around it and such. And we got some queries sitting inside there, so we could manually try to run.
Since this is an environment with a load of security measures implemented it was a safe bet that security would be blocking us somewhere. The MSF ticket came to the same conclusion already and proceeded to investigate the security policies for possible reasons.
Meanwhile the SQL team could take the queries we got to try and run those queries using the runas account. Logging on with the monitoring service account and opening the SQL Server Configuration Manager -> nothing listed! Run the WMI query -> nothing listed!
Since this was a development box it could be moved to an OU with less policies and immediately everything worked. So moved it back again into the policies and it did not work again.
We were on the right track now. And MSFT support came with an article about an old SQL version whereby they had a case like this where sql server properties could not be accessed and read.
The answer can be found in the following article:
https://support.microsoft.com/en-us/help/941823/some-or-all-sql-server-2005-services-are-not-listed-in-sql-server-conf
This states that the NETWORK SERVICE account need read rights to the relevant SQL Windows Service on the machine in order to read from it. Adjust it in a test policy and do a gpupdate and restart the SCOM agent to force the discoveries to start running. Few minutes later SQL SSAS was discovered!
Now normally there will not be many problems in discovery of SQL server components by SCOM, but it can happen. Usual suspects are agent health, agent proxy setting, browser service not running, runas account, runas account rights, not having the management pack for the sql server version imported. In this case it was a rigid security policy blocking access. Not that many people will run into this and therefore it is hard to search for similar events on the internet.
Hope it helps somebody running into something similar!
Again this is a case where one needs a team to get to the bottom of things, in this case SQL, SCOM, MSFT support, the SQL pack builders (Thanks Alexander and friends!) to get things solved.
Bob Cornelissen
