Connecting Power BI to on-prem data: install and run the On-Premises Data Gateway

Plenty of SMB data still lives on a server in the office: a SQL Server database, an accounting system, a file share. To refresh a Power BI report in the cloud from that on-premises data, you need the on-premises data gateway. This guide walks through installing the standard (enterprise) gateway, registering it to your tenant, binding a SQL Server source, choosing between scheduled refresh and DirectQuery, managing credentials, and troubleshooting the failures we see most often. It is aimed at the IT generalist or analyst setting this up for the first time.

Before you start

• A Windows machine that stays on, ideally a server (Windows Server 2019 or later, or Windows 10/11), that can reach both your data source and the internet. .NET Framework 4.8 or later must be installed.
• A Microsoft work or school account (an organization account in Microsoft Entra ID) with permission to register a gateway.
• The connection details for your data source: server name, database name, and a service account to read it.
• Admin rights on the gateway machine to run the installer.

1. Choose the right gateway

There are two flavors. The standard (sometimes called enterprise) on-premises data gateway supports multiple users, multiple data sources, and several Microsoft services (Power BI, Power Apps, Power Automate). The personal mode gateway is single-user and Power BI only. For anything a team relies on, install the standard gateway. The rest of this guide assumes the standard gateway.

2. Download and install

1. From the official Microsoft download page for the on-premises data gateway, download the installer to the gateway machine.
2. Run the installer. Accept the location (or change it), agree to the terms, and select Install.
3. When prompted, enter the email address of your Microsoft work or school account and select Sign in.

3. Register the gateway to your tenant

1. After sign-in, choose Register a new gateway on this computer.
2. Give the gateway a name that is unique across your tenant (for example, OfficeSQL-Gateway).
3. Enter a recovery key of at least eight characters and store it somewhere safe. You need this key to recover, restore, or move the gateway to another machine. There is no way to retrieve it later.
4. Select Configure. The gateway registers and shows a green, ready status.

4. Bind an on-prem SQL Server data source

1. Sign in to the Power BI (Fabric) service, select the Settings gear, and open Manage connections and gateways (under the On-premises data gateways area).
2. Find your gateway, then add a new connection (data source) under it.
3. Set the Connection type to SQL Server, enter the Server and Database names exactly as your report uses them, and choose an Authentication method (Windows or Basic).
4. Enter the credentials for the account that reads the database, and save. A green check means the gateway can reach the source.

5. Decide: scheduled refresh or DirectQuery

Both use the gateway, but they behave differently.

• Scheduled refresh (import) copies the data into the model on a schedule. Reports are fast because the data is cached, but they are only as current as the last refresh.
• DirectQuery leaves the data on the server and queries it live each time someone interacts with the report. The data is always current, but report performance depends on your server and network, and it puts continuous load on the source.

For most SMB reporting, scheduled refresh is the right default. Reach for DirectQuery only when the data must be real-time or is too large to import.

6. Hook your model up to the gateway

1. Publish your report to the workspace.
2. In the service, open the semantic model settings, expand Gateway and cloud connections, and bind the model's data source to the gateway connection you created.
3. Expand Scheduled refresh, turn it on, and set the frequency and times.

7. Manage credentials over time

The data source credentials live with the gateway connection, not in the report. When the service account's password changes, refreshes start failing until you update it.

• Update credentials in Manage connections and gateways, on the data source, under Edit credentials.
• Use a dedicated service account with a long-lived or non-expiring password for the gateway, so a routine password rotation does not silently break every report overnight.

8. Troubleshoot the common failures

• "Cannot find gateway" or no gateway listed: the gateway service is stopped, or the machine is offline. Open the gateway app on the machine and confirm it shows online; restart the On-premises data gateway service if needed.
• Refresh fails with a credentials error: the service account password changed or is wrong. Re-enter it under Edit credentials.
• "Cannot connect to the data source": the server or database name in the gateway connection does not match the name in the report exactly. They must be identical, including any instance name.
• Firewall or timeout errors: the gateway machine cannot reach the SQL Server, or the query is too slow. Confirm network access from the gateway machine and check for blocked ports.

Verify it works

In the service, open the semantic model and select Refresh now. Watch Refresh history for a successful run. Then change a row at the source, refresh again, and confirm the change appears in the report. A clean manual refresh means your scheduled refreshes will run too.

What to do next

With the gateway installed, registered, and bound to your source, your cloud reports can refresh from on-prem data without anyone exporting anything. From here, you can add more data sources to the same gateway and tune refresh schedules. Gateways are also a good candidate for high availability (a cluster of two) once reporting becomes business-critical. If you would rather not own the gateway plumbing, it is exactly the kind of thing we run and monitor for SMB clients. Let's set it up together.