How to Programmatically Pass Credentials in an Embedded Power BI Report
How to Programmatically Pass Credentials in an Embedded Power BI Report
Figure 1: Programmatically passing credentials in C# You can always confirm that the embedded SSRS report did indeed run under a passed credential (i.e. user test2) by checking the dbo.ExecutionLog3 view in SQL Server’s ReportServer database, as shown in Figure 2.
Figure 2: T-SQL query in ReportServer database
The only control you have with HTML iframes/object tags is setting the URL of the embedded Power BI Report Server report. You could try passing both username and password as part of the URL in the src (source) attribute of the iframes tag as underlined below:
There are several issues with this approach and the biggest one that comes to mind is that URLs with embedded credentials are a security threat as users with malicious intent can sniff out credentials out of the URL. Consequently, the practice of embedding credentials in a URL gets blocked by major internet browsers. Fortunately, not all internet browsers are blocking such requests, as shown in Figure 3, whilst browsers such as Microsoft Edge and Chrome will not render an iframe whose URL contains embedded credentials, Firefox continues to support such URL requests. However, it does mean that you will have to advice users of your web application to access it using internet browsers that support URLs with embedded credentials such as Firefox.
Figure 3 Switch to Power BI Service
Another option is to replace your on-prem Power BI Report Server environment with the cloud-based Power BI Service. As per this link, Microsoft has released several tutorials and source code that easily allows you to embed a cloud-based Power BI report within .Net applications. As shown in Figure 4, you can then use the Web.config file to pass credentials that will be used to connect and render a Power BI report. Furthermore, you can make use of Power BI gateways to ensure that your cloud-based Power BI reports are being fed by a dataset that is hosted on-prem (within your data center).
Figure 4 Again, there seem to be disadvantaged with this approach. For instance, if you have already invested in infrastructure and licensing of Power BI Report Server, you may not have any sufficient budget to further signup for the cloud version. As per the aforementioned link to existing Microsoft tutorials, the cloud-based solution requires not only a powerbi.com account but also an Azure AD tenant, which is usually not free. Lastly, even if cost and budgeting were not constraints for you, there are some organizations who are still reluctant to host any of their enterprise solutions (i.e. reporting, data) on the cloud.
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu Latest posts by Sifiso Ndlovu (see all) Dynamic column mapping in SSIS: SqlBulkCopy class vs Data Flow - February 14, 2020 Monitor batch statements of the Get Data feature in Power BI using SQL Server extended events - July 1, 2019 Bulk-Model Migration in SQL Server Master Data Services - May 30, 2019
SQLShack
SQL Server training EspañolHow to Programmatically Pass Credentials in an Embedded Power BI Report
November 20, 2018 by Sifiso Ndlovu In the article, How to embed a Power BI Report Server report into an ASP.Net web application, we looked at available options for embedding a Power BI Report Server report into an ASP.NET web application. Since the publication of the article, I have received several questions relating to how one goes about programmatically passing credentials for report server connection within an embedded Power BI Report Server report. The simple answer to such questions is that it is currently not possible to implement user impersonation in an embedded Power BI Report Server. However, like in most scenarios, there are workarounds that one could temporarily employ – at least until Microsoft comes up with a permanent solution to what is becoming a top requested feature at ideas.powerbi.com.A Case for Programmatically Passing Credentials
Sometimes there are instances whereby your web application needs to programmatically override credentials of the currently logged in user with those of another trusted account with elevated privileges. Say, for instance, you have a public web application (i.e. a gym website) that is accessed using anonymous authentication. This public web application has a section in its front page that displays Popular Classes during Weekdays. The Popular Classes during Weekday’s section is, in turn, an embedded SSRS or Power BI Report Server (PBIRS) report. Now, without successful authentication to the report server (SSRS or PBIRS), the Popular Classes during Weekday’s section will not be successfully rendered in the gym website. At the same time, it is not feasible that you grant report server access for every user accessing the public web application. One viable solution, however, would be to programmatically pass credentials in the background that will be used to handle all connections to the report server and thereby removing the need to prompt site visitors for report server credentials. Again, when evaluating what can and cannot be implemented in Power BI Report Server, it is always preferable that you compare it against SSRS. Thus, the rest of this article will focus on demonstrating options for programmatically passing credentials in an embedded SSRS report versus an embedded Power BI Report Server report.Programmatically Passing Credentials in Embedded SSRS Report
The ReportViewer control is very useful to successfully embed SSRS reports within web applications. Typically, whenever an ASP.NET embedded SSRS report is rendered within a ReportViewer control, credentials of the currently logged in user are used. However, the ReportViewer control further gives developers the ability to override credentials of the currently logged in user by either impersonating a windows identity or specifying a different network credential for connecting to an SSRS report server instance. This is made possible through a combination of creating a user-defined class (i.e. MyCustomReportCred) that implements the IReportServerCredentials interface as well as mapping the output of a method from that user-defined class to ReportViewer‘s ServerReport. ReportServerCredentials property, as illustrated in Figure 1 (the source code shown in Figure 1 is available under the Downloads section at the bottom of this article).Figure 1: Programmatically passing credentials in C# You can always confirm that the embedded SSRS report did indeed run under a passed credential (i.e. user test2) by checking the dbo.ExecutionLog3 view in SQL Server’s ReportServer database, as shown in Figure 2.
Figure 2: T-SQL query in ReportServer database
Programmatically Passing Credentials in Embedded Power BI Report Server Report
Perhaps the fact that the current version of ReportViewer control doesn’t support rendering of .pbix (Power BI) files, makes it very difficult to programmatically pass credentials to an embedded Power BI Report Server report as we are only left with using HTML iframes/object tags for embedding Power BI Report Server reports. Passing login credentials in the URLThe only control you have with HTML iframes/object tags is setting the URL of the embedded Power BI Report Server report. You could try passing both username and password as part of the URL in the src (source) attribute of the iframes tag as underlined below:
There are several issues with this approach and the biggest one that comes to mind is that URLs with embedded credentials are a security threat as users with malicious intent can sniff out credentials out of the URL. Consequently, the practice of embedding credentials in a URL gets blocked by major internet browsers. Fortunately, not all internet browsers are blocking such requests, as shown in Figure 3, whilst browsers such as Microsoft Edge and Chrome will not render an iframe whose URL contains embedded credentials, Firefox continues to support such URL requests. However, it does mean that you will have to advice users of your web application to access it using internet browsers that support URLs with embedded credentials such as Firefox.
Figure 3 Switch to Power BI Service
Another option is to replace your on-prem Power BI Report Server environment with the cloud-based Power BI Service. As per this link, Microsoft has released several tutorials and source code that easily allows you to embed a cloud-based Power BI report within .Net applications. As shown in Figure 4, you can then use the Web.config file to pass credentials that will be used to connect and render a Power BI report. Furthermore, you can make use of Power BI gateways to ensure that your cloud-based Power BI reports are being fed by a dataset that is hosted on-prem (within your data center).
Figure 4 Again, there seem to be disadvantaged with this approach. For instance, if you have already invested in infrastructure and licensing of Power BI Report Server, you may not have any sufficient budget to further signup for the cloud version. As per the aforementioned link to existing Microsoft tutorials, the cloud-based solution requires not only a powerbi.com account but also an Azure AD tenant, which is usually not free. Lastly, even if cost and budgeting were not constraints for you, there are some organizations who are still reluctant to host any of their enterprise solutions (i.e. reporting, data) on the cloud.
Summary
For a platform such as SQLShack.com, this type of article may be a level above the typical intended audience but I believe it is key that BI teams and architects alike are aware of some limitations in Power BI Report Server – with respect to user impersonation and passing credentials. I think for teams who are still considering rolling out Power BI, this article can be used to substantiate your decision to either go the on-prem or the cloud route for running Power BI environment. Downloads Embedded SSRS Report Solution File Author Recent Posts Sifiso NdlovuSifiso is Data Architect and Technical Lead at SELECT SIFISO – a technology consulting firm focusing on cloud migrations, data ingestion, DevOps, reporting and analytics. Sifiso has over 15 years of across private and public business sectors, helping businesses implement Microsoft, AWS and open-source technology solutions. He is the member of the Johannesburg SQL User Group and also hold a Master’s Degree in MCom IT Management from the University of Johannesburg.Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu Latest posts by Sifiso Ndlovu (see all) Dynamic column mapping in SSIS: SqlBulkCopy class vs Data Flow - February 14, 2020 Monitor batch statements of the Get Data feature in Power BI using SQL Server extended events - July 1, 2019 Bulk-Model Migration in SQL Server Master Data Services - May 30, 2019