4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection
4.8 On-premises Data Gateway in Power BI
Start with a problem
Ravindra published a report that reads the office SQL Server. In the Service, refresh fails: the cloud cannot reach the office network.
Explain simply
An on-premises data gateway is a small program on an always-on machine inside the network. The Service sends refresh requests through it to on-premises sources.
Show the data
| Source | Location |
|---|---|
| SQL Server QCSERVER01 | Office network |
| Targets.xlsx | SharePoint Online |
| orders CSV | Shared drive \fileserver\qc |
| Google Sheet of targets | Cloud |
| Azure SQL Database | Cloud (firewall allows Power BI) |
About this data: This table uses columns: Source, Location. This table uses columns: Connection. This table uses columns: Source, Gateway needed?. Values shown are the lesson examples already on this page (not a new dataset).
Fictional sample data. Practice file: quickcommerce_practice.sql (source: src/practice/).
Demonstrate
- Install the gateway (standard mode) on an always-on machine; sign in; register it as
QC-Pune-GWand store the recovery key safely. - For MySQL or PostgreSQL sources, install the same drivers on this machine.
-
Service › Settings › Manage connections and gateways › + New › gateway QC-Pune-GW › SQL Server › server
QCSERVER01, databaseQuickCommerceDB› credentials.Your result should look like this:
Connection Online -
Semantic model Settings › Gateway and cloud connections › map the source › Apply.
- Set Scheduled refresh (for example 07:00 IST).
Show the result
Your result should look like this (fictional sample):
| Source | Gateway needed? |
|---|---|
| SQL Server on the office network | Yes |
| Targets.xlsx in SharePoint Online | No |
| CSV on a shared drive | Yes |
| Google Sheet | No |
| Azure SQL Database (firewall allows Power BI) | Usually no |
Explain what happened
Only sources the cloud cannot reach directly need the gateway. 2 of the 5 sources here are on-premises (SQL Server and the shared drive).
Explore further
- Personal mode is for one user and Import only; use standard mode for teams.
Detailed reference for this lesson:
The Power BI Service is in the cloud. It cannot reach a SQL Server inside the office network, or a file on someone's laptop, without a gateway. The gateway is a small program installed on a machine that stays on. It securely passes refresh and DirectQuery requests.
| Source | Gateway needed for refresh? |
|---|---|
| SQL Server / MySQL on the office network | Yes |
| Excel/CSV on a local drive or network share | Yes |
| Files in SharePoint Online / OneDrive for work | No |
| Azure SQL Database, most cloud services | Usually no |
| Google Sheets, public web pages | No (cloud connector) |
Steps
- Download the on-premises data gateway (standard mode) from Microsoft and install it on an always-on machine (not a laptop that goes home).
- Sign in with your work account and register a new gateway (give it a name like QC-Pune-GW and a recovery key. Keep the key safe).
- For MySQL, install MySQL Connector/NET on this machine as well.
- In the Power BI Service: Settings (gear icon) › Manage connections and gateways › + New › choose the gateway › Connection type SQL Server › enter the server, database and credentials › Create.
- Open your published semantic model's Settings › Gateway and cloud connections › map each data source to the connection › Apply.
- Set up Scheduled refresh in the same settings page (Module 25).
Personal mode gateway: for one user only, supports Import refresh only (no DirectQuery), and is not for team use. Prefer standard mode.
Ravindra Bagale's Tip
The mistake seen most often with gateways is a server name that doesn't match exactly, for example QCSERVER01 in Desktop but qcserver01.corp.local in the gateway connection. The Service then can't map the source and refresh fails. Also keep the gateway machine switched on and updated, and prefer standard mode over personal mode for team reports. Don't make this mistake!
Ravindra Bagale's Tip – मराठी
Gateway मध्ये सगळ्यात जास्त दिसणारी चूक म्हणजे server name अगदी जुळत नसणं, उदाहरणार्थ Desktop मध्ये QCSERVER01 पण gateway connection मध्ये qcserver01.corp.local. मग Service source map करू शकत नाही आणि refresh fail होतो. तसंच gateway machine चालू आणि updated ठेवा, आणि team reports साठी personal mode ऐवजी standard mode वापरा. ही चूक करू नका!
Ravindra Bagale's Tip – हिंदी
Gateway में सबसे ज़्यादा दिखने वाली गलती है server name का बिल्कुल match न होना, जैसे Desktop में QCSERVER01 पर gateway connection में qcserver01.corp.local. फिर Service source को map नहीं कर पाती और refresh fail हो जाता है. साथ ही gateway machine चालू और updated रखो, और team reports के लिए personal mode की जगह standard mode चुनो. यह गलती मत करना!
Fix common mistakes
| Wrong result | Reason | Correction |
|---|---|---|
| Data source not found on gateway | Server name differs (QCSERVER01 vs qcserver01.corp.local) | Use exactly the same server and database names |
| Refresh stops at night | Gateway on a laptop that goes home | Install on an always-on machine |
| MySQL refresh fails only in the Service | Driver missing on the gateway | Install the driver there |
Practise independently
Practice task
Add two sources: a PostgreSQL server in the office and a public web page. Which need the gateway?
Hints
- Office network vs internet.
Solution
PostgreSQL in the office: yes (plus the Npgsql driver on the gateway). Public web page: no.
Think like a professional
List every source with its location before publishing (for example SQL Server on the office LAN, a file share, and an on-prem Oracle box); the gateway plan follows from that source list.
More worked examples: on-premises data gateway
Suppose we are at Infosys. A client database runs on a SQL Server inside the office network. Power BI Service lives in the cloud and cannot reach inside that network by itself. The on-premises data gateway is a small program installed on an office computer. It carries the data safely between the office database and Power BI Service.
Example 1 (simplest): do I need a gateway?
Check three cases.
Steps:
- Report reads an Excel file on OneDrive or SharePoint: no gateway needed.
- Report reads an Azure SQL database: no gateway needed.
- Report reads SQL Server on an office computer and must refresh in Power BI Service: gateway needed.
Output: You install a gateway only when the data source is inside your own network.
Example 2: install and register the gateway
Use a computer that stays switched on and can reach the SQL Server.
Steps:
- Download the On-premises data gateway (standard mode) from Power BI Service: Settings, then Download, then Data gateway.
- Run the installer and sign in with your work account.
- Choose Register a new gateway, give it a name like
office-gatewayand set a recovery key.
Output: The gateway shows as online in Power BI Service under Manage connections and gateways.
Example 3: add the SQL Server connection and schedule refresh
The report is already published to Power BI Service.
Steps:
- In Power BI Service open Manage connections and gateways and click New.
- Choose the gateway, type SQL Server and the database name, and enter the login.
- Open the semantic model settings, map it to this connection, and turn on Scheduled refresh at 7:00 AM.
Output: Every morning the report refreshes with the latest office data.
See also: DirectQuery in Power BI · Connect MySQL to Power BI · Connect SQL Server to Power BI
Frequently asked questions
What is an on-premises data gateway?
It is a program installed on a computer inside your network. It lets Power BI Service read data from databases or files in that network.
Can Power BI connect to SQL Server without a gateway?
Power BI Desktop can, because it runs inside your network. For scheduled refresh in Power BI Service, an on-premises SQL Server needs a gateway.
Where should I install the gateway?
On a computer that is always on and can reach the data source. Do not install it on a laptop that goes to sleep.
Revision card
Interview card
Comparison card