This blog post is about connecting O365-based Power BI service to On Premise installed Dynamics CRM.
The goal of this project was to develop Power BI reports which combine data from on premise Dynamics CRM and other on premise data sources (SQL). The reasons why Power BI was selected as reporting platform:
- Power BI is a modern tool which has built-in capabilities to fetch data from several data sources and implement presentations of that data using easy to use development environment
- Power BI implementations are easy and quick to publish for example in O365-based Sharepoint intranet
- The organization for this project had licensing ready for Power BI so if some other BI platform would have been deployed, it would have required licensing investment
- Power BI has easy to configure solution to fetch data from On Premise data sources that are behind firewall to the cloud and it does not require customer’s IT to drill holes into the firewall. This technical architecture is the subject of this blog post.
As we are dealing with Dynamics CRM installed on premise, we cannot utilize the Dynamics CRM 365 connector in Power BI available for Dynamics CRM Online instances. Instead we utilize the Power BI On Premise Data Gateway to connect to the Dynamics CRM database and other SQL data sources needed in this project.
There are a few separate components in the Power BI On Premise Data Gateway. The following simplified diagram shows the components and the data flow between them.
Here is a list of steps explained that happens when the data flows from SQL Server to Power BI service:
- Power BI report (or other component) makes a query to the configured data source
- This query is processed by the gateway component on the cloud service side
- The query is pushed into a queue on the Azure service bus
- Application gateway on the customer side behind the firewall polls for queries stored in the Azure service bus. Once it discovers one in the queue, it starts processing the query and the actual query is executed on the data source. In our scenario, it is the SQL Server DB of the Dynamics CRM application.
- The query results are sent back to the application gateway and further to the cloud service
The magic that makes the data flow possible without opening any incoming ports on the corporate firewall is the architecture in which the application gateway polls for queries stored in the Azure service bus. And the Azure service bus does not require any other ports open than 443 (TCP) on the firewall. Naturally inside the corporate network, the application gateway needs to be able to access the data source itself. It is recommended to install the application gateway as close to the data source as possible to make the performance optimal but it is not necessary to install the gateway on the same server than the data source if not possible.
Authentication and authorization
The application gateway within the corporate network runs as a Windows service. By default, there is a local user account used to run the Windows service. However, this is not the credential which is used to connect to the data source and execute the data query. Rather the connection is opened and the data query is executed using the stored credential in the cloud side. This credential can be of type Basic (SQL authentication) or Windows authentication. Whichever credential type is used, the credentials are sent as encrypted from the cloud to the gateway running on the corporate network side.
In the cloud service side, it is configurable that which users are allowed to administer the On Premise Gateway and which users are allowed to use the data source.
The Power BI On Premise Data Gateway hides much of the complexities in terms of setting up the connectivity between the cloud service and on premise data stores. For most of the basic configuration stuff, there is a configuration UI available. In our scenario, we had the connectivity up and running from cloud service to on premise SQL Server within ten minutes or so. Furthermore the data connectivity through the secure “tunnel” made by Azure service bus seems to work fine so the data within the Power BI reports stay up-to-date as required for this project’s purposes.
More posts on our blog