Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
bheepatel
Resolver IV
Resolver IV

Cannot connect to SQL Server in Dataflow

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.
Capture.JPG

 


Any help would be much appreciated! 🙂

1 ACCEPTED SOLUTION
Mariusz
Community Champion
Community Champion

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

 

View solution in original post

4 REPLIES 4
v-yingjl
Community Support
Community Support

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
Community Champion
Community Champion

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

 

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
Community Champion
Community Champion

@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.


Did I answer your question? Mark my post as a solution!

Blog: nickyvv.com | @NickyvV


Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors