Dawn has attended:
Power BI Modelling, Visualisation and Publishing course
Power BI Gateway
Hi there, Currently I am trying to create a report for colleagues which uses a Direct query data connection to SQL Server database held in Azure cloud - we need these reports to run from live data.
My understanding of how this works is
1. On Power BI Desktop create a report using Direct Query as the data source so we are picking up current data at all times
2. Once you have created your report and added filters etc then save it
3. Import this report to a Power BI Service Workspace
4. Check it is working fine
5. Invite appropriate users to link to the report so that they are able to run this when required
First 2 steps are fine, but after the 3rd step Import I am getting message saying there is no Gateway to access the data source
I believe once this Gateway is in place it will need connecting up to the Power BI Service?
When looking at the settings for “Connections and Gateways”
The Connections Tab, Connections has Database name, SQL Server as connection type, but then when I check the status it is offline
Whilst the other Tabs – On-premises data gateways, Virtual Network data gateways and Azure Key Vault references are all empty.
Is my understanding of how the process works correct (steps 1 – 5) – you can import a direct query report to the PBI Service and then the user will get the live data as at when they run the report?
Do you have any advice on which type of connection/gateway would be best for our requirements. It will involve getting other departments involved and I would like to be passing the correct information to the correct area.
Any clarification/examples you have would be appreciated.
Thank you
Dawn
RE: Power BI Gateway
Hi Dawn,
Thank you for the forum question.
You're on the right track with your understanding of how DirectQuery works in Power BI, and your steps 1–5 are mostly correct. However, the issue you're encountering with the gateway is a common one when connecting Power BI Service to a cloud-hosted SQL Server database, especially when using DirectQuery.
Let me break this down and clarify everything for you:
Your Understanding: Steps 1–5
Create report in Power BI Desktop using DirectQuery – Correct.
Add filters, visuals, save the report – Correct.
Publish to Power BI Service – Correct.
Check it works in the Service – !!!! This is where the gateway issue arises.
Share with users – Correct, but only works once the gateway is properly configured.
Why You're Seeing the Gateway Error
Power BI Service needs a way to connect to your Azure SQL Server database to fetch live data. Even though the database is in Azure, Power BI still needs a data gateway if:
The SQL Server is not publicly accessible (e.g., behind a firewall or private VNet).
You're using on-premises SQL Server or SQL Server in a VM in Azure.
You're not using Azure SQL Database or Azure Synapse Analytics, which can be accessed directly without a gateway if configured correctly.
Types of Gateways
There are two main types of gateways:
On-premises Data Gateway
Used when your SQL Server is hosted on-premises or in a VM in Azure.
Installed on a machine that has access to the SQL Server.
Required for DirectQuery, scheduled refresh, and live connections.
Virtual Network (VNet) Data Gateway
Used when your data source is in a secured Azure Virtual Network.
Managed by Azure, no need to install anything manually.
Ideal for enterprise-scale and cloud-native architectures.
What You Should Do
Here’s how to proceed:
Step 1: Identify Your SQL Server Type
Is it Azure SQL Database (PaaS)?
Or SQL Server in a VM (IaaS)?
Or on-premises SQL Server?
Step 2: Choose the Right Gateway
Azure SQL Database (PaaS): No gateway needed if firewall and authentication are configured correctly.
SQL Server in VM or on-prem: Use On-premises Data Gateway or VNet Gateway.
Step 3: Install and Configure Gateway
Download and install the On-premises Data Gateway on a machine that can access the SQL Server.
Sign in with the same account used in Power BI Service.
In Power BI Service, go to Manage Gateways and add your data source under the gateway.
Step 4: Map the Dataset to the Gateway
In Power BI Service, go to Dataset settings.
Under Gateway connection, map your dataset to the configured gateway and credentials.
Example Scenario
If your SQL Server is in an Azure VM:
Install the On-premises Data Gateway on that VM or another machine that can access it.
Configure the gateway in Power BI Service.
Use Windows or SQL authentication to connect.
If your SQL Server is Azure SQL Database:
Ensure the firewall allows Azure services.
Use Azure Active Directory or SQL authentication.
No gateway is needed if configured correctly.
Resources
Install and configure on-premises data gateway
Use VNet data gateway
Would you like help determining which type of SQL Server you're using or how to configure the gateway step-by-step?
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
