Compartilhar via

Manually configuring Power BI integration in Reporting Services

  • Artigo

SQL Server 2016 Reporting Services bridges the cloud and on-premise gap by enabling customers that make use of both Power BI and Reporting Services to keep track of key data by pinning visuals from Paginated Reports to their Power BI dashboard. Visuals pinned to Power BI dashboards are updated with fresh data regularly, and users can click on a pinned visual to visit the original SSRS report.

To make use of pinning Administrators first need to connect Reporting Services with Power BI through the “Power BI Integration” tab within Reporting Services Configuration Manager. Behind the scenes Configuration Manager creates a new Azure Active Directory Web App to represent Reporting Services when communicating with Power BI and stores the details in the SSRS catalog.

1

As Microsoft enables Azure AD users to add new applications by default, any user who is a SSRS admin can enable Power BI integration. Some organizations however may choose to prevent users from adding new application and force integrated application to be added manually by an Azure AD Global Admin. If you receive a “You may not have permissions to register an app with Azure Active Directory” error while configuring Power BI integration -

2

Then “Users may add integrated applications” is likely disabled in Azure AD. The Azure AD Global Admin will be able to confirm this.

3

In this situation the easiest path forward is to make the Azure AD Global admin a SSRS admin and ask them to configure Power BI. In organizations where this isn’t possible Power BI integration can be configured manually in a two-step process.

 

For the Azure AD Admin

1) Create a new “Web Application” via the Azure AD Portal

4

2) Then configure the basic app properties

 

Sign-On URL After a user authenticates with Azure AD they are redirected back to the referring app. For security reasons the URLs that a user can be returned too need to be pre-registered with Azure AD. Reporting Services expects the user to be returned to /pages/LoginComplete.aspx on the Report Server endpoint. For a default install this URL will look like -https://{MyReportServerName}:80/reportserver/pages/LoginComplete.aspx If user can access Reporting Services through multiple URLs then all possible permutations of the LoginComplete.aspx page need to be registered. 
App ID Uri The App ID Uri is a unique identifier. Recommend using a URI in the formathttps://{MyReportServerName}/{SomeGuid}

3) Move to the ‘Configure’ page. Create a new client secret and note the Client ID which will be needed by the SSRS admin.

4) Download the application manifest from the Azure AD portal. The SSRS admin will need to know the App Object ID which is stored in the manifest.

5) Edit the ‘requiredResourceAccess’ section of the application manifest and then re-upload

In order for users of the Azure AD application to communicate with Power BI the needed Power BI resources need to be pre-approved. Add the Power BI ViewUserGroupsAccess, ViewAllDashboardsAccess and CreateContentAccess resources to the requiredResourceAccess section –

{ “id”: “47df08d3-85e6-4bd3-8c77-680fbe28162e”, “type”: “Scope” },

{ “id”: “2448370f-f988-42cd-909c-6528efd67c1a”, “type”: “Scope” },

{ “id”: “f3076109-ca66-412a-be10-d4ee1be95d47”, “type”: “Scope” }

6) Locate and store the Azure AD Tenant ID and Tenant Name. The Tenant Id can be found in the AAD Admin Portal URL, or follow the instructions at https://support.office.com/en-us/article/Find-your-Office-365-tenant-ID-6891b561-a52d-4ade-9f39-b492285e2c9b.

 

Instructions for the SSRS admin

1) Download the SSRS Admin Power Shell scripts from https://github.com/Microsoft/Reporting-Services/tree/master/Scripts.

2) From Power Shell load the configuration scripts

3) Call the Register-PowerBI function and pass in the details provided by the Azure AD admin. Configuration can be performed against any instance in the SSRS farm

Register-PowerBI -ClientId "UpdateWithClientID" -ClientSecret "UpdateWithClientSecret" -AppObjectId "UpdateWithObjectId" -TenantName "UpdateWithTenantName" -TenantId "UpdateWithTenantId" -RedirectUrls "UpdateWithRedirectUrls" -SqlServerInstance "UpdateWithSqlServerInstance"

 

Cheat sheet

Information needed to register a new Azure AD App

 

Property Notes
App type Web App
Sign-on URL Needs to point to the Report Server endpoint. Register all URLs that SSRS can be accessed from.Format:  https://{MyServerName}:80/reportserver/pages/LoginComplete.aspx
App permissions Add to the Azure AD Application Manifest {"id": "47df08d3-85e6-4bd3-8c77-680fbe28162e", "type": "Scope" },{ "id": "2448370f-f988-42cd-909c-6528efd67c1a", "type": "Scope" },{ "id": "f3076109-ca66-412a-be10-d4ee1be95d47", "type": "Scope" }

 

Information needed to configure Power BI integration in SSRS through the WMI PowerShell scripts

 

Property Notes
Sign-on URL As used when registering the Azure AD app.
Tenant Name Ie. "Microsoft" or "Contoso". Needs to match what is configured in Azure AD.
Tenant ID Azure AD Tenant ID - can be fetched from the Azure AD portal, or https://support.office.com/en-us/article/Find-your-Office-365-tenant-ID-6891b561-a52d-4ade-9f39-b492285e2c9b
Client secret Secrete key created via the Azure AD portal
Client ID Fetch from the "Configuration page" after creating the app
AppObjectId Find in the "ApplicationMinifest" which can be downloaded from the Azure AD portal.

 

More information

Pinning to Power BI MSDN documentation: https://msdn.microsoft.com/en-us/library/mt604784.aspx

Comments