Forum Discussion

bheepatel's avatar
bheepatel
Resolver IV
6 years ago
Solved

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.

 


Any help would be much appreciated! 🙂

Need Help
  • Mariusz's avatar
    Mariusz
    6 years ago

    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

     

4 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    6 years ago

    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

     

    • nickyvv's avatar
      nickyvv
      Most Valuable Professional
      6 years ago
      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.
    • bheepatel's avatar
      bheepatel
      Resolver IV
      6 years ago

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

  • v-yingjl's avatar
    v-yingjl
    Community Support
    6 years ago

    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.