I must admit that writing this blog post was a long time coming. I can now finally show you how to install Power BI Report Server as a ConfigMgr reporting services point. Spoiler alert! There aren’t any real tricks to this installation compared to how you would install the normal SSRS reporting services point. Why would you use Power BI Report Server (PBRS) over SQL Server Reporting Services (SSRS)? This is a great question and the answer is simple. With PBRS, you can run BOTH Power BI and SSRS reports with ConfigMgr/SCCM/MEMCM on the same server. In other words, you get the best of both worlds!
In addition to being able to run both Power BI and SSRS reports with SCCM on the same server, there were three more reasons why I decided to document how to install a PBRS as a SCCM reporting services point.
First, I was hoping that this new reporting services point had new built-in ConfigMgr/SCCM/MEMCM Power BI reports in it.
Second, when someone searches this topic, they get the answer they are looking for with this step-by-step guide.
I can’t lie to you, there was also a fourth reason! We are extending both products by having more Power BI content published directly to our customers’ Power BI reporting sites. More to come on that later as it happens!
Here are the high-level steps:
· Add the site server computer account as the local administrator on the PBRS.
· Add the site server computer account as a SQL Server administrator within SQL Server.
· Install a SCCM reporting services point on PBRS.
· Validate that the reporting services point is working.
The following items are required before you can start installing the SCCM reporting services point.
· SQL Server 2019 is installed.
· Power BI Report Server 2019 is installed and configured.
· The PBRS connection account is created or you can reuse the SSRS account. Read the following section for more details.
· SCCM current branch is setup.
PBRS/SSRS Connection Account
Before starting, I want to say a few words about the PBRS/SRSS connection account. When you create this account in AD, grant it NO extra permissions within the domain. Please don’t make it a domain admin. All it needs is regular, low rights. Don’t even think about making it a local administrator on SQL Server or the PBRS. Why am I saying this? SCCM takes care of granting the permissions it needs within SQL Server and PBRS.
Adding the Site Server Computer Account as the Local Administrator
Note: In this section I reused the screenshots and text (with a few edits) from the original SSRS version of this blog post. Other than a few text edits, NOTHING else was changed. The steps are exactly the same.
On the PBRS, start by opening up Computer Management. Expand Local Users and Groups and then Groups. In the center panel, double-click on the Administrators group.
Click Object Types…
Select Computers and click OK.
Type the site server’s name with a dollar sign $ at the end of it and then click Check Names.
In the above screenshot you can see that the text of the site server’s name is now in uppercase letters and it is underlined. This indicates that the computer account was found and everything is good in order to proceed to the next step. Click OK.
Click OK and then close the Computer Management window.
Add the Site Server Computer Account as a SQL Server Administrator within SQL Server
I cover these steps in another blog post, How to Create a SQL Server Computer Account Login, so I won’t repeat them here.
Please note: this step must be completed before proceeding.
Installing a Power BI Report Server as a ConfigMgr Reporting Services Point
Open your SCCM console, select the Administration workspace, expand the nodes Site Configuration and then Servers and Site System Roles. On the Home tab, select Create Site System Server.
Enter the server’s name (in this example it’s CM-PBI19E-CB1) and then click on the Check Names button. Once the server’s name is underlined and in uppercase (similar to the one in the screenshot above), you know that everything is good to go. Click OK.
Select your site code (CB1 for my needs).
Once you select your site code, click Next.
Click Next again.
Note: Since I am attaching the PBRS to a CAS and not a primary site, there are fewer options available within the list above.
Select Reporting services point and then click Next.
The Reporting Services server instance is automatically selected.
Notice that the Reporting Services server instance is PBIRS? This is the default name for PBRS.
Click Set… At this point you have a choice to make. Will you use an Existing Account or a New Account? Below are the steps for both methods.
Select the account and click OK. You can now skip down to the, “After the Account is Set,” section.
Enter the reporting services point account name and then click on the Check Names button. Once the account is underlined and in uppercase (similar to the example in the screenshot above), click OK.
Enter and confirm the password, then click OK.
After the Account is Set
After setting either an Existing Account or a New Account, you end-up back here. Click on the Summary node.
Click Close. With that last step completed, the SCCM reporting services point is installed. I recommend waiting around 15-30 minutes before checking that everything is setup correctly.
Validating the SCCM Reporting Services Point
Below are several tests you should perform in order to validate that the SCCM reporting services point is working properly for BOTH Power BI and SSRS reports.
This test confirms that the SCCM reports can be accessed from the PBRS itself.
First, on the PBRS, browse to http://<servername>/reports. This should always work and, if it is successful, you should see a similar webpage as the one shown above. At first glance, this webpage looks EXACTLY like the one for SSRS reports, so I am going to test an SSRS report first.
Drill down on the ConfigMgr_<yoursitecode> link. You should, again, see a webpage that’s similar to the one above.
Finally, select a report and run it. In the screenshot above, you see a Warranty Information Reporting SSRS report. My test confirms that PBRS works perfectly and, as a bonus, Enhansoft’s Warranty Information Reporting works perfectly with PBRS!
In the worst-case scenario, your report may show no results, but this test still confirms that it is a functioning report.
Remote PBRS Test
This test proves that any remote computer (from the PBRS) can access the reporting site. Once again, you are going to perform the same steps as you did in the first test and the results of each step look exactly like the screenshots shown in the previous section.
On a computer that is remote to the PBRS, browse to http://<servername>/reports. If all the firewall ports are setup correctly, this test works perfectly. If it doesn’t work see the blog post, Why Can’t I Access My SSRS Site Remotely?
Next, drill down on the ConfigMgr_<yoursitecode> link. Again, you should see a webpage that’s similar to the one in the first test.
Finally, select a report and run it. Ideally it’s the same report you selected in the first test, so you should see the exact same results.
SCCM Console Test – SSRS Reports
There are two console tests for SSRS reports. First, locate the report that you want to run (ideally it is the same report you used in the previous tests), right-click on it and select Run in Browser. This launches a browser window for the report and the results should look exactly like the results from the remote PBRS test.
For the second console test, again, locate the same SSRS report, right-click on it and select Run. This launches the report view on your computer. The results in the report should look exactly like the ones in the browser version test.
Built-in Microsoft Power BI Reports
Remember how I mentioned earlier that I was hoping that this latest reporting services point had new built-in SCCM Power BI reports? Unfortunately, I was a little disappointed that there weren’t any (yet), but I’m sure they are coming in upcoming releases of SCCM!
Power BI Report Test
Given that there were no built-in reports, I confirmed that the SCCM reporting services point was working properly with Power BI reports by using Enhansoft’s Warranty Information Reporting (WIR) Power BI report set. In order to facilitate this test, I uploaded Enhansoft’s WIR Power BI report set (the PBIX file, not the PBIT file!) to my reporting services point (that’s the same URL as the one for SSRS reports, http://<servername>/reports).
After uploading the Power BI file within the Warranty Information Reporting v3 folder, I quickly saw that, “Power BI Reports,” was its own heading section and that the report was called, “WIR v2.” I clicked on it in order to run the report.
I was excited to see that the report set worked without any issues. Here you can see and interact with all of the Power BI report tabs!
SCCM Console – Power BI Reports
Now, back to the console. There is only one test you need to perform for Power BI reports. First, select the same report you chose in the previous test. Right-click on the report and then select Run in Browser. Of course, the results should be the same as the ones in your Power BI report test.
With the SCCM reporting services point validated, you can now hand out the link to anyone who needs to view Power BI or SSRS reports within SCCM!
Setting up the SCCM/ConfigMgr/MEMCM reporting services point with PBRS is virtually the same as the steps you take with SSRS. I’m super-excited that Enhansoft’s Power BI reports work with PBRS and, better yet, show-up within the console a few minutes after they get installed! We have a bit of work ahead of us in terms of adding an opt-in for our customers to automatically upload our Power BI report sets, but that is coming VERY soon.
If you have any questions about how to install a Power BI report server as a ConfigMgr reporting services point, please feel free to contact me at @GarthMJ.