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
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 चुनो. यह गलती मत करना!