Power BI Report Server Integration (Configuration Manager)

Article
12/19/2023

Applies to: ✅ SQL Server 2016 (13.x) Reporting Services and later ✅ Power BI Report Server

The Power BI Integration page in Reporting Services Configuration Manager is used to register the report server with the desired Microsoft Entra tenant to allow users of the report server to pin supported report items to Power BI dashboards. For a list of the supported items you can pin, see Pin Reporting Services items to Power BI Dashboards.

Requirements for Power BI Integration

In addition to an active internet connection so you can browse to the Power BI service, the following are requirements to complete Power BIintegration.

Microsoft Entra ID: Your organization must use Microsoft Entra ID (formerly Azure Active Directory), which provides directory and identity management for Azure services and web applications. For more information, see What is Microsoft Entra ID?
Microsoft Entra tenant: The Power BI dashboard you want to pin report items to must be part of a Microsoft Entra tenant. A tenant is created automatically the first time your organization subscribes to Azure services such as Microsoft 365 and Microsoft Intune. Viral tenants are not supported.
The user performing the Power BI integration needs to be a member of the Microsoft Entra tenant, a Reporting Services system administrator and a system administrator for the ReportServer catalog database.
The user performing the Power BI integration needs to start the Reporting Services Configuration Manager either with the account used to install Reporting Services, or the account the Reporting Services service is running under.
The server with Reporting Services installed needs to be configured to use TLS 1.2 or newer. For more information, see Transport Layer Security (TLS) best practices with the .NET Framework.
Reports that you want to pin from must use stored credentials. This is not a requirement of the Power BI integration itself but of the refresh process for the pinned items. The action of pinning a report item creates a Reporting Services subscription to manage the refresh schedule of the tiles in Power BI. Reporting Services subscriptions require stored credentials. If a report does not use stored credentials, a user can still pin report items but when the associated subscription attempts to refresh the data to Power BI, you will see an error message similar to the following on the My Subscriptions page.

PowerBI Delivery error: dashboard: IT Spend Analysis Sample, visual: Chart2, error: The current action cannot be completed. The user data source credentials do not meet the requirements to run this report or shared dataset. Either the user data source credential.

For more information on how to store credentials, see the section "Configure stored credentials for a report-specific data source" in Store Credentials in a Reporting Services Data Source.

An administrator can review the Reporting Services log files for more information. They will see messages similar to the following. A great way to review and monitor Reporting Services logs files is to use Microsoft Power Query over the files. for more information and a short video, see Report Server Service Trace Log.

subscription!WindowsService_1!1458!09/24/2015-00:09:27:: e ERROR: PowerBI Delivery error: dashboard: IT Spend Analysis Sample, visual: Chart2, error: The current action cannot be completed. The user data source credentials do not meet the requirements to run this report or shared dataset. Either the user data source credentials are not stored in the report server database, or the user data source is configured not to require credentials but the unattended execution account is not specified.
notification!WindowsService_1!1458!09/24/2015-00:09:27:: e ERROR: Error occurred processing subscription fcdb8581-d763-4b3b-ba3e-8572360df4f9: PowerBI Delivery error: dashboard: IT Spend Analysis Sample, visual: Chart2, error: The current action cannot be completed. The user data source credentials do not meet the requirements to run this report or shared data set. Either the user data source credentials are not stored in the report server database, or the user data source is configured not to require credentials but the unattended execution account is not specified.

To Integrate and Register the Report Server

Complete the following steps from the Reporting Services Configuration Manager. For more information, see Report Server Configuration Manager.

Select the Power BI integration page.
Select Register with Power BI.

Note

Make sure that port 443 is not blocked.
At the Microsoft sign-in dialog, enter the credentials you use to sign into Power BI.
After the registration is complete, the Power BI Registration Details section will note the Azure Tenant ID and the Redirect URL(s). The URLs are used as part of the sign-in and communication process for the Power BI dashboard to communicate back to the registered report server.
Select the Copy button in the Results window to copy the registration details to the Windows clipboard so you can save them for future reference.

Unregister With Power BI

Unregister: Un-registering the report server from Microsoft Entra ID will result in the following:

The My Settings link will no longer be visible from the web portal menu bar.
Report items that have already been pinned will still be pinned to dashboards, however the tiles will no longer be updated on the dashboard.
The Reporting Services subscriptions that were updating the tiles will still exist on the report server but when they run on their configured schedule, they will show an error message similar to the following.

The delivery extension for this subscription could not be loaded

From the Power BI page of configuration manager, select the Unregister with Power BI button.

Update Registration

Use the Update Registration if the configuration of your report server has changed. For example if you want to add or remove the URLS your users use to browse to the web portal.

In Reporting Services Configuration Manager, select the Web Portal URL

Select Advanced.
Select Add to add a new HTTP identity for the web portal and then select OK.

The Power BI icon will change to indicate the server configuration has changed.
On the Power BI Integration page, select Update Registration.

You will be prompted to login to Microsoft Entra ID. The page will refresh and you will see the new URL listed in the Redirect URLs.

Summary of the Power BI Integration and Pin Process

This sections summarizes the basic steps and technologies involved when you integrate your report server with Power BI and pin a report item to a dashboard.

Integrate:

In Configuration manager, when you select the Register with Power BI button, you will be prompted to sign in to Microsoft Entra ID.
The Power BI Client App is registered with your managed Tenant.
Your managed tenant within Microsoft Entra ID is where the Power BI Client app is created.
The registration includes a redirect URL(s) that are used when users sign in from the report server. The App ID and URLS are saved to the ReportServer database. The redirect URL is used during authentication calls to Azure so that the call can return to the report server. For example, when users sign in or pin items to a dashboard.
The App ID and URLS are displayed in Configuration Manager.

ssrs_pbiflow_integration

When a user pins a report item to a dashboard:

Users preview reports in the Reporting Services web portal and the first time they click to pin a report item from the web portal.
They will be redirected to the Microsoft Entra sign-in page. They can also sign in from the web portal My Settings page. When users sign in to the Azure managed tenant, a relationship is established between their Azure account and the Reporting Services permissions. For more information, see My Settings for Power BI Integration (web portal).
A user security token is returned to the report server.
The user security token is saved to the ReportServer database.
A list of groups, and dashboards, the user has access to are retrieved from the Power BI service. The user selects the destination group, and dashboard, and the configure how often they want the data refreshed on the Power BI tile.
The report item is pinned to the dashboard.
A Reporting Services subscription is created to manage the scheduled refresh of the report item to the dashboard tile. The subscription uses the security token that was created when the user signed in.

The token is good for 90 days, after which users need to sign in again to create a new user token. When the token is expired, the pinned tiles will still be displayed on the dashboard but the data will no longer be refreshed. The Reporting Services subscriptions used for the pinned items will error until a new user token is created. See My Settings for Power BI Integration (web portal). for more information.

The second time a user pins an item, the steps 1-4 are skipped and instead the App id and URLS are retrieved from the ReportServer database and the flow continues with step 5.

Diagram showing what happens when a user pins a report item to a dashboard.

When a subscription fires to refresh a dashboard tile:

When the Reporting Services subscription fires, the report is rendered.
The user token is retrieved from the ReportServer database.
The report item state and data is sent with the token to the Power BIservice.
The token is sent to Microsoft Entra ID for validation. If the token is valid, the report item data is sent to the dashboard tile and the date property of the tile is updated.
If the token is not valid, and error is returned and logged with the report server. No status or other information is sent to the dashboard.

Diagram showing what happens when a subscription fires to refresh a dashboard tile.