What is Gateway Connection in Power BI?

If you've built a Power BI report using data from a file on your computer or a server inside your company's network, you'll eventually face a critical question: how do I keep the data fresh once I publish it online? The built-in schedulers in the Power BI service can't see behind your company firewall, and this is where Power BI Gateway plays an important role. A gateway is an essential piece of infrastructure that acts as a secure bridge, connecting your on-premises data sources to the cloud-based Power BI service.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

This article will walk you through what a Power BI gateway is, how it works, and the step-by-step process of installing and configuring one so you can automatically refresh your reports.

What Exactly is a Power BI Gateway?

Think of the Power BI service as an island on the internet. It can easily connect to other cloud-based "islands" like Google Analytics, Salesforce, or Azure SQL databases because they all speak the language of the web. Your company's data, however, often lives on a separate "mainland" behind a secure firewall - on local desktops, file servers, or internal databases like SQL Server.

A Power BI Gateway is a secure-data chauffeur. It's a small piece of software you install on a computer within your private network. This gateway's only job is to receive query requests from the Power BI service, fetch the requested data from your local source, encrypt it, and securely deliver it back to Power BI in the cloud to update your reports.

Crucially, the gateway does not move your entire dataset to the cloud, it only fetches the requested data, encrypts it, and sends that portion of data to the service. The gateway listens for requests and securely facilitates data transfer for refreshes or DirectQuery connections, ensuring your sensitive on-premises data remains safely behind your firewall.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How Does the Gateway Connection Work?

The process of refreshing data through a gateway might sound complicated, but it's a well-orchestrated and secure sequence of events happening behind the scenes. Let's break down the journey of a single data query.

1. A Request is Made in the Cloud: It all starts when a user opens a DirectQuery report or a scheduled refresh kicks off in the Power BI service. Power BI creates a query, encrypts the credentials for your on-premise data source, and sends this package to a cloud service called the Azure Service Bus.
2. The Gateway Listens: The on-premises data gateway, which you've installed inside your network, is constantly polling the Azure Service Bus to see if there are any pending requests for it.
3. The Request is Received: When the gateway sees a request addressed to it, it accepts it. It then decrypts the data source credentials that were included in the package.
4. Connecting to the Data Source: Using the decrypted credentials, the gateway connects directly to your on-premises data source, just like Power BI Desktop would. This could be a SQL server, an Oracle database, or even a local Excel file directory.
5. The Query is Executed: The gateway passes the query from Power BI to the data source, which runs the query and gathers the results.
6. The Secure Return Trip: The data source returns the results to the gateway. The gateway then encrypts this data and sends it back to the Azure Service Bus.
7. Your Report is Updated: The Power BI service receives the encrypted data, decrypts it, and uses it to update the visualizations in your reports and dashboards.

This entire workflow ensures that your database credentials and your data are encrypted from end to end, providing a robust security layer for your business intelligence operations.

Understanding the Different Gateway Modes

When you download the gateway installer, you'll be prompted to choose between two different modes. Choosing the right one depends entirely on your specific needs and organizational structure.

1. On-premises Data Gateway (Standard Mode)

This is the standard and most recommended option for almost all business use cases. Think of Standard Mode as a centralized hub for your entire organization.

• Shared Access: It can be shared and used by multiple people across your organization. Administrators can grant specific users permission to use the gateway to create connections for their reports.
• Broad Service Support: It supports more than just Power BI. The same standard gateway can be used for Power Apps, Power Automate, Azure Logic Apps, and Azure Analysis Services, making it a versatile piece of data infrastructure.
• Ideal Use Case: Perfect for departmental or enterprise-level deployments. You install it on a dedicated, always-on server, and it acts as an official, managed pipeline for all your company's on-premises data refresh needs.

2. On-premises Data Gateway (Personal Mode)

As the name implies, personal mode is tailored for a single individual without the ability to share connections.

• For a Single User: It runs as an application on your personal computer under your Windows account. Only you can use it, and you can't share it with colleagues.
• Power BI Only: This mode only works with Power BI and no other Microsoft Power Platform services.
• Ideal Use Case: This is best suited for individual analysts or citizen developers who need to connect to a personal data source for a proof-of-concept project. Because it usually runs on a user's laptop, it's not reliable for scheduled business-critical refreshes, as it only works when the user is logged in and the laptop is on.

