Cannot connect to SQL Server in Dataflow

[bheepatel]

Good day,

I am creating a Dataflow in which I have chosen a "Blank Query" connection. In this "Blank Query" I copied and pasted a query I had built in PBI Desktop. This query uses a SQL Server connection and it works fine in PBI Desktop.

However, when I try to load it in Service, it gives me the error below. I have ensured that the Microsoft/Azure IP address is whitelisted in our firewall but it still does not connect to the DB and instead asks me to choose a gateway. I have a gateway but I cannot use as it would defeat the purpose of automating the refresh.

Any help would be much appreciated! 🙂

[v-yingjl]

Hi @bheepatel ,

In power bi service, if you want to connect to datasource on premiss, you should configure a gateway actually first. If just use the instance. you can use the connector as @Mariusz  previously said.

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[Mariusz · Accepted solution]

Hi @bheepatel 

If you are connecting to SQL server on-prem then you will need to install enterprise data gateway on this server in order for the service to be able to access, if you are using Azure SQL database or azure data warehouse instances then just use the relevant connectors and no gateway is required

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[bheepatel]

Thanks @Mariusz - I was misinformed that the server was on Azure. It was indeed an on-prem server and works fine with the gateway.

[nickyvv]

@Mariusz is right, using dataflows is not a workaround of not using the EDG. For on-premises sources you still need to use the gateway.