It’s all about permissions…

Got by the Microsoft TechNet OpsMgr forum a note  -based on my postings - about empty reports. I was told the postings were OK, but somehow they didn’t get filled reports for SQL (SQL Database space report). Besides that, the Views in the Monitoring Pane (Microsoft > SQL Server > Databases > Database Free Space and ~> Transaction Log Free Space) were empty as well. Question was asked whether certain rules needed to be activated to get this working. But the SQL MP Guide never said anything about it.

So I started to investigate it in one of mine test environments, and indeed there I had the same issue. But besides just importing the SQL MP, I hadn't done anything else. This was a very new environment and I had just imported some MPs without having done any configuration. (even I do sleep sometimes…). Then I looked in another test environment, older but configured and here all is well.

So I started to configure the SQL MP in the new environment properly, as stated in the MP guide for the SQL MP. Also I checked the OpsMgr event log of this server and here I saw two interesting events (right after a restart of the Health Service), both with EventID 21406:

The first was the most interesting one since it told me there was an issue with the needed permissions:

And almost straight afterwards, I saw the second event which told me why there was no data to be found in the reports and the Views:

Yes, SQL Distributed Management Objects (DMO) is installed on this SQL-server so is has to be an authorization issue as well.

The SQL MP guide is clear about authorizations, page 21, Security Considerations, Run As Profiles states clearly:
 

The same document also tells how to go about it and what authorizations are needed in order to get this MP up & running. Page 22:

So I made an account in AD with the needed permissions, added it to SQL (SysAdmin role) as well. Then I used this account for the profiles as stated in the SQL MP guide and chose for More Secure distribution, selecting the SQL server.

Then I restarted the Health Service and checked the OpsMgr Event log. It seemed to be OK now since no more EventID’s 21406 were popping up.

Checked the Views (Microsoft > SQL Server > Databases > Database Free Space and ~> Transaction Log Free Space) and after 10 minutes they got populated! So the report will work as well but needs a bit more time in order to collect data.

So RTFM (Read The Friendly Manual) is very important. Especially in OpsMgr R2 where one can import MPs straight from the Console. Nice feature but when the related guides aren’t read, it is prone to error. Even when one wants to use this new functionality, it is to be advised to RTFM first by downloading the MPs from the MP Catalog, unpack them as I described in another blog posting (also why), read the related guide, understand it and then and only then to import the related MP.