These days I went through an interesting situation that I would like to share.
We are implementing System Center Configuration Manager 2012 and one of its features is called Reporting Point, its reporting service, that integrates to SQL Server Reporting Services (SSRS). Until the previous version of SCCM you had the option to use a reporting feature of its own, but since 2012 version you must use SSRS for it.
The installation process of Reporting Point is pretty simple, but at the moment of selecting SSRS instance where the reports are going to be published
you may face a problem where the instances are not listed.
On the previous screen we can see listed the default instance “MSSQLSERVER” of SSRS, but it is common that you face a problem where this box is grey/unavailable.
So you ask yourself “… but I have available instances of SSRS, so why it is not listing them?”.
We may start our analysis with the text availabe in an article from technet that explains the installation process of Reporting Point, where we see the text:
System Center 2012 Configuration Manager makes a connection in the context of the current user to Windows Management Instrumentation (WMI) on the selected site system to retrieve the instance of SQL Server for Reporting Services. The current user must have Read access to WMI on the site system, or the Reporting Services instances cannot be retrieved.
In this text we notice that listing the instances depends on a WMI query, what leave us with a number of different possibilities like the WMI service itself, permissions, etc.
I will list here some steps to analyse that everything in your system is working fine. If you are already sure of this you may go to the end of the article and see the part where I talk about SCCM itself.
In order to analyse this a first step would be to check that WMI queries are working on the machine you are running reporting point installer. To do this follow these steps:
- Start button -> Run -> type wbemtest -> ENTER
- Click on “Connect…”, type “root\Microsoft\SqlServer\ComputerManagement11” and click on “Connect”***
- Click on “Query”, type “SELECT * FROM ServerSettings” and click on “Apply”
To check if WMI queries are working on the machine that hosts SSRS you can follow these steps:
- Click start button -> Run -> type wbemtest -> ENTER
- Click on “Connect…”, type “\\SSRS_machine_name\root\Microsoft\SqlServer\reportserver” and click on “Connect”*
- Click on “Query”, type “SELECT * __namespace” and click on “Apply”
If the query fails with an error similar or equal to “0×80041010, Invalid Class”, you might have a problem with the WMI service.
***the value 11 might be other, depending on the version of SQL Server (11 = SQL Server 2012).
In this case, the problem might be related to DLLs not registered or the configuration of the WMI provider.
To register the DLLs related to WMI, you may use REGSVR32, in the format: REGSVR32 name_of_dll.
The DLLs that might need to be registered are:
- C:\Program Files (x86)\Microsoft SQL Server\100\Shared\reportingserviceswmiprovider.dll
- C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmprovider.dll
In the case this is not the problem, you may follow the procedure listed on this link, that register some WMI namespaces and do other tasks.
Considering that we have eliminated all the possible problems related to WMI, we may start to wonder the problem is related to System Center.
“When you install a SQL Reporting Services Point, Configuration Manager 2007 queries Windows Management Instrumentation (WMI) for installed instances of SQL Reporting Services. The report folder is installed on the first instance of SQL Reporting Services found by the WMI query. If you have installed multiple instances of SQL Reporting Services and are unsure about whether the instance on which you want to install the report folder is the first stored in WMI, it is recommended that you install the SQL Reporting Services Point on a computer running only one instance of SQL Reporting Services.”
Altough referencing SCCM 2007, its also useful for 2012. It states that the installation is made on the FIRST instance listed by the WMI query made by SCCM. So again we think “..but I have SSRS instances and it does not list any of them”. Anyway, this information will be useful later.
Further in the same article its demonstrated how a SCCM .dll was analysed in order to find out which WMI query is made (I could not find the same .dll on SCCM 2012).
That WMI query would be “select * from MSReportServer_Instance”
on the namespace “\\maquina_do_SSRS\root\Microsoft\sqlserver\reportserver\rs_mssqlserver\v10“, where v10 refer to SQL Server 2008 R2 (SQL Server 2012 instances at this moment are not supported by SCCM 2012, only 2007).
This query may look nothing much, but it gives us an important help, showing that the WMI query is made on the DEFAULT instance (MSSQLSERVER).
On that same article it also mentions some registry keys that are accessed by the WMI query to list the instances, and these keys are on the path “HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\“. It will also be important later.
At this point I realized that if you do NOT have a DEFAULT instance, the namespace where SCCM makes it search will not be available. So you MUST have a DEFAULT instance of SSRS to make the query work.
Obs: this is only important in the case you have MORE than one SSRS instance. In the case you have only one named SSRS instance the instance will be listed with no problem.
So here you have two options. The first one would be install a default SSRS instance and continue the installation process. The other one would be to try to “cheat” the SCCM and make it “think” there is a default SSRS instance.
You might wonder why I would ever like to cheat SCCM, and in a future post I will talk about a situation where you can’t install or might prefer not to install a default SSRS instance and how to avoid it.
Well, considering that you have installed a default SSRS instance, you will problably think “ok, now I can finish the Reporting point installation!”, and the answer is, it depends.
Do you remember the text I mentioned earlier on this article? I will repeat it:
The report folder is installed on the first instance of SQL Reporting Services found by the WMI query
The WMI query will list only the FIRST SSRS instance found and, case it is not the instance where you would like to install the reports from SCCM, you will not be able to select the correct instance, since the box where you choose the instance won’t let you to do so.
Again on this article its mentioned that the order of the listing is defined by registry keys on the path (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\RS) and that, to change this order, you just need to delete and recreate the key of the instance that is being listed by SCCM, making this key the last one and the next one becoming the first, until the instance you need/want become the first/listed one.
With this procedure now you should have the desired instance listed on SCCM Reporting Point installation screen and you can proceed with the installation.
To use Reporting Point, now you only need to configure permissions on SSRS for the desired service account defined during the installation. I have not found any really detailed article, only this one, but it gives too many permissions.
To finish this article, I just would like to say that I do not understand why Microsoft made this process so complicated, instead of just listing all the available instances during the installation of Reporting Point, mainly for the fact that the process isn’t well documented, you may only find a few articles on the web. By what I have found, this problem was known on SCCM 2007 and still exists on 2012.