System Center Configuration Manager x SQL Server Reporting Services x WMI – English Version

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

Selecting SSRS instance

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:

  1. Start button -> Run -> type wbemtest -> ENTER
  2. Click on “Connect…”, type “root\Microsoft\SqlServer\ComputerManagement11” and click on “Connect”***
  3. 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:

  1. Click start button -> Run -> type wbemtest -> ENTER
  2. Click on “Connect…”, type “\\SSRS_machine_name\root\Microsoft\SqlServer\reportserver” and click on “Connect”*
  3. 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.

Registering WMI namespaces

In the case you want to check if WMI permissions are correct, just follow the process listed here, or in a more detailed way here.

Considering that we have eliminated all the possible problems related to WMI, we may start to wonder the problem is related to System Center.

Searching on the web I have found this interesting article. It mentions this article on technet that states:

“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).

WMI query on .dll file

That WMI query would be “select * from MSReportServer_Instance

Querying SSRS instances

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).

Queried Namespace

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.

Registry keys queried

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.

Listing of SSRS instances

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.

Esse post foi publicado em Artigos, WMI. Bookmark o link permanente.

10 respostas para System Center Configuration Manager x SQL Server Reporting Services x WMI – English Version

  1. Pingback: Adding Reporting Services in an existing clustered instance of SQL Server | Vladimir M. B. Magalhães – Learn and Share

  2. Pingback: System Center Operations Manager 2012 x SQL Server [English] | Vladimir M. B. Magalhães – Learn and Share

  3. Wasim Shaikh disse:

    Thanks for posting a detailed article.. This helps me in troubleshooting the problem..
    wbemtest tool is working fine when connected from SCCM server to SQL2008R2(SP2) server, and the query is also running which gives the correct instance name of reporting server.

    Don’t know why the wizard is not able to find the reporting server instance name. 😦

  4. beroccaboy disse:

    if you are still having problems, open up the firewall on the sql server for:

    1. [tcp] rpc endpoint mapper; and
    2. [tcp] rpc dynamic ports

    create your own inbound rule. thxs for the post(s) and thxs wireshark for the rpc hint.

  5. Candice disse:

    Exceptional post however I was wondering if you could write a litte more on this topic?
    I’d be very thankful if you could elaborate a little bit more. Cheers!

  6. Roland disse:

    Do you know if installing the reporting role is supposed to work and supported on a new site system, not on the SQL box itself?
    I wasn’t able to find a statement on that, but think why not…?

    I have a primary, with local SQL 2012. When I try to add the role to a new external server, to not have it on the primary, the instance is not shown, but the wmi queries all work and disabling the domain policy firewall made also no difference.
    If I try to install the role on the primary server itself, I do get the instance listed.

    Is using a different server even supposed to work and if so, why isn’t it?

    • magalhaesv disse:

      I don’t think it is going to work on another machine.
      Even though I don’t see a reason for this, it seems to have been designed to work like this.
      Maybe you can try to use mofcomp and add wmi classes with it, trying to fool the installer, but I really don’t know if this is going to work (I would like to test this, but don’t have the time right now)..

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do

Você está comentando utilizando sua conta Sair /  Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )


Conectando a %s