Power BI for Office 365 has a component known as Data Management Gateway which allows us to expose the on premise data sources to cloud. On premise boxes which host the data sources do not require a public IP but you servers still require an Internet connection. Currently Data Management Gateway supports SQL Server, Oracle and Power Query sources. This article guides you how to setup the Power BI Data Management Gateway and expose a SQL Server database to cloud. First Go to Power BI Admin Center from your Office 365 subscription.

In the Power BI Admin Center click gateways and then click new gateway

Enter a name for you gateway and an optional description. If you want to store the credentials of the data sources you want to link to Office 365 in the cloud you can check the “Enable cloud credential store to archive business continuity for gateway” option. By checking this you agree to store the data source credentials in the cloud in a secure way. Storing the credentials in the cloud gives you the ability to restore the gateway without entering credentials. Most of the cases this would be an organizational decision based on the trust. This link explains the advantages of enabling cloud credential store. 

Once you click Create you will be directed to the second step. Here you will get the link to download the Data Management Gateway and the key to build the trust between the gateway and Office 365.

Copy the key (later you can change this). Download the Data Management Gateway and install in your on premise machine. Data Management Gateway is a Windows Service. Once the installation is done, you will get the Microsoft Data Management Gateway Configuration Manager in your machine. Open it and paste your gateway key and register the gateway.

In the settings you can configure the protocol and the port for the communication between your Data Management Gateway service and Office 365.  As of now we have completed setting up the gateway to communicate to the Office 365 environment. Below diagrams explains how the communication happens between your on premise machine and Office 365.

Click on the Settings tab in the Microsoft Data Management Gateway Configuration Manager in order to configure protocol and the port of the communication.

Next step is to create data sources, go to data sources tab in the Power BI admin center and setup a data source. Here we’ll setup a SQL Server data source.

Then we have to select how to expose the data source, only as a cloud source or as well as a OData feed. Enabling the data sources as OData feed provides the opportunity to use this data source in Power Query. Cloud Access makes the data source available in SharePoint online and refreshing is possible.

In the next wizard enter the connection details. Select the Gateway, data source type and the appropriate provider. In this case my on premise database is SQL Server 2012 so I’ve used SQL Server Native Client 11.0. Then enter the server name and database name in order to construct the connection string. If you select the connection string option (rounded in blue) you need not to provide these details one by one, you can simple enter your connection string.

After setting up the server and database details along with the provider information, you can see that the credentials button is enabled. Click on the credentials this will launch a one click application. In that application enter your database credentials either Windows Authentication or SQL Authentication.

Test the connection and if the connection is successful click OK. This saves the credentials in the local machine specific to the application. The credentials are not transferred to the Office 365 since we didn’t select the Enable Cloud Credential Store. Now the data source setup is complete. Go to the data settings tab in the data sources and select the desired tables and views to be exposed from the Office 365.

That’s it, we have connected our on premise data source to Office 365. Go to data source section and click the properties shortcut (triple dots) in the context menu you can see the OData Feed URL for your on premise data base.

It’s amazingly simple to expose your on premise data sources across the organization and even to extranet through Power BI for Office 365. You can get the OData URL and start accessing your data source from Excel Power Query.

 

Paste the URL (note that OData feeds are exposed via HTTPS) and select the Organizational Account to enter your Office 365 credentials.

 

Then you can access the data exposed through the OData feed in Excel. The below image shows the data exposed in this sample.

How does this entire process work ?