How to Configure On-Premises Gateway in Power BI

Connecting Power BI to your cloud-based data is simple, but what about the valuable information stored on your local servers? To bring data from sources like an on-premises SQL Server or a local file share into your Power BI reports, you'll need to set up a special bridge. This tutorial guides you through every step of installing and configuring the Power BI on-premises data gateway to securely connect your local data to the cloud.

What is an On-Premises Data Gateway?

Think of the Power BI on-premises data gateway as a secure doorman standing between your internal, on-location data sources and the Microsoft Power BI service in the cloud. It's a small piece of software you install on a computer within your network that manages data requests, handles authentication, and encrypts traffic. Without it, the Power BI service has no way to see or access data that isn't publicly available on the internet.

The gateway authenticates requests from the cloud, forwards them to your local data source (like a SQL server or an analysis services cube), and then sends the results back to Power BI. This allows you to create dashboards and reports that refresh automatically, even when the underlying data lives on a server in your office.

Standard Mode vs. Personal Mode

Power BI offers two types of gateways, and choosing the right one is important:

• Standard Mode: This is the recommended gateway for most business situations. It’s designed for multiple users and can be shared across various reports and services like Power BI, Power Apps, and Azure Logic Apps. You manage it centrally and can create clusters for high availability, ensuring there isn't a single point of failure. If you're working on a team or building reports for your organization, this is the one you should use.
• Personal Mode: This gateway is intended for a single user (typically a data analyst) to connect to sources without needing an administrator. It runs as an application under your account and can only be used with Power BI. You can’t share it with other users, and if your machine is turned off, the refresh fails. It’s best for individual practice or one-off projects, not for business-critical reporting.

For this tutorial, we will focus on configuring the Standard Mode gateway, as it's the most common and robust option for business intelligence.

Before You Start: Prerequisites and Requirements

Before you jump into the installation, make sure the machine you plan to use as your gateway server meets the minimum requirements. Installing the gateway on a machine that’s already overworked or underpowered can lead to slow refresh times and errors.

System Requirements:

• Operating System: Windows 8, Windows 10, Windows 11, Windows Server 2012 R2, Windows Server 2016, Windows Server 2019, or Windows Server 2022.
• .NET Framework: Version 4.7.2 or later (The gateway installer includes it if it’s missing).
• CPU: At least 4 cores.
• Memory (RAM): At least 8 GB.
• Storage: Adequate free disk space for the installation and spooling (caching compressed data). Spooling can use significant disk space, depending on your data volume. A solid-state drive (SSD) is highly recommended for the spooling directory to improve performance.

Important Considerations:

• Use a dedicated machine. Avoid installing the gateway on a computer that you use daily, like your personal laptop. The gateway machine must be on and connected to the internet 24/7 for scheduled refreshes to work reliably. A virtual machine is an excellent option.
• Don't install it on a domain controller. This is not supported and can cause security and performance issues.
• Place it close to your data. For the best performance, install the gateway on a server with a low-latency connection to your data source. The less network hopping, the better.

Step 1: Download and Install the On-Premises Gateway

With an appropriate server ready, the first step is to get the installer from the Power BI service.

1. Log in to your Power BI account at https://app.powerbi.com.
2. In the top-right corner, click the Download icon (a downward-pointing arrow) and select Data Gateway.
3. On the download page, click the Download standard mode button. This will download the latest version of the installer file ("GatewayInstall.exe").
4. Once downloaded, move the installer to your designated gateway server and run it by right-clicking and selecting Run as administrator.
5. Accept the license terms and privacy statement. You can choose the installation path, but the default is usually fine. Click Install.
6. The installer will download and install the necessary components. This might take a few minutes.

Step 2: Configure the Gateway

After the installation finishes, the configuration wizard will launch automatically. Here's how to get your new gateway registered and connected to your Power BI account.

1. Sign In: You’ll first be prompted for an email address. Enter the email address associated with your organizational Power BI account and click "Sign in." You will be taken through your company’s standard login process.

2. Register Your Gateway: Since this is a new installation, select the option to "Register a new gateway on this computer" and click "Next."

3. Name Your Gateway and Create a Recovery Key: This is the most critical part of the configuration process. You need to provide two pieces of information:

• New on-premises data gateway name: Give your gateway a descriptive name so you can easily identify it later. For example, "Marketing SQL Server Gateway" or "Finance Department Gateway."
• Recovery Key: Create a strong, unique password and save it somewhere extremely secure, like a password manager. This key is absolutely essential. You will need it to restore, migrate, or take over ownership of the gateway. If you lose this key, you cannot recover it. You would have to create a new gateway and reconfigure all data sources that depend on it.

Confirm the recovery key and click "Configure."

4. Get Confirmation: The wizard will take a moment to configure the service and register it with the Power BI cloud service. Once complete, you’ll see a success screen confirming that "The gateway [Your Gateway Name] is online and ready to be used."

Your gateway is now officially installed and running as a Windows service on your server. It's connected to your Power BI tenant, waiting for you to tell it which data sources to manage.

Step 3: Add a Data Source to the Gateway

With the gateway running, the next step is to teach it how to connect to one of your on-premises data sources. For this example, let’s assume we are connecting to a SQL Server database.

1. Back in your browser, go to the Power BI service (https://app.powerbi.com).
2. Click the Settings gear icon in the top-right corner and select Manage connections and gateways.
3. You are now in your central gateway management screen. You'll see a tab for On-premises data gateways. The gateway you just created should appear in the list with a green status indicator.
4. To add a data source, click on the gateway name. Then, in the top menu bar, click the New button. This opens the "New connection" form.
5. Here, you will configure the connection details:
6. Once all fields are complete, click Create.
7. Power BI will test the connection. If everything is correct, you'll see a green "Connection successful" message. Your on-premises data source is now available for use in the Power BI Service.

Step 4: Using the Gateway for Scheduled Refresh

The real power of the gateway is enabling automatic data refreshes. Here’s how to link an existing Power BI report dataset to your newly configured data source.

1. Publish a Power BI Desktop report that uses data from your on-premises SQL Server.
2. In the Power BI service, navigate to the workspace where you published the report.
3. Find your new dataset in the list (it will likely have the same name as your PBIX file). Click the three dots (...) next to its name and select Settings.
4. Expand the Gateway and cloud connections section. You will notice Power BI has identified that your dataset requires a gateway but doesn’t know which one to use.
5. In the "Maps to" dropdown, you should now see the data source connection you just created ("Sales_DW_Production" in our example). Select it and click Apply.
6. Now that the data source is successfully mapped, expand the Scheduled refresh section.
7. Toggle the "Keep your data up to date" switch to On.
8. Configure your desired refresh frequency (Daily or Weekly), select your time zone, and add the times you want the refresh to occur. For example, you might set it to refresh every day at 7:00 AM so the latest data is ready for the team's morning meetings.

That's it! Your report will now automatically refresh on the schedule you set, pulling fresh data from your on-premises server via the gateway.

Final Thoughts

Configuring an on-premises data gateway is a fundamental skill for any Power BI developer working with internal data. By following these steps, you create a secure and reliable channel that unlocks the full potential of scheduled refreshes, keeping your dashboards current and your business decisions informed by the very latest information.

Setting up gateways and managing data pipelines is often where the real friction in data analysis lies. Manual setups, server maintenance, and complex configurations can slow things down, especially when your data is spread across different platforms. At https://www.graphed.com/register, we’ve focused on simplifying this process. By connecting directly to cloud data sources like Google Analytics, Shopify, and Salesforce with just a few clicks, we help you get straight to the insights. You can use simple, natural language to build dashboards in seconds, bypassing the technical setup so you can spend your time analyzing data, not just preparing it.