How to Add Data Source to Gateway in Power BI

Refreshing your on-premises data in the Power BI service feels like it should be simple, but it requires a crucial piece of infrastructure: the data gateway. This gateway acts as a secure bridge between your local data sources - like a SQL Server right in your office - and the Power BI Cloud. This article is your detailed, step-by-step guide to adding a data source to an existing Power BI gateway, unlocking automated refreshes and live connections for your reports.

Graphed

Still Building Reports Manually?

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

Watch the Demo

What Exactly is a Power BI Gateway?

Think of the Power BI gateway as a secure data pipeline. Power BI lives in the cloud, but many businesses have important data stored on-premises (on servers physically located within their organization). The gateway is a free software you install on a server within your network that brokers the conversation between the two, securely encrypting and transferring data requests and results.

Without a gateway, you’d be forced to manually republish your Power BI Desktop file every time you wanted to see updated data. The gateway automates this by enabling two key features:

• Scheduled Refresh: Automatically update the imported data in your Power BI reports on a set schedule (e.g., daily at 8 AM).
• DirectQuery/Live Connection: Run live queries directly against your on-premises data source every time a user interacts with a report, ensuring the data is always up-to-the-second current.

Standard Gateway vs. Personal Mode

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

• On-premises data gateway (standard mode): This is the recommended and most common type for businesses. It allows multiple users to connect to multiple on-premises data sources. It can be used by Power BI, Power Automate, Power Apps, and other services. This is the one we'll focus on.
• On-premises data gateway (personal mode): This version is for a single user, runs as an application instead of a service, and can only be used with Power BI. It's suitable for a solo analyst, but it’s less robust and can't be shared with a team.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Prerequisites: Before You Add a Data Source

Before jumping into the setup process, let’s ensure you have everything ready. This will save you from common roadblocks down the line.

• A Power BI Pro or Premium Account: You need a paid Power BI license to publish reports and manage gateways effectively.
• Gateway Installed and Running: This guide assumes you or your IT team have already installed the standard on-premises data gateway on a server. The gateway service must be active and running.
• Gateway Admin Rights: You must be an administrator of the specific gateway you want to configure. You can't add data sources to a gateway you don't manage.
• Data Source Credentials: You need the exact connection details and credentials for your data source. This typically includes the server name, database name, and a valid username and password with at least read permissions.
• Network Access: The computer hosting the gateway must be able to connect to the computer hosting the data source. Firewalls and other network security must be configured to allow communication between them.

Step-by-Step Guide: Adding a Data Source to Your Gateway

Once your prerequisites are in order, adding the data source itself is done entirely within the Power BI service (the browser version).

Step 1: Navigate to Gateway Settings

First, log into your Power BI account at app.powerbi.com. In the top-right corner of the screen, click the Settings gear icon, then select Manage connections and gateways from the dropdown menu.

Step 2: Select Your Target Gateway

This screen centralizes all your gateway management. Click on the On-premises data gateways tab. You will see a list of all gateway clusters for which you are an administrator. Click on the name of the gateway you want to add a data source to.

You’ll see a status indicator next to the gateway name. If it’s green and says “Online,” you’re good to go!

Step 3: Begin Creating a New Connection

At the top of the gateway dashboard, you’ll see some details about the gateway cluster. Directly below this, click the + New button to start the process of adding a new connection.

Graphed

Still Building Reports Manually?

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

Watch the Demo

Step 4: Configure a New Connection

This next form is where you provide all the information about your data source. Accuracy is critical here. Let’s use a SQL Server database as a common example.

• Connection Name: Create a clear, unique name for this connection. Be descriptive so your team knows what it is, like "Marketing SQL Database - Sales Data" instead of just "SQL Connection."
• Connection Type: From the dropdown, select the type of your data source. Power BI supports a huge list, from SQL Server and Oracle to file systems and web sources. Choose the one that matches your source.
• Server Name: Enter the name or IP address of the server where your data lives.
• Database Name: Enter the name of the specific database you are connecting to.

Choosing Your Authentication Method

This is arguably the most important and sometimes confusing part of the configuration. This tells Power BI how to "log in" to your database.

• Basic: The most straightforward method. You'll enter a specific username and password (for example, a dedicated SQL service account). This is very common for scheduled refreshes.
• Windows: Uses Windows authentication. Power BI will attempt to connect using the computer and user credentials of the Windows account running the gateway service on the server. This can be more complex to configure correctly due to domain permissions.
• OAuth2: Used for modern, cloud-based data sources that are being accessed through a gateway for specific reasons. Credentials are authenticated through a pop-up dialog.

After choosing your method, enter the required username and password in the fields provided. Double-check them for typos.

Step 5: Test and Finalize the Connection

With all the details filled in, now it's time to create the connection. Before you do, you need to set a Privacy Level. For most internal databases, selecting Organizational is the correct choice.

Now, click the Create button.

Power BI will now use the gateway to try and connect to your data source with the details you provided. If everything is successful, you'll see a green banner at the top saying "Connection successful." You've officially added the data source to your gateway!

If it fails, read the error message carefully. Common errors include:

• Incorrect credentials: Double-check the username and password.
• Network/Firewall issues: The gateway server cannot reach the database server.
• Driver errors: The server hosting the gateway might be missing a necessary data provider or driver (more common for non-Microsoft sources like Oracle).

Graphed

Still Building Reports Manually?

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

Watch the Demo

Connecting Your Report Dataset to the Gateway

You're not done yet! Adding a data source to a gateway just makes it available. Now you need to tell your published Power BI report to use that gateway connection.

1. Navigate to the Power BI workspace that contains the report you want to refresh.
2. Find the Dataset associated with your report (it often has the same name and an orange icon) and click the three-dot menu (...). Select Settings.
3. In the dataset settings, expand the Gateway and cloud connections section.
4. You'll see the data source listed from when you built the report in Power BI Desktop. Next to it will be a dropdown menu labeled Maps to.
5. Click the dropdown and select the data source connection you just created on the gateway.
6. Click Apply.

Congratulations! Your cloud-based dataset is now officially linked to your on-premises data source via the gateway. From this very same settings screen, you can now expand the Scheduled refresh section and toggle it on to set a daily or weekly refresh schedule.

Best Practices for Gateway Management

As you add more sources, keeping things organized is key to avoiding future headaches.

• Be Descriptive: Give your gateways and data source connections clear, unambiguous names. PROD-Finance-Gateway is much better than Gateway_1.
• Use Service Accounts: Never use your personal login for a connection. Work with your IT department to create dedicated service accounts with the minimum necessary permissions - just read access is often enough.
• Keep Your Server Updated: Install the gateway on a reliable, always-on server and make sure to update the gateway software whenever Microsoft releases a new version.
• Watch for Mismatches: Make sure the server and database names you used in your Power BI Desktop file exactly match what you enter in the gateway data source settings. Even a small difference can prevent the mapping from working.

Final Thoughts

Setting up the Power BI Gateway is an essential step for any organization that relies on on-premises data. By creating this secure link, you empower your team to work with fresh, relevant data through automated refreshes and live connections, turning your BI reports into reliable decision-making tools.

While mastering Power BI gateways is crucial for connecting to legacy on-prem systems, dealing with dozens of modern cloud-based marketing and sales apps creates its own data-wrangling headache. Toggling between Google Ads, Facebook, Salesforce, and Shopify just to get a simple performance report is a huge time-sink. That tedious, manual process is exactly why we built Graphed. We connect all your cloud data sources with one-click integrations, so you can stop wrestling with setups and start asking questions in plain English to build real-time dashboards in seconds.