Learn more about SQL Server tools

mssqltips logo

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories


Connect to on-premises data sources with Power BI Personal Gateway

By:   |   Read Comments   |   Related Tips: More > Power BI

Fix SQL Server Performance Issues - Free Webcast


How do I connect Power BI Online to on premise data sources?


Microsoft continues to rapidly develop and deploy tools within the Power BI application stack, of course there is the Power Query and Power Pivot tools in Excel. Furthermore, in a previous tip, Getting Started with Power BI Desktop, we described in detail the new Power BI Desktop (AKA PBID) that Microsoft has added to the Power BI tool set.

Currently Power BI Desktop connects easily to online datasources or content packs, but not to on premise datasources without the assistance of the Power BI Personal Gateway (I am a bit surprised that the team used the term Personal, as connections can be made to multiple sources that are not "so personal" in nature). Thus in order to connect to on premise (on-prem) data sources, you will need to utilize the Power BI Personal Gateway tool. The tool can be downloaded from: https://www.microsoft.com/en-us/download/details.aspx?id=47753.

Installing Power BI Personal Gateway

Once you download the Power BI Personal Gateway, you will need to install the tool on an on-prem machine. There are, of course certain restrictions on which machine you install the Gateway on.  These restrictions include:

  • Computer must be running a 64-bit Windows OS.
  • The Gateway is only available for those accounts using PowerBI Pro.
  • Computer cannot have the Power BI Analysis Services Connector installed too (the Power BI Analysis Services Connector is used to connect Power BI tools to Analysis Services databases / cubes).
  • The administrative privileges of the user installing and running the gateway controls how it runs in the background and how it handles data refreshes:
    • User has Administrative Privileges = Gateway runs as a service and user account does not need to be logged into machine as long as the service is running.
    • User has Non Administrative Privileges = Gateway runs as an application and user must be logged into the computer for scheduled refreshes.

The install process is rather easy and normal; just read the instructions and agreements and then click "Next.. Next.. Next........ Finish. Some of the install is shown below.




Configure Power BI Personal Gateway

The next step is to configure the connection to your Online Power BI service on the Azure platform. As shown below, our first step is to sign in to our Power BI Web Service. The sign in may be a several step process which redirects you to your organization's Azure login.



After connecting to the Power BI Service, we must specify the credentials under which the service will run.

gateway credentials

If all is successful, we will get a confirmation similar to the below screen print.


Furthermore, you will also see the Gateway showing up in the Status Bar.

Gateway On

Connecting to the Power BI Personal Gateway

Now we need to move to the Power BI website, http://powerbi.microsoft.com and create the connection to the Personal Gateway. You will of course need to login using the same PowerBI account/credentials you used during the Personal Gateway installation and setup. We will specifically be working with the Datasets section. For this example we will be using the Web Query Report dataset. This dataset (and the related Report) was uploaded from the Power BI Desktop application and queries data in the AdventureWorks database located on my laptop.


To complete the refresh connection, we will need to click on the Dataset, Web Query Report in our example, and click on the ellipse (3 dots) button to open up the dataset settings window. The settings window will open, and you will want to click on the Schedule Refresh option to complete the online setup.

set Refresh

If all the connections are setup correctly, then the Gateway Status will show as "online".

Gateway OK

However, you may run into a problem if your Gateway is not "turned on" as shown below.

Gateway not OK

Or you may be connected to the gateway, but a problem exists with the connection to the actual database. In the below example, I actually purposely stopped the service; the credentials error message is a misnomer error. However, this condition can easily be resolved by starting the service and then using the Edit Credentials link as shown in the below screen print to reset the connection.

Gateway connected local connection needed

If we truly need to adjust the credentials, for a SQL Server connection either Window or Basic (Standard SQL Server Login) credentials can be used. These screens can also be used if the credentials account and/or password change.

Configure Credentials

SQL Credentials

Finally, we can set the refresh schedule for the dataset. Please note a couple items about the schedule:

  • You need to "switch" the Keep your data up to date to "Yes". I found that option was not quite intuitive; just click the bar to set to yes--#1
  • The Refresh Option available will be based on your account type. Since I am using the basic edition, I can only update daily --#2
  • You need to add a specific time for the refresh to take place, otherwise the update will occur as 00:00--#3
  • You can add multiple update times--#4
  • Be sure to click Apply when finished setting the schedule--#6
set refresh schedule

Finally, you can add Cortana search capabilities and Featured or "default" Q & A questions.


We can always check the status by clicking the Refresh History link. The Type column will note whether the refresh was requested On Demand or Scheduled.

Refresh History

If you checked "Send refresh failure notification email to me", then upon failure, you will receive a notification similar to the below message.

Refresh Fail Mail

You can manually Refresh the dataset, by going to the dataset settings and then selecting Refresh Now.

Refresh Now

However, if a refresh is already occurring, a new refresh will not be allowed

Refresh in Progress

Finally, Microsoft has recently released an Enterprise Gateway in preview or beta mode. This gateway will have expanded capabilities for monitoring and access control; please be on the lookout for a future tip on the Enterprise version of the Gateway.


This tip covered how to use the Power BI Personal Gateway to query and retrieve data from on prem data sources. The Gateway tool allows you to connect to datasources that you connect to "locally" or on-prem. The Gateway needs to be installed on a 64 bit windows machine, but can run either as a service or as an application. In order to complete the "connections" between Power BI online and the local datasets, the Gateway requires adding credentials to connect to both your Power BI account and the local data source. Once these connections are completed locally within the Gateway, you must move to Power BI online to finish the setup; connections to the gateway must be confirmed and then a Refresh schedule can be configured.

Next Steps

Last Update:

next webcast button

next tip button

About the author
MSSQLTips author Scott Murray Scott Murray has a passion for crafting BI Solutions with SharePoint, SSAS, OLAP and SSRS.

View all my tips

Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 

Send me SQL tips:


Learn more about SQL Server tools