For any serious BI work involving scheduled refreshes, Standard Mode is always the right choice.

When Do You Actually Need a Gateway?

Knowing when to use a gateway is just as important as knowing how to set one up. You can spare yourself the setup work if your data is already in the cloud.

You WILL need a gateway if your data source is:

• Located on a database server inside your company's private network (e.g., SQL Server, Oracle, SharePoint Server).
• A file type like Excel, CSV, or XML located on your personal computer or a mapped network drive.
• Any other data source that sits behind your company firewall and is not publicly accessible via the internet.

You WILL NOT need a gateway if your data source is:

• A cloud-based service, such as Azure SQL Database, SharePoint Online, Salesforce, or Google Analytics. Power BI can connect to these sources directly over the internet.
• A file stored in a personal or business version of OneDrive or SharePoint Online. Power BI has a native ability to sync with files in these locations.

How to Install and Configure Your Power BI Gateway (Standard Mode)

Setting up your gateway is a straightforward process. Here’s a guided walkthrough of the essential parts.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Download and Install the Gateway

First, log in to your Power BI service account online. In the top-right corner, click the "..." menu, and select Download > Data Gateway. Run the installer file that downloads. The setup wizard is simple – just accept the terms and click "Install".

Step 2: Sign In and Register

Once installed, you'll need to sign into the same Power BI account you use for publishing reports. You will then be prompted to either register a new gateway or migrate an existing one. Select "Register a new gateway on this computer" and click Next.

Step 3: Name Your Gateway and Create a Recovery Key

Give your gateway a descriptive name that you and your colleagues will recognize, for example, "Finance Department - SQL Gateway". The next step is a password. In the unlikely case, you install Power BI Gateway again, this key allows you to have the old gateway's properties back.

After your Gateway Name, add and confirm your Recovery key. Save this recovery key in a secure place like a password manager. You will not be able to see it again, so be careful and save it. The best key should meet the minimum length (8 characters) and complexity (Upper-Case, Lower-Case, Number, Special Character).

Step 4: Verify the Installation

The final confirmation screen will show you that your gateway is online and ready for use. Now, you need to go back to the Power BI service online to configure it.

Step 5: Add a Data Source in the Power BI Service

In the Power BI service, click the settings option, then choose "Manage connections and gateways." Under this tab, you will see the name of your created Gateway. Check the checkbox, and next to it, choose to add data sources to use this gateway connection.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 6: Configure and Test the Connection

You’ll now define the specifics of your connection. Select the correct Data Source Type (e.g., SQL Server, File) and fill in the details like the Server Name and Database Name.

Under the Authentication section, enter the username and password that has permission to access that data source then click "Create".

Once clicked, the Power BI service will use the gateway to attempt a connection to your data source. You want to see the 'Connection Successful' notification.

Step 7: Map the Gateway in Your Dataset Settings

The final step is to tell your published dataset to use this new gateway connection. Navigate to your workspace, find the dataset you want to refresh, click the "..." menu, and go to "Settings". Expand the "Gateway connections" header, then under "Maps to", click the radio button to use your new gateway. Finally, press "Apply".

Your on-premises data is now fully connected to the cloud, and you can configure a scheduled refresh to keep your report data effortlessly up-to-date.

Final Thoughts

The Power BI Data Gateway is the unsung hero of many business intelligence setups, working silently to bridge the gap between secure on-premises data and the dynamic, cloud-based Power BI Service. By following the steps to install and configure it, you unlock the ability to keep your dashboards and reports automatically updated, delivering fresh insights without manual intervention. Your Power BI Gateway needs a connection with different settings to different data sources, and you need to provide login credentials for each of them.

Instead of struggling with managing multiple gateway settings, consider trying a more innovative approach. With our new product, "Graphene," you can write code in English and generate reports in seconds with live data to your BI system, eliminating the need for a Power BI Gateway. All you need is a tool like Graphed. With just a few clicks, you have everything you need. No complexity, no learning curve - just get your work done effortlessly.