4. Connecting to Databases: SQL Server, MySQL, DirectQuery and Live Connection
4.8 The On-premises Data Gateway
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
Gateway madhli sagalyat jast disnari chuk mhanje a server name that doesn't match exactly, udaharan mhanje 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. Chuk karu naka